Data Modelling: Databricks Data Engineer Professional Practice Questions

Correct answer: E

WHY E: APPLY CHANGES INTO ... KEYS (customer_id) SEQUENCE BY sequenceNum STORED AS SCD TYPE 2 is the idiomatic Lakeflow/DLT construct for SCD Type 2: it keys on the business key, orders changes by the sequence column, and automatically maintains historical versions with validity boundary columns. WHY NOT B: STORED AS SCD TYPE 1 overwrites in place; Change Data Feed records changes for downstream consumers but does not turn a Type 1 dimension into a queryable Type 2 history with validity columns. WHY NOT C: The AUTO CDC/APPLY CHANGES API natively supports STORED AS SCD TYPE 2, so hand-coded MERGE is unnecessary; the premise that only Type 1 is supported is false. WHY NOT D: SEQUENCE BY is required to order CDC changes correctly; omitting it is not how Type 2 ordering works and would leave change ordering undefined. WHY NOT A: The sequence column must NOT be part of KEYS; keys identify the business entity, and adding sequenceNum to the key would make every change a separate entity, breaking the dimension.

Correct answer: B

WHY B is correct: Databricks best practices explicitly state: 'When deleting and recreating a table in the same location, you should always use a CREATE OR REPLACE TABLE statement.' The CREATE OR REPLACE TABLE command performs the drop and recreate as a single ACID transaction, ensuring no partial states and no risk of orphaned transaction log entries or metadata inconsistencies at the storage location. It is deterministic and safe for concurrent access during the operation. WHY NOT A: Running DROP TABLE followed by a separate CREATE TABLE is a two-step non-atomic operation. Between the DROP and the CREATE, the table location is in an undefined state — any concurrent reader or job that queries between these two statements would fail or see no data. This is the explicit anti-pattern that CREATE OR REPLACE TABLE is designed to replace. WHY NOT C: Delta Lake documentation explicitly warns: 'Do not manually modify, add, or delete Parquet data files in a Delta table, because this can lead to lost data or table corruption.' Delta relies on the transaction log to track all file additions and removals — bypassing the transaction log by directly deleting files corrupts the table state. This is one of the key ways Delta Lake differs from raw Parquet. WHY NOT D: ALTER TABLE ... SET SCHEMA (or equivalently schema evolution operations) modifies the column definitions of an existing table but does not drop and recreate the table or its underlying data. When the goal is a full table replacement with new data and structure at the same location, CREATE OR REPLACE TABLE is the correct command.

Correct answer: C

WHY C is correct: The requirement to preserve full historical records with effective date ranges so that historical fact joins always reflect the correct dimension state at the time of the event is the canonical definition of SCD Type 2. Under SCD Type 2, when a customer's address changes, a new row is inserted for that customer with a new surrogate key, effective_start_date, effective_end_date, and an is_current flag. The old row's end date is updated to mark it as expired. Historical fact table rows, which reference the old surrogate key, continue to join correctly to the historical customer address. Databricks explicitly supports this via Lakeflow Spark Declarative Pipelines: 'Lakeflow Spark Declarative Pipelines has native support for tracking and applying SCD Type 1 and Type 2. Use AUTO CDC ... INTO with Lakeflow Spark Declarative Pipelines to ensure that out of order records are handled correctly when processing CDC feeds.' WHY NOT A: SCD Type 1 uses an in-place overwrite of the existing row (via MERGE WHEN MATCHED THEN UPDATE). This gives the simplest result but permanently destroys historical attribute values. A historical sales order cannot be correctly attributed to the customer's old address because only the newest address is stored. SCD Type 1 does not meet the requirement to preserve historical records. WHY NOT B: SCD Type 3 adds a fixed number of 'previous value' columns (e.g., previous_address, current_address) to the dimension row. This approach retains exactly ONE level of history — only the most recent change. If the customer changes their address three times, only the most recent two addresses are preserved. This does not meet the requirement for full historical tracking with arbitrary change history. WHY NOT D: SCD Type 0 means no changes are ever applied to dimension records after initial load — the dimension is 'fixed'. This would mean the customer's address NEVER gets updated in the dimension table, which fails the requirement to reflect any address changes at all. Additionally, Delta Lake's transaction log does not prevent row-level updates; Delta explicitly supports UPDATE, MERGE, and DELETE operations — ACID compliance means these operations are atomic and consistent, not that they are blocked.

