uplift.page
← library
prompt🔁 Data pipelinesv2 · updated 2026-06-12

Reliable data pipeline

Ingest → transform → schedule with dbt, DuckDB/Postgres, and Dagster.

Run it as a prompt

Paste this into any AI agent, or fetch it: curl -s https://uplift.page/api/v1/prompts/data-pipeline/raw

prompt.md
# Reliable data pipeline

Move data from sources to a queryable warehouse on a schedule, with tests that catch bad data before dashboards do.

## Recommended stack

- **dbt** - transformations. SQL models with tests, docs, and lineage - transformations become reviewable code.
- **DuckDB (small) / Postgres (live)** - warehouse. DuckDB handles surprisingly large workloads for free; graduate only when concurrency demands it.
- **Dagster** - orchestration. Asset-based scheduling with backfills and a UI that shows what's stale.
- **Python + pydantic** - ingestion. Typed extractors that fail loudly on schema drift.

## Build steps

1. Land raw data immutably (append-only, source timestamps) - never transform in the extractor.
2. Build staging models that rename/cast only, then marts that join and aggregate.
3. Add dbt tests: unique keys, not-null, accepted values, and one row-count sanity check per source.
4. Schedule with Dagster assets; make every job idempotent and re-runnable for any date window.
5. Alert on freshness, not just failure - a silently stale table is worse than a loud crash.

## Watch out for

- Transforming during ingestion; you can't re-derive what you didn't keep raw.
- Non-idempotent backfills that double-count on retry.
- Dashboards reading raw tables directly, bypassing the tested layer.

## Definition of done

- Any day's pipeline can be re-run safely
- Bad source data fails a dbt test, not a stakeholder meeting
- Lineage answers 'where did this number come from' in one click

The full prompt

Move data from sources to a queryable warehouse on a schedule, with tests that catch bad data before dashboards do.

Recommended stack

  • dbt - transformations. SQL models with tests, docs, and lineage - transformations become reviewable code.
  • DuckDB (small) / Postgres (live) - warehouse. DuckDB handles surprisingly large workloads for free; graduate only when concurrency demands it.
  • Dagster - orchestration. Asset-based scheduling with backfills and a UI that shows what's stale.
  • Python + pydantic - ingestion. Typed extractors that fail loudly on schema drift.

Build steps

  1. Land raw data immutably (append-only, source timestamps) - never transform in the extractor.
  2. Build staging models that rename/cast only, then marts that join and aggregate.
  3. Add dbt tests: unique keys, not-null, accepted values, and one row-count sanity check per source.
  4. Schedule with Dagster assets; make every job idempotent and re-runnable for any date window.
  5. Alert on freshness, not just failure - a silently stale table is worse than a loud crash.

Watch out for

  • Transforming during ingestion; you can't re-derive what you didn't keep raw.
  • Non-idempotent backfills that double-count on retry.
  • Dashboards reading raw tables directly, bypassing the tested layer.

Definition of done

  • Any day's pipeline can be re-run safely
  • Bad source data fails a dbt test, not a stakeholder meeting
  • Lineage answers 'where did this number come from' in one click

Served from the uplift.page library and refreshed within 5 minutes of every update.