Paths/Data Engineer/Batch Ingestion & ETL / Reliable Loads
Load Data Idempotently
Pipelines get retried - the load has to converge to the same table every time. Write the merge that updates changed rows and inserts the new ones.
Validator
IDLE — checks run live as you type commands; submit when done
01Requirements
live checks- Directory ~/pipelines exists○
- File ~/pipelines/load_ingest.sql exists○
- The statement merges instead of blindly inserting○
- The statement handles matched and unmatched rows○
02Walkthrough
4 steps- 01
Create the pipelines directory
Pipelines are code - they are versioned, reviewed and deployed like anything else.
mkdir -p ~/pipelines - 02
Open the merge statement
MERGE is the idempotent workhorse - it compares the source with the target and does the right thing for every row.
echo MERGE INTO orders AS target\nUSING staging_orders AS source\nON target.order_id = source.order_id\nWHEN MATCHED THEN\n UPDATE SET status = source.status, amount = source.amount\nWHEN NOT MATCHED THEN\n INSERT (order_id, status, amount)\n VALUES (source.order_id, source.status, source.amount); > ~/pipelines/load_ingest.sql - 03
Read the statement back
cat prints the statement. Read the ON clause first - it is the whole correctness argument.
cat ~/pipelines/load_ingest.sql - 04
Submit for validation
The validator checks that the load merges, updates and inserts.
03Stuck?
0 of 3 revealedTake a hint without losing the lab — each one points at the thing to look at, not at the answer.
04AI Tutor
guides, never solvesTell me what you're stuck on — I'll nudge, not solve. Which requirement are you looking at?
04Notebook
saved locally in this browserlearner@lab — ubuntu:24.04
Session is disposable
↑/↓ recalls commands · Ctrl+Enter submits · type help for the command list