Databricks Data Engineer Professional · 6% practice allocation

Databricks Data Engineer Professional: Data Modeling Practice Questions

5 free sample questions from the Data Modeling domain, with answers and explanations. The full Data Engineer Professional bank has 311 questions across 10 domains. Questions are scenario-based and written from the official Databricks exam guide.

A company stores sensor readings in a Delta table. A data engineer needs to add a new column calibration_factor DOUBLE to the table without rewriting existing data files, and all future reads should show NULL for this column in existing rows. Which command achieves this with the LEAST disruption?

A. ALTER TABLE sensors ADD COLUMN calibration_factor DOUBLE;✓ Correct
B. df.write.option('mergeSchema', 'true').mode('append').save('/path/to/sensors');
C. INSERT OVERWRITE sensors SELECT *, NULL AS calibration_factor FROM sensors;
D. spark.sql("ALTER TABLE sensors SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name')")

Explanation

Option A is correct. ALTER TABLE ... ADD COLUMN is the standard DDL command to add a new column to a Delta table. Delta Lake supports this operation without rewriting existing data files — existing rows will return NULL for the new column when read. This is a metadata-only operation. Option C is wrong — INSERT OVERWRITE rewrites all data, which is unnecessarily disruptive and resource-intensive. Option B is wrong — using mergeSchema with an append write adds the column but also writes new data; it is not appropriate when the goal is simply to add a column without writing any new records. Option D is wrong — setting column mapping mode enables features like column renaming and dropping, but does not itself add a new column; it is a prerequisite step for some schema evolution operations, not the column-addition command itself.

A data engineering team needs to implement SCD Type 2 for a vendors dimension in Delta Lake. They want to use a MERGE statement. After a new batch of vendor updates arrives, which combination of MERGE clauses correctly implements SCD Type 2 semantics? (Select TWO)

A. A WHEN MATCHED AND source.vendor_name <> target.vendor_name THEN UPDATE SET target.is_current = false, target.end_date = current_date() clause to expire the old record.✓ Correct
B. A WHEN NOT MATCHED THEN INSERT clause that inserts the new version of changed records with is_current = true and start_date = current_date().✓ Correct
C. A WHEN MATCHED THEN UPDATE SET target.vendor_name = source.vendor_name clause that overwrites the existing record in place (SCD Type 1 behavior).
D. A WHEN NOT MATCHED AND source.is_changed = true THEN INSERT clause that inserts both the expired old record and the new record in a single MERGE operation.
E. A WHEN MATCHED THEN DELETE clause to remove the old record before inserting the new version.

Explanation

SCD Type 2 requires two operations: (1) expiring the current record by setting is_current = false and end_date when a match with a change is detected (option A), and (2) inserting the new version of the record as a new row with is_current = true (option B—this is typically done in a second MERGE pass or via a pre-staged union). Option C is wrong because in-place UPDATE overwrites history, which is SCD Type 1, not Type 2. Option D is wrong because a single MERGE cannot insert both the expired old record and the new record from a single matched row in standard Delta SQL—the expire-then-insert pattern requires two passes or a UNION-based approach. Option E is wrong because deleting the old record destroys history, which is the opposite of SCD Type 2's purpose.

In a dimensional model, which table holds the measurable business events (e.g., sales transactions with quantities and amounts) and foreign keys to dimensions?

A. The fact table✓ Correct
B. A dimension table
C. A bronze raw table
D. A staging view

Explanation

The fact table stores measurable events (measures like quantity and amount) plus foreign keys to dimension tables. Dimension tables (B) hold descriptive context, a bronze raw table (C) holds ingested data, and a staging view (D) is intermediate.

A data engineer is implementing SCD Type 2 for a customers dimension table in Delta Lake. Each change to a customer record must create a new version with effective_date and end_date columns, while the previous record's end_date is updated. Which MERGE statement logic correctly handles this pattern?

A. Use a single MERGE with WHEN MATCHED AND source.hash != target.hash THEN UPDATE SET end_date = current_date(), and a separate INSERT for the new version row.✓ Correct
B. Use MERGE with WHEN MATCHED THEN UPDATE to overwrite all columns including effective_date, relying on Delta's transaction log to recover old values if needed.
C. Use Delta's built-in SCD2 function by setting spark.conf.set('delta.scd.type', '2') before running the MERGE.
D. Use MERGE with WHEN NOT MATCHED THEN INSERT only; changes to existing records are handled by a nightly full reload with INSERT OVERWRITE.

Explanation

Option A is correct. SCD Type 2 in Delta Lake requires two operations per changed record: (1) expire the current active row by updating its end_date, and (2) insert a new row with the updated values and a new effective_date. A MERGE statement can handle the UPDATE (close old record) in a WHEN MATCHED clause, and the INSERT (new version) can be executed as a subsequent operation or using a UNION-based MERGE pattern. Option B is wrong — overwriting all columns destroys historical data, which is SCD Type 1, not Type 2. The transaction log preserves file history but does not reconstruct dimension history in query results. Option C is a fabrication — Delta Lake has no built-in delta.scd.type configuration. Option D is wrong — a nightly full reload with INSERT OVERWRITE is destructive and does not preserve historical versions.

An organization is migrating a large Delta table (5 TB, date-partitioned) to use Liquid Clustering instead of partition-based clustering. Which TWO statements accurately describe Liquid Clustering behavior after running ALTER TABLE t CLUSTER BY (region, event_type)?

Select TWO.

A. Existing data files are immediately reclustered when the CLUSTER BY clause is applied.
B. New data written to the table will be clustered by (region, event_type) automatically during write operations.
C. Running OPTIMIZE on the table will trigger incremental reclustering of data files based on the new clustering columns.✓ Correct
D. Liquid Clustering replaces the need for ZORDER commands, and explicitly calling ZORDER after enabling CLUSTER BY will raise an error.
E. Partition pruning continues to function normally because Liquid Clustering internally creates hidden partition metadata.
F. The table retains its existing partition layout after ALTER TABLE; the CLUSTER BY definition takes effect only for future OPTIMIZE runs and new writes.✓ Correct

Explanation

Options C and F are correct. When CLUSTER BY is added to an existing table, the clustering definition is registered in the table metadata, but existing files are NOT immediately reclustered (F is correct — the partition layout is retained until OPTIMIZE is run). Running OPTIMIZE incrementally reclusters files using the new clustering columns (C is correct). Option A is wrong — ALTER TABLE does not immediately rewrite existing files. Option B is partially misleading: new writes are laid out respecting the clustering definition over time, but full clustering effectiveness comes from OPTIMIZE, not automatically at write time for all engines. Option D is wrong — while ZORDER and Liquid Clustering are alternatives, using ZORDER on a Liquid Clustered table does not raise an error in all versions; however, ZORDER is incompatible with CLUSTER BY and its use is discouraged, but the statement 'raises an error' is too absolute. Option E is wrong — Liquid Clustering does NOT create hidden partition metadata; it uses file statistics for data skipping, not partition pruning.

Test yourself across every Data Engineer Professional domain

The free Data Engineer Professional practice test mixes about 20 questions weighted like the exam. The full 311-question bank adds instant grading, flashcards and a timed mock exam.

Take the free Data Engineer Professional practice test Practice the full bank

Study this domain

Data Modeling study guide (Data Engineer Professional) →

Other Data Engineer Professional domains

← All Databricks Data Engineer Professional study resources