Databricks Certified Data Analyst Associate free question 9 / 10 · Domain 8: Data Modeling with Databricks SQL
Each source order inserts a row into fact_orders and updates dim_customer, in two statements. Sometimes the fact insert succeeds and the dimension update fails. What does the data modeling page say about this by default?
- The two statements form one transaction, so the fact insert is rolled back too.
- Databricks retries the failed update until both tables are in sync.
- Catalog commits are on by default, so both writes share one transaction.
- Transactions are scoped to single tables; each write succeeds or fails alone.
Show the answer and explanation
Answer: D
Why this is correct
The data modeling page says that by default Databricks scopes transactions to individual tables. When ingesting a source record requires inserting or updating rows in two or more tables, those are independent transactions that can succeed or fail on their own, and downstream queries need to be tolerant of a state mismatch. Tables with catalog commits enabled can take part in transactions that span multiple statements and tables.
Why the other options are wrong
"The two statements form one transaction, so the fact insert is rolled back too." … By default the writes are separate transactions. The fact insert is not rolled back when the dimension update fails.
"Catalog commits are on by default, so both writes share one transaction." … By default Databricks scopes transactions to individual tables. Only tables with catalog commits enabled can take part in transactions that span several tables.
"Databricks retries the failed update until both tables are in sync." … Each transaction can succeed or fail independently of the others, and the page puts the burden on downstream queries to tolerate a state mismatch.
Designing for it
Analysts reading a star schema loaded this way should expect brief gaps, such as a fact row whose dimension row has not been updated yet, and design joins and checks that tolerate them.