DAA-C01 Question 50
Single answerImplement constraintsA retail analytics team is redesigning its Snowflake star schema. The team wants BI users to rely on key relationships for easier query authoring, but they also want to avoid performance overhead during high-volume batch loads into large fact and dimension tables. The data is already validated upstream in an ETL process. Which approach should the data analyst recommend to implement these requirements in Snowflake?
- A
Define PRIMARY KEY and FOREIGN KEY constraints on the standard tables and leave them as the default enforced constraints so Snowflake validates every load.
- B
Define PRIMARY KEY and FOREIGN KEY constraints on the standard tables as NOT ENFORCED so the relationships are documented for tooling and metadata without adding load-time validation overhead.
- C
Use UNIQUE constraints only on the fact table because Snowflake enforces UNIQUE constraints on standard tables and this is the best way to guarantee referential integrity.
- D
Convert the fact and dimension tables to temporary tables because temporary tables support metadata-only constraints that do not affect loading performance.
Show answer and explanation
Correct answer: B
Explanation
The best recommendation is to define PRIMARY KEY and FOREIGN KEY constraints as NOT ENFORCED on the star schema tables. In Snowflake, constraints on standard tables are primarily metadata for most workloads rather than fully enforced integrity rules. This is useful in analytical environments where data quality is often validated upstream and where documenting relationships helps BI tools, data modelers, and query authors. Snowflake documentation notes that for standard tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are not enforced in the same way as in traditional transactional databases. As a best practice, teams should not rely on Snowflake to enforce referential integrity on standard analytical tables; instead, they should validate data in ETL/ELT pipelines and use constraints to represent intended relationships.
- A. Incorrect.
Incorrect. In Snowflake, constraints on standard tables are generally informational and not enforced for most constraint types such as PRIMARY KEY, FOREIGN KEY, and UNIQUE. The premise that Snowflake will validate every load on standard tables because the constraints are 'default enforced' is inaccurate. Choosing this option reflects a common misconception carried over from traditional OLTP databases.
- B. Correct.
Correct. On standard Snowflake tables, PRIMARY KEY and FOREIGN KEY constraints can be created primarily to document relationships and support metadata-aware tools, while avoiding the overhead of enforcement during batch ingestion. This matches the scenario: the ETL process already validates data upstream, and the team wants relationship metadata for BI/querying without load-time constraint checking.
- C. Incorrect.
Incorrect. UNIQUE constraints on standard tables are also not enforced in the way many relational databases enforce them. In addition, a fact table typically contains repeating dimension keys, so using UNIQUE on a fact table would usually be semantically wrong. This option mixes an incorrect assumption about enforcement with poor dimensional modeling practice.
- D. Incorrect.
Incorrect. Temporary tables are a lifecycle/storage choice, not a mechanism for implementing useful relational metadata for a shared star schema. They are session-scoped and unsuitable for persistent fact and dimension tables used by BI users. This option confuses table type with constraint behavior.