Data pipeline

What is a data pipeline?

A data pipeline is an automated chain of steps that moves data from a source system to a destination. It extracts data, transforms it if needed, and writes the result somewhere useful, usually a data warehouse, lakehouse, operational system, or analytics model.

A simple example: every night at 2:00, a pipeline reads invoices and payments from accounting software, converts them into a consistent format, loads them into the warehouse, and leaves the management report ready in the morning.

Think of a pipeline as a production line. Raw material enters at one end, each station does a piece of work, and a finished product comes out at the other end. The point is repeatability: nobody should have to open exports by hand every morning.

The parts of a pipeline

Most pipelines contain the same building blocks, even when the tools look different.

Source
The system the data comes from: CRM, ERP, accounting software, webshop, database, API, event stream, or a folder with files.

Extraction
The step that reads the data. It may read the full source each time, or only the records that changed since the previous run. Change data capture is one way to capture those changes.

Transformation
Cleaning and shaping: standardising dates, removing duplicates, joining sources, calculating business fields, mapping codes to readable categories, or applying validation rules.

Destination
The place where the result lands: warehouse, lakehouse, data mart, semantic model, file store, search index, CRM, or another operational system.

Schedule or trigger
The thing that starts the run. It can be a clock schedule, a manual start, a new file, a source event, or another pipeline finishing successfully.

Monitoring and logging
Every run should leave a trail: when it started, whether it succeeded, how many rows were read and written, which step failed, and who was alerted.

Batch or streaming?

Batch pipelines run at set times and process a group of records at once. Nightly accounting loads, hourly CRM syncs, and daily reporting refreshes are usually batch jobs. For most reporting, batch is enough.

Streaming pipelines process events continuously as they happen. They make sense when the business needs to react within seconds or minutes: fraud detection, stock updates, live telemetry, or real-time customer interactions.

Streaming is more expensive to run and harder to operate because the pipeline is always on. For most SMEs, batch is the right starting point. Stream only when someone or something acts on the data quickly enough to justify the extra complexity.

Batch pipelines can still follow ETL or ELT. ETL transforms data before loading it into the destination. ELT loads raw data first and transforms it inside the warehouse or lakehouse.

When is a scheduled refresh enough?

Not every data flow deserves a pipeline. Power BI can connect to a source, transform data in Power Query, and refresh on a schedule. For one report on one clean source, that can be perfectly fine.

A real pipeline starts to pay off when one of these things happens:

• Several sources need to be combined. Accounting, CRM, webshop, and support data become hard to manage inside each report.

• You need history the source does not keep. If the source only stores the current state, the pipeline can take snapshots over time.

• Several reports reuse the same logic. Margin, active customer, product category, and revenue definitions belong in one shared place.

• The source is too slow or too sensitive. Heavy reporting queries should not hit the operational database all day.

• The output feeds more than a report. AI applications, portals, operational syncs, and exports often need a pipeline rather than a dashboard refresh.

What makes a pipeline reliable?

Idempotency
You should be able to rerun a failed step without creating duplicate rows or half-results. Retries and backfills are dangerous without this property.

Retries and error handling
A source that times out for thirty seconds should not break the whole night. Temporary failures can often be retried automatically.

Alerting
A failed run that nobody notices is worse than a loud failure. Send alerts to a real owner, not a forgotten shared mailbox.

Data checks
A pipeline can finish green and still load zero useful rows. Check row counts, freshness, schema, and key business rules.

Logs
When the report looks wrong, you need to see which run processed which rows and where the difference started.

Data pipeline versus deployment pipeline

Both are called pipelines, but different things move through them.

A data pipeline moves data from source to destination.

A deployment pipeline moves software, configuration, reports, or models from development to test to production. That is CI/CD.

They meet when your data pipeline definition is code or configuration. In a mature setup, you deploy the pipeline definition through a deployment pipeline, and then the data pipeline moves data in production.

Common tools

Fabric Data Pipelines
Microsoft Fabric Data Factory pipelines are visual workflows made of activities, triggers, parameters, and control flow. They can move data, call notebooks, run dataflows, branch on conditions, and show run history in Fabric monitoring.

Azure Data Factory
The standalone Azure service for data movement and transformation. It is still widely used in Azure environments and shares many concepts with Fabric Data Factory.

Apache Airflow
An open-source orchestrator where pipelines are usually defined as Python DAGs. It is strong for complex dependencies and backfills, but needs more engineering ownership.

dbt
dbt is not a general movement tool. It handles transformations and tests inside a warehouse or lakehouse engine and is often started by Airflow, Fabric, Dagster, or another orchestrator.

What to watch out for

Silent failures
A green run that loaded zero rows should still be an incident. Test the result as well as the process status.

Clock-based dependencies
If pipeline B starts at 3:00 because pipeline A is usually done by then, you have a race condition. Make B wait for A explicitly.

One giant pipeline
A huge chain becomes hard to debug and restart. Split work into smaller, clear pipelines where each part can be rerun safely.

Unclear alert ownership
Decide who responds, how quickly, and what the first triage steps are.