---
title: "The Data Pipeline"
subtitle: "November 9 · Make the refresh safe before the handoff"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## One question will organize today

::: {.question}
If the source changes tonight, can tomorrow's analyst trust the database—and can the receiving team explain why?
:::

## Course source and adaptation

::: {.source-note}
Today's structure follows Alex Reinhart's [The Data Pipeline](https://www.refsmmat.com/courses/msp-computing/data-engineering/data-pipeline.html). 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

```text
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

::: {.checkpoint}
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:

```text
NOAA → database → report
```

Useful:

```text
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

```sql
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

```text
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

```python
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

::: {.checkpoint}
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 {.smaller}

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

::: {.checkpoint}
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 {.smaller}

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

::: {.project-prompt}
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

```text
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.