Free SnowPro® Advanced: Data Engineer Practice Questions

Sample Practice Questions

Try these 10 sample questions from the SnowPro® Advanced: Data Engineer practice bank. Each includes the answer and explanation. Continue with the interactive practice above to review the full question set.

1. 1A bulk export consists of a few very large compressed files. Load parallelism is constrained by the file count. Which preparation change follows Snowflake's guidance?

   1. A. Combine the compressed files into a single larger file.
   2. B. Split the files by record boundaries into multiple smaller files.
   3. C. Recompress each file more tightly without changing the file count.
   4. D. Coalesce the files into fewer files to reduce per-file scheduling.

   Answer: B

   Explanation: Snowflake states: "Split larger files into a greater number of smaller files to distribute the load among the compute resources in an active warehouse." Splitting creates more units for file-level load parallelism. Combining/coalescing reduces that parallelism, and recompression alone leaves the constraining file count unchanged.

   Reference: Preparing your data files — File sizing best practices — General file sizing recommendations

2. 2A join operator dominates query time and produces millions of rows from inputs containing only thousands. The intended result should have one match per business key. What should the engineer investigate first?

   1. A. Whether the join predicate is missing or matches multiple rows per key.
   2. B. Whether an overly restrictive inner-join predicate is rejecting most matches.
   3. C. Whether both sides have unique keys and the join is strictly one-to-one.
   4. D. Whether a semijoin returns only matching rows from its left input.

   Answer: A

   Explanation: Snowflake states: "produces significantly (often by orders of magnitude) more tuples than it consumes." Missing/nonunique predicates explain multiplication. Rejecting matches, a one-to-one join and a left-row semijoin do not explain output orders of magnitude larger than the inputs.

   Reference: Monitor query activity with Query History — Common query problems — Exploding joins

3. 3A table's effective Time Travel retention period is 0 days. What does this setting mean for a plan to query a prior version after accidental modification?

   1. A. A zero-day setting is automatically raised to one day after every modification.
   2. B. Time Travel is effectively deactivated for this table.
   3. C. Fail-safe history becomes directly queryable when Time Travel is set to zero.
   4. D. A zero-day setting retains every historical version until the table is dropped.

   Answer: B

   Explanation: Snowflake states: "A retention period of 0 days for an object effectively deactivates Time Travel for the object." Effective retention zero disables historical Time Travel access. It does not create a one-day minimum, expose Fail-safe through queries, or retain versions until object deletion.

   Reference: Understanding and using Time Travel — Data retention period — Note

4. 4A governance application assigns an object tag value that looks numeric, such as cost_center=410. How does Snowflake represent the assigned tag value?

   1. A. As a NUMBER inferred from the characters.
   2. B. As a BOOLEAN inferred from the assigned value.
   3. C. As a string in the tag's key-value pair.
   4. D. As an OBJECT containing a typed numeric value.

   Answer: C

   Explanation: Snowflake states: "the tag value is always a string." Tag values are strings, including numeric-looking values. Assignment does not infer NUMBER/BOOLEAN or construct a typed OBJECT.

   Reference: Introduction to object tagging — What is a tag?

5. 5A reusable SQL function must return zero, one, or many rows with named output columns and be queried in a FROM clause. Which function form matches that interface?

   1. A. A scalar SQL UDF returning NUMBER.
   2. B. A scalar JavaScript UDF returning STRING.
   3. C. A SQL UDTF declared with RETURNS TABLE.
   4. D. An aggregate UDF returning one scalar per group.

   Answer: C

   Explanation: The guide says SQL UDTFs "return a set of rows, consisting of 0, 1, or multiple rows". They declare output columns through RETURNS TABLE and can be accessed in a query's FROM clause.

   Reference: Tabular SQL UDFs (UDTFs) — Introduction; Usage — RETURNS TABLE

6. 6A pipeline loads RFC4180-compliant, uncompressed CSV files larger than 128 MB. Which settings meet Snowflake's documented conditions for parallel scanning within these files?

   1. A. MULTI_LINE=TRUE, COMPRESSION=NONE, ON_ERROR=CONTINUE
   2. B. MULTI_LINE=FALSE, COMPRESSION=GZIP, ON_ERROR=CONTINUE
   3. C. MULTI_LINE=FALSE, COMPRESSION=NONE, ON_ERROR=CONTINUE
   4. D. MULTI_LINE=FALSE, COMPRESSION=NONE, ON_ERROR=SKIP_FILE

   Answer: C

   Explanation: The guide specifies parallel scanning "when MULTI_LINE is set to FALSE, COMPRESSION is set to NONE, and ON_ERROR is set to ABORT_STATEMENT or CONTINUE." Only this combination meets all the stated conditions.

   Reference: Preparing your data files — File sizing best practices — General file sizing recommendations

7. 7Query Profile shows a UnionAll operator with an expensive Aggregate above it. The SQL uses UNION, but the application permits duplicate output rows. Which rewrite removes the unnecessary duplicate-elimination work?

   1. A. Replace UNION with an additional SELECT DISTINCT.
   2. B. Replace UNION with UNION ALL.
   3. C. Add DISTINCT to each branch before UNION.
   4. D. Add an outer GROUP BY over every result column.

   Answer: B

   Explanation: The guide states: "UNION ALL simply concatenates inputs" while UNION also eliminates duplicates. Because duplicates are allowed, UNION ALL removes unnecessary aggregation without violating the result requirement.

   Reference: Monitor query activity with Query History — Common query problems — UNION without ALL

8. 8On an Enterprise account, a permanent table has DATA_RETENTION_TIME_IN_DAYS=1 and the account has MIN_DATA_RETENTION_TIME_IN_DAYS=3. What is the table's effective data retention period?

   1. A. 1 day
   2. B. 4 days
   3. C. 3 days
   4. D. 90 days

   Answer: C

   Explanation: The guide determines effective retention using "MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS)." The maximum of 1 and 3 is 3, rather than their sum.

   Reference: Understanding and using Time Travel — Enabling and deactivating Time Travel — minimum retention

9. 9A governance tag is assigned to a table at a higher level in the securable-object hierarchy. Under tag inheritance, which contained objects inherit that assignment?

   1. A. Only columns whose names match the tag name.
   2. B. Only columns selected in the most recent query.
   3. C. Only columns referenced by an existing masking policy.
   4. D. All columns in that table.

   Answer: D

   Explanation: The guide states: "if a tag is set on a table, the tag will be inherited by all columns in that table." Inheritance follows the object hierarchy rather than query activity or matching names.

   Reference: Introduction to object tagging — Key benefits — Tag inheritance

10. 10A team wants consumers to execute a UDF while restricting visibility of its definition to authorized owners. Which function attribute provides the documented definition-visibility protection?

    1. A. IMMUTABLE
    2. B. VOLATILE
    3. C. STRICT
    4. D. SECURE

    Answer: D

    Explanation: For a secure function, the guide says definition details "are visible only to authorized users". It identifies users granted the function-owning role as the authorized population.

    Reference: Secure UDFs and stored procedures — Limiting the visibility of a UDF or procedure definition