Home / DE Professional practice test / Data Modelling

Free · 8 questions with explanations

Data Modelling: Databricks Data Engineer Professional Practice Questions

Exam-style questions on Data Modelling. Pick your answer, then open the explanation to see why it's right — and why the other options are wrong.

Updated November 16, 2026

1 Data Modelling Medium

Which of the following is a key LIMITATION of Z-order (OPTIMIZE ... ZORDER BY) compared to liquid clustering?

  1. AZ-ordering cannot cross partition boundaries — for partitioned tables, ZORDER clustering is applied independently within each partition, limiting its effectiveness when a query spans multiple partitions.
  2. BZ-ordering requires statistics columns to be declared at table creation time and cannot be changed after the table is created.
  3. CZ-ordering requires Databricks Runtime 15.2 or above, while liquid clustering is available on all supported Databricks Runtime versions.
  4. DZ-ordering can only be applied to partitioned tables; unpartitioned tables cannot benefit from ZORDER skipping.
Show answer & explanation

Correct answer: A

WHY A 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 D: 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 B: 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.

2 Data Modelling Easy

A data engineering team is building a large Delta Lake table and uses a MERGE operation nightly to upsert records from a daily CDC feed. The table is partitioned by event_date. The MERGE is running slowly despite the source data only touching records from the last 7 days. Which approach BEST improves MERGE performance in this scenario without changing the partitioning scheme?

  1. ARaise spark.sql.shuffle.partitions to a high value such as 2000 to give the MERGE shuffle more tasks and spread each partition's write across more files.
  2. BAdd a date filter to the MERGE condition (events.event_date >= current_date() - INTERVAL 7 DAYS) to restrict the search space to the relevant partitions.
  3. CReplace the MERGE with an INSERT OVERWRITE of each affected partition, driven by a for-loop, to avoid the overhead of the MERGE match evaluation.
  4. DCache the target Delta table with spark.table('events').cache() before running MERGE, keeping the rows to be matched in memory for the join phase.
Show answer & explanation

Correct answer: B

WHY B: The Delta Lake documentation 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 touch records from a bounded time window, adding the partition filter to the match condition (on the target, and on the source as well) makes Delta scan those partitions rather than the whole 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 A: spark.sql.shuffle.partitions does control the number of tasks during the MERGE shuffle phase, but raising it does not reduce the amount of DATA read during the match phase, and a very high value can produce excessive small output files. The bottleneck is reading the whole table to find matches: a search space problem, not a shuffle parallelism problem. WHY NOT C: INSERT OVERWRITE replaces a partition's data wholesale, not individual records, so it cannot implement selective upsert logic. A for-loop is also an anti-pattern for distributed workloads, introduces driver-side bottlenecks, and cannot reproduce the record-level change semantics that MERGE provides. WHY NOT D: Databricks documents that Spark caching should NOT be used with Delta Lake tables. Caching a Delta table loses data skipping benefits, can 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.

3 Data Modelling

A data engineer must maintain a Type 2 slowly changing customer dimension in a Lakeflow Spark Declarative Pipeline, so that each change to a customer produces a new versioned row with validity boundaries while prior versions are retained. The source is a CDC feed with a sequenceNum ordering column and an operation column. Which configuration of the APPLY CHANGES INTO (AUTO CDC) API correctly produces the SCD Type 2 history?

  1. AUse APPLY CHANGES INTO target ... STORED AS SCD TYPE 2 but set KEYS to include both customer_id and sequenceNum, because the sequence column must be part of the key so that each version is treated as a distinct entity and retained.
  2. BUse APPLY CHANGES INTO target FROM source KEYS (customer_id) SEQUENCE BY sequenceNum STORED AS SCD TYPE 1, then enable Change Data Feed on the target; SCD Type 1 plus CDF preserves history because CDF retains the overwritten versions as change records.
  3. CUse a plain MERGE INTO inside a @dlt.table function with WHEN MATCHED THEN UPDATE SET end_date = current_date(); the AUTO CDC/APPLY CHANGES API only supports Type 1, so Type 2 history must be hand-coded with MERGE.
  4. DUse APPLY CHANGES INTO without a SEQUENCE BY clause but with STORED AS SCD TYPE 2; omitting SEQUENCE BY is required for Type 2 because history ordering is derived from the Delta commit version, not a source column.
  5. EUse APPLY CHANGES INTO target FROM source KEYS (customer_id) SEQUENCE BY sequenceNum STORED AS SCD TYPE 2, so the API keeps every historical version keyed by customer_id, orders changes by sequenceNum, and maintains the validity (start/end) columns automatically.
