DAA-C01 Question 45
Single answerDefine primary keys for tablesA data analyst is redesigning a star schema in Snowflake for a BI workload. The fact table FACT_SALES will join to DIM_CUSTOMER on CUSTOMER_ID, and the team wants the table definitions to clearly document the intended uniqueness of each dimension row. They also know upstream ETL occasionally loads duplicate CUSTOMER_ID values by mistake. Which approach should the analyst take when defining the primary key on DIM_CUSTOMER?
- A
Define PRIMARY KEY (CUSTOMER_ID) on DIM_CUSTOMER to document the intended key, but also implement separate data quality checks because Snowflake does not enforce primary key constraints on standard tables.
- B
Define PRIMARY KEY (CUSTOMER_ID) on DIM_CUSTOMER because Snowflake will reject any future duplicate CUSTOMER_ID values automatically.
- C
Do not define a primary key in Snowflake because primary keys are only supported on temporary tables and not on permanent dimension tables.
- D
Create a unique clustering key on CUSTOMER_ID instead of a primary key, because clustering keys enforce row uniqueness during loads.
Show answer and explanation
Correct answer: A
Explanation
For Snowflake standard tables, PRIMARY KEY constraints can be defined to express relational design intent, but they are not enforced in the same way as in many transactional databases. That means Snowflake will not automatically block duplicate key values in a dimension table simply because a primary key was declared. In a real-world analytics environment, the best practice is to define the primary key for metadata clarity and schema design consistency, while also implementing ETL or data quality controls such as duplicate detection queries, MERGE logic, staging validations, or downstream monitoring. Snowflake documentation on constraints explains that UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are supported but generally not enforced on standard tables, making them primarily informational for most analytic workloads.
- A. Correct.
Correct. In Snowflake, PRIMARY KEY constraints on standard tables are generally informational rather than enforced. They are useful for documenting data model intent and can help with metadata, modeling clarity, and some optimizer assumptions in certain contexts, but they do not prevent duplicate values from being inserted into standard tables. Therefore, if upstream ETL can introduce duplicates, the analyst should define the primary key for semantic clarity and separately implement validation or deduplication logic.
- B. Incorrect.
Incorrect. This reflects a common misconception from traditional OLTP databases. In Snowflake standard tables, defining a PRIMARY KEY does not cause Snowflake to enforce uniqueness by rejecting duplicate rows. Relying on the constraint alone would leave the table vulnerable to duplicate CUSTOMER_ID values.
- C. Incorrect.
Incorrect. Snowflake supports defining primary key constraints on permanent, transient, temporary, and other standard table types. The issue is not lack of support on permanent tables, but that these constraints are typically not enforced on standard tables.
- D. Incorrect.
Incorrect. Clustering keys are used to improve pruning and query performance for large tables by co-locating related data in micro-partitions. They do not enforce uniqueness and are not a substitute for a primary key constraint. A clustering key on CUSTOMER_ID would not prevent duplicate customer rows from being loaded.