The fail-safe retention period is how many days?
- A . 1 day
- B . 7 days
- C . 45 days
- D . 90 days
B
Explanation:
Reference: https://docs.snowflake.com/en/user-guide/data-failsafe.html
True or False: A 4X-Large Warehouse may, at times, take longer to provision than a X-Small Warehouse.
- A . True
- B . False
A
Explanation:
You can experiment the same with snowflake UI.
How would you determine the size of the virtual warehouse used for a task?
- A . Root task may be executed concurrently (i.e. multiple instances), it is recommended to leave some margins in the execution window to avoid missing instances of execution
- B . Querying (select) the size of the stream content would help determine the warehouse size. For example, if querying large stream content, use a larger warehouse size
- C . If using the stored procedure to execute multiple SQL statements, it’s best to test run the stored procedure separately to size the compute resource first
- D . Since task infrastructure is based on running the task body on schedule, it’s recommended to configure the virtual warehouse for automatic concurrency handling using Multi-cluster warehouse (MCW) to match the task schedule
The Information Schema and Account Usage Share provide storage information for which of the following objects? (Choose three.)
- A . Users
- B . Tables
- C . Databases
- D . Internal Stages
What is the default File Format used in the COPY command if one is not specified?
- A . CSV
- B . JSON
- C . Parquet
- D . XML
A
Explanation:
Reference: https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html
True or False: Reader Accounts are able to extract data from shared data objects for use outside of Snowflake.
- A . True
- B . False
True or False: Loading data into Snowflake requires that source data files be no larger than 16MB.
- A . True
- B . False
B
Explanation:
By default, COPY INTO location statements separate table data into a set of output files to take advantage of parallel operations. The maximum size for each file is set using the MAX_FILE_SIZE copy option. The default value is 16777216 (16 MB) but can be increased to accommodate larger files. The maximum file size supported is 5 GB for Amazon S3, Google Cloud Storage, or Microsoft Azure stages. To unload data to a single output file (at the potential cost of decreased performance), specify the SINGLE = true copy option in your statement. You can optionally specify a name for the file in the path.
True or False: A Virtual Warehouse can be resized while suspended.
- A . True
- B . False
A
Explanation:
Reference: https://docs.snowflake.com/en/user-guide/warehouses-tasks.html#effects-of-resizing-a-suspended-warehouse
True or False: When you create a custom role, it is a best practice to immediately grant that role to ACCOUNTADMIN.
- A . True
- B . False
B
Explanation:
Reference: https://docs.snowflake.com/en/user-guide/security-access-control-considerations.html
What are two ways to create and manage Data Shares in Snowflake? (Choose two.)
- A . Via the Snowflake Web Interface (Ul)
- B . Via the data_share=true parameter
- C . Via SQL commands
- D . Via Virtual Warehouses
AC
Explanation:
Reference: https://docs.snowflake.com/en/user-guide/data-sharing-provider.html
True or False: Fail-safe can be disabled within a Snowflake account.
- A . True
- B . False
B
Explanation:
Reference: https://docs.snowflake.com/en/user-guide/data-failsafe.html
Separate and distinct from Time Travel, Fail-safe ensures historical data is protected in the event of a system failure or other catastrophic event, e.g. a hardware failure or security breach. Fail safe feature cannot be enabled or disabled from the user end .
True or False: It is possible for a user to run a query against the query result cache without requiring an active Warehouse.
- A . True
- B . False
A
Explanation:
Query result cache is all about fetching the data from cloud services layer and saving the cost by not running the virtual warehouse.
A virtual warehouse’s auto-suspend and auto-resume settings apply to which of the following?
- A . The primary cluster in the virtual warehouse
- B . The entire virtual warehouse
- C . The database in which the virtual warehouse resides
- D . The Queries currently being run on the virtual warehouse
B
Explanation:
https://docs.snowflake.com/en/user-guide/warehouses-overview.html#:~:text=Similarly%2C%20auto%2Dresume%20ensures%20that,individual%20clusters %20in%20the%20warehouse.
Which of the following Snowflake features provide continuous data protection automatically? (Select TWO).
- A . Internal stages
- B . Incremental backups
- C . Time Travel
- D . Zero-copy clones
- E . Fail-safe
CE
Explanation:
Time travel and fail safe are the two continuous data protection features support the recovery of data automatically.
Snowflake provides powerful CDP features for ensuring the maintenance and availability of your historical data (i.e. data that has been changed or deleted):
Querying, cloning, and restoring historical data in tables, schemas, and databases for up to 90 days through Snowflake Time Travel.
Disaster recovery of historical data (by Snowflake) through Snowflake Fail-safe.
https://docs.snowflake.com/en/user-guide/data-availability.html
Which of the following conditions must be met in order to return results from the results cache? (Select TWO).
- A . The user has the appropriate privileges on the objects associated with the query
- B . Micro-partitions have been reclustered since the query was last run
- C . The new query is run using the same virtual warehouse as the previous query
- D . The query includes a User Defined Function (UDF)
- E . The query has been run within 24 hours of the previously-run query
Which of the following are benefits of micro-partitioning? (Select TWO)
- A . Micro-partitions cannot overlap in their range of values
- B . Micro-partitions are immutable objects that support the use of Time Travel.
- C . Micro-partitions can reduce the amount of I/O from object storage to virtual warehouses
- D . Rows are automatically stored in sorted order within micro-partitions
- E . Micro-partitions can be defined on a schema-by-schema basis
What is the minimum Snowflake edition required to create a materialized view?
- A . Standard Edition
- B . Enterprise Edition
- C . Business Critical Edition
- D . Virtual Private Snowflake Edition
B
Explanation:
Materialized views require Enterprise Edition. To inquire about upgrading, please contact Snowflake Support
https://docs.snowflake.com/en/sql-reference/sql/create-materialized-view.html#:~:text=Materialized%20views%20require%20Enterprise%20Edition,upgrading%2C%20please%20contact%20Snowflake%20Support.
What happens to the underlying table data when a CLUSTER BY clause is added to a Snowflake table?
- A . Data is hashed by the cluster key to facilitate fast searches for common data values
- B . Larger micro-partitions are created for common data values to reduce the number of partitions that must be scanned
- C . Smaller micro-partitions are created for common data values to allow for more parallelism
- D . Data may be colocated by the cluster key within the micro-partitions to improve pruning performance
Which feature is only available in the Enterprise or higher editions of Snowflake?
- A . Column-level security
- B . SOC 2 type II certification
- C . Multi-factor Authentication (MFA)
- D . Object-level access control
A
Explanation:
https://docs.snowflake.com/en/user-guide/intro-editions.html
Which of the following are valid methods for authenticating users for access into Snowflake? (Select THREE)
- A . SCIM
- B . Federated authentication
- C . TLS 1.2
- D . Key-pair authentication
- E . OAuth
- F . OCSP authentication
During periods of warehouse contention which parameter controls the maximum length of time a warehouse will hold a query for processing?
- A . STATEMENT_TIMEOUT__IN__SECONDS
- B . STATEMENT_QUEUED_TIMEOUT_IN_SECONDS
- C . MAX_CONCURRENCY__LEVEL
- D . QUERY_TIMEOUT_IN_SECONDS
B
Explanation:
The parameter STATEMENT_QUEUED_TIMEOUT_IN_SECONDS sets the limit for a query to wait in the queue in order to get its chance of running on the warehouse. The query will quit after reaching this limit. By default, the value of this parameter is 0 which mean the queries will wait indefinitely in the waiting queue
https://community.snowflake.com/s/article/Warehouse-Concurrency-and-Statement-Timeout-Parameters#:~:text=The%20parameter%20STATEMENT_QUEUED_TIMEOUT_IN_SECONDS%20sets%20the,indefinitely%20in%20the%20waiting%20queue.
Which of the following indicates that it may be appropriate to use a clustering key for a table? (Select TWO).
- A . The table contains a column that has very low cardinality
- B . DML statements that are being issued against the table are blocked
- C . The table has a small number of micro-partitions
- D . Queries on the table are running slower than expected
- E . The clustering depth for the table is large
Which Snowflake object enables loading data from files as soon as they are available in a cloud storage location?
- A . Pipe
- B . External stage
- C . Task
- D . Stream
A
Explanation:
Snowpipe enables loading data from files as soon as they’re available in a stage. This means you can load data from files in micro-batches, making it available to users within minutes, rather than manually executing COPY statements on a schedule to load larger batches. https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro.html
A user needs to create a materialized view in the schema MYDB.MYSCHEMA.
Which statements will provide this access?
- A . GRANT ROLE MYROLE TO USER USER1;
CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO ROLE MYROLE; - B . GRANT ROLE MYROLE TO USER USER1;
CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO USER USER1; - C . GRANT ROLE MYROLE TO USER USER1;
CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO USER1; - D . GRANT ROLE MYROLE TO USER USER1;
CREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO MYROLE;
What is the default character set used when loading CSV files into Snowflake?
- A . UTF-8
- B . UTF-16
- C . ISO S859-1
- D . ANSI_X3.A
A
Explanation:
https://docs.snowflake.com/en/user-guide/intro-summary-loading.html#:~:text=For%20delimited%20files%20(CSV%2C%20TSV,encoding%20to%20use%20for% 20loading.
For delimited files (CSV, TSV, etc.), the default character set is UTF-8. To use any other characters sets, you must explicitly specify the encoding to use for loading. For the list of supported character sets, see Supported Character Sets for Delimited Files (in this topic).
A sales table FCT_SALES has 100 million records.
The following Query was executed
SELECT COUNT (1) FROM FCT__SALES;
How did Snowflake fulfill this query?
- A . Query against the result set cache
- B . Query against a virtual warehouse cache
- C . Query against the most-recently created micro-partition
- D . Query against the metadata excite
Which cache type is used to cache data output from SQL queries?
- A . Metadata cache
- B . Result cache
- C . Remote cache
- D . Local file cache
B
Explanation:
https://community.snowflake.com/s/article/Caching-in-Snowflake-Data-Warehouse
What is a key feature of Snowflake architecture?
- A . Zero-copy cloning creates a mirror copy of a database that updates with the original
- B . Software updates are automatically applied on a quarterly basis
- C . Snowflake eliminates resource contention with its virtual warehouse implementation
- D . Multi-cluster warehouses allow users to run a query that spans across multiple clusters
- E . Snowflake automatically sorts DATE columns during ingest for fast retrieval by date
What is a limitation of a Materialized View?
- A . A Materialized View cannot support any aggregate functions
- B . A Materialized View can only reference up to two tables
- C . A Materialized View cannot be joined with other tables
- D . A Materialized View cannot be defined with a JOIN
D
Explanation:
There are several limitations to using materialized views:
A materialized view can query only a single table.
Joins, including self-joins, are not supported.
What features does Snowflake Time Travel enable?
- A . Querying data-related objects that were created within the past 365 days
- B . Restoring data-related objects that have been deleted within the past 90 days
- C . Conducting point-in-time analysis for Bl reporting
- D . Analyzing data usage/manipulation over all periods of time
B
Explanation:
Snowflake Time Travel enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. It serves as a powerful tool for performing the following tasks: Restoring data-related objects (tables, schemas, and databases) that might have been accidentally or intentionally deleted.
Duplicating and backing up data from key points in the past. Analyzing data usage/manipulation over specified periods of time. https://docs.snowflake.com/en/user-guide/data-time-travel.html
Which statement about billing applies to Snowflake credits?
- A . Credits are billed per-minute with a 60-minute minimum
- B . Credits are used to pay for cloud data storage usage
- C . Credits are consumed based on the number of credits billed for each hour that a warehouse runs
- D . Credits are consumed based on the warehouse size and the time the warehouse is running
D
Explanation:
Snowflake credits are used to pay for the consumption of resources on Snowflake. A Snowflake credit is a unit of measure, and it is consumed only when a customer is using resources, such as when a virtual warehouse is running, the cloud services layer is performing work, or serverless features are used.
https://docs.snowflake.com/en/user-guide/what-are-credits.html
What Snowflake features allow virtual warehouses to handle high concurrency workloads? (Select TWO)
- A . The ability to scale up warehouses
- B . The use of warehouse auto scaling
- C . The ability to resize warehouses
- D . Use of multi-clustered warehouses
- E . The use of warehouse indexing
When reviewing the load for a warehouse using the load monitoring chart, the chart indicates that a high volume of Queries are always queuing in the warehouse.
According to recommended best practice, what should be done to reduce the Queue volume? (Select TWO).
- A . Use multi-clustered warehousing to scale out warehouse capacity.
- B . Scale up the warehouse size to allow Queries to execute faster.
- C . Stop and start the warehouse to clear the queued queries
- D . Migrate some queries to a new warehouse to reduce load
- E . Limit user access to the warehouse so fewer queries are run against it.
Which of the following objects can be shared through secure data sharing?
- A . Masking policy
- B . Stored procedure
- C . Task
- D . External table
D
Explanation:
Secure Data Sharing enables sharing selected objects in a database in your account with other Snowflake accounts.
The following Snowflake database objects can be shared:
✑ Tables
✑ External tables
✑ Secure views
✑ Secure materialized views
✑ Secure UDFs
Snowflake enables the sharing of databases through shares, which are created by data providers and “imported” by data consumers.
https://docs.snowflake.com/en/user-guide/data-sharing-intro.html#:~:text=Secure%20Data%20Sharing%20enables%20sharing,External%20tables
Which of the following commands cannot be used within a reader account?
- A . CREATE SHARE
- B . ALTER WAREHOUSE
- C . DROP ROLE
- D . SHOW SCHEMAS
- E . DESCRBE TABLE
A user unloaded a Snowflake table called mytable to an internal stage called mystage.
Which command can be used to view the list of files that has been uploaded to the staged?
- A . list @mytable;
- B . list @%raytable;
- C . list @ %m.ystage;
- D . list @mystage;
Which of the following Snowflake capabilities are available in all Snowflake editions? (Select TWO)
- A . Customer-managed encryption keys through Tri-Secret Secure
- B . Automatic encryption of all data
- C . Up to 90 days of data recovery through Time Travel
- D . Object-level access control
- E . Column-level security to apply data masking policies to tables and views
B, D
Explanation:
https://docs.snowflake.com/en/user-guide/intro-editions.html
Which command is used to unload data from a Snowflake table into a file in a stage?
- A . COPY INTO
- B . GET
- C . WRITE
- D . EXTRACT INTO
A
Explanation:
https://docs.snowflake.com/en/user-guide/data-unload-snowflake.html#:~:text=Your%20User%20Stage-,Use%20the%20COPY%20INTO%20command%20to%20unload%20all%20the,more%20files%20in%20your%20stage.
How often are encryption keys automatically rotated by Snowflake?
- A . 30 Days
- B . 60 Days
- C . 90 Days
- D . 365 Days
What are value types that a VARIANT column can store? (Select TWO)
- A . STRUCT
- B . OBJECT
- C . BINARY
- D . ARRAY
- E . CLOB
B, D
Explanation:
Characteristics of a VARIANT
A VARIANT can store a value of any other type, including OBJECT and ARRAY. The maximum length of a VARIANT is 16 MB.
https://docs.snowflake.com/en/sql-reference/data-types-semistructured.html#:~:text=VARIANT-,Characteristics%20of%20a%20VARIANT,a%20VARIANT%20is%2016%20MB.
A user has an application that writes a new Tile to a cloud storage location every 5 minutes.
What would be the MOST efficient way to get the files into Snowflake?
- A . Create a task that runs a copy into operation from an external stage every 5 minutes
- B . Create a task that puts the files in an internal stage and automate the data loading wizard
- C . Create a task that runs a GET operation to intermittently check for new files
- D . Set up cloud provider notifications on the Tile location and use Snowpipe with auto-ingest
D
Explanation:
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro.html
Which of the following are best practice recommendations that should be considered when loading data into Snowflake? (Select TWO).
- A . Load files that are approximately 25 MB or smaller.
- B . Remove all dates and timestamps.
- C . Load files that are approximately 100-250 MB (or larger)
- D . Avoid using embedded characters such as commas for numeric data types
- E . Remove semi-structured data types
C, D
Explanation:
https://docs.snowflake.com/en/user-guide/data-load-considerations-prepare.html
A user has 10 files in a stage containing new customer data.
The ingest operation completes with no errors, using the following command: COPY INTO my__table FROM @my__stage;
The next day the user adds 10 files to the stage so that now the stage contains a mixture of new
customer data and updates to the previous data. The user did not remove the 10 original files.
If the user runs the same copy into command what will happen?
- A . All data from all of the files on the stage will be appended to the table
- B . Only data about new customers from the new files will be appended to the table
- C . The operation will fail with the error uncertain files in stage.
- D . All data from only the newly-added files will be appended to the table.
A user has unloaded data from Snowflake to a stage.
Which SQL command should be used to validate which data was loaded into the stage?
- A . list @file__stage
- B . show @file__stage
- C . view @file__stage
- D . verify @file__stage
What happens when a cloned table is replicated to a secondary database? (Select TWO)
- A . A read-only copy of the cloned tables is stored.
- B . The replication will not be successful.
- C . The physical data is replicated
- D . Additional costs for storage are charged to a secondary account
- E . Metadata pointers to cloned tables are replicated
Which data types does Snowflake support when querying semi-structured data? (Select TWO)
- A . VARIANT
- B . ARRAY
- C . VARCHAR
- D . XML
- E . BLOB
A, B
Explanation:
https://docs.snowflake.com/en/user-guide/semistructured-intro.html#label-loading-semi-structured-data
A VARIANT stores semi-structured data in Snowflake. It can store a value of any other type, including OBJECT and ARRAY.
The maximum length of a VARIANT is 16 MB.
A Snowflake ARRAY is similar to an array in many other programming languages. An ARRAY contains 0 or more pieces of data. Each element is accessed by specifying its position in the array.
Which of the following describes how multiple Snowflake accounts in a single organization relate to various cloud providers?
- A . Each Snowflake account can be hosted in a different cloud vendor and region.
- B . Each Snowflake account must be hosted in a different cloud vendor and region
- C . All Snowflake accounts must be hosted in the same cloud vendor and region
- D . Each Snowflake account can be hosted in a different cloud vendor, but must be in the same region.
A user is loading JSON documents composed of a huge array containing multiple records into Snowflake. The user enables the strip__outer_array file format option.
What does the STRIP_OUTER_ARRAY file format do?
- A . It removes the last element of the outer array.
- B . It removes the outer array structure and loads the records into separate table rows,
- C . It removes the trailing spaces in the last element of the outer array and loads the records into separate table columns
- D . It removes the NULL elements from the JSON object eliminating invalid data and enables the ability to load the records
B
Explanation:
Data Size Limitations
The VARIANT data type imposes a 16 MB size limit on individual rows.
For some semi-structured data formats (e.g. JSON), data sets are frequently a simple concatenation of multiple documents. The JSON output from some software is composed of a single huge array containing multiple records. There is no need to separate the documents with line breaks or commas, though both are supported.
If the data exceeds 16 MB, enable the STRIP_OUTER_ARRAY file format option for the COPY INTO <table> command to remove the outer array structure and load the records into separate table rows: copy into <table>
from @~/<file>.json
file_format = (type = ‘JSON’ strip_outer_array = true);
https://docs.snowflake.com/en/user-guide/semistructured-considerations.html
What are the default Time Travel and Fail-safe retention periods for transient tables?
- A . Time Travel – 1 day. Fail-safe – 1 day
- B . Time Travel – 0 days. Fail-safe – 1 day
- C . Time Travel – 1 day. Fail-safe – 0 days
- D . Transient tables are retained in neither Fail-safe nor Time Travel
What is a best practice after creating a custom role?
- A . Create the custom role using the SYSADMIN role.
- B . Assign the custom role to the SYSADMIN role
- C . Assign the custom role to the PUBLIC role
- D . Add__CUSTOM to all custom role names
B
Explanation:
When creating roles that will serve as the owners of securable objects in the system, Snowflake recommends creating a hierarchy of custom roles, with the top-most custom role assigned to the system role SYSADMIN. This role structure allows system administrators to manage all objects in the account, such as warehouses and database objects, while restricting management of users and roles to the USERADMIN role.
Which of the following Snowflake objects can be shared using a secure share? (Select TWO).
- A . Materialized views
- B . Sequences
- C . Procedures
- D . Tables
- E . Secure User Defined Functions (UDFs)
Will data cached in a warehouse be lost when the warehouse is resized?
- A . Possibly, if the warehouse is resized to a smaller size and the cache no longer fits.
- B . Yes. because the compute resource is replaced in its entirety with a new compute resource.
- C . No. because the size of the cache is independent from the warehouse size
- D . Yes. became the new compute resource will no longer have access to the cache encryption key
Which Snowflake partner specializes in data catalog solutions?
- A . Alation
- B . DataRobot
- C . dbt
- D . Tableau
A
Explanation:
Alation provides Data Cataloging functionality. They state they are the ‘One Place to Find, Understand, & Govern Data Across an Enterprise. https://docs.snowflake.com/en/user-guide/ecosystem-all.html
What is the MOST performant file format for loading data in Snowflake?
- A . CSV (Unzipped)
- B . Parquet
- C . CSV (Gzipped)
- D . ORC
Which copy INTO command outputs the data into one file?
- A . SINGLE=TRUE
- B . MAX_FILE_NUMBER=1
- C . FILE_NUMBER=1
- D . MULTIPLE=FAISE
Where would a Snowflake user find information about query activity from 90 days ago?
- A . account__usage . query history view
- B . account__usage.query__history__archive View
- C . information__schema . cruery_history view
- D . information__schema – query history_by_ses s i on view
Which Snowflake technique can be used to improve the performance of a query?
- A . Clustering
- B . Indexing
- C . Fragmenting
- D . Using INDEX__HINTS
A
Explanation:
https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html
User-level network policies can be created by which of the following roles? (Select TWO).
- A . ROLEADMIN
- B . ACCOUNTADMIN
- C . SYSADMIN
- D . SECURITYADMIN
- E . USERADMIN
Which command can be used to load data into an internal stage?
- A . LOAD
- B . copy
- C . GET
- D . PUT
D
Explanation:
https://medium.com/@divyanshsaxenaofficial/snowflake-loading-unloading-of-data-part-1-internal-stages-7121cc3cc9
What happens when an external or an internal stage is dropped? (Select TWO).
- A . When dropping an external stage, the files are not removed and only the stage is dropped
- B . When dropping an external stage, both the stage and the files within the stage are removed
- C . When dropping an internal stage, the files are deleted with the stage and the files are recoverable
- D . When dropping an internal stage, the files are deleted with the stage and the files are not recoverable
- E . When dropping an internal stage, only selected files are deleted with the stage and are not recoverable
How long is Snowpipe data load history retained?
- A . As configured in the create pipe settings
- B . Until the pipe is dropped
- C . 64 days
- D . 14 days
In which use cases does Snowflake apply egress charges?
- A . Data sharing within a specific region
- B . Query result retrieval
- C . Database replication
- D . Loading data into Snowflake
Which account__usage views are used to evaluate the details of dynamic data masking? (Select TWO)
- A . ROLES
- B . POLICY_REFERENCES
- C . QUERY_HISTORY
- D . RESOURCE_MONIT ORS
- E . ACCESS_HISTORY
Query compilation occurs in which architecture layer of the Snowflake Cloud Data Platform?
- A . Compute layer
- B . Storage layer
- C . Cloud infrastructure layer
- D . Cloud services layer
Which is the MINIMUM required Snowflake edition that a user must have if they want to use AWS/Azure Privatelink or Google Cloud Private Service Connect?
- A . Standard
- B . Premium
- C . Enterprise
- D . Business Critical
In the query profiler view for a query, which components represent areas that can be used to help optimize query performance? (Select TWO)
- A . Bytes scanned
- B . Bytes sent over the network
- C . Number of partitions scanned
- D . Percentage scanned from cache
- E . External bytes scanned
A marketing co-worker has requested the ability to change a warehouse size on their medium virtual warehouse called mktg__WH.
Which of the following statements will accommodate this request?
- A . ALLOW RESIZE ON WAREHOUSE MKTG__WH TO USER MKTG__LEAD;
- B . GRANT MODIFY ON WAREHOUSE MKTG WH TO ROLE MARKETING;
- C . GRANT MODIFY ON WAREHOUSE MKTG__WH TO USER MKTG__LEAD;
- D . GRANT OPERATE ON WAREHOUSE MKTG WH TO ROLE MARKETING;
When reviewing a query profile, what is a symptom that a query is too large to fit into the memory?
- A . A single join node uses more than 50% of the query time
- B . Partitions scanned is equal to partitions total
- C . An Aggregate Operacor node is present
- D . The query is spilling to remote storage
Which stage type can be altered and dropped?
- A . Database stage
- B . External stage
- C . Table stage
- D . User stage
Which command can be used to stage local files from which Snowflake interface?
- A . SnowSQL
- B . Snowflake classic web interface (Ul)
- C . Snowsight
- D . .NET driver
A
Explanation:
https://docs.snowflake.com/en/user-guide/snowsql-use.html
What is the recommended file sizing for data loading using Snowpipe?
- A . A compressed file size greater than 100 MB, and up to 250 MB
- B . A compressed file size greater than 100 GB, and up to 250 GB
- C . A compressed file size greater than 10 MB, and up to 100 MB
- D . A compressed file size greater than 1 GB, and up to 2 GB
A
Explanation:
https://www.phdata.io/blog/how-to-optimize-snowpipe-data-load/#:~:text=Snowpipe%20is%20typically%20used%20to,data%20within%20one%2Dminute%20int ervals.
Which services does the Snowflake Cloud Services layer manage? (Select TWO).
- A . Compute resources
- B . Query execution
- C . Authentication
- D . Data storage
- E . Metadata
C, E
Explanation:
https://docs.snowflake.com/en/user-guide/intro-key-concepts.html
The cloud services layer is a collection of services that coordinate activities across Snowflake. These services tie together all of the different components of Snowflake in order to process user requests, from login to query dispatch. The cloud services layer also runs on compute instances provisioned by Snowflake from the cloud provider.
Services managed in this layer include:
✑ Authentication
✑ Infrastructure management
✑ Metadata management
✑ Query parsing and optimization
✑ Access control
What data is stored in the Snowflake storage layer? (Select TWO).
- A . Snowflake parameters
- B . Micro-partitions
- C . Query history
- D . Persisted query results
- E . Standard and secure view results
In which scenarios would a user have to pay Cloud Services costs? (Select TWO).
- A . Compute Credits = 50 Credits Cloud Services = 10
- B . Compute Credits = 80 Credits Cloud Services = 5
- C . Compute Credits = 10 Credits Cloud Services = 9
- D . Compute Credits = 120 Credits Cloud Services = 10
- E . Compute Credits = 200 Credits Cloud Services = 26
What transformations are supported in a CREATE PIPE … AS COPY … FROM (….) statement? (Select TWO.)
- A . Data can be filtered by an optional where clause
- B . Incoming data can be joined with other tables
- C . Columns can be reordered
- D . Columns can be omitted
- E . Row level access can be defined
What is a responsibility of Snowflake’s virtual warehouses?
- A . Infrastructure management
- B . Metadata management
- C . Query execution
- D . Query parsing and optimization
- E . Management of the storage layer
Which of the following compute resources or features are managed by Snowflake? (Select TWO).
- A . Execute a COPY command
- B . Updating data
- C . Snowpipe
- D . AUTOMATIC__CLUSTERING
- E . Scaling up a warehouse
What happens when a virtual warehouse is resized?
- A . When increasing the size of an active warehouse the compute resource for all running and queued queries on the warehouse are affected
- B . When reducing the size of a warehouse the compute resources are removed only when they are no longer being used to execute any current statements.
- C . The warehouse will be suspended while the new compute resource is provisioned and will resume automatically once provisioning is complete.
- D . Users who are trying to use the warehouse will receive an error message until the resizing is complete
A developer is granted ownership of a table that has a masking policy. The developer’s role is not able to see the masked data.
Will the developer be able to modify the table to read the masked data?
- A . Yes, because a table owner has full control and can unset masking policies.
- B . Yes, because masking policies only apply to cloned tables.
- C . No, because masking policies must always reference specific access roles.
- D . No, because ownership of a table does not include the ability to change masking policies
Which of the following describes how clustering keys work in Snowflake?
- A . Clustering keys update the micro-partitions in place with a full sort, and impact the DML operations.
- B . Clustering keys sort the designated columns over time, without blocking DML operations
- C . Clustering keys create a distributed, parallel data structure of pointers to a table’s rows and columns
- D . Clustering keys establish a hashed key on each node of a virtual warehouse to optimize joins at run-time
What is a machine learning and data science partner within the Snowflake Partner Ecosystem?
- A . Informatica
- B . Power Bl
- C . Adobe
- D . Data Robot
D
Explanation:
https://docs.snowflake.com/en/user-guide/ecosystem-analytics.html
Which of the following is a valid source for an external stage when the Snowflake account is located on Microsoft Azure?
- A . An FTP server with TLS encryption
- B . An HTTPS server with WebDAV
- C . A Google Cloud storage bucket
- D . A Windows server file share on Azure
Which data type can be used to store geospatial data in Snowflake?
- A . Variant
- B . Object
- C . Geometry
- D . Geography
D
Explanation:
https://docs.snowflake.com/en/sql-reference/data-types-geospatial.html#:~:text=have%20SRID%3D4326.-,Geospatial%20Object%20Types,Point
What can be used to view warehouse usage over time? (Select Two).
- A . The load HISTORY view
- B . The Query history view
- C . The show warehouses command
- D . The WAREHOUSE_METERING__HISTORY View
- E . The billing and usage tab in the Snowflake web Ul
Which Snowflake feature is used for both querying and restoring data?
- A . Cluster keys
- B . Time Travel
- C . Fail-safe
- D . Cloning
B
Explanation:
https://docs.snowflake.com/en/user-guide/data-availability.html#:~:text=Snowflake%20provides%20powerful%20CDP%20features,days%20through%20Snowflake%20Time%20Travel.
A company strongly encourages all Snowflake users to self-enroll in Snowflake’s default Multi-Factor Authentication (MFA) service to provide increased login security for users connecting to Snowflake.
Which application will the Snowflake users need to install on their devices in order to connect with MFA?
- A . Okta Verify
- B . Duo Mobile
- C . Microsoft Authenticator
- D . Google Authenticator
Which Snowflake objects track DML changes made to tables, like inserts, updates, and deletes?
- A . Pipes
- B . Streams
- C . Tasks
- D . Procedures
B
Explanation:
https://dataterrain.com/how-to-change-tracking-using-table-streams-in-snowflake/#:~:text=A%20stream%20is%20a%20Snowflake,as%20metadata%20about%20each%20ch ange.
What tasks can be completed using the copy command? (Select TWO)
- A . Columns can be aggregated
- B . Columns can be joined with an existing table
- C . Columns can be reordered
- D . Columns can be omitted
- E . Data can be loaded without the need to spin up a virtual warehouse
What feature can be used to reorganize a very large table on one or more columns?
- A . Micro-partitions
- B . Clustering keys
- C . Key partitions
- D . Clustered partitions
B
Explanation:
https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html
What SQL command would be used to view all roles that were granted to user.1?
- A . show grants to user USER1;
- B . show grants of user USER1;
- C . describe user USER1;
- D . show grants on user USER1;
Which of the following can be executed/called with Snowpipe?
- A . A User Defined Function (UDF)
- B . A stored procedure
- C . A single copy_into statement
- D . A single insert__into statement
What Snowflake role must be granted for a user to create and manage accounts?
- A . ACCOUNTADMIN
- B . ORGADMIN
- C . SECURITYADMIN
- D . SYSADMIN
A
Explanation:
https://docs.snowflake.com/en/user-guide/security-access-control-considerations.html
When unloading to a stage, which of the following is a recommended practice or approach?
- A . Set SINGLE: = true for larger files
- B . Use OBJECT_CONSTRUCT ( * ) when using Parquet
- C . Avoid the use of the CAST function
- D . Define an individual file format
When is the result set cache no longer available? (Select TWO)
- A . When another warehouse is used to execute the query
- B . When another user executes the query
- C . When the underlying data has changed
- D . When the warehouse used to execute the query is suspended
- E . When it has been 24 hours since the last query
Which of the following describes external functions in Snowflake?
- A . They are a type of User-defined Function (UDF).
- B . They contain their own SQL code.
- C . They call code that is stored inside of Snowflake.
- D . They can return multiple rows for each row received
A
Explanation:
External functions are user-defined functions that are stored and executed outside of Snowflake. External functions make it easier to access external API services such as geocoders, machine learning models, and other custom code running outside of Snowflake. This feature eliminates the need to export and reimport data when using third-party services, significantly simplifying your data pipelines.
https://docs.snowflake.com/en/sql-reference/external-functions.html#:~:text=External%20functions%20are%20user%2Ddefined,code%20running%20outsi de%20of%20Snowflake.
What are ways to create and manage data shares in Snowflake? (Select TWO)
- A . Through the Snowflake web interface (Ul)
- B . Through the DATA_SHARE=TRUE parameter
- C . Through SQL commands
- D . Through the enable__share=true parameter
- E . Using the CREATE SHARE AS SELECT * TABLE command
A company’s security audit requires generating a report listing all Snowflake logins (e.g.. date and user) within the last 90 days.
Which of the following statements will return the required information?
- A . SELECT LAST_SUCCESS_LOGIN, LOGIN_NAME
FROM ACCOUNT_USAGE.USERS; - B . SELECT EVENT_TIMESTAMP, USER_NAME
FROM table(information_schema.login_history_by_user()) - C . SELECT EVENT_TIMESTAMP, USER_NAME
FROM ACCOUNT_USAGE.ACCESS_HISTORY; - D . SELECT EVENT_TIMESTAMP, USER_NAME
FROM ACCOUNT_USAGE.LOGIN_HISTORY;
Which semi-structured file formats are supported when unloading data from a table? (Select TWO).
- A . ORC
- B . XML
- C . Avro
- D . Parquet
- E . JSON
D, E
Explanation:
Semi-structured JSON, Parquet
https://docs.snowflake.com/en/user-guide/data-unload-prepare.html#:~:text=Supported%20File%20Formats,-The%20following%20file&text=Delimited%20(CSV%2C%20TSV%2C%20etc.)
What is the purpose of an External Function?
- A . To call code that executes outside of Snowflake
- B . To run a function in another Snowflake database
- C . To share data in Snowflake with external parties
- D . To ingest data from on-premises data sources
A
Explanation:
https://docs.snowflake.com/en/sql-reference/external-functions.html
A user created a new worksheet within the Snowsight Ul and wants to share this with teammates How can this worksheet be shared?
- A . Create a zero-copy clone of the worksheet and grant permissions to teammates
- B . Create a private Data Exchange so that any teammate can use the worksheet
- C . Share the worksheet with teammates within Snowsight
- D . Create a database and grant all permissions to teammates