Data Transformation, Cleansing, and Quality
Deduplicate deliberately
dropDuplicates keeps an arbitrary row; when the question needs the latest record, use a window (row_number() over a partition ordered by an ingestion timestamp descending) or dropDuplicatesWithinWatermark for streaming. Tie-breaking matters: if the ordering column is not unique, results are non-deterministic.
Upserts and history
MERGE INTO is the workhorse for silver/gold. Watch for: duplicate source rows matching one target row (error), multiple source rows matching each other (error, fix by deduping first), and the fact that whenMatched clauses are evaluated in order - the first true clause wins. For SCD Type 2, MERGE can close the current row and insert the new version, but the pattern usually needs two passes or a whenNotMatchedBySource clause. Schema evolution on MERGE requires withSchemaEvolution() in the DataFrame writer or mergeSchema options.
Quality enforcement
In Lakeflow Declarative Pipelines, expectations define quality: expect (warn), expect_or_drop, expect_or_fail, expect_or_quarantine. Violations land in the pipeline event log, which is where you investigate. Outside pipelines, implement quarantine tables and constraints (CHECK, NOT NULL) on Delta tables, remembering that constraints are enforced on write and existing violating rows block adding a constraint.
Rule of thumb: cleanse in silver (types, nulls, dedup, standardisation), aggregate in gold; never let cast silently produce nulls - use try_cast and count the failures you consider acceptable.