Databricks Certified Data Engineer Professional practice exam

A job processes ~120 GB of input with a wide transformation. The team has set spark.sql.shuffle.partitions to 8 on a cluster with 64 total executor cores, reasoning that fewer partitions means less overhead. The Spark UI shows only 8 shuffle tasks, each processing ~15 GB, with heavy disk spill and most cores sitting idle during the shuffle stage. AQE is disabled for this job. What change best improves throughput?

1. Raise spark.sql.shuffle.partitions to a value near or above the total core count (e.g., 128) so partitions are smaller, spill is reduced, and all cores stay busy ✓ (correct)
2. Lower spark.sql.shuffle.partitions further to 4 so there is even less shuffle coordination overhead
3. Broadcast the 120 GB dataset so the shuffle is skipped entirely on all executors
4. Increase executor memory so each of the 8 tasks can hold its 15 GB partition without spilling, keeping 8 partitions

A customer table contains a national_id column. Analysts in the 'analytics' group must run aggregate queries over the table but must never see raw national IDs, while a small 'compliance' group must see them in the clear. The team wants a single physical table (no copies) and wants the masking enforced at query time regardless of which tool connects. Which Unity Catalog approach satisfies this?

1. Grant SELECT on the table to analytics but REVOKE the national_id column; Unity Catalog will automatically null out revoked columns in returned rows.
2. Attach a column mask UDF to national_id that returns the raw value when is_account_group_member('compliance') is true and a redacted value otherwise; the mask is enforced by Unity Catalog at query time for all clients. ✓ (correct)
3. Create a view that hashes national_id and point analytics at the view; since the underlying table grant is what UC enforces, analysts querying the base table will also receive the hashed value.
4. Create two separate tables — one with the real national_id for compliance and one with the column dropped for analytics — and grant each group access to only one table.

A retail data team ingests a daily customer dimension feed of ~5 million rows into a silver Delta table. They must track the full history of changes to a customer's `loyalty_tier` and `region` so that historical fact-table joins reflect the attribute values that were valid at transaction time. Only the rows that actually changed should produce new history records, and existing current rows must be flagged out-of-date when superseded. Which approach correctly implements this requirement?

1. Overwrite the entire dimension table each day with `INSERT OVERWRITE`, then rebuild history nightly by diffing two adjacent table versions with time travel, since this avoids the cost of MERGE on 5 million rows.
2. Implement SCD Type 2 with a single MERGE that uses a staged set of changed-and-new keys: the WHEN MATCHED clause (filtered to rows where tracked columns differ) sets `is_current = false` and `end_date`, and a second INSERT pass writes the new versions with `is_current = true` and an open `end_date`. ✓ (correct)
3. Use a single MERGE with WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT against the natural key, relying on the MERGE to automatically version rows because Delta tracks attribute changes as Type 2 history when `is_current` exists.
4. Implement SCD Type 1 with a MERGE WHEN MATCHED THEN UPDATE that overwrites `loyalty_tier` and `region` in place, since Delta's transaction log retains every prior version and time travel can reconstruct the history for any join.