SnowPro Advanced Data Engineer common mistakes
SnowPro Advanced Data Engineer · Understand the traps and how to avoid them.
Re-running COPY INTO against the same stage file and expecting the rows to load again, without setting FORCE=TRUE or renaming the file.
Snowflake records file-load metadata and skips files already loaded within the retention window; candidates often assume COPY INTO is idempotent only by accident or that it always re-reads the stage.
What to do instead
Treat load metadata as the default dedupe mechanism. Use FORCE=TRUE only for deliberate reloads, verify with COPY_HISTORY and VALIDATION_MODE, and design stage file names so retries are distinguishable.
Setting delimiter, header, or compression options on the stage but overriding them with a named file format in COPY INTO, then debugging errors as if the stage settings were used.
COPY INTO file-format options take precedence over the stage definition; candidates memorize stage creation syntax but not the precedence rules.
What to do instead
Decide where the file format lives and keep one source of truth. Use DESCRIBE STAGE and a small VALIDATION_MODE test before loading, and explicitly pass the same file format when testing.
Using LATERAL FLATTEN on a VARIANT array without OUTER=>TRUE and not noticing that parent rows with empty or missing arrays disappear.
FLATTEN behaves like an inner lateral join by default; candidates focus on extracting fields and forget join semantics on empty arrays.
What to do instead
Use LATERAL FLATTEN(INPUT => ..., OUTER => TRUE) when parent rows must survive, test with empty arrays and NULLs, and compare row counts before and after the flatten step.
Running MERGE with a source that has multiple rows per target key and expecting a deterministic last-write-wins result.
Snowflake does not guarantee which duplicate source row wins and can raise a duplicate-row error; candidates often test only with unique source data.
What to do instead
Deduplicate the source before MERGE using QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) or an aggregate, and add a uniqueness check on the source key as part of the pipeline.
Relying on PRIMARY KEY, UNIQUE, or FOREIGN KEY constraints to reject duplicate or orphan rows in production.
Snowflake does not enforce these constraints except NOT NULL; they are metadata and optimizer hints, but candidates carry relational-database assumptions into schema design.
What to do instead
Use constraints for documentation and optimizer hints only. Enforce data quality with MERGE logic, streams and tasks, tests in the transformation layer, or explicit validation queries.
Choosing a clustering key on a very high-cardinality column such as a UUID or a second-level timestamp and expecting every query to get faster.
Clustering helps selective range and equality predicates on large tables, but high-cardinality keys increase clustering depth and maintenance cost; candidates equate clustering with indexing.
What to do instead
Cluster on columns used in frequent selective filters with moderate cardinality. Validate with SYSTEM$CLUSTERING_INFORMATION and compare partitions scanned in Query Profile before and after.
Responding to a slow query by resizing the warehouse larger before checking partition pruning, joins, spilling, or concurrency queueing.
Many performance problems are not compute-size problems; scaling up does not fix poor pruning or exploding joins, and scaling out is needed for concurrency.
What to do instead
Read Query Profile first: partitions scanned versus total, bytes spilled, join row counts, and queue time. Scale up for memory-heavy queries, scale out with multi-cluster warehouses for concurrency, and fix pruning or join logic before resizing.
Assuming the result cache or local disk cache will be reused after changing the session role or after DML changes the underlying table.
Result cache reuse requires identical query text, the same role, and unchanged micro-partitions; warehouse cache is local to a warehouse and is lost when it suspends. Candidates treat caching as unconditional.
What to do instead
Design for correct results first, then check cache behavior in QUERY_HISTORY. Test performance with the same role and query text, and do not rely on cache hits for SLA-critical pipelines.
Granting SELECT on a table to a role but leaving the user unable to query it because USAGE on the database and schema was never granted.
Privileges are hierarchical: object access also requires USAGE on container objects, and the role must be granted to the user and active in the session. Candidates test only the final object grant.
What to do instead
Grant USAGE on the database and schema, grant the role to the user, and add future grants for new schemas and tables. Verify by running the query with the target role, not as ACCOUNTADMIN.
Creating a scheduled task and expecting it to run immediately, without resuming the task or its child tasks in a DAG.
Tasks are created suspended and must be resumed; child tasks in a DAG also need to be active, and the task runs with its owner role. Candidates validate task SQL manually but not task state.
What to do instead
Resume the root task and dependent tasks, confirm the owner role has the needed privileges and warehouse, and check TASK_HISTORY after the first scheduled run.