How to Build a Data Pipeline: A Seven-Step Guide with a Worked Example (2026)

Most guides to building a data pipeline describe the architecture — ingestion, storage, transformation, serving — and then stop before the part where you have to make decisions. This one walks the seven steps in order, on a concrete example, and flags the choices that are expensive to reverse.

The worked example throughout: a SaaS company replicating its production Postgres database and its Stripe billing data into Snowflake, so the finance team can report on revenue and the product team can analyse feature usage. It is deliberately ordinary. It is also roughly 80% of the data pipelines that actually get built.

Step 1: Define the question before the pipeline

The most common expensive mistake in data engineering is building a pipeline before deciding what it is for. It produces a faithful copy of the production database in the warehouse, at real cost, that nobody queries, because the tables mirror application concerns rather than analytical ones.

Work backwards. Write down the specific questions: what was monthly recurring revenue by plan tier last month, and how did it change? Which features do accounts that renew use in their first thirty days? Those two questions determine which tables you need, how they must be joined, and — critically — how fresh they must be.

The freshness question is the one that decides your architecture, and people answer it wrong by default. Asked whether they want real-time data, stakeholders always say yes. Asked whether a decision changes based on data from four hours ago versus four seconds ago, the honest answer for monthly revenue reporting is no. Freshness requirements should come from decisions, not preferences, because streaming costs several times what batch costs to build and to operate.

For our example: monthly revenue reporting needs daily freshness. Product usage analysis needs daily. Nothing here needs streaming.

Step 2: Inventory the sources and choose an extraction method

For each source, you need to know how you will read it without degrading the system that generates it. This is where pipelines cause outages.

Production databases. Querying the primary directly puts analytical scans in competition with application queries for the same resources. A full table scan against a busy production table is how a data pipeline causes a customer-facing latency incident. Three approaches, in increasing order of both safety and complexity:

• Read replica. Point the extraction at a replica rather than the primary. Simple, and enough for most teams.
• Incremental extraction. Pull only rows where updated_at is newer than the last run. Cheap and effective — but it requires a reliable updated_at that the application maintains on every write path, and it silently misses hard deletes.
• Change data capture. Read the database's transaction log directly — the WAL in Postgres, the binlog in MySQL — rather than querying tables at all. Debezium is the standard open-source implementation. CDC catches deletes, imposes almost no load on the source, and gives near-real-time change events. It also requires the database to be configured for logical replication, and it is meaningfully more infrastructure to operate.

For our example: a read replica with incremental extraction on updated_at, and a note that the subscriptions table needs CDC later because subscription rows get hard-deleted on cancellation and incremental extraction would never notice.

Third-party SaaS APIs. Stripe, Salesforce, HubSpot, Google Ads. Do not write these connectors yourself. Managed connectors from Fivetran, Airbyte or Stitch handle authentication, pagination, rate limiting, incremental sync state and — the part that consumes the most engineering time over a pipeline's life — upstream schema changes. A hand-rolled Stripe connector is two days to write and then an ongoing maintenance obligation for as long as it runs.

Event streams. If the application already publishes events to Kafka or Kinesis, the pipeline consumes from there.

Step 3: Choose the destination

Two real options, and most teams need both eventually.

A cloud data warehouse — Snowflake, BigQuery, Redshift, Databricks SQL — for anything that will be queried with SQL by analysts or BI tools. Columnar storage, elastic compute, and access for people who know SQL but not Python. This is the right default for analytics.

Object storage — S3, GCS, ADLS — for raw data, semi-structured data, and anything feeding ML training. Cheap enough to store data before you know how you will use it, which matters more than it sounds: raw data you kept can be reprocessed when business logic changes, and raw data you discarded cannot.

The common shape is both: raw lands in object storage, modelled data lives in the warehouse. For our example, Snowflake is the destination and raw JSON payloads from the Stripe connector land in S3 first.

Step 4: Batch or streaming — and why the answer is usually batch

Batch processes data in scheduled chunks. A nightly job extracts yesterday's rows, transforms them, and loads them by morning. Simple to build, simple to reason about, simple to backfill when something breaks — you re-run the job for the affected window.

Streaming processes continuously as events arrive. Kafka with Flink or Spark Streaming. Sub-minute latency, and a genuinely different engineering discipline: you now own state management across restarts, out-of-order and late-arriving events, exactly-once versus at-least-once delivery semantics, and backpressure when a downstream consumer slows down. Backfilling a streaming pipeline after a bug is materially harder than re-running a batch job.

Build streaming when a system or a person takes an action within seconds of the event: fraud scoring, real-time personalisation, operational alerting, live inventory. Build batch for everything else, which includes almost all reporting and analytics. Teams routinely build streaming pipelines for dashboards that get read once a morning, and pay for that in operational overhead for years.

For our example: batch, running nightly.

Step 5: Load raw first, transform in the warehouse

The 2026 default is ELT rather than ETL — land raw data in the destination, then transform it there using the warehouse's own compute.

Two reasons this won. Cloud warehouse compute is elastic and cheap enough to do transformation work. And keeping raw data means that when a business rule changes — the definition of an active account, say — you re-run transformations over history you already have, instead of re-extracting from source systems that may no longer hold the old state at all.

dbt is the standard transformation layer. Each model is a SQL SELECT that defines one table. dbt resolves the dependency graph, runs models in order, and version-controls the whole thing in git.

The layering convention that keeps a dbt project maintainable at scale:

• Staging models — one per raw source table. Rename columns to a consistent convention, cast types, and nothing else. No joins, no business logic.
• Intermediate models — the joins and the reshaping. Not consumed directly.
• Mart models — what analysts and BI tools actually query. One per business concept: dim_accounts, fct_subscription_events, fct_feature_usage.

The discipline that matters: business logic lives in exactly one place. When "active account" is defined in three different dbt models, three dashboards disagree, and rebuilding trust in the numbers takes longer than the pipeline took to build.

Step 6: Orchestrate so failures are visible

A pipeline is not one job — it is jobs with dependencies. Transformation must not run before ingestion finishes. The BI refresh must not run before transformation finishes. Encoding those dependencies is what an orchestrator does, and it is the point where cron stops being sufficient.

Apache Airflow is the most widely deployed. Pipelines are Python DAGs; the operator ecosystem covers essentially anything. It is also the oldest, and its scheduling model shows its age.

Dagster models pipelines around data assets rather than tasks — you declare the tables that should exist and how they are produced, rather than the sequence of steps. That framing matches how data teams actually think, and its typed inputs and outputs catch a class of error Airflow finds at runtime.

Prefect is the lightest to adopt, with the best experience for turning existing Python into orchestrated flows.

dbt Cloud schedules and monitors dbt alone. If transformation is the only orchestrated thing you have, it is enough, and you can add a full orchestrator when you have non-dbt dependencies.

Whichever you pick, three things are non-negotiable from day one: retries with exponential backoff for transient failures, alerting to a channel a human actually reads when retries are exhausted, and a runbook for the failures that recur.

Step 7: Test and monitor before you have consumers

The failure mode that damages a data team's credibility is not the pipeline that crashes. A crash is loud and someone fixes it. It is the pipeline that succeeds while producing wrong data.

Data quality tests. dbt ships four that cover most of it: not_null, unique, relationships (referential integrity between models), and accepted_values. Add them to every mart model. Great Expectations and Soda handle validation more complex than that.

Freshness monitoring. Alert when expected data has not arrived within its window. A pipeline that completes successfully with data from 48 hours ago is a failure from the consumer's perspective, and nothing in the job status will tell you.

Row count anomaly detection. If yesterday's load brought 40,000 rows and today's brought 400, something upstream broke in a way that did not raise an error. Compare against a trailing average and alert on deviation.

Do this before the pipeline has users, not after the first bad report. Once analysts have found wrong numbers themselves, they start verifying every figure manually and building their own exports — and rebuilding that trust takes months of demonstrated reliability, far longer than adding the tests would have taken.

The default stack, assembled

For the worked example, and for most first pipelines:

Layer	Choice	Why
Ingestion	Airbyte or Fivetran	Managed connectors; do not hand-roll SaaS APIs
Raw landing	S3	Cheap; enables reprocessing
Warehouse	Snowflake or BigQuery	SQL access, elastic compute
Transformation	dbt	Version-controlled SQL, dependency graph, built-in tests
Orchestration	Dagster or Airflow	Dependencies, retries, alerting
Serving	Metabase, Looker or Tableau	Whatever the analysts already know

Substituting components is fine. Skipping layers is where pipelines go wrong — most commonly by skipping orchestration and testing, which are the two that only look optional until the first silent failure.

The mistakes that cost the most

Building before defining the question. Covered in step one, and still the most expensive.

Streaming when batch would do. Multiplies operational cost for freshness nobody uses.

Transformation logic scattered across the pipeline. Some in the extraction script, some in dbt, some in the BI tool's calculated fields. Now no one can say where a number comes from. Keep it in one layer.

No raw data retained. Transform-on-write with the source discarded means every logic change requires re-extraction, and history you can no longer reach.

Testing added after launch. By then the tests are archaeology on data whose problems are already downstream.

Getting help

At Ortem Technologies, our data engineering practice designs and builds production data pipelines for clients across SaaS, e-commerce, healthcare and financial services — typically the stack above, sized to the team that will inherit it. We also do pipeline audits for teams that have one running and do not trust its output.

Talk to our data engineering team | Discuss your data pipeline requirements

Related reading: ETL vs ELT explained for the transformation-order decision in step five, and data warehouse vs data lake vs lakehouse for the destination choice in step three.