SnowPro Advanced: Data Engineer (DEA-C02) — Practice Questions and Mock Exam

MATCH_BY_COLUMN_NAME (CASE_SENSITIVE or CASE_INSENSITIVE) in COPY INTO <table> maps named fields in semi-structured data (JSON, Avro, Parquet, ORC) to target table columns by name rather than ordinal position. Unmatched source keys are silently ignored; unmatched target columns receive NULL. Default is NONE (positional mapping). (Ref: docs.snowflake.com/en/sql-reference/sql/copy-into-table) Why other options are wrong: “It enforces strict schema validation and rejects files…”) Extra source columns are silently ignored, not rejected — there is no strict schema validation enforcement. “It creates new columns in the target table…”) Snowflake never auto-alters the target table schema during COPY operations to add new columns. “It renames the source columns to match the target table…”) The option matches names as-is; it does not rename any source columns.

ACCESS_HISTORY in SNOWFLAKE.ACCOUNT_USAGE records column-level access for all queries. It captures direct_objects_accessed (columns in SELECT output) and base_objects_accessed (all source columns including those in JOINs/WHERE). Also tracks objects_modified for DML. Retains 365 days. Requires Enterprise Edition+. (Ref: docs.snowflake.com/en/sql-reference/account-usage/access_history) Why other options are wrong: “QUERY_HISTORY”) QUERY_HISTORY tracks query execution metadata (duration, warehouse, status) but does not record which specific columns were accessed. “LOGIN_HISTORY”) LOGIN_HISTORY records authentication events (logins, failures, client info), not query-level data access patterns. “COLUMNS”) COLUMNS is an Information Schema view listing column metadata (names, data types, defaults) — it does not track access.

UNDROP TABLE restores a dropped table within its Time Travel retention period. If a table with the same name already exists in the schema, UNDROP returns an error. The workaround: rename the conflicting table first (ALTER TABLE ORDERS RENAME TO ORDERS_TEMP), then execute UNDROP TABLE ORDERS to restore the original. (Ref: docs.snowflake.com/en/sql-reference/sql/undrop-table) Why other options are wrong: “No, the original table is permanently lost when a new table with the same name is created”) The original table is NOT permanently lost — Time Travel retains dropped table data for the configured retention period (up to 90 days on Enterprise+). “Yes, by using UNDROP TABLE ORDERS with a query ID from before the drop”) UNDROP TABLE does not accept a query ID parameter — it operates on the most recently dropped object with that name. “Yes, by using UNDROP TABLE ORDERS which automatically renames the current table”) UNDROP does not automatically rename the current table — it fails with a name conflict error that must be resolved manually.

A UUID column is the LEAST effective clustering key because its extremely high cardinality (unique per row) prevents meaningful micro-partition pruning. Effective clustering keys group rows into overlapping ranges so the query optimizer can skip irrelevant partitions. With UUIDs, each micro-partition contains random unique values, meaning no partitions can be pruned—resulting in full table scans. Re-clustering costs are also excessive. Distractor A (date with range filters) and C/D (low-to-medium cardinality with equality filters) are good clustering key candidates enabling effective partition pruning. Ideal keys have low-to-medium cardinality and appear frequently in WHERE or JOIN clauses. Ref: docs.snowflake.com/en/user-guide/tables-clustering-keys.