Correct answer: E

WHY E: Liquid Clustering uses CLUSTER BY keys instead of rigid partition directories, so it avoids the small-partition/skew problems that fixed partitioning causes, clusters newly written data incrementally (no full table rewrite), and — importantly — allows changing the clustering keys later without rewriting existing data. This directly targets all three observed problems. WHY NOT B: Liquid Clustering does NOT create per-value physical directories like Hive partitioning; that directory model is exactly what it replaces, and it does not require rewriting to change keys. WHY NOT C: Liquid Clustering supports multiple clustering columns, so you can cluster on both event_date and customer_id without Hive partitioning. WHY NOT D: CLUSTER BY is not implemented by requiring a manual ZORDER after each write; clustering is maintained by Delta's clustering/OPTIMIZE machinery, and ZORDER is a separate, older technique. WHY NOT A: Liquid Clustering still benefits from OPTIMIZE (or predictive optimization) to cluster accumulated data; writes are not guaranteed to be perfectly fully sorted with zero maintenance.

Correct answer: B

WHY B is correct: The Databricks documentation explicitly states: 'Z-order works in tandem with the OPTIMIZE command. You cannot combine files across partition boundaries, and so Z-order clustering can only occur within a partition. For unpartitioned tables, files can be combined across the entire table.' This is a fundamental constraint: on a partitioned table, OPTIMIZE ZORDER BY runs independently per partition, meaning that files from different partitions are never co-sorted together. This limits the effectiveness of ZORDER on multi-partition queries. Liquid clustering, by contrast, organizes data across all files in the table regardless of any partition structure, and does not have a partition-boundary limitation. WHY NOT A: This is exactly backwards. Z-ordering actually works BETTER on unpartitioned tables because files can be combined across the entire table. On partitioned tables, ZORDER is constrained to within-partition boundaries. Z-ordering can be applied to both partitioned and unpartitioned tables. WHY NOT C: Z-ordering is a long-standing Delta Lake feature available in all supported Databricks Runtime versions — it does not require Runtime 15.2. Runtime 15.2 is the Databricks Runtime version at which liquid clustering GA support for Delta Lake tables was introduced, not ZORDER. This swaps the version requirement of the two features. WHY NOT D: Z-ordering requires that statistics be collected on the columns used in ZORDER BY (documentation: 'Z-ordering on columns that do not have statistics collected on them would be ineffective'). However, statistics collection can be configured at any time using ALTER TABLE SET TBLPROPERTIES with dataSkippingStatsColumns or dataSkippingNumIndexedCols. Statistics columns are not frozen at table creation time.

Correct answer: D

WHY D is correct: The Databricks documentation precisely describes automatic liquid clustering as follows: (1) 'Analyzes query workload: Databricks analyzes the table's historical query workload and identifies the best candidate columns for clustering.' (2) 'Adapts to changes: If your query patterns or data distributions change over time, automatic liquid clustering selects new keys to optimize performance.' (3) 'Cost-aware selection: Databricks changes clustering keys only when the predicted cost savings from data skipping improvements outweigh the data clustering cost.' Additionally, automatic key selection requires predictive optimization and runs asynchronously as a maintenance operation, not during writes. WHY NOT A: Automatic liquid clustering does NOT select a fixed column at creation time and hold it permanently. Its defining characteristic is adaptation — if query patterns change over time, the platform selects new clustering keys to reflect the updated workload. Cardinality alone does not determine key selection; historical query filter patterns are the primary signal. WHY NOT B: Automatic liquid clustering (CLUSTER BY AUTO) does NOT require the engineer to specify initial clustering columns. The entire point is that Databricks automatically selects the keys based on workload analysis. Engineers simply specify CLUSTER BY AUTO (or ALTER TABLE ... CLUSTER BY AUTO) and the platform handles key selection entirely. WHY NOT C: Automatic liquid clustering does NOT recluster all records on every INSERT. OPTIMIZE operations trigger the incremental physical reclustering, and they run asynchronously via predictive optimization. Write operations may apply clustering on write only when data in the transaction meets a defined size threshold. Reclustering ALL records on every write would be prohibitively expensive for large tables.

