SnowPro Advanced: Data Engineer Practice Exam

Free sample questions

1. Sample · question 1 · Snowpark lazy evaluation

   A Data Engineer builds a Snowpark Python transformation that reads from a source table, joins it to a dimension table, and applies several filters. The script constructs the DataFrame but never calls .show(), .collect(), .save_as_table(), or any other action method. What is true about how Snowflake handles this code?

   • A.The transformations execute eagerly on the compute layer and their results are cached on the client side.
   • B.No SQL is sent to Snowflake — the DataFrame is a lazy plan that only compiles and executes when an action is invoked.correct
   • C.Joins execute on the driver client while filters push down to Snowflake.
   • D.Filters push down but joins wait for an explicit .execute() call.

   Why: Snowpark uses lazy evaluation. Building a DataFrame only assembles a logical plan; no SQL is emitted until an action method (collect, show, save_as_table, count, etc.) is called. This is intentional — it lets the Snowpark optimizer combine transformations into a single pushed-down query rather than issuing one request per transformation.

   Open this question on its own page →
2. Sample · question 2 · Dynamic tables with TARGET_LAG

   A reporting layer needs to stay within 15 minutes of source-table freshness with minimal operational overhead. The transformation involves a multi-way join and a few aggregations. Which Snowflake feature best meets this requirement?

   • A.A materialized view over the joined source tables.
   • B.A dynamic table with TARGET_LAG = '15 minutes'.correct
   • C.A scheduled task that runs CREATE OR REPLACE TABLE AS SELECT every 15 minutes.
   • D.A stream on each source table consumed by a task on a 15-minute schedule.

   Why: Dynamic tables were designed exactly for this — declare a TARGET_LAG and Snowflake incrementally maintains the table to meet the freshness SLA. Materialized views don't support arbitrary joins across multiple large tables. Scheduled CTAS does a full rebuild every run (wasteful). Streams + tasks work but require you to write and maintain the orchestration that dynamic tables give you for free.

   Open this question on its own page →
3. Sample · question 3 · Snowpipe Streaming for sub-10-second latency

   An application produces roughly 200 clickstream events per second. Downstream analysts need to query the events in Snowflake within 10 seconds of arrival. Which ingestion approach best matches this SLA?

   • A.Snowpipe with cloud storage auto-ingest triggered by SQS/PubSub notifications.
   • B.Snowpipe Streaming via the Snowflake Ingest SDK.correct
   • C.A Snowpark job that reads S3 every 30 seconds on a schedule.
   • D.An external table over the raw S3 prefix with a materialized view on top.

   Why: Snowpipe Streaming provides sub-10-second end-to-end latency for row-level streaming ingestion — that's its purpose. File-based Snowpipe has a ~1-minute floor because it batches per file. Scheduled Snowpark jobs inherit their schedule interval as latency. External tables add per-query metadata refresh cost and don't materialize rows into Snowflake tables.

   Open this question on its own page →
4. Sample · question 4 · Fan-in task DAGs with AFTER

   Two independent tasks (LOAD_A and LOAD_B) must both complete before a third task (JOIN_AB) runs, and all three should share the same schedule. What is the cleanest Snowflake-native setup?

   • A.Set LOAD_A and LOAD_B on the same schedule; create JOIN_AB with AFTER LOAD_A, LOAD_B.correct
   • B.Chain LOAD_A -> LOAD_B -> JOIN_AB serially so JOIN_AB implicitly waits.
   • C.Trigger LOAD_A and LOAD_B on the schedule and have JOIN_AB poll SYSTEM$TASK_STATUS in a WHEN clause.
   • D.Use an external orchestrator; Snowflake tasks cannot express fan-in dependencies.

   Why: Snowflake task DAGs support multiple predecessors — the AFTER clause creates a fan-in dependency so the child runs only after every named predecessor completes successfully within the same run. Serial chaining defeats the parallelism between LOAD_A and LOAD_B. Polling status is wasteful when the platform provides fan-in natively. External orchestrators are unnecessary.

   Open this question on its own page →
5. Sample · question 5 · Time Travel vs Fail-safe recovery window

   A permanent table was dropped 5 days ago in an account where the table's DATA_RETENTION_TIME_IN_DAYS was 3. The team now needs to recover the data. What is the current state?

   • A.The data can be recovered by running UNDROP TABLE immediately.
   • B.The data is in Fail-safe and is only recoverable through a Snowflake Support request.correct
   • C.The data is permanently gone — both Time Travel and Fail-safe have expired.
   • D.The data is available via SELECT ... AT (TIMESTAMP => ...) for up to 90 days back.

   Why: The 3-day Time Travel window expired 2 days ago, so UNDROP no longer works. Permanent tables get 7 additional days of Fail-safe after Time Travel ends (day 3 → day 10), so the data still exists but is not user-accessible — Fail-safe recovery requires opening a support ticket with Snowflake. If this were day 11+, the data would be gone for good.

   Open this question on its own page →
