Azure Databricks Data Engineer Associate: Data Transformation and Modeling

Data Transformation and Modeling

This domain tests transformation logic and table design: Spark SQL/DataFrame operations, medallion layers, Delta modeling, upserts, deduplication, and data-quality expectations. Questions often ask which modeling or layout choice fits a workload.

Transformation and modeling rules

• Bronze stores raw ingested data; silver cleanses, conforms, deduplicates, and joins; gold serves curated star-schema facts and dimensions. Keep transformations idempotent and set-based.
• MERGE INTO handles upserts and SCD changes; deduplicate the source first with row_number() over the business key ordered by timestamp, or MERGE can multiply rows.
• SCD Type 1 overwrites attributes; SCD Type 2 adds version rows with valid_from, valid_to, and is_current.
• Partition Delta tables on low-cardinality columns used in filters (for example, date), not on high-cardinality IDs. Liquid clustering is the preferred layout for changing or multi-dimensional access patterns; it replaces the need to choose static partitions and Z-order for new tables.
• In Lakeflow Declarative Pipelines, expectations enforce quality: EXPECT ... ON VIOLATION DROP ROW or FAIL UPDATE; use a quarantine pattern for rows to inspect later.
• Window functions (row_number, lag, sum over) avoid self-joins; filter after windowing in a subquery.

Worked example

A CDC feed has multiple updates per customer_id; the silver table must keep the latest record. Reasoning: read the feed, then use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) and keep row 1. MERGE that deduplicated DataFrame into silver on customer_id. If history is required, close the current row and insert a new SCD2 row; do not overwrite. Repartition by the merge key only if shuffle pressure justifies it.

Traps

• Partitioning by customer_id or another high-cardinality column.
• MERGE without source dedupe, causing duplicate matches.
• Using Python UDFs for logic that native functions can do.
• Calling collect() to inspect large data on the driver.
• Applying Z-order to a liquid-clustered table, or expecting expectations outside pipelines.