DAA-C01 Question 49
Single answerImplement constraintsA retail analytics team is building a star schema in Snowflake. The FACT_SALES table will be loaded from multiple upstream systems, and analysts want BI tools to understand the relationship between FACT_SALES and DIM_CUSTOMER. The team also wants Snowflake to reject any sales rows whose CUSTOMER_KEY does not exist in DIM_CUSTOMER. DIM_CUSTOMER is created as a standard Snowflake table, not a hybrid table.
Which action should the data analyst recommend?
- A
Define a FOREIGN KEY on FACT_SALES(CUSTOMER_KEY) referencing DIM_CUSTOMER(CUSTOMER_KEY), because Snowflake will enforce referential integrity on standard tables.
- B
Define the FOREIGN KEY so metadata-aware BI tools can use the relationship, but implement data quality checks in the load process because Snowflake does not enforce foreign keys on standard tables.
- C
Create a masking policy on FACT_SALES.CUSTOMER_KEY so invalid customer keys are blocked during INSERT operations.
- D
Convert DIM_CUSTOMER into a temporary table so the FOREIGN KEY becomes enforced during the session.
Show answer and explanation
Correct answer: B
Explanation
The best recommendation is to declare the FOREIGN KEY for metadata purposes while enforcing data quality through the pipeline. In Snowflake, constraints such as PRIMARY KEY, UNIQUE, and FOREIGN KEY on standard tables are primarily declarative and not enforced. This means they can help with schema documentation and can be consumed by downstream tools, but they do not guarantee referential integrity at insert or update time. If the business requirement is to reject FACT_SALES rows whose CUSTOMER_KEY does not exist in DIM_CUSTOMER, the validation must be implemented in ETL/ELT logic, for example by joining to the dimension table before insert, isolating invalid rows in an error table, or using a task/procedure-driven quality check. This aligns with Snowflake best practices for implementing constraints on standard tables and understanding the distinction between declared versus enforced constraints.
- A. Incorrect.
Incorrect. In Snowflake, PRIMARY KEY, UNIQUE KEY, and FOREIGN KEY constraints on standard tables are generally informational and are not enforced. Defining the FOREIGN KEY can document the relationship and may help some tools with metadata, but Snowflake will not reject invalid CUSTOMER_KEY values in a standard table based on that constraint alone.
- B. Correct.
Correct. For standard Snowflake tables, key constraints are not enforced, so they should not be relied on to reject bad data. However, defining the FOREIGN KEY can still be useful for documentation and for BI/query tools that read table metadata. To actually prevent or detect orphaned fact rows, the team should implement validation logic in ETL/ELT, such as anti-join checks, MERGE filtering, or load-time exception handling.
- C. Incorrect.
Incorrect. Masking policies are used to control how sensitive data is displayed to users at query time, not to enforce referential integrity between fact and dimension tables. They do not validate whether a foreign key value exists in another table during DML operations.
- D. Incorrect.
Incorrect. Temporary tables differ in lifecycle and session scope, but changing DIM_CUSTOMER to a temporary table does not make FOREIGN KEY constraints enforced. Constraint enforcement behavior is not enabled this way for standard Snowflake tables.