Correct answer: A

WHY A is correct: Liquid clustering is the Databricks-recommended approach for this scenario based on multiple matching criteria documented explicitly: (1) 'Tables that are often filtered by high cardinality columns' — customer_id with 10 million distinct values is a canonical high-cardinality case where partitioning would create millions of tiny partitions but liquid clustering handles efficiently. (2) 'Tables that have concurrent write requirements' — liquid clustering is specifically cited as benefiting tables with concurrent writes, unlike traditional partitioning which can cause more write conflicts. (3) If access patterns change (e.g., new filters emerge), clustering keys can be changed via ALTER TABLE CLUSTER BY without rewriting all existing data. (4) Databricks recommends liquid clustering for all new tables. WHY NOT C: Partitioning by customer_id with 10 million distinct values would create 10 million partitions — a classic over-partitioning problem. Databricks explicitly warns: 'Tables where a typical partition key could leave the table with too many or too few partitions' are a scenario that particularly benefits from liquid clustering instead. Over-partitioned tables suffer from severe metadata overhead, too many small files, and poor query performance. WHY NOT B: While pairing date partitioning with ZORDER BY (customer_id) is a valid legacy strategy, Databricks now recommends liquid clustering as the successor to this approach. Additionally, ZORDER is not idempotent, cannot cross partition boundaries, and requires full OPTIMIZE re-runs after each batch of writes. Databricks documentation states: 'Databricks recommends using liquid clustering for all new tables. You cannot use ZORDER in combination with liquid clustering.' WHY NOT D: While tables under 1 TB generally don't need partitioning, the specific access pattern here — primarily filtering by a high-cardinality column (customer_id) — means ingestion time clustering (which clusters by insertion order, not by customer_id) would NOT efficiently serve these queries. Liquid clustering on customer_id physically co-locates records for the same customer, enabling data skipping that ingestion time clustering cannot provide.

Correct answer: A

WHY A is correct: The Delta Lake documentation explicitly identifies 'reduce the search space for matches' as the primary technique to speed up MERGE. When a table is partitioned (e.g., by event_date) and the source data is known to only touch records from a bounded time window, adding the partition filter to the MATCH condition forces Delta to scan only those matching partitions rather than the entire table. Databricks documents this pattern directly: 'Adding the following condition makes the query faster, as it looks for matches only in the relevant partitions.' This avoids a full-table scan during the merge join phase, dramatically reducing I/O for large tables. WHY NOT B: While spark.sql.shuffle.partitions does control the number of tasks during the MERGE shuffle phase, simply increasing it does not reduce the amount of DATA read during the match phase. A very high value can also produce excessive small output files. The bottleneck described is reading the entire large table to find matches — a search space problem, not a shuffle parallelism problem. WHY NOT C: INSERT OVERWRITE on each partition replaces entire partition data, not individual records — this is a full-overwrite pattern that cannot implement selective upsert logic. Using a for-loop is also an anti-pattern for distributed workloads, introduces driver-side bottlenecks, and cannot match individual record-level change semantics that MERGE provides. WHY NOT D: Databricks explicitly documents that Spark caching should NOT be used with Delta Lake. Caching a Delta table loses data skipping benefits, may return stale data if the table is updated concurrently or accessed via a different identifier, and does not help with the MERGE match-phase scan.