Download Snowflake SnowPro Core Practice Exam and more Exams Technology in PDF only on Docsity!
Question 1. Which layer of Snowflake’s architecture is responsible for storing data in micro‑partitions? A) Compute layer B) Cloud Services layer C) Database Storage layer D) Network Services layer Answer: C Explanation: The Database Storage layer holds all data in immutable micro‑partitions that are managed by Snowflake’s storage service. Question 2. In Snowflake, which object type can be created without a physical storage footprint and is automatically dropped at the end of a session? A) Permanent table B) Temporary table C) Transient table D) External table Answer: B Explanation: Temporary tables exist only for the duration of a user session and do not incur storage costs after the session ends. Question 3. Which of the following Snowflake view types enforces row‑level security and hides underlying data from the consumer? A) Standard view B) Secure view C) Materialized view D) Inline view Answer: B
Explanation: Secure views are evaluated in the context of the view owner and prevent the consumer from seeing underlying data or metadata. Question 4. Which data type should be used to store semi‑structured JSON data that may contain nested arrays and objects? A) STRING B) VARIANT C) OBJECT D) ARRAY Answer: B Explanation: VARIANT is Snowflake’s generic semi‑structured data type capable of holding JSON, Avro, XML, or Parquet structures with nested arrays and objects. Question 5. What is the primary purpose of a Snowflake Stream object? A) To schedule recurring SQL statements B) To continuously load data from external sources C) To capture DML changes on a table for downstream processing D) To replicate data across regions Answer: C Explanation: Streams provide change data capture (CDC) by recording inserts, updates, and deletes on a source table. Question 6. Which Snowflake service enables automatic clustering of data without user‑defined clustering keys? A) Search Optimization Service B) Automatic Clustering Service C) Materialized View Service
B) Additional authentication factor beyond password C) Encryption of data at rest D) Automatic role assignment Answer: B Explanation: MFA requires a second factor (e.g., OTP, hardware token) in addition to the password, strengthening login security. Question 10. Which Snowflake object is used to enforce column‑level masking based on the querying role? A) Row Access Policy B) Secure View C) Dynamic Data Masking (DDM) policy D) Data Masking Function Answer: C Explanation: DDM defines masking rules that are applied at query time, showing masked values to roles without the required privilege. Question 11. How does Snowflake’s Result Cache improve query performance? A) Stores raw data files in local SSDs B) Caches the final result set of a query for 24 hours C) Stores compiled query execution plans D) Replicates data across warehouses Answer: B Explanation: The Result Cache retains the exact result set of a query for 24 hours, allowing identical queries to return instantly without recomputation.
Question 12. Which warehouse size provides roughly eight times the compute resources of a SMALL warehouse? A) MEDIUM B) LARGE C) X-LARGE D) 2X-LARGE Answer: C Explanation: Snowflake’s size hierarchy doubles compute resources each step; X‑LARGE is 8× SMALL (2³). Question 13. When would you enable a Multi‑Cluster Warehouse in Snowflake? A) To reduce storage costs B) To increase compute for a single query C) To handle high concurrent user workloads D) To enable automatic clustering Answer: C Explanation: Multi‑Cluster Warehouses spin up additional clusters on demand to serve many concurrent queries without queuing. Question 14. Which Snowflake command creates a new file format for reading CSV files with a header row? A) CREATE FILE FORMAT my_csv TYPE = 'CSV' SKIP_HEADER = 1; B) CREATE STAGE my_stage FILE_FORMAT = (TYPE = CSV, HEADER = TRUE); C) CREATE OR REPLACE FILE FORMAT my_csv TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1; D) CREATE PIPE my_pipe USING CSV HEADER = TRUE; Answer: C
D) Snapshot Answer: A Explanation: Cloning creates a zero‑copy, instantly writable clone that shares the same micro‑partitions until diverged. Question 18. Which of the following is NOT a valid Snowflake data type for semi‑structured data? A) VARIANT B) OBJECT C) ARRAY D) STRUCT Answer: D Explanation: Snowflake supports VARIANT, OBJECT, and ARRAY for semi‑structured data, but STRUCT is not a native type. Question 19. In Snowflake, which command is used to grant SELECT privilege on a schema to a role? A) GRANT SELECT ON DATABASE schema TO ROLE my_role; B) GRANT SELECT ON SCHEMA my_schema TO ROLE my_role; C) GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO ROLE my_role; D) GRANT SELECT ON SCHEMA my_schema FROM ROLE my_role; Answer: B Explanation: The GRANT statement syntax specifies the object type (SCHEMA) followed by the target role. Question 20. Which Snowflake object can be used to enforce row‑level security based on a column value (e.g., region)? A) Secure View
B) Row Access Policy C) Data Masking Policy D) Column Level Security Answer: B Explanation: Row Access Policies evaluate a predicate for each row and return TRUE/FALSE to allow or block access. Question 21. What does the SEARCH OPTIMIZATION SERVICE improve in Snowflake? A) Result cache hit rate B) Metadata caching speed C) Predicate push‑down for selective queries on large tables D) Automatic clustering of micro‑partitions Answer: C Explanation: The Search Optimization Service builds auxiliary structures that accelerate selective scans, especially for low‑selectivity predicates. Question 22. Which Snowflake feature enables you to share data with external organizations without copying or moving the data? A) Data Export B) Database Replication C) Secure Data Sharing (Shares) D) External Table Answer: C Explanation: Shares provide live, read‑only access to objects in the provider account, eliminating data duplication.
Explanation: Result caching is not applied to queries that reference temporary or volatile tables, as their data can change unpredictably. Question 26. Which Snowflake object type is best suited for storing large binary files such as images or PDFs? A) VARIANT column in a table B) External Stage C) Snowflake Table with BINARY column D) Snowflake File Format Answer: C Explanation: A BINARY column stores raw binary data directly within a table, making it appropriate for files like images or PDFs. Question 27. Which of the following statements about Snowflake’s automatic scaling (scale‑out) is true? A) It adds more CPUs to the existing warehouse. B) It creates additional independent clusters to handle concurrency. C) It permanently increases the warehouse size. D) It reduces storage costs. Answer: B Explanation: Scale‑out creates additional, independent compute clusters that share the same underlying storage, improving concurrency. Question 28. When using the FLATTEN function on a VARIANT column, what does the “path” argument specify? A) The file system location of the data B) The hierarchical JSON key to be expanded C) The output column name
D) The storage integration name Answer: B Explanation: The “path” parameter tells FLATTEN which nested element (e.g., $.orders) to explode into separate rows. Question 29. Which Snowflake command would you use to view the credit usage of a specific virtual warehouse over the past 7 days? A) SHOW WAREHOUSES; B) SELECT * FROM ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WHERE WAREHOUSE_NAME='MY_WH' AND START_TIME > DATEADD('day',-7,CURRENT_TIMESTAMP()); C) DESCRIBE WAREHOUSE MY_WH; D) SELECT * FROM INFORMATION_SCHEMA.WAREHOUSE_USAGE; Answer: B Explanation: ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY provides detailed credit consumption per warehouse; filtering by name and time yields the desired view. Question 30. Which Snowflake feature allows you to execute Python code directly inside the database engine? A) Snowpark Python B) Snowpipe C) SnowSQL D) Snowflake Connector for Python Answer: A Explanation: Snowpark Python enables developers to write UDFs, stored procedures, and data pipelines in Python that run inside Snowflake’s compute environment. Question 31. Which of the following is a valid reason to use a TRANSIENT table instead of a PERMANENT table?
Question 34. Which of the following statements about Snowflake’s “Zero‑Copy Cloning” is FALSE? A) Clones share the same underlying storage until diverged. B) Cloning a table consumes additional credits immediately. C) Clones can be created for databases, schemas, and tables. D) Clones can be used for testing without affecting the source. Answer: B Explanation: Zero‑copy cloning does not copy data and therefore does not immediately consume storage credits; credits are only used when the clone diverges. Question 35. Which Snowflake command would you use to create a user that authenticates with an RSA public key? A) CREATE USER alice RSA_PUBLIC_KEY='ssh-rsa AAA...'; B) CREATE USER alice TYPE=KEY_PAIR RSA_PUBLIC_KEY='AAA...'; C) CREATE USER alice RSA_PUBLIC_KEY='AAA...' DEFAULT_ROLE=PUBLIC; D) CREATE USER alice KEY_PAIR='AAA...'; Answer: C Explanation: The RSA_PUBLIC_KEY parameter stores the user’s public key for key‑pair authentication; other clauses are optional. Question 36. What is the primary benefit of using Snowflake’s “Materialized Views” over standard views? A) They automatically encrypt data at rest. B) They store pre‑computed results, improving query latency. C) They allow write‑through updates to the underlying tables. D) They bypass the need for a virtual warehouse. Answer: B
Explanation: Materialized Views maintain refreshed result sets, enabling faster query performance for repetitive analytic workloads. Question 37. Which Snowflake feature helps to reduce the time spent scanning large tables when queries use highly selective predicates? A) Data Caching B) Result Caching C) Clustering Keys D) Automatic Failover Answer: C Explanation: Clustering keys define how data is physically organized, allowing Snowflake to prune micro‑partitions and reduce scan time for selective filters. Question 38. Which command creates a new role named “ANALYST_ROLE” and grants it the SELECT privilege on all tables in schema “SALES”? A) CREATE ROLE ANALYST_ROLE; GRANT SELECT ON ALL TABLES IN SCHEMA SALES TO ROLE ANALYST_ROLE; B) CREATE ROLE ANALYST_ROLE; GRANT SELECT ON SCHEMA SALES TO ROLE ANALYST_ROLE; C) CREATE ROLE ANALYST_ROLE; GRANT SELECT ON DATABASE SALES TO ROLE ANALYST_ROLE; D) CREATE ROLE ANALYST_ROLE; GRANT SELECT ON TABLE SALES.* TO ROLE ANALYST_ROLE; Answer: A Explanation: The GRANT statement with “ALL TABLES IN SCHEMA” correctly assigns SELECT on every table within the specified schema. Question 39. Which Snowflake function returns the size of a VARIANT column in bytes? A) LENGTH() B) BYTE_SIZE() C) OCTET_LENGTH()
C) Sharing data with external partners via Secure Share D) Enabling automatic clustering on a large fact table Answer: B Explanation: Transient tables are ideal for temporary staging data because they have reduced Time Travel and no Fail‑Safe, lowering storage costs. Question 43. Which Snowflake account parameter controls the maximum number of concurrent queries a single warehouse can execute before queuing? A) MAX_CONCURRENCY_LEVEL B) QUERY_QUEUE_TIMEOUT C) RESOURCE_MONITOR D) WAREHOUSE_CONCURRENCY_LIMIT Answer: A Explanation: MAX_CONCURRENCY_LEVEL defines how many queries can run concurrently on a warehouse; excess queries are queued. Question 44. Which Snowflake feature can be used to automatically apply a transformation to newly ingested data without writing custom code? A) Streams B) Tasks C) Snowpipe with COPY INTO transformations D) Materialized Views Answer: C Explanation: Snowpipe’s COPY INTO command can include file format options and column transformations, allowing automatic data processing on load. Question 45. Which Snowflake object type is used to schedule the periodic execution of a stored procedure?
A) Pipe B) Stream C) Task D) Scheduler Answer: C Explanation: Tasks define a schedule (cron or interval) and can run SQL statements, including calls to stored procedures. Question 46. What does the “AUTOMATIC CLUSTERING” option do when enabled on a table? A) Creates a materialized view automatically. B) Re‑clusters micro‑partitions in the background based on defined clustering keys. C) Enables data compression. D) Forces all queries to use result caching. Answer: B Explanation: Automatic clustering continuously reorganizes micro‑partitions using the table’s clustering keys without manual intervention. Question 47. Which Snowflake role is required to create a new network policy? A) ACCOUNTADMIN B) SECURITYADMIN C) SYSADMIN D) PUBLIC Answer: B Explanation: NETWORK POLICIES are managed under the SECURITYADMIN role, which governs security‑related objects.
Question 51. What does the “COPY INTO FROM ” command accomplish? A) Loads data from an external location into a Snowflake table. B) Exports data from a Snowflake table to an external stage or local file system. C) Clones a table into a new location within Snowflake. D) Creates a backup of the entire database. Answer: B Explanation: COPY INTO with a location writes the result set of a table to files in an internal or external stage. Question 52. Which Snowflake feature allows you to enforce that a column’s value is always stored in uppercase? A) Column Masking Policy B) Row Access Policy C) Column Default Expression with UPPER() D) Secure View Answer: C Explanation: A default expression can call UPPER() to transform incoming values, ensuring they are stored in uppercase. Question 53. Which Snowflake function can be used to retrieve the current Snowflake edition (e.g., Standard, Enterprise)? A) CURRENT_EDITION() B) SYSTEM$EDITION() C) SELECT CURRENT_ACCOUNT(); D) SELECT CURRENT_ROLE(); Answer: B Explanation: SYSTEM$EDITION() returns the edition of the Snowflake account. Question 54. Which of the following statements about Snowflake’s “Result Set Caching” is true? A) Cached results are stored on the client machine. B) Cached results are invalidated if the underlying data changes. C) Cached results are retained for 7 days regardless of query changes. D) Result caching works only for queries that use the COPY command. Answer: B Explanation: Result cache entries are invalidated when any underlying data referenced by the query changes. Question 55. Which Snowflake command is used to create a secure view that masks a column for users without the “UNMASKED” role? A) CREATE SECURE VIEW masked_view AS SELECT CASE WHEN CURRENT_ROLE() = 'UNMASKED' THEN credit_card ELSE 'XXXX-XXXX-XXXX-'||RIGHT(credit_card,4) END FROM customers; B) CREATE VIEW masked_view SECURE AS SELECT * FROM customers; GRANT MASKING POLICY TO ROLE UNMASKED; C) CREATE VIEW masked_view AS SELECT MASKED(credit_card) FROM customers; D) CREATE SECURE VIEW masked_view AS SELECT credit_card FROM customers WITH MASKING; Answer: A Explanation: The CASE expression checks the current role and conditionally masks the column, and the view is defined as SECURE to enforce view owner security. Question 56. Which Snowflake object can be used to automatically generate sequential numeric values without gaps? A) Sequence B) Auto‑Increment Column
