A nightly warehouse load finishes successfully. Every scheduled task is green, the expected tables exist, and the dashboard refresh completes, but the finance total is still wrong.
Perhaps a source extract omitted late transactions. A join may have duplicated order lines. A currency rule could have used the wrong effective date. The pipeline might also have loaded the correct rows into a model whose grain was misunderstood.
The completion message proves that the process ran. It doesn’t prove that the resulting information is complete or correctly interpreted.
Did the extract include every source change within the agreed boundary?- Could joins, filters, or aggregations have duplicated or removed valid records?
- Were rejected records retained and made visible to the right owner?
- Can a failed run be restarted without loading the same event twice?
- Do warehouse totals reconcile to suitable source or control totals?
- Can the team explain which rules and code produced a published result?
Three Activities, Two Common Sequences
The basic activities are straightforward in principle:
Extract
Read data from operational systems, files, services, or event streams.
Transform
Reshape structure, meaning, quality, and relationships for the intended analytical use.
Load
Write data into a store from which it can be processed or consumed.
ETL
Extract, transform, load
- Extract
- Transform
- Load
Substantial transformation happens before loading data into the analytical target. A separate integration engine or staging process might clean, combine, and reshape the data before writing warehouse dimensions and facts.
ELT
Extract, load, transform
- Extract
- Load
- Transform
Data first lands in the analytical platform, often in a source-shaped area. Transformation then uses the platform’s own processing capabilities to produce curated structures.
| Consideration | ETL | ELT |
|---|---|---|
| Main transformation location | Before the analytical target is loaded | Within the target analytical platform after landing |
| Data initially retained in the platform | Usually prepared data, although staging may also be retained | Often a closer representation of the extracted source data |
| Compute used for substantial transformation | A separate integration or processing engine | The analytical platform’s processing engine |
| Potential advantage | Limits what reaches the target and can suit specialised transformation or compliance controls | Retains a reusable landing point and can use scalable target compute |
| Design concern | Processing and movement outside the target can add infrastructure and hand-offs | Landed data still needs security, lifecycle controls, capacity management, and clear publication boundaries |
Microsoft’s Azure Architecture Center explanation of ETL and ELT similarly distinguishes the approaches primarily by where transformation occurs. The choice depends on the sources, target, data volume, latency, security, skills, cost model, and operational constraints.
ETL and ELT change the transformation sequence, but both need evidence that the curated result is complete and correctly interpreted.
Extraction Sets the Boundary of What Can Be Trusted
A pipeline can’t transform a change it never captured. Extraction therefore needs a defined boundary, not simply a connection string and a schedule.
Complete scope
Full extract
Reads the complete required dataset. It can be simple to reason about and useful for small reference data.
Main trade-off: repeatedly scanning and moving a large operational table may be slow, expensive, or disruptive.
Changed scope
Incremental extract
Reads only data added or changed since an established point, reducing repeated source scanning and movement.
Common methods: modification timestamps, increasing keys, source audit tables, or change data capture (CDC) for inserts, updates, and sometimes deletions.
The design should account for more than newly inserted rows:
- Updates: How will a change to an existing source record be recognised?
- Deletes: Will removal be captured, represented as a status, or deliberately ignored?
- Extraction boundary: Can two runs leave a gap or select the same change twice?
- Source impact: Will extraction locks, scans, or API calls affect operational users?
- Late commits: Could a transaction become visible after the pipeline has passed its apparent timestamp?
- Schema change: What happens when a field is added, removed, renamed, or changes type?
Keeping a source-shaped landing copy can make investigation and replay easier, but it doesn’t make the extract complete by itself. The team still needs to record what it requested, when the source made it available, what arrived, and which boundary the next run should use.
Transformation Is Where Business Policy Becomes Data
The word “transformation” can sound like formatting work: rename a column, convert a date, remove a blank value. Those tasks matter, but warehouse transformation also implements decisions made throughout this series.
It can determine:
Which source records represent the same customer, product, or account.- What one row in a fact table represents and which events qualify for inclusion.
- How currencies, units, calendars, statuses, and classifications are standardised.
- Which attributes overwrite earlier values and which create historical versions.
- How missing, late, duplicated, corrected, or contradictory records are handled.
- Which measures are stored, derived, allocated, or deliberately left to a semantic layer.
A short transformation can carry a consequential policy. Filtering cancelled orders, choosing the latest customer record, or joining a sale to an effective-dated territory version can change a published total. Source-to-target mappings and transformation rules should make those choices reviewable by business and technical participants.
Orchestration Coordinates Work, but Doesn’t Prove It
Orchestration controls when activities run, their dependencies, parameters, retries, timeouts, and notifications. It might wait for an extract, load dimensions before facts, prevent publication after a failed check, or start a semantic-model refresh only after warehouse tables are ready.
That coordination is necessary, but a green task normally means its software completed according to its technical success condition. A query can execute successfully while returning no rows. A copy can finish after receiving only half of an upstream file. A stored procedure can commit a mathematically valid but duplicated total.
Misleading signs of success
Every scheduled activity completed without a technical error.- The target contains approximately the expected number of rows.
- The dashboard refreshed and displays plausible figures.
- The latest partition or load timestamp is present.
- No records appeared in the pipeline's reject output.
- A retry completed after the original run failed part-way through.
These signals are useful operational information, but they become misleading when treated as a guarantee of data correctness.
Restartability Needs a Deliberate Design
Failures happen after different amounts of work. A source connection may fail before extraction, a transformation may stop after writing intermediate data, or publication may fail after curated tables have changed.
A restartable pipeline knows which work completed and which work remains safe to repeat. An idempotent operation produces the same intended state when repeated with the same input, rather than adding another copy of the result.
That may involve:
- Assigning a load or batch identifier to extracted and published records.
- Loading into temporary structures before an atomic switch or controlled merge.
- Using stable business or event keys to prevent duplicate insertion.
- Recording checkpoints only after the corresponding data is durable.
- Separating preparation from publication so users don't see a partial release.
- Defining when a failed load is resumed, replayed, reversed, or rebuilt.
Idempotence doesn’t mean blindly running everything twice. It means understanding the effect of repetition and controlling it.
Rejected Data Still Needs Ownership
Some records won’t satisfy the rules needed for a curated model. A customer key may be missing, a date may be impossible, a currency may be unsupported, or two source records may claim incompatible states.
Silently discarding them understates the business. Loading them as though nothing is wrong can contaminate the model. Stopping every load for every imperfect optional attribute may make the service unusable.
A governed rejection or quarantine process should answer:
- Which failures should stop publication, and which should allow processing to continue?
- Where should the original record and rejection reason be retained securely?
- Who owns the investigation and correction of each class of problem?
- Should corrected data return automatically, or require approval?
- How should consumers be warned about incomplete or provisional results?
- How should unresolved exceptions appear in reconciliation and service reporting?
The policy should reflect materiality and intended use. A missing marketing description doesn’t necessarily deserve the same response as an unrecognised currency on a financial transaction.
Validation and Reconciliation Answer Different Questions
Validation
Does this data satisfy the rules we expected?
Validation checks individual expectations. Examples include a required key being present, an effective period not overlapping, an order quantity being non-negative, or a fact resolving to one valid dimension version.
Reconciliation
Can we account for what changed between the source and the published result?
Reconciliation compares related evidence. It may use source and target row counts, financial control totals, counts by status, date boundaries, or balances across a transformation boundary.
Useful checks exist at several levels:
| Level | Example question | Evidence |
|---|---|---|
| Extraction | Did the pipeline receive the complete agreed source window? | Source control count, file manifest, CDC boundary, extract count |
| Structure | Does the incoming data match the expected contract? | Required columns, types, uniqueness, schema-change record |
| Transformation | Did joins, filters, and calculations behave as designed? | Rule-specific tests, duplicate checks, accepted and rejected counts |
| Warehouse model | Do facts have valid keys and the intended grain? | Referential checks, grain uniqueness, effective-date boundary tests |
| Business result | Does the curated output reconcile to an appropriate source or control? | Totals by period and status, explained adjustments, signed-off exceptions |
| Publication | Did consumers receive one complete approved release? | Release identifier, refresh status, published watermark, audit record |
Reconciliation doesn’t always mean equality. A warehouse may intentionally exclude test orders, translate currencies, or represent cancellations differently. The evidence should explain the difference rather than hide it inside a transformation.
The Kimball Group’s dimensional-modelling guidance describes an audit dimension as one way to associate processing metadata and quality indicators with loaded fact rows. The exact implementation can vary, but retaining traceable load evidence remains useful when a result is questioned.
Evidence That a Load Is Ready to Publish
The appropriate controls depend on the data and its use, but a credible process can usually demonstrate:
A recorded source boundary and load identifier for the processing run.- Input, accepted, rejected, inserted, updated, and deleted counts where relevant.
- Tests for important structural, modelling, and business rules.
- Safe restart behaviour demonstrated at realistic failure points.
- Reconciliation to agreed source or control totals, with differences explained.
- Traceability from a published result to its data, rules, code, and run evidence.
- A publication boundary that prevents consumers seeing a partial release.
- Named ownership for exceptions, approval, recovery, and communication.
ETL and ELT influence tooling, movement, processing cost, security, and team responsibilities. They don’t remove the need to define the data boundary, implement business policy carefully, recover safely, and retain evidence.
Questions to Take into Operation
Once a pipeline can produce a reconciled release, the concern shifts from one successful run to a dependable service.
- What service level do users need for freshness, availability, and recovery?
- Which failures, delays, quality changes, and capacity pressures need monitoring?
- Who can access source-shaped, rejected, curated, and published data?
- How will code, schemas, rules, and dependencies change without breaking consumers?
- Which recovery scenarios have been tested rather than merely documented?
- Who owns the service after the initial project team moves on?
Those questions lead into Article 7, which examines the operational, security, performance, recovery, and change responsibilities that continue after the warehouse has been built.