Show answer & explanation

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.

4 Data Modelling Easy

A data team is implementing a star-schema dimensional model for e-commerce analytics. A key dimension table dim_customer changes slowly over time — when a customer updates their home address, the team needs to preserve the full historical record (including the old address) with effective date ranges so that historical sales can always be linked to the correct customer address at the time of purchase. Which Slowly Changing Dimension (SCD) type should they use, and which Delta Lake feature natively supports it?

  1. ASCD Type 1: overwrite the existing customer row in place with Delta MERGE (WHEN MATCHED THEN UPDATE), keeping one row per customer that holds the latest address.
  2. BSCD Type 2: use AUTO CDC INTO in Lakeflow Spark Declarative Pipelines, which adds a new row with an effective date range for each change and keeps the previous record.
  3. CSCD Type 3: add previous_address and current_address columns to dim_customer and use Delta MERGE to shift the current address into the previous column on each change.
  4. DSCD Type 0: leave dim_customer unchanged once loaded and rely on the Delta Lake transaction log, which blocks row-level updates and thereby preserves the historical records.
Show answer & explanation

Correct answer: B

WHY B: The requirement to preserve full historical records with effective date ranges so that historical fact joins reflect the 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, and the old row's end date is updated to mark it as expired. Historical fact rows, which reference the old surrogate key, continue to join to the historical address. Databricks supports this natively: '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 overwrites the existing row in place (via MERGE WHEN MATCHED THEN UPDATE). It is the simplest result but destroys historical attribute values: with one row per customer holding the newest address, a historical sales order cannot be attributed to the old address. WHY NOT C: SCD Type 3 adds a fixed number of 'previous value' columns (e.g., previous_address, current_address) to the dimension row. It retains exactly ONE level of history: after three address changes, the two most recent addresses are preserved. That does not meet the requirement for full history with effective date ranges. WHY NOT D: SCD Type 0 means no changes are applied to dimension records after the initial load, so an address change is not reflected at all. In addition, the Delta transaction log does not block row-level updates: Delta supports UPDATE, MERGE, and DELETE, and ACID compliance means these operations are atomic and consistent, not that they are blocked.

5 Data Modelling Medium

A data engineering team enables automatic liquid clustering (CLUSTER BY AUTO) on a Unity Catalog managed Delta table using Databricks Runtime 15.4 LTS. Which of the following statements CORRECTLY describes how automatic liquid clustering selects and maintains clustering keys?

  1. AAutomatic liquid clustering selects the highest-cardinality column at table creation and keeps it as the clustering key while query patterns evolve.
  2. BAutomatic liquid clustering takes an initial set of clustering columns from the engineer and then tunes their order while keeping the same columns.
  3. CAutomatic liquid clustering picks keys from the table's historical query workload and changes them when predicted savings exceed the clustering cost.
  4. DAutomatic liquid clustering applies clustering synchronously during each write, reclustering the existing records on each INSERT to keep the layout.
Show answer & explanation

Correct answer: C

WHY C: The Databricks documentation 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. 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: CLUSTER BY AUTO does NOT require the engineer to specify initial clustering columns. The point is that Databricks selects the keys based on workload analysis. Engineers specify CLUSTER BY AUTO (or ALTER TABLE ... CLUSTER BY AUTO) and the platform handles key selection. WHY NOT D: Automatic liquid clustering does NOT recluster the existing records on every INSERT. OPTIMIZE operations trigger the incremental physical reclustering, and they run asynchronously via predictive optimization. Write operations apply clustering on write only when data in the transaction meets a defined size threshold. Reclustering every record on every write would be prohibitively expensive for large tables.

6 Data Modelling Medium

A data engineer is designing a star schema for a large analytical workload in Databricks. The central fact table fact_orders (500 GB, growing ~5 GB/day) is joined frequently with dim_product (10 MB) and dim_customer (200 MB) on product_id and customer_id respectively. Analysts filter primarily by order_date and product_category. Which combination of design decisions BEST optimizes query performance for this star schema?

  1. APartition fact_orders by order_date, add CLUSTER BY (product_category) on the same table, and broadcast both dimensions with a broadcast() hint in each query
  2. BPartition fact_orders by product_id and customer_id so joins with both dimensions prune partitions, and keep the dimension joins as shuffle sort-merge joins
  3. CPartition fact_orders by product_id, run OPTIMIZE ... ZORDER BY (order_date, product_category) on it, and broadcast both dimensions with a broadcast() hint
  4. DUse CLUSTER BY (order_date, product_category) on fact_orders and broadcast both dimensions, raising spark.sql.autoBroadcastJoinThreshold or hinting for dim_customer
