Domain 3 of 4 · Chapter 3 of 4

Cleanse, Transform, and Load Data

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Implementing Data Engineering Solutions Using Azure Databricks premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • Read the data before you reshape it
  • Type and parse on the way in
  • Cleanse: duplicates, nulls, and null-safe logic
  • Transform with set-based SQL
  • Reshape and denormalize for the gold layer
  • Load idempotently: MERGE, append, and overwrite
  • Exam-pattern recognition

Choosing a Delta load mode: append, overwrite, REPLACE WHERE, MERGE INTO

AspectINSERT INTO (append)INSERT OVERWRITEREPLACE WHEREMERGE INTO
What it doesAdds new rows onlyReplaces the whole table's dataAtomically overwrites rows matching a predicateMatches source to target on keys, then updates, deletes, or inserts
Corrects existing rows?NoYes, all of themOnly within the predicate rangeYes, by key
Idempotent re-run?No; a re-append double-loadsYes; a full replaceYes; same predicate reloads the same rangeYes; matched keys upsert once
Typical useIncremental bronze appendFull refresh of a tableReload one day, region, or partition rangeCDC upsert, SCD maintenance, deduplicating load

Decision tree

Update or deleteexisting rows by key?Replace existing data,not just add?Overwrite only rowsmatching a predicate?MERGE INTOupsert, SCD, dedupINSERT INTO (append)add rows onlyREPLACE WHEREoverwrite matching rowsINSERT OVERWRITE (full)replace whole tableYesNoNoYesYesNo
Cheat sheet

Cheat sheet

  • Profile data to get summary statistics and distributions
  • Distribution profiling exposes skew and outliers
  • Choose column types matching value domain and precision
  • Cast text source columns to typed columns on load
  • Parse and flatten semi-structured JSON into typed columns
  • Remove duplicates with DISTINCT or dropDuplicates on keys
  • Resolve nulls by dropping or imputing per requirement
  • NULL comparisons need null-safe operators
  • WHERE filters rows before grouping; HAVING filters groups
  • GROUP BY collapses rows into per-group aggregates
  • Join type controls which unmatched rows are kept
  • INTERSECT, EXCEPT, and UNION return distinct set results
  • Set operators require matching column count and types
  • Denormalize by joining dimensions into a wide table
  • PIVOT rotates row values into columns
  • UNPIVOT collapses columns into rows
  • MERGE performs upserts and SCD in one atomic statement
  • Append adds rows; overwrite replaces table or partition data
  • INSERT matches by position unless BY NAME is used

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. Databricks Utilities (dbutils) reference
  2. https://learn.microsoft.com/en-us/azure/databricks/notebooks/eda-tutorial
  3. Data profiling
  4. DECIMAL type
  5. try_cast function
  6. cast function
  7. Query variant data
  8. Query JSON strings
  9. dropDuplicates
  10. dropDuplicatesWithinWatermark
  11. Apply watermarks to control data processing thresholds
  12. coalesce function
  13. NULL semantics
  14. equal_null function
  15. GROUP BY clause
  16. HAVING clause
  17. Work with joins on Azure Databricks
  18. JOIN
  19. Set operators
  20. PIVOT clause
  21. UNPIVOT clause
  22. MERGE INTO
  23. Upsert into a Delta Lake table using merge
  24. AUTO CDC INTO (pipelines)
  25. Selectively overwrite data with Delta Lake
  26. INSERT