Ingest and transform data practice questions

In the high-water-mark pattern the stored watermark is the highest change-indicator value actually loaded, taken from the extracted data in the same column the filter uses.

Taking MAX(ModifiedAt) from the extracted rows ties the watermark to the source's own clock and to what was really captured. A clock time taken after a long copy jumps past changes that happened during the copy and were not in the extract, so they are lost. The minimum value and the last-written row are both safe from loss but re-read data, because neither marks how far the load reached.

1. Read the stored watermark at the start of the run.
2. Extract rows with ModifiedAt greater than the stored value.
3. Load the rows, then compute MAX(ModifiedAt) over the extracted rows.
4. Persist that maximum in the control table only after the load succeeds.

Exam tip: Advance the watermark to the maximum change value you actually extracted, never to the pipeline's clock.

Change data capture (CDC) reads changes from the source database and yields insert, update and delete operations that a downstream load can apply. It is the standard way to propagate hard deletes incrementally, because query-based extraction only sees rows that still exist.

The requirement has two parts: incremental (no full reload) and delete-aware. Watermark and date-filter extracts are incremental but blind to deletes. Delta change data feed is a real CDC feature, but it records changes made to the target Delta table, not changes in the source. Only source-side CDC observes the delete itself, so the load can apply it with a MERGE that has a delete clause.

1. Check whether the source has hard deletes that must reach the target; here it does.
2. Rule out query-based extracts: a deleted row cannot be selected.
3. Check where the change feed lives: it must be on the source, not the target.
4. Apply the captured changes with a MERGE that includes a WHEN MATCHED delete clause.

Exam tip: Hard deletes need source CDC (or soft-delete flags, or a full reload); a watermark and target-side change feed cannot see them.

MERGE (upsert) matches source rows to target rows on a key and applies WHEN MATCHED (update or delete) and WHEN NOT MATCHED (insert) actions in a single atomic operation on a Delta table.

The requirement is update-or-insert per row in one operation, which is exactly what MERGE does. INSERT INTO duplicates existing customers, UPDATE skips new ones, and INSERT OVERWRITE replaces the table with only the current batch.

1. Identify the business key that matches staged rows to target rows: CustomerID.
2. Map the required behaviour: matched rows are updated, unmatched rows are inserted.
3. Use MERGE with WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT.

Exam tip: Update-if-present, insert-if-absent in one atomic step = MERGE.

A MERGE allows each target row to be matched by at most one source row. When the source has duplicate join keys, the engine cannot choose which values to apply and the operation fails.

A change log holds one row per change, not one row per order, so an order updated more than once since the last run appears several times in the merge source. The fix is to reduce the source to one row per key, normally the most recent change, using a window function such as row_number() partitioned by OrderID ordered by ModifiedAt descending. Duplicates in the target, a missing insert clause and concurrency each produce different behaviour.

1. Read the error: it names multiple source rows, so look at the staged data.
2. Confirm by grouping the staged data by OrderID and finding counts above 1.
3. Keep only the latest row per OrderID with a window function.
4. Rerun the MERGE on the deduplicated set.

Exam tip: MERGE needs at most one source row per target row; deduplicate change sets to the latest row per key first.

An idempotent load produces the same target state whether a batch is applied once or several times. For incremental batches into Delta tables, a MERGE on the business key is the usual way to achieve it.

Reruns and re-delivery are normal, so the write itself must absorb them. A keyed MERGE turns a repeated row into an update with identical values. Appending duplicates rows, a later cleanup leaves a window of wrong data, and overwriting is idempotent but discards everything not in the current incremental extract.

1. Note that the extract is incremental, so the table must keep rows from earlier runs.
2. Note that a rerun must not change the table.
3. Choose a write that matches rows on a stable business key: MERGE on CustomerID.

Exam tip: Make incremental loads idempotent with a keyed MERGE; overwrite is idempotent only for full loads.

In a star schema the fact table holds numeric measures at a declared grain plus foreign keys to dimensions; dimension tables hold the descriptive attributes used to filter and group.

Quantity and NetAmount are additive measures, so they stay in the fact table at order-line grain. ProductName, ProductCategory, StoreName and StoreRegion describe who, what and where, so they move into product and store dimensions, and the fact keeps a key to each. Reversing the roles, keeping attributes on the fact, or pointing dimensions at facts all break the model.

1. Classify each column: numeric and additive (measure) or descriptive (attribute).
2. Group descriptive columns by entity: product, store, date.
3. Keep measures in the fact table with one key per dimension.

Exam tip: Measures plus dimension keys in the fact; descriptive attributes in dimensions.