The Data Pipeline

November 9 · Make the refresh safe before the handoff

MaDS Databases & SQL

One question will organize today

If the source changes tonight, can tomorrow’s analyst trust the database—and can the receiving team explain why?

Course source and adaptation

Today’s structure follows Alex Reinhart’s The Data Pipeline. His end-to-end pipeline, deployment, and monitoring ideas are adapted to the two Fall 2026 client databases and their November 13 handoff.

By the end of class

You should be able to:

• draw the project pipeline from source to client result,
• define an idempotent refresh and its transaction boundary,
• place validation before and after writes,
• separate data, code, and service monitoring,
• write a receiver-centered refresh runbook.

The pipeline is larger than ingestion

source → acquire → stage → validate → transform
       → load → query/report → monitor → refresh

A download script is one step, not a pipeline.

Start from the decision

Climate: which geography or peril deserves underwriting attention?

EV: which corridor deserves deeper charging-site study?

Work backward from the client output to the evidence it needs.

Every arrow has a contract

For each transition, record:

• input and output grain,
• schema and units,
• owner and cadence,
• validation and failure behavior,
• evidence that the step completed.

Checkpoint 1 · Draw the actual path

In pairs, draw one current project result backward to its raw sources. Mark every format, grain, and ownership boundary.

Circle the arrow most likely to fail during the receiving team’s first run.

Debrief · A useful map is executable

Weak:

NOAA → database → report

Useful:

CSV event row → staging.storm_events_raw
→ typed event record → county-year metric

The second version gives the receiver something to test.

Idempotent means safe to repeat

Running the same refresh twice should reach the same intended state.

Common tools:

• stable natural or surrogate keys,
• UNIQUE constraints,
• INSERT ... ON CONFLICT,
• source checkpoints or watermarks,
• deterministic transformations.

“Append” is not a rerun strategy

INSERT INTO observations (...)
VALUES (...)
ON CONFLICT (station_id, observation_date)
DO UPDATE SET
  precip = EXCLUDED.precip,
  source_updated_at = EXCLUDED.source_updated_at;

The conflict key states what “the same fact” means.

Stage before touching trusted tables

downloaded file
  ↓
raw/staging table
  ↓ validate shape, types, dates, duplicates
trusted table
  ↓ validate counts and coverage
client query

Keep the bad row available for diagnosis.

A transaction defines all-or-none

with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        load_stage(cur, rows)
        validate_stage(cur)
        merge_trusted(cur)
# commit on clean exit; rollback on exception

The boundary should match the unit the receiver can safely retry.

Checkpoint 2 · Predict the second run

Take one current load step. Predict row counts after run 1, run 2, and a run interrupted halfway through.

Write the constraint, transaction boundary, and recovery command that make those predictions true.

Validation has layers

Layer	Example
File	expected columns; nonempty; parseable
Row	valid timestamp; latitude range
Table	unique business key; foreign keys resolve
Batch	plausible count and date coverage
Domain	no negative fatalities; charger type recognized
Client	published metric still uses comparable coverage

Missing data and failed data differ

• Missing in source: an evidence limitation.
• Rejected by validation: a pipeline result.
• Not downloaded: an operational failure.
• Filtered by design: a documented choice.

Do not collapse them into NULL without provenance.

Monitor the pipeline, not just the server

Track at least:

• last successful source timestamp,
• rows read, accepted, rejected, inserted, updated,
• minimum/maximum business date,
• duration and status by step,
• a domain metric that should not jump silently.

Drift can invalidate a successful run

The code exits zero. The table loads. The client answer is still wrong.

Possible causes:

• source categories changed,
• geographic coverage changed,
• a field’s unit changed,
• revision policy changed,
• client meaning changed.

Checkpoint 3 · Design the handoff alarm

Choose one failure the receiving team might miss. Define the signal, threshold, owner, and first diagnostic command.

Prefer a check tied to the client result over “server is running.”

The runbook begins with smallest success

The receiver should see:

1. prerequisites and safe credentials mechanism,
2. one command to connect,
3. one command for a small dry run,
4. expected output and validation values,
5. full refresh command,
6. rollback/retry instructions,
7. escalation and known issues.

Project transfer

Run the builder refresh twice. Capture counts, coverage, validation output, and failure/recovery behavior for the handoff pack.

Do not demonstrate against undocumented local state.

Homework starts here

Homework 2 reuses today’s evidence:

• pipeline map,
• rerun prediction and result,
• forced failure result,
• receiver-centered smallest-success command.

The pattern to keep

start from the client decision
  → name every contract
  → stage and validate
  → write atomically
  → prove the rerun
  → monitor meaning
  → document smallest success

Next: call SQL safely from Python and make one project result runnable by a receiver.