Skip to content
EncryptTechBD
Home Industries Our Work Blog Pricing About Contact Discuss Your Project
বাংলা (BN)
Data & Analytics 6 min read

ETL pipeline architecture explained

What an ETL pipeline actually contains, how data moves from source systems to a warehouse, where idempotency and late-arriving rows decide whether the numbers hold, and when the architecture matters.

E
EncryptTechBD · 0 comments
Printed reports and charts spread across a desk
Photo: WDnet Studio / StockSnap

An ETL pipeline is the route data takes from the systems that create it to the place where someone makes a decision with it. Extract, transform, load describes three verbs. Architecture describes the promises around them: that the same input produces the same output, that a failed run can be repeated safely, and that a number on a dashboard can be traced back to a record.

This explainer is for managers and product owners who need to understand what they are buying or reviewing. It describes a reference architecture, walks through a commerce example, and names the design decisions that separate a pipeline that survives from one that needs a person to babysit it.

ETL and ELT are not the same bet

In classic ETL, data is transformed before it enters the warehouse. In ELT, raw data lands first and transformation happens inside the warehouse or lakehouse using its compute. ELT has become common because storage is cheaper and warehouse engines are capable, but the choice is not automatic.

ETL still fits when data must be reduced or masked before storage, when the target has limited compute, or when a regulated dataset should not land in raw form. ELT fits when you want to preserve raw history and iterate on transformations without re-extracting from source systems. Either way, the pipeline needs the same controls.

The reference flow

  1. Source inventory. Name every system, owner, identifier and extraction method. Include spreadsheets and manual exports; they are sources too.
  2. Extraction. Pull full or incremental data using a stable cursor such as an updated timestamp, sequence number or change data capture stream.
  3. Landing zone. Store the raw payload exactly as received, with an ingestion timestamp and source reference. This is the evidence layer.
  4. Validation and staging. Check schema, required fields, duplicates and referential integrity before transformation.
  5. Transformation. Apply business rules: currency conversion, tax treatment, status mapping, deduplication and derived measures.
  6. Load. Write to the warehouse or mart using an idempotent pattern such as merge or partition replace.
  7. Orchestration. Schedule tasks, manage dependencies, retries, backfills and alerts.
  8. Observability and governance. Track freshness, volume, failures, lineage, access and cost.

The landing zone is often skipped to save storage. That decision makes every later dispute harder, because the team can no longer prove what the source system actually sent.

A worked example

An online retailer wants a daily report of gross margin by product and delivery performance by courier. The sources are Shopify orders, a warehouse stock table, a payment provider settlement file and a courier status export.

The pipeline extracts orders incrementally by update timestamp, lands the raw JSON, and stages one row per order line. It validates that every line has a variant identifier and a price. It joins stock movements to calculate cost of goods, and joins settlement records to distinguish collected revenue from invoiced revenue. A separate branch maps courier status events into four states: dispatched, delivered, returned and failed.

The output is two marts: margin by product per day, and delivery performance by courier per week. Orchestration runs the order branch first, then the settlement and courier branches, then the marts. If the courier file arrives late, the pipeline marks the delivery mart as stale rather than publishing a partial week.

Design decisions that decide the outcome

Idempotency

A pipeline will be re-run. A backfill, a bug fix or a provider outage guarantees it. Every write must be safe to repeat: merge on a natural key, replace a partition, or delete and insert within a transaction. A pipeline that appends on every run doubles the revenue when someone retries it.

Incremental extraction

Full extracts are simple and increasingly expensive. Incremental extracts need a reliable cursor, a late-arrival window and a plan for records that change without updating the cursor. Test what happens when a record is edited during extraction.

Late-arriving data

Orders, refunds and courier events can arrive days after the business date. Decide how many days the pipeline will reopen. A common pattern is to recompute a rolling window rather than only today's partition.

Schema drift

Source systems add fields and change types. A pipeline that fails loudly on an unexpected column is safer than one that silently drops it. Record the schema version with each load and alert on change.

History and slowly changing dimensions

If a customer changes address or a product changes category, reports need to know which version applied at the time. Type 2 history keeps effective dates; type 1 overwrites. Choose per dimension and document the choice.

Replay and lineage

Every output should be reproducible from a named input set. Store the run identifier, source snapshot and code version. When a number is questioned, the team should be able to reconstruct the answer rather than explain the pipeline from memory.

Failure modes to design for

SymptomLikely causeDesign response
Revenue doubles after a retryAppend-only load without a keyIdempotent merge or partition replace
Yesterday is missing from the dashboardLate source file or failed dependencyFreshness check and stale-state flag
Totals differ from the source systemFilter applied during extractionReconcile row counts and sums at the landing zone
Product names change retroactivelyDimension overwritten in placeEffective-dated history
Pipeline succeeds but data is wrongTests check execution, not contentData quality tests on keys, ranges and relationships
Cloud bill rises without volume growthFull refreshes and unpartitioned scansIncremental models, partitioning and cost monitoring

When you do not need this

If one person exports a CSV once a month and the answer fits in a spreadsheet, a warehouse pipeline is overkill. The architecture starts to pay when multiple sources must be joined repeatedly, when the same definitions are rebuilt by different people, or when a decision needs data fresher than the monthly export.

Common misconceptions

  • A pipeline is a script. A script moves data once. A pipeline has ownership, scheduling, testing, retries and documentation.
  • The warehouse is the hard part. The hard part is agreeing what a customer, an order and revenue mean across systems.
  • More tests mean more confidence. Tests that nobody reads or acts on add cost without reducing risk.
  • Real-time is always better. Freshness has a cost. Daily data is sufficient for many management decisions.
  • Once built, it runs itself. Sources change, volumes grow and providers deprecate endpoints. Pipelines need maintenance.

Where our team fits

We design pipelines, data models and reporting layers under Business Automation. Scope and starting points are on the pricing page. If you are selecting tools and partners rather than learning the architecture, read ETL pipeline architecture: a buying guide.

Sources

Services that deliver this

Want this implemented properly?

Pricing →

Every engagement starts with a written scope. If a requirement is outside it, we quote it separately before starting.

Responses (0)

No comments yet. Add the first one below.

Leave a response

Your email address will not be published. Required fields are marked *.

Keep reading

More field notes