DAA-C01 Question 44
Select 21.4 Use best practice considerations relating to data integrity structures.A retail analytics team loads daily order data from multiple source systems into a Snowflake table named FACT_ORDERS. Analysts have discovered duplicate ORDER_ID values, missing CUSTOMER_ID values, and occasional references to PRODUCT_ID values that do not exist in the product dimension. The team wants to improve data integrity for analytics workloads while minimizing unnecessary performance overhead and avoiding assumptions that Snowflake will automatically block bad data. Which TWO actions are the best practice recommendations in this scenario?
- A
Define PRIMARY KEY and FOREIGN KEY constraints on the relevant tables for documentation and optimizer metadata, and enforce data quality in ETL/ELT logic before or during loads
- B
Rely on standard PRIMARY KEY and FOREIGN KEY constraints on Snowflake standard tables to reject duplicate and orphaned records during INSERT operations
- C
Add NOT NULL constraints to required columns such as CUSTOMER_ID where nulls should not be allowed, because Snowflake enforces NOT NULL on standard tables
- D
Create materialized views on FACT_ORDERS to automatically prevent duplicate ORDER_ID values from being loaded
- E
Use Streams on FACT_ORDERS as the primary mechanism to guarantee referential integrity between FACT_ORDERS and DIM_PRODUCT
Show answer and explanation
Correct answers: A, C
Explanation
The best answers are to use informational key constraints together with pipeline-based validation, and to enforce NOT NULL where appropriate. Snowflake best practices distinguish between enforced and non-enforced integrity structures. On standard tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are typically not enforced, so they should not be relied on to reject bad data. Instead, data engineers should implement integrity checks in ETL/ELT processes, for example by deduplicating on ORDER_ID, validating dimension lookups, or using MERGE patterns and exception handling before loading curated tables. By contrast, NOT NULL constraints are enforced and are a practical native control for required columns. This aligns with Snowflake documentation on constraints, which notes that most key constraints on standard tables are metadata-only, while NOT NULL is enforced.
- A. Correct.
Correct. In Snowflake, PRIMARY KEY and FOREIGN KEY constraints on standard tables are generally informational rather than enforced. They are still useful for communicating data model intent and, in some cases, providing metadata that can help query optimization. Because they do not automatically reject invalid data on standard tables, best practice is to validate and deduplicate data in ingestion or transformation pipelines using ETL/ELT logic, tasks, merge patterns, or quality checks.
- B. Incorrect.
Incorrect. This reflects a common misconception from traditional relational databases. On Snowflake standard tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are typically not enforced. Defining them does not cause INSERT statements to reject duplicates or orphaned rows. Assuming they are enforced can lead to poor data quality controls.
- C. Correct.
Correct. NOT NULL constraints are enforced by Snowflake on standard tables and are appropriate for columns that must contain values, such as mandatory business keys or foreign key references required for analytics. This is one of the few native integrity structures that actively prevents invalid data from being stored, so it is a strong best practice when nulls are not valid.
- D. Incorrect.
Incorrect. Materialized views improve performance for repeated query patterns by storing precomputed results, but they are not a data integrity mechanism. They do not prevent duplicate rows from being inserted into the base table and should not be used as a substitute for data quality validation or constraint design.
- E. Incorrect.
Incorrect. Streams capture change data for downstream processing and are useful for CDC-style workflows, but they do not enforce referential integrity. A stream can help detect changes that need validation, but by itself it does not guarantee that PRODUCT_ID values in the fact table exist in the product dimension.