Start here
Data Pipeline in plain language
A nightly import and an event stream are both pipeline designs. Choose the timing from the decision the data supports, rather than assuming every report requires real-time updates.
Completion is not the same as correctness. A job can run successfully while publishing duplicate rows or silently omitting a source.
How it works
From source records to usable data
A simple analytical pipeline
- Ingest
Read source records and retain identifiers.
- Validate + transform
Check structure, duplicates and business rules.
- Publish
Expose checked data and its freshness to consumers.
Keep failed records or jobs traceable so they can be corrected and replayed.
Define the data contract
Specify fields, identifiers, types and expected arrival times.
Handle imperfect delivery
Plan for duplicates, late records and interrupted runs.
Reconcile the destination
Check counts, totals and freshness before downstream users rely on the result.
Illustrative example · Fictional data
Replaying a fictional order import
- Input
- An import contains order O-10 for ₹500 twice and order O-11 for ₹700 once.
- Process
- Use the order identifier and defined update rules to recognise the repeated O-10 record.
- Expected output
- Two unique orders totalling ₹1,200, not three rows totalling ₹1,700.
- How to check it
- A repeated identifier could also carry a legitimate update. Specify whether the pipeline replaces, versions or rejects it rather than blindly deleting rows.
Where it helps
- Feeding reporting systems from operational sources.
- Preparing datasets for ML.
- Synchronising information between applications.
Batch, streaming and orchestration
- Batch
- Processes a bounded set on a schedule or trigger.
- Streaming
- Processes arriving events with defined timing and delivery semantics.
- Orchestration
- Coordinates job dependencies, retries and scheduling.
ETL and ELT: where does transformation happen?
A CRM, ERP and order system may use different field names, identifiers and update frequencies. The pipeline must reconcile those differences before the combined data is trustworthy. Simply copying every table does not produce an analytical model.
ETL extracts records, transforms them in a processing step and then loads the destination. ELT loads the extracted data first and transforms it using the destination’s compute. Choose based on destination capability, privacy restrictions, cost and the need to retain raw inputs—not a rule that one pattern has replaced the other.
Keeping a controlled raw layer can help replay a transformation when business rules change. It does not automatically preserve every historical version: capture deletions and updates deliberately, and define retention and access rules.
Connectors, orchestration and dbt have different jobs
An extraction connector or script reads the source through an API, file transfer or database connection. Examples include managed connectors such as Fivetran and connector platforms such as Airbyte. Check incremental-loading behaviour, source limits and schema-change handling before choosing one.
An orchestrator such as Apache Airflow schedules tasks and their dependencies, records failures and coordinates retries. It can run the extraction and transformation tasks; it is not a substitute for the business logic inside them.
dbt supports transformation models, dependencies, tests and documentation. In a SQL workflow, an analyst describes a model with a SELECT statement and dbt manages its materialisation in the target platform. Source extraction remains a separate responsibility.
Track lineage from the published metric back to the model and original source. Document each table’s grain and owner, monitor freshness and row counts, and test a retry: the same input should not silently create duplicate business records.
What changes in a streaming pipeline?
A producer publishes events to a broker or event service; processing code consumes them, applies transformations and writes a serving store or analytical destination. This separates event delivery from the calculation performed on each event.
Streaming adds decisions about event time versus arrival time, late events, duplicates, ordering, replay and backpressure. A daily report may be better served by a simpler batch job. Fraud alerts or live inventory can need a shorter delay, but the required end-to-end latency must be specified and measured rather than assumed from the tool name.
Related reading and learning pathways
Common pitfalls
- Reruns can double-count records.
- Late data can change an already published total.
- Silent schema changes can misinterpret a field.
Put the idea to work
Try it yourself
Add a corrected O-10 value of ₹550 to the example. Define how the update changes the destination and how a reviewer can trace the correction.
Common questions
Is every pipeline ETL?
No. Pipelines can use ETL, ELT, direct replication or other processing patterns. In ELT, raw data is loaded into a warehouse first and transformed there, which is common with modern cloud warehouses. Streaming pipelines process events continuously instead.
Does streaming mean instant results?
No. Processing, delivery and downstream systems introduce latency. Define the freshness needed for the task. A stream may still take seconds or minutes from source to dashboard. Decide whether the use case needs near-real-time data or whether hourly batches are enough.
Your next step
Continue learning
Connected concepts
Sources and further reading
Examples on this page are teaching exercises using fictional information. They are not client case studies or measured outcomes.