SnowPro Core · 15% of the exam

Data loading, unloading, and transformation: free practice questions

5 sample questions from our 75-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Snowflake exam.

1. A data engineer needs to query a VARIANT column named EVENT_DATA that stores JSON objects like the following: {"user": {"id": 1042, "region": "us-west"}, "action": "login"} Which two of the following expressions correctly retrieve the value of the "region" field? (Select TWO.)

  • A. EVENT_DATA['user']['region']✓ Correct
  • B. EVENT_DATA:user:region✓ Correct
  • C. EVENT_DATA->user->region
  • D. GET(EVENT_DATA, 'user.region')
  • E. EVENT_DATA.user.region
Explanation

Snowflake supports two notations for traversing VARIANT JSON: bracket notation (EVENT_DATA['user']['region']) and colon notation (EVENT_DATA:user:region). Both are valid and return the same result. The arrow operator (->) is not valid Snowflake SQL syntax for VARIANT traversal. GET(col, 'user.region') attempts a single-level key lookup for the literal string 'user.region', which would return NULL because the key is not at the top level. Dot notation (EVENT_DATA.user.region) is only valid in Snowpark DataFrame APIs, not in SQL queries.

2. A data engineering team needs to track row-level changes (inserts, updates, deletes) on a source table named CUSTOMER_EVENTS so that a downstream ETL process can apply only incremental changes to a target table. Which Snowflake object should they create on CUSTOMER_EVENTS to support this pattern?

  • A. A materialized view with REFRESH = AUTO on CUSTOMER_EVENTS
  • B. A standard stream on CUSTOMER_EVENTS✓ Correct
  • C. A dynamic table that SELECT * FROM CUSTOMER_EVENTS
  • D. A task that runs COPY INTO on CUSTOMER_EVENTS every minute
Explanation

Option B is correct. A Snowflake Stream (specifically a standard stream) records DML changes — inserts, updates, and deletes — at the row level against the source table. Downstream processes consume the stream by reading the special metadata columns (METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID) and applying those changes incrementally. Option A (materialized view) is an automatically refreshed read-only copy of query results; it does not expose individual CDC change records for downstream consumption. Option C (dynamic table) continuously materializes query results similar to a materialized view with automatic refresh, but it does not expose change metadata rows; it is a target result set, not a CDC source. Option D (COPY INTO) is for bulk loading data from staged files, not for tracking DML change events on an existing table.

3. A company loads data from an external S3 stage every night using COPY INTO. After six months, a data engineer notices that some files are being silently skipped during loads. What is the MOST likely cause?

  • A. The IAM role attached to the external stage has expired and must be rotated manually.
  • B. Snowflake's load metadata tracks files that have been successfully loaded and skips them by default to prevent duplicate loads.✓ Correct
  • C. The COPY INTO command requires the FORCE = TRUE option to process any file more than 30 days old.
  • D. The external stage's PURGE = TRUE setting is automatically removing files before they can be loaded again.
Explanation

Option B is correct: Snowflake maintains load metadata for each table that records every successfully loaded file for up to 64 days. Subsequent COPY INTO executions skip files already recorded in this metadata, which is the default behavior to prevent duplicate ingestion. Option A is wrong because IAM role expiration would cause an authentication error, not silent skipping. Option C is wrong because there is no 30-day age rule for COPY INTO; FORCE = TRUE re-loads files regardless of load history, but its absence does not cause an age-based skip. Option D is wrong because PURGE = TRUE removes files from the stage AFTER a successful load, which would explain missing files but not silent skipping of files that are still present.

4. A developer needs to create a Snowflake SQL UDF that returns a table (i.e., multiple rows) from a single input parameter. Which statement BEST describes how to implement this?

  • A. Use CREATE FUNCTION ... RETURNS TABLE (...) to define a SQL UDTF (User-Defined Table Function) and call it with the TABLE() keyword in the FROM clause.✓ Correct
  • B. SQL UDFs cannot return multiple rows; only JavaScript UDFs support returning tabular results in Snowflake.
  • C. Define the UDF with RETURNS VARIANT and use FLATTEN on the result to expand it into multiple rows.
  • D. Use CREATE FUNCTION ... RETURNS ARRAY and then use LATERAL FLATTEN on the UDF output within a SELECT statement.
Explanation

Option A is correct. Snowflake supports User-Defined Table Functions (UDTFs) in SQL (and other languages) using the RETURNS TABLE(...) clause. The function is invoked using the TABLE() constructor in the FROM clause, e.g., SELECT * FROM TABLE(my_udtf(arg)). Option B is incorrect because SQL UDTFs are natively supported in Snowflake; JavaScript UDFs can also return tabular results but SQL is not excluded. Option C is incorrect because returning VARIANT and then flattening is an indirect workaround — it is not the proper mechanism for tabular output, and it would require the developer to serialize results as JSON first, which is cumbersome and non-idiomatic. Option D is incorrect because RETURNS ARRAY produces a single-row result containing an array value; while LATERAL FLATTEN could then expand it, this is not how table functions are formally defined, and the result handling would differ significantly from a proper UDTF.

5. A financial services company is designing a near-real-time reporting layer. They must choose between a Materialized View and a Dynamic Table. Their requirements are: (1) the refresh latency target is 5 minutes, (2) the transformation query contains multi-table JOINs across three source tables, and (3) the reporting queries are complex aggregations. Which recommendation is MOST appropriate?

  • A. Use a Materialized View because it supports automatic query rewrite, making complex aggregation queries transparent to end users.
  • B. Use a Dynamic Table because Materialized Views in Snowflake do not support JOINs across multiple tables, whereas Dynamic Tables support arbitrary multi-table query definitions including JOINs.✓ Correct
  • C. Use a Materialized View because it has lower latency than Dynamic Tables for sub-minute refresh requirements.
  • D. Use a Dynamic Table because both options support multi-table JOINs equally, but Dynamic Tables are cheaper due to serverless compute.
Explanation

Snowflake Materialized Views have significant restrictions: they do not support JOINs across multiple tables, UDFs, certain subqueries, or HAVING clauses, among others. Dynamic Tables support virtually any SQL query including multi-table JOINs, making them the correct choice for this requirement. Materialized Views do support transparent query rewrite but cannot handle multi-table JOINs, so option A is incorrect. Option C is incorrect because Materialized Views do not inherently have lower latency than Dynamic Tables and also cannot support the required multi-table JOIN. Option D correctly identifies that Dynamic Tables support the JOIN requirement but is factually misleading about cost—Dynamic Tables use warehouse compute (not always serverless by default) and cost is not the distinguishing factor here.

70 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free