DAA-C01 Question 46
Single answerDefine primary keys for tablesA retail analytics team is redesigning its dimensional model in Snowflake. The FACT_SALES table joins to DIM_CUSTOMER on CUSTOMER_ID, and BI developers want the schema to clearly document that each customer row in DIM_CUSTOMER is uniquely identified by CUSTOMER_ID. They also know that duplicate CUSTOMER_ID values can still arrive because upstream ingestion is not fully controlled. Which action should the data analyst take to define the key correctly in Snowflake while setting the right expectation for behavior?
- A
Add a PRIMARY KEY constraint on DIM_CUSTOMER(CUSTOMER_ID), understanding that on a standard Snowflake table this is metadata and is not enforced.
- B
Create a UNIQUE index on DIM_CUSTOMER(CUSTOMER_ID) so Snowflake blocks duplicate values during loading.
- C
Add a PRIMARY KEY constraint on FACT_SALES(CUSTOMER_ID) because primary keys should be defined on the foreign-key column used in joins.
- D
Convert DIM_CUSTOMER to a temporary table first, then add a PRIMARY KEY constraint so Snowflake enforces uniqueness only for the session.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, a PRIMARY KEY can be defined to identify the intended unique row identifier for a table, which is useful for documentation, schema readability, and interoperability with modeling tools. For a dimension table such as DIM_CUSTOMER, CUSTOMER_ID is the appropriate column on which to define the primary key if it uniquely identifies each customer row. However, candidates must know that on standard Snowflake tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are generally informational and not enforced. Therefore, if duplicate values are a risk, the data team must implement separate data quality controls in ELT pipelines, tasks, streams, or validation queries. This aligns with Snowflake documentation on constraints, which explains that most table constraints are optional metadata for standard tables rather than enforced integrity rules.
- A. Correct.
Correct. In Snowflake, you can define a PRIMARY KEY constraint on a table column to document intended uniqueness and support data model clarity. However, for standard Snowflake tables, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are generally not enforced. This means duplicate CUSTOMER_ID values can still be loaded unless additional ETL or validation logic is implemented. This is the right choice because it defines the key correctly and reflects Snowflake's actual behavior.
- B. Incorrect.
Incorrect. Snowflake does not support traditional user-managed indexes such as a UNIQUE index for enforcing uniqueness. This option reflects a common misconception from other relational database platforms. In Snowflake, indexing is not used this way, and uniqueness is not enforced through a user-created unique index.
- C. Incorrect.
Incorrect. CUSTOMER_ID in FACT_SALES is typically a foreign key referencing the customer dimension, not the primary key of the fact table. A primary key on a dimension should be defined on the dimension table's unique business or surrogate identifier, not on the corresponding join column in the fact table. This option confuses primary keys with foreign keys.
- D. Incorrect.
Incorrect. Temporary tables do not change constraint enforcement behavior. Snowflake does not enforce PRIMARY KEY constraints on standard tables simply because they are temporary. This distractor targets the misconception that table type affects relational constraint enforcement in this way.