Download Snowflake Data Warehouse Features and Capabilities and more Exams Nursing in PDF only on Docsity!
SNOWFLAKE CERTIFICATION EXAM Questions
With 100% Complete Solutions Graded A+ Latest
Updates 2024
True or False: Data Storage is independent from compute? - Answer True True or False: All virtual warehouses have access to all data? - Answer True True or False: Al interactions with data are initialized through the services layer? - Answer True True or False: Multi-Cluster Warehouses support high concurrency? - Answer True True or False: Warehouses can be dynamically expanded to adjust to workloads? - Answer True Which layer provides centralized services for the data warehouse? - Answer Cloud Services Which layer contains virtual warehouses for data access? - Answer Query Processing Which layer contains the data in compressed, columnar format? - Answer Database Storage True or False: Snowflake's security and authentication includes Multi-Factor authentication? - Answer True True or False: Snowflake's security and authentication includes Snowflake Failures alerts? - Answer False True or False: Snowflake's security and authentication includes data encryption using Snowflake-managed keys? - Answer False True or False: Snowflake's security and authentication includes object-level access?
- Answer True True or False: Snowflake's architecture includes advance capabilities in the cloud services layer that delivers transaction management for consistent operations on the same data at the same time? - Answer True True or False: Snowflake's architecture includes advance capabilities in the cloud services layer that delivers metadata service? - Answer True True or False: Snowflake's architecture includes advance capabilities in the cloud services layer that delivers security and authentication control? - Answer True
True or False: Snowflake's architecture includes advance capabilities in the cloud services layer that delivers query optimization? - Answer True True or False: Snowflake supports landing data into internal stage on the cloud storage platform? - Answer True True or False: Snowflake supports landing data into external stage on the cloud storage platform? - Answer True True or False: Snowflake supports landing data into bring your own device? - Answer False True or False: Compute resources used by Snowflake for data loading jobs can by provide by user managed virtual warehouse? - Answer True True or False: Compute resources used by Snowflake for data loading jobs can by provide by Snowflake managed service? - Answer True True or False: Compute resources used by Snowflake for data loading jobs can by provide by hardware provisioned by user directly from cloud providers? - Answer False True or False: Stages are unique database objects in Snowflake? - Answer True True or False: Pipe are unique database objects in Snowflake? - Answer True True or False: Table are unique database objects in Snowflake? - Answer False Which approach would result in improved performance through linear scaling of data ingestion workload: A) Resize virtual warehouse B) Consider the practice of organizing data by granular path C) Consider the practice of splitting input file batch within the recommended range of 10MB and 100MB? D) All of the above - Answer D) All of the above True or False: Snowflake Support Services addresses customer issues covering troubleshooting failed queries? - Answer True True or False: Snowflake Support Services addresses customer issues covering product usage questions? - Answer True True or False: Snowflake Support Services addresses customer issues covering individual query syntax improvement? - Answer True True or False: Snowflake Support Services addresses customer issues covering 3rd party application configuration support? - Answer False
A defined data set is a point in time snapshot and can be updated by customers is? - Answer Snowflake's Cloning A defined data set can be shared with customers outside the Snowflake account is?
- Answer Snowflakes's Data Sharing Query statement encryption is supported on ___________ accounts? A) Standard B) Enterprise C) Enterprise for Sensitive (ESD) D) Virtual Private Snowflake (VPS) - Answer C) Enterprise for Sensitive (ESD) True or False: Caching techniques are supported by Snowflake's performance optimizing query methods? - Answer True True or False: B-tree type indexes are supported by Snowflake's performance optimizing query methods? - Answer False True or False: Retrieving results of previous query from cache is supported by Snowflake's performance optimizing query methods? - Answer True True or False: Snowflake security supports user-based access control? - Answer True True or False: Federated authentication in Snowflake is complaint with SAML 2.0? - Answer True True or False: Tri-secret requires that customers manage their own keys? - Answer True All security information is stored in the ___________ layer in the Snowflake architecture? A) Storage B) Compute C) Service D) All of the above - Answer C) Service True or False: One benefit of client-side encryption is that it provides a secure system for managing data in cloud storage? - Answer True True or False: One benefit of client-side encryption is that the data is encrypted before loading into storage layer? - Answer False True or False: One benefit of client-side encryption is the storage service layer only contains encrypted version of the data? - Answer True True or False: One benefit of client-side encryption is that queries can be encrypted on the client side? - Answer False
True or False: MFA (Multi-factor Authentication) is automatically enabled for your account and available for all users to self-enroll? - Answer True True or False: MFA (Multi-factor Authentication) is an integrated feature powered by Duo Security service? - Answer True True or False: MFA (Multi-factor Authentication) can be used for connecting to Snowflake via the Snowflake JDBC driver? - Answer True True or False: MFA (Multi-factor Authentication) login is desired only for connecting to Snowflake through the web interface? - Answer False Snowflake includes Role-Based Access Control to enable administrators to: A) Limit access to data and privileges B) Manage secure access to the Snowflake account and data C) Establish role hierarchy and privilege inheritance to align access D) All of the above - Answer D) All of the above With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with ODBC Driver? - Answer True With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with Python Connector? - Answer True With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with JDBC Driver? - Answer True With an IdP (identity provider) configured for your account, Snowflake supports using SSO to connect and authenticate with SnowSQL? - Answer Choosing separate accounts in Snowflake enables users to have: A) Different editions of Snowflake and different regions B) Billing at the account level C) Simpler database object deployment between environments D) All of the above - Answer D) All of the above True or False: Different editions of Snowflake instances require separate accounts? - Answer True True or False: Snowflake instances in different regions require separate accounts? - Answer True True or False: Data can be shared READ ONLY across accounts, and can be cloned accounts? - Answer False An enterprise view of data is useful because: A) A data set can be stored once and shared multiple times
C) Maximized D) Auto-Scale F) None of the above - Answer C & D To help control the usage of credits in Auto-scale mode, Snowflake provides a property, _________________, that determines the scaling policy to use when automatically starting or shutting down additional clusters. Select the property: A) Auto_Scale B) Scaling_policy C) Maximum_number_of_server_clusters D) Minimum_number of server clusters E) None of the above - Answer B) Scaling_Policy Standard Scaling Policy - Answer Definition: Prevents/minimizes queuing by favoring starting additional clusters over conserving credits. Cluster Starts.... Immediately when either a query is queued or the system detects that there's one more query than the currently-running clusters can execute. Cluster Shuts down.... After 2 to 3 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loaded cluster could be redistributed to the other clusters without spinning up the cluster again. Economy Scaling Policy - Answer Definition: Conserves credits by favoring keeping running clusters fully-loaded rather than starting additional clusters, which may result in queries being queued and taking longer to complete. (default setting) Cluster Starts.... Only if the system estimates there's enough query load to keep the cluster busy for at least 6 minutes. Cluster Shuts down.... After 5 to 6 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loaded cluster could be redistributed to the other clusters without spinning up the cluster again. Semi-structured data strings are stored in a column with a Snowflake data type of __________________? A) Object B) Character C) Varchar D) Variant E) None of the above - Answer D) Variant Not A) Object - key pair values like Hadoop
When sizing a Snowflake warehouse, which of the following factors should not be considered: A) Number of users B) Number of concurrent queries C) Number of tables being queried D) Data size and composition E) All of the above - Answer A) Number of Users - if users are not querying the database, then they don't take any resources Snowflake utilizes per _______________ billing. A) Millisecond B) Second C) Minute D) Hour E) None of the above - Answer B) Second True or False: Each server in a cluster has a position. Servers are always removed from the warehouse in reverse order of when they were added (aka LIFO, "Last In, First Out") - Answer True True or False: The size of the cache is determined by the number of servers in the all of warehouses for an account - Answer False - size of the cache is determined by the number of servers in a warehouse. Snowflake has three types of caching to optimize performance. Select the three types of caches from the list: A) Server B) Warehouse C) Client D) Results E) Security F) Metadata G) Database H) User - Answer B, D, and F This type of cache lives on the Compute instance? A) Server B) Results C) Metadata D) Warehouse - Answer D) Warehouse cache This type of cache lives on the Cloud Services layer? A) Server B) Results
A) AWS
B) Google C) Microsoft's Azure G) All of the above - Answer A & C - Google is not support, but is coming in the future True or False: Snowflake automatically partitions the data so that the user does not need to define partition scheme? - Answer True Which of the following is not a characteristic of "automatic micro-partitions" in Snowflake? A) New partitions are created on logical properties B) Avoids skews between partitions C) Partitioning is automatically completed on the natural ingestion order of the data D) The natural ingestion order maintains correlations between columns which could be useful for pruning - Answer A) New partitions are created on logical properties Snowflake uses physical properties. The partitions can overlap in ranges. True or False: Snowflake optimizes the storage for semi-structured data based on the repeating elements within the semi-structured strings? - Answer True True or False: Snowflake store structure and semi-structure data in different proprietary file formats? - Answer False - they use the same proprietary file format Snowflake has two key features in their storage architecture. They are (select two): A) Time Travel B) Time Machine C) Time Warp D) Snapshot Cloning E) Zero-Copy Cloning F) Replication - Answer A & E Select the best description of Zero-Copy Cloning: A) Metadata-only operation B) No replication of data C) Unmodified data stored once; modified data stored as new blocks D) All of the above - Answer D) All of the above Which is not a characteristic of Time Travel? A) Protection from accidental data operations B) Recover data with the cost of running backups C) Previous versions of data automatically retained D) Retention period selected by customers (up to 90 days for Enterprise edition) - Answer B) Recover data with the cost of running backups
There is no cost in Snowflake for running backups, purchasing additional hardware, incurring downtime and overhead of additional administration True or False: When defining columns to contain dates or timestamps, Snowflake recommend choosing a date or timestamp data type rather than a character data type? - Answer False - Snowflake stores DATE and TIMESTAMP data more efficiently than VARCHAR, resulting in better query performance. True or False: Referential integrity constraints in Snowflake are enforced? - Answer False - Referential integrity constraints in Snowflake are informational are not enforced. True or False: Since integrity constraints are not enforced, the developers should not the creation of primary and foreign keys. - Answer False - The primary keys and foreign keys enable members of your project team to orient themselves to the schema design and familiarize themselves with how the tables relate with one another. And most business intelligence (BI) and visualization tools import the foreign key definitions with the tables and build the proper join conditions. This approach saves you time and is potentially less prone to error than someone later having to guess how to join the tables and then manually configuring the tool. True or False: Reclustering a small table typically doesn't improve query performance significantly? - Answer True When might you might consider specifying a clustering key and reclustering the table? A) When a small is get out of sequence with the clustering key B) The order in which the data is loaded does not match the dimension by which it is most commonly queried C) To reorder a table to match another clustering key in another table that the users perform frequent joins D) All of the above E) None of the above - Answer B) The order in which the data is loaded does not match the dimension by which it is most commonly queried True or False: Zero-Copy cloning allow a customer to provision real, Production data for development and test environments without physically copying the data? - Answer True True or False: Snowflake storage cost are based only on a daily average of all database compressed data storage? - Answer False - Database data and data stored for Time Travel retention and failsafe. True or False: A Snowflake Role is the only thing that connects Storage (Database) to Compute (Virtual Warehouse)? - Answer True True or False: Snowflake provides embedded multi-factor authentication across some of its editions? - Answer False - multi-factor authentication is embedded in all editions
E) None of the above - Answer A) One minute After the one minute - accounts are billed by the second True or False: Virtual Warehouse in Snowflake is where the data is stored? - Answer False - Virtual Warehouses is the Compute operation Snowflake supports data in VARIANTs up to a maximum size of: A) 4 MB uncompressed B) 8 MB uncompressed C) 16 MB compressed D) 32 MB compressed E) None of the above - Answer C) 16 MB compressed Non-native values such as dates and timestamps are stored as strings when loaded into a VARIANT column so which statements are true: A) Operations on these values could be slower B) These columns would consume more space than when stored in a relational column with the corresponding data type C) Both A and B are true D) Neither A or B are true - Answer C) Both A and B are true The Snowflake UI is divided into for basic areas. Which of the following areas is not part of the UI: A) Query B) Databases C) Warehouse D) History - Answer A) Query Worksheet is the name of the query section True or False: The PUT and GET commands can be executed via the Snowflake UI?
- Answer False - These commands can only be executed using SNOWSQL client. They are not supported by ODBC driver. The commands for loading data into Snowflake are: A) COPY & PUT B) COPY & INSERT C) INSERT & PUT D) None of the above - Answer B) COPY & INSERT True or False: COPY statement allows insert on SELECT against a staged file, and a WHERE clause can be used? - Answer False - INSERT command allows the Where clause, not the COPY command True or False: The COPY command is more performant than the INSERT statement? - Answer Trie
To load data into Snowflake, what needs to be in place (check all that apply)? A) Virtual Warehouse B) Predefined target table C) Staging location with data staged D) File Format - Answer A, B, C, and D True or False: Snowpipe is a continuous data ingestion service that detects and loads streaming data? - Answer True Which of the following is not a feature of Snowpipe: A) The service can load data from any internal or external stage B) Snowpipe has a server-less compute model C) The service provides REST endpoints and uses Snowflake provided compute resources to load the data and retrieve history reports D) Snowpipe loads data after it is stage and the user executes the LOADDATA command - Answer D) Snowpipe loads data after it is stage and the user executes the LOADDATA command No LOADDATA command - and the data is automatically loaded within minutes after the files are added to the stage Name all of the file/data types that Snowflake support for data loading? - Answer Text Delimited (CSV, TAB, etc.) JSON XML Avro, Parquet ORC Which layer does Snowflake store the various statistics for databases, tables, columns, and files? A) Storage B) Compute C) Cloud Services D) Snowflake does not store statistics - Answer C) Cloud Services - the metadata is stored in this layer True or False: Can a query run against Result Cache with no Virtual Warehouse running and retrieve results (assuming the query is cached)? - Answer True ______________ is used to analyze the execution details of a query? A) Statistics B) Metadata C) Query Plan D) Query Profile - Answer D) Query Profile
C) Setting all of the load files to the optimal size of 10 to 100 MB in compressed size D) The number of data files that are processed in parallel is determined by the number and capacity of servers in a warehouse E) All of the above - Answer E) All of the above True or False: users control the file split and size of data being load and the how the data is divided into micro-partitions? - Answer False - Snowflake determines how the data is divided into micro-partitions. Users cannot create or configure these partitions. True or False: Micro-partitions are immutable? - Answer True - subsequent changes of any type to the data will be written to additional micro-partitions A Snowflake mechanism that is used to limit the number of micro-partitions scanned by a query is called ________? A) Governor B) Regulator C) Pruning D) None of the above - Answer C) Pruning Database designer should consider using Clustering Keys when (check all that apply): A) The tables is very large (multi TB) B) Table is large enough to reside on many micro-partitions C) Columns in the table can provide sufficient filtering to select a subset of these micro-partitions D) All of the above - Answer D) All of the above What are they general indicators that can help determine whether to define a clustering key for a very large table: A) Queries on the table are running slower than expected or have noticeably degraded over time B) The clustering ratio for the table is very low and the clustering depth is very large - Answer True or False: Snowflake's metadata repository stores references to all of the micro- partitions files for each table, as well as tracking of all versions of the table data within the data retention window? - Answer True True or False: Both a user defined Clustering Key and a Natural Clustering Key can be establish for table? - Answer False - Snowflake data is divided into and stored in micro-partitions. Only one clustering key can be used for that process. True or False: For most tables, it is a best practice to allow Snowflake's automated micro-partitioning process to fully manage the table's micro-partitions? - Answer True
The process of co-locates column data with same values in the same micro-partition, if possible, is called ___________? A) Re-Clustering B) Natural Clustering C) Partitioning D) None of the above - Answer B) Natural Clustering The number of Snowflake credits are consumed is determined by (check all that apply) A) Size of the warehouse B) Number of Users for that warehouse C) How long (in secs) the warehouse runs D) All of the above - Answer A & C True or False: Setting the Auto-Suspend value to "Never" will produce efficient credit consumption? - Answer False - the warehouse will never go into suspend mode (i.e. it will always be running and consuming credits) True or False: User can view and modify Resource Monitors? - Answer True - But the ACCOUNTADMIN has to enable the user first (by granting permissions) Snowflake's Resource Monitors have the following capabilities (check all that apply): A) impose limits on the number of credits that a warehouse can consume B) Trigger actions (i.e. like suspending) when the limit is reached C) Can only be created by an ACCOUNTADMIN D) All of the above - Answer D) All of the above True or False: The key to sizing a warehouse is the experiment with different types of queries and different warehouse sizes to determine the best combinations that best meet your specific query requirements and workload. - Answer True Identify the best practices for ACCOUNTADMIN role and permissions (select all that apply): A) This role should be given to any user that need a high level of authority B) There should be at least two user granted this role C) All ACCOUNTADMIN users should have multi-factor authentication enabled D) Objects should not be created using this role E) All of the above - Answer B, C, & D True or False: It is a best practice to have the majority of objects owned by SYSADMIN? - Answer True True or False: There is a technical difference between an object access role and a business function role in Snowflake. - Answer False - There is no difference. The difference is in how they are used logically to assemble and assign sets of privileges to groups of users.
D) Do not make ACCOUNTADMIN the default role for any users in the system - Answer D) Do not make ACCOUNTADMIN the default role for any users in the system True or False: What is data skew is when partitions in database become disproportionately-sized? - Answer True True or False: The closer the ratio of scanned micro-partitions and columnar data is to the ratio of actual data selected, the more efficient is the pruning performed on the table? - Answer True True or False: Some DDL operations are metadata only operations? - Answer True - deleting all rows in a table is a metadata only What are the steps Snowflake pruning? A) Eliminate cluster key entries from any query filters B) Eliminate micro-partitions that are not needed for the query C) Eliminate columns by sorting within the micro-partition D) Eliminate by column within the remaining micro-partitions E) All of the above - Answer B & D - and they are performed in that order True or False: The clustering depth for a table is an absolute or precise measure of whether the table is well-clustered. - Answer False - Ultimately, query performance is the best indicator of how well-clustered a table is. True or False: A table that consists of a single micro-partition or no micro-partitions (i.e. an empty table) always has a clustering depth of 0? - Answer True True or False: As a general rule, Snowflake recommends ordering the keys from lowest cardinality to highest cardinality for a clustering key. - Answer True True or False: If you want to use a column with very high cardinality as a clustering key, we recommend defining the key as an expression on the column, rather than on the column directly, to reduce the number of distinct values. - Answer True - instead of c_timestamp, use to_date(c_timestamp) True or False: To recluster a table, an admin would execute the RECLUSTER command? - Answer False - Reclustering is done automatically. Manual reclustering has been deprecated. Which services are multi-tenant? A) Global Services B) Virtual Warehouse - Compute C) Storage D) All of the above E) None of the above - Answer A & C - Compute is customer based
True or False: Snowflake deploys into a customer VPC or VNET? - Answer False - Snowflake only deploys within it own VPC. True or False: An account level Resource Monitor can override a Resource Monitor assignment for individual warehouse? - Answer False - It cannot override the Resource Monitor that is assigned to individual warehouse. Resource Monitor is a 1st class object has elements defined to its class. Select all that apply? A) Credit terms B) Credit Quota C) Triggers D) Credit Allocation E) Credit Usage F) Credit Remaining G) All of the above - Answer B, C, and E True or False: A suspend trigger on a resource monitor cancels all in-flight transactions and bring down the warehouse once the quota is reached? - Answer False: Suspend Immediately cancels all transactions and brings down the warehouse (i.e Kill - 9). Whereas Suspend, lets all in-flight transactions complete normally. If the Credit Quota of a Resource Monitor is reached, suspended warehouses can not be resumed until one of the conditions is met (select all that apply)? A) A new billing cycle starts B) Credit quota of the monitor is increased C) The credit threshold for the trigger is increased D) The monitor is no longer assigned to the warehouse E) The monitor is dropped F) All of the above - Answer F) All of the above True or False: When a Resource Monitor has multiple warehouses assigned to it, the credit quota is allocated evenly across the warehouses? - Answer False - credit usage of one warehouse can impact other warehouses. True or False: Resource Monitor notification are automatically assigned to ACCOUNTADMIN role? - Answer False - Resource Monitor notifications are disabled by default. True or False: ACCOUNTADMINs are the only users that receive resource monitor notifications? - Answer True Which command can be grant to roles outside of the ACCOUNTADMIN role to accessing resource monitors? A) MODIFY B) ALTER
