DAA-C01 Question 43
Single answer1.4 Use best practice considerations relating to data integrity structures.A retail analytics team is building a curated SALES_FACT table in Snowflake from multiple upstream sources. Analysts have reported duplicate order lines, orphaned customer IDs, and occasional negative quantities caused by bad source data. The team wants to improve data integrity while keeping query performance high and avoiding misleading assumptions about enforcement behavior in Snowflake. Which approach is the BEST choice?
- A
Define PRIMARY KEY, FOREIGN KEY, and CHECK constraints on the standard SALES_FACT table so Snowflake will enforce uniqueness, referential integrity, and valid quantity values during loads.
- B
Create a hybrid SALES_FACT table and define the appropriate PRIMARY KEY, FOREIGN KEY, and CHECK constraints so Snowflake can enforce those integrity rules at write time.
- C
Add NOT NULL constraints to all columns in the standard SALES_FACT table, since NOT NULL constraints on standard tables also guarantee uniqueness and valid business-rule ranges.
- D
Rely on clustering keys on ORDER_ID and CUSTOMER_ID in the standard SALES_FACT table, because clustering prevents duplicates and orphaned references by physically organizing related rows together.
Show answer and explanation
Correct answer: B
Explanation
The key best-practice consideration is understanding which Snowflake data integrity structures are actually enforced versus merely documented as metadata. In standard Snowflake tables, many constraints such as PRIMARY KEY, UNIQUE, and FOREIGN KEY are not enforced, so they should not be relied on to prevent bad data. That makes option 1 a trap based on a common misunderstanding. If the requirement is true write-time enforcement of relational integrity and business rules, hybrid tables are the Snowflake feature designed for this purpose. They support enforced constraints and are appropriate when data integrity must be guaranteed by the platform rather than handled only in ETL or downstream validation. NOT NULL alone is too limited, and clustering is purely a performance optimization, not an integrity mechanism. This aligns with Snowflake documentation on constraints and hybrid tables, which distinguishes informational constraints on standard tables from enforced constraints on hybrid tables.
- A. Incorrect.
Incorrect. On standard Snowflake tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are informational for most table types and are not enforced. Assuming they will block duplicate or orphaned data is a common misconception. CHECK constraints are also not the right answer here for guaranteed enforcement on a standard table in this scenario.
- B. Correct.
Correct. Hybrid tables support enforced constraints, including PRIMARY KEY and FOREIGN KEY, and are designed for workloads that need transactional integrity controls. Using a hybrid table with the appropriate constraints is the best way to have Snowflake enforce data integrity rules such as uniqueness, referential integrity, and valid values at write time.
- C. Incorrect.
Incorrect. NOT NULL can help prevent missing values where supported, but it does not enforce uniqueness, referential integrity, or domain rules like preventing negative quantities. This option confuses nullability with broader integrity controls.
- D. Incorrect.
Incorrect. Clustering keys improve pruning and query performance for selective filters, but they do not enforce data quality rules. Physical organization does not prevent duplicate rows, invalid foreign keys, or negative measures.