Databricks Certified Data Engineer Professional free question 10 / 10 · Domain 9: Data Modeling
You declared PRIMARY KEY (product_id) on the dimension dim_product and a FOREIGN KEY to dim_product on the fact fact_sales. Because of an upstream bug, an INSERT that adds one more row with the same product_id to dim_product ran. What happens?
- The INSERT succeeds and the duplicate row goes in; primary and foreign keys are not enforced
- The INSERT succeeds, and because of the primary key declaration the later row replaces the earlier row
- The INSERT succeeds, but the next write to fact_sales fails the foreign key check on product_id
- The INSERT fails with a primary key violation, and dim_product is left unchanged by the failed statement
Show the answer and explanation
Answer: A
Why this is correct
Under Primary and foreign keys on Databricks, primary and foreign keys are informational and not enforced. This is the same as many cloud databases but differs from many traditional relational databases. Even if declared, an INSERT with a duplicate key does not fail, so two rows with the same product_id end up in dim_product. In a star schema, duplicates in a dimension multiply rows when joined with the fact and inflate aggregates.
Why the other options are wrong
"The INSERT fails with a primary key violation, and dim_product is left unchanged by the failed statement" … Primary and foreign keys in Databricks are informational declarations and are not enforced. An INSERT of a duplicate key goes through without failing. Uniqueness of keys has to be guarded on the pipeline side.
"The INSERT succeeds, but the next write to fact_sales fails the foreign key check on product_id" … A foreign key is, like a primary key, an informational declaration and is not checked at write time. Writes to fact_sales do not fail because of the foreign key either.
"The INSERT succeeds, and because of the primary key declaration the later row replaces the earlier row" … A primary key declaration has no function of replacing a row with the same key, and since it is not enforced, both rows go in as they are. If you want to replace by key, write with MERGE or AUTO CDC.
Related setting
By default Databricks confines a transaction to a single table. If one record's ingestion writes to several tables, each succeeds or fails independently, so downstream should tolerate inconsistency between tables. For a new model, star or snowflake schemas are recommended over heavily normalized models such as third normal form.