6. Sample · question 6 · Maintaining externally managed Iceberg tables

   An externally-managed Apache Iceberg table backed by S3 is written by a Spark job using merge-on-read with position delete files. Over several weeks, Snowflake queries against the table have become significantly slower and the number of small delete files in S3 keeps growing. What is the correct fix?

   • A.Enable Snowflake Automatic Clustering on the Iceberg table so Snowflake will compact the data and delete files.
   • B.Set ENABLE_ICEBERG_MERGE_ON_READ = FALSE in Snowflake to have Snowflake rewrite the files into copy-on-write form.
   • C.Run regular data-file compaction and delete-file cleanup in the external engine (Spark), then let Snowflake pick up the results via its normal metadata refresh.correct
   • D.Increase the frequency of ALTER ICEBERG TABLE ... REFRESH in Snowflake so old delete files are pruned faster.

   Why: For externally-managed Iceberg tables Snowflake is a read-only consumer — it cannot rewrite data files or clean up delete files owned by the external catalog. Compaction and maintenance must be run in the writing engine (Spark, Trino, etc.). Snowflake's job is to refresh its metadata pointer once the external maintenance completes.

   Open this question on its own page →
7. Sample · question 7 · External table partition metadata refresh

   An external table backed by S3 has partition columns for year, month, and day. New date-partitioned folders arrive daily. A query with WHERE year=2026 AND month=1 shows a full scan in the Query Profile instead of the expected partition pruning. What is the most likely cause and fix?

   • A.Partition columns must be VARIANT; recreate the table with VARIANT partition columns to enable pruning.
   • B.External tables do not support partition pruning; layer a materialized view on top to get pruning.
   • C.The external table's partition metadata is stale — run ALTER EXTERNAL TABLE ... REFRESH (or enable AUTO_REFRESH with cloud event notifications) so Snowflake sees the new partitions.correct
   • D.Partition pruning on external tables requires the query to reference METADATA$PARTITION_ID explicitly in the WHERE clause.

   Why: External tables prune only what their registered partition metadata knows about. Newly-added S3 folders are invisible until Snowflake refreshes — either manually via ALTER EXTERNAL TABLE ... REFRESH, or automatically if AUTO_REFRESH is enabled and cloud event notifications are wired. Once metadata is current, the WHERE clause filters normally with proper pruning.

   Open this question on its own page →
8. Sample · question 8 · Tag propagation across data movement

   A governance policy requires that tags applied to a source table's columns automatically appear on any table derived from it through CTAS, COPY INTO, or INSERT-SELECT. Which statement about Snowflake tag propagation is correct?

   • A.Tags always propagate automatically to any derived object.
   • B.Tag propagation across data-movement operations must be explicitly enabled on the tag.correct
   • C.Tags only propagate to derived objects that live in the same schema as the source.
   • D.Tags cannot propagate — every derived object must be tagged manually with ALTER.

   Why: By default, Snowflake tags remain pinned to the object they were applied to. To have a tag follow data through CTAS / COPY INTO / INSERT-SELECT, propagation has to be enabled on the tag itself. That gives governance teams control over which tags should track lineage across derived objects and which should stay on the original.

   Open this question on its own page →
9. Sample · question 9 · Query Acceleration max scale factor

   An engineer runs ALTER WAREHOUSE wh SET ENABLE_QUERY_ACCELERATION = TRUE, QUERY_ACCELERATION_MAX_SCALE_FACTOR = 0. Shortly afterward, credit consumption on that warehouse jumps unexpectedly. What is the most likely cause and the correct remediation?

   • A.Setting the scale factor to 0 disabled Query Acceleration entirely; credits are up for unrelated reasons — no change needed.
   • B.A value of 0 means no upper bound on the acceleration scale factor, so QAS is spending freely. Set a finite cap (for example 4 or 8) to constrain it.correct
   • C.Query Acceleration was applied to every query on the warehouse. Move affected queries to a warehouse with QAS disabled.
   • D.The warehouse rejected queries during the change; rerun them once the configuration stabilizes.

   Why: For QUERY_ACCELERATION_MAX_SCALE_FACTOR, 0 means 'no maximum' — Snowflake can scale acceleration compute as high as it needs. That easily produces surprise costs. The fix is a finite cap that matches your budget, such as 4 or 8, so QAS still helps eligible queries without unbounded spend.

   Open this question on its own page →
10. Sample · question 10 · Alerting on Cortex AI credit usage

    A single AI_SUMMARIZE_AGG call on a large log table consumed 1,200 credits, even though it ran on a Small warehouse. The team wants a Snowflake-native automated alert whenever any Cortex AI SQL query consumes more than 300 credits, so they can review the query text and the model used. Which approach best meets that requirement?

    • A.Attach a resource monitor with a 300-credit limit to the warehouse; its notification will identify the offending query.
    • B.Set STATEMENT_TIMEOUT_IN_SECONDS to a value that maps to about 300 credits of runtime so long queries are terminated.
    • C.Create a scheduled task that queries SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AISQL_USAGE_HISTORY for CREDITS_USED > 300, joins to QUERY_HISTORY for the SQL text and model, and sends a message via a notification integration.correct
    • D.Alert on SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY where SERVICE_TYPE = 'AI_SERVICES' and TOTAL_CREDITS > 300 for the current day.

    Why: Resource monitors act on aggregate warehouse credit usage, not per-query Cortex spend. Statement timeout terminates by wall-clock time, not credits, and can't discriminate Cortex from other work. Daily aggregate views detect the problem after the day is over, when nothing can be reviewed in-flight. The purpose-built path is a scheduled task over CORTEX_AISQL_USAGE_HISTORY joined to QUERY_HISTORY, with a notification integration for delivery — exactly the pattern Snowflake recommends for per-query Cortex cost observability.

    Open this question on its own page →