Data Pipeline

A data pipeline is a technical process that moves data from one or more sources through storage and transformation stages to systems that consume the resulting data.

A pipeline may extract, validate, store, transform, enrich, aggregate, and deliver data. It is not only a sequence of scripts: a production pipeline also needs orchestration, retries, monitoring, data quality checks, safe reruns, and dependency management.

Why data pipelines exist#

Source systems create data for operational purposes. Consumers usually need a different shape: cleaned fields, consistent types, joined entities, calculated metrics, or a predictable refresh schedule.

A pipeline provides a controlled path between those needs. It makes the work repeatable and gives each stage a clear responsibility, so teams can inspect, repair, and operate the system as data volume and usage grow.

Mental model: a layered pipeline#

Source   ↓Ingestion   ↓Raw   ↓Staging   ↓Mart   ↓Consumer

This is a common layered architecture, not a universal mandatory standard. A small system may combine stages, while a larger system may use several stores or processing jobs for one stage. The useful idea is to keep responsibilities clear.

Source#

Where data originates: a REST API, relational database, CSV or JSON file, application event, log, message queue, or external service.

Ingestion#

Collects data from the source and brings it into the data platform. Ingestion may use API extraction, database reads, file loading, event consumers, or scheduled jobs. Its main responsibility is reliable transport and capture, so unnecessary business transformations should generally wait for a later layer.

Raw#

The Raw layer is the closest preserved representation of source data. It keeps source information available for reprocessing, debugging, and traceability.

Raw does not always mean completely untouched bytes. Small technical normalization may be necessary to store or query the data, but business transformation should usually be avoided here.

Staging#

Staging makes data structurally consistent and ready for analytical transformation. Typical operations include type conversion, normalization, deduplication, basic validation, column naming, schema alignment, and handling missing values.

Staging prepares data without turning it into final business-facing datasets.

Mart#

A Data Mart is a consumer-oriented representation of data. It may contain business-friendly structures, aggregates, calculated metrics, joined datasets, or domain-specific tables such as daily_sales, customer_metrics, and product_performance.

Mart is optimized for use, not for preserving source structure.

Consumer#

Consumers use the resulting data. They may be BI dashboards, analysts, machine learning systems, applications, APIs, reports, or downstream pipelines.

Pipeline design should consider consumers from the beginning: their required fields, freshness, access pattern, and tolerance for missing or late data.

Why layers matter#

Raw, Staging, and Mart should not automatically be collapsed into one table or one transformation step. Separating them provides:

• easier debugging when a result looks wrong
• reproducibility from preserved source data
• traceability from a consumer result back to its origin
• simpler transformations with narrower responsibilities
• safer reprocessing after a code or source-data change
• clearer ownership between technical cleaning and business logic

The right number of layers depends on the system. The goal is not to create more tables; it is to make the path and responsibilities understandable.

ETL vs. ELT#

ETL and ELT describe where transformation happens in relation to loading:

ETL: Extract → Transform → LoadELT: Extract → Load → Transform

Modern warehouses and analytical databases often make ELT practical because they can perform substantial transformations inside the destination platform. ETL can still be a good fit when data must be transformed before it is loaded, or when the target system should receive only prepared data. Neither approach is universally better.

Batch vs. streaming#

Batch processing handles data in groups at intervals, such as every hour, once per day, or every fifteen minutes. Streaming processes events continuously or near real time as they arrive.

The choice affects latency, operational complexity, cost, and reliability. A dashboard that refreshes once per day may not need streaming, while an application that reacts to events quickly may require lower latency.

Many systems use both: streaming for time-sensitive events and batch jobs for periodic reconciliation, aggregation, or backfills.

Orchestration: Airflow and DAGs#

Apache Airflow is one example of a workflow orchestrator. Airflow does not become the data pipeline itself; it coordinates execution of pipeline steps.

It helps answer questions such as:

• When should this task run?
• What runs first?
• What depends on what?
• What should happen after a failure?
• Should the task retry?
• What is the execution state?

A DAG is a Directed Acyclic Graph. It describes tasks and the dependencies between them:

extract_orders       ↓load_raw_orders       ↓transform_staging       ↓build_sales_mart       ↓run_quality_checks

Tasks represent units of work, and dependencies define execution order. The DAG should describe workflow structure. Transformation logic should ideally live in reusable SQL, Python, or application code rather than becoming deeply embedded in the DAG definition.

The layers remain distinct:

• Airflow is the orchestration layer.
• SQL, Python, dbt, and processing jobs contain transformation or processing logic.
• PostgreSQL, ClickHouse, warehouses, and object storage persist data.

Data quality#

Successful execution does not guarantee correct data. A pipeline can complete without an error while producing incomplete, duplicated, stale, or incorrectly typed records.