Show answer & explanation

Correct answer: D

WHY D: Liquid clustering on fact_orders with CLUSTER BY (order_date, product_category) physically co-locates records sharing the same date and product category, enabling data skipping when analysts filter by these columns. Liquid clustering is the Databricks-recommended layout for large fact tables with growing data and analytical query patterns. dim_product (10 MB) falls at the default spark.sql.autoBroadcastJoinThreshold (10 MB) and is broadcast automatically, while dim_customer (200 MB) is broadcast after raising the threshold or with an explicit broadcast() hint, a standard star-schema tuning that eliminates shuffle-based joins; the small dimension tables need no additional layout optimization. WHY NOT A: Liquid clustering and Hive-style partitioning cannot be combined on the same Delta table. The documentation states: 'Clustering is not compatible with partitioning or ZORDER.' Liquid clustering can be enabled only on unpartitioned tables (ALTER TABLE ... CLUSTER BY requires the table to be unpartitioned), so attempting both results in an error. WHY NOT B: Both product_id and customer_id are high cardinality (potentially millions of distinct values), so partitioning on them creates millions of tiny partitions, the scenario Databricks documentation warns against: 'Tables where a typical partition key could leave the table with too many or too few partitions' are not suited to partitioning. Joins on dimension foreign keys are served by broadcast joins of the small dimension tables, not by partition pruning on the fact table, and leaving them as shuffle sort-merge joins wastes that advantage. WHY NOT C: Partitioning by high-cardinality product_id creates too many partitions for the same reason. ZORDER can handle high-cardinality columns, but Databricks now recommends liquid clustering over ZORDER for new tables, and ZORDER operates within each partition's boundary: on a table over-partitioned by product_id, ZORDER on order_date cannot combine files across product partitions.

7 Data Modelling Easy

A data engineer needs to drop an existing Delta Lake table and recreate it at the same storage location with a new schema. Which is the CORRECT approach?

  1. ARun DROP TABLE prod.events followed by CREATE TABLE prod.events LOCATION '/data/events' ... to recreate the table.
  2. BRun ALTER TABLE prod.events SET SCHEMA ... to update the schema in place without recreating the table.
  3. CDelete the underlying Parquet files directly from cloud storage, then register the new table schema in the metastore using CREATE TABLE.
  4. DUse CREATE OR REPLACE TABLE prod.events LOCATION '/data/events' ... to atomically replace the table in a single ACID transaction.
Show answer & explanation

Correct answer: D

WHY D 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 B: 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.

8 Data Modelling

A data engineer designs a large Delta fact table that is frequently filtered on event_date and, increasingly, on customer_id. The current design uses Hive-style partitioning by event_date plus periodic OPTIMIZE ... ZORDER BY (customer_id). Problems observed: many small partitions for low-volume days, expensive full rewrites when they tried to also partition by customer_id, and the inability to change the clustering keys without rewriting the whole table. The engineer evaluates Liquid Clustering. Which statement correctly describes why Liquid Clustering addresses these problems?

  1. ALiquid Clustering eliminates the need for OPTIMIZE entirely because every write is automatically fully sorted on the clustering keys at ingestion time, guaranteeing perfectly clustered files without any background maintenance.
  2. BLiquid Clustering is a faster form of Hive partitioning that creates one physical directory per clustering key value, so it still requires choosing the key at table creation and rewriting the table to change keys, but it compacts small partitions automatically.
  3. CLiquid Clustering only improves point lookups on a single key and cannot cluster on more than one column; to filter on both event_date and customer_id you must still partition by event_date and layer Liquid Clustering on customer_id.
  4. DLiquid Clustering requires you to run ZORDER after each write to take effect, because CLUSTER BY only records the intended layout as metadata while the actual data ordering is still produced by the ZORDER command.
  5. ELiquid Clustering replaces both partitioning and ZORDER with clustering keys defined via CLUSTER BY; it avoids the small-file/skew problems of fixed partition directories, incrementally clusters new data without full rewrites, and lets you change clustering keys later without rewriting existing data.
Show answer & explanation

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.

Take the full DE Professional practice test →