Useful checks may verify that:

• required fields are present
• values are within expected ranges
• duplicates are controlled
• row counts are reasonable
• the schema is correct
• relationships remain valid

Quality checks can appear at multiple stages:

Source   ↓Ingestion   ↓Raw   ↓Quality Check   ↓Staging   ↓Quality Check   ↓Mart   ↓Quality Check   ↓Consumer

The checks should match the responsibility of the stage. A Raw check may verify that source records were captured, while a Mart check may verify business relationships or metric completeness.

Idempotency#

A pipeline step is idempotent when running it again with the same input does not create an incorrect duplicate or inconsistent result.

For example, a bad design may insert 100 rows on the first run and insert the same 100 rows again on a retry, leaving 200 rows. Safer approaches include:

• overwriting a known partition
• upserting with a stable key
• deduplicating by a defined rule
• tracking processed records
• producing deterministic outputs

Idempotency matters for retries, recovery after failures, manual reruns, and scheduled execution. A rerun should repair or reproduce the intended result, not silently multiply it.

Incremental loading#

A full load reprocesses all available data. An incremental load processes only new or changed data.

Common incremental strategies use:

• a timestamp such as updated_at
• an increasing ID
• partitions
• change tracking
• checkpoints or watermarks

Conceptually, a run may remember a last_processed_timestamp and then load rows where updated_at is greater than that checkpoint. This reduces the work per run, but it introduces state-management complexity: the checkpoint must be persisted correctly, late changes must be considered, and failures must not advance the state incorrectly.

Practical example: e-commerce orders#

Consider a neutral order pipeline:

Orders API     ↓Ingestion Job     ↓Raw Orders     ↓Staging Orders     ↓Sales Mart     ↓Analytics Dashboard

The pipeline stages are:

• Orders API is the source.
• Ingestion Job captures the source records.
• Raw Orders preserves the source representation for traceability and reprocessing.
• Staging Orders applies type conversion, validation, and deduplication.
• Sales Mart contains consumer-oriented sales structures and metrics.
• Analytics Dashboard is the consumer.

An Airflow DAG could coordinate the workflow:

extract_orders       ↓load_raw       ↓prepare_staging       ↓build_sales_mart       ↓validate_mart

The DAG describes when each step runs and what it depends on. The extraction, SQL transformations, and quality checks remain processing logic that can be tested and reused outside the DAG definition.

Best practices#

• Give each pipeline stage one clear concern.
• Preserve Raw data when reprocessing may be necessary.
• Keep transformations deterministic where possible.
• Design for reruns and retries.
• Make incremental state explicit.
• Validate data between important stages.
• Keep orchestration separate from business transformation logic.
• Make dependencies visible.
• Add useful logging and monitoring.
• Avoid unnecessary complexity.
• Choose batch or streaming based on actual latency requirements.

Common mistakes#

One giant Python script does everything#

When source extraction, cleaning, transformation, storage, and reporting are tightly coupled, failures are difficult to isolate and changes are risky.

No Raw layer#

Transforming source data immediately removes an important recovery and traceability point. When the source changes or a transformation is fixed, reprocessing becomes much harder.

Raw and Mart are mixed#

Technical source representation and business-facing models become impossible to distinguish. Consumers cannot tell whether a field is preserved, cleaned, or calculated.

Airflow contains all transformation logic#

The DAG becomes hard to test, reuse, and maintain when it also contains the core business transformations.

Retry creates duplicates#

If a retry inserts the same records again, the step was not designed for idempotency.

Full reload every time#

Reloading all available data may work on a small dataset but becomes inefficient as volume grows. Incremental loading can reduce the work when its additional state and correctness requirements are handled deliberately.

No data quality checks#

A pipeline may report success while producing incorrect data. Execution status and data correctness are different signals.

Streaming without a real need#

Streaming can add significant operational complexity when the consumer does not need low latency. Choose it for a real requirement, not as a default.

Production checklist#

Architecture#

• Source is clearly defined
• Ingestion responsibility is clear
• Raw data strategy is defined
• Staging transformations are separated
• Consumer-facing Mart is defined

Reliability#

• Pipeline can be safely rerun
• Retries do not create duplicates
• Failure states are observable
• Incremental state is persisted correctly

Data quality#

• Schema checks exist
• Required fields are validated
• Duplicate strategy exists
• Final outputs are validated

Orchestration#

• Dependencies are explicit
• Scheduling is defined
• Retry behavior is intentional
• Workflow code is separated from processing logic

Operations#

• Logs are useful
• Configuration is externalized
• Credentials are not hardcoded
• Pipeline can be reproduced in another environment

Quick reference#

A data pipeline is a controlled path that moves data from source to consumer while keeping transformation, reliability, quality, and execution manageable.

See also#