What it means
A business wants one sales report from an online shop, store tills and an invoicing system, but each source describes customers and amounts differently. An ETL process extracts records, transforms them to an agreed definition and loads them into a reporting destination.
ETL is commonly described as combining, cleaning and organising data before loading it into a warehouse or other target, and the business rules are what make the output interpretable, since the tool alone does not settle conflicting meanings. Extraction may take a full copy or only new and changed records, so state which method is used and how deletions are captured.
Record source timestamps and identifiers to trace a reported figure back to original transactions. Transformation can normalise dates, currencies and code values, and the conversion rule must be documented and tested against unusual source values.
A customer with two source IDs should not be merged merely because the names look alike, since matching requires a reliable key or reviewed rule. Validate missing and unexpected values, because quietly replacing blanks with zero can misstate revenue.
Mask or restrict sensitive information where the target does not need it, and check access to logs and staging files, which can contain personal details even if the final report is aggregated. Loading may insert, update or replace target records, and the chosen method affects duplicates, history and the ability to recover from a failed run.
Design idempotency (the property that a retry gives the same result) where appropriate, so retrying a failed job does not create two copies of every sale. Reconcile counts and totals at each stage, as an extract with 10,000 rows and a load with 9,900 needs an explained difference, and a filter that removes 100 invalid rows may be legitimate but the reason and exception queue should be visible.
Schedule the process to meet the reporting need, because a nightly pipeline cannot provide an up-to-the-minute dashboard, and late-arriving transactions can change a prior period's result, so define how backfills are handled. Monitor errors, duration, freshness and data quality, since a green scheduler status can coexist with bad output.
Assign owners for source definitions and pipeline operation, because engineers cannot decide accounting treatment alone, and test schema changes, as a source team adding or renaming a field may break transformations. Use version control for rules and code so a changed tax mapping is traceable to an approved decision, and keep a path to correct past outputs, since a transformation bug may require reprocessing earlier periods.
ETL differs from ELT in order, because ELT loads data before many transformations and neither approach is automatically superior, while the concept describes a process rather than a schedule, so a one-off migration and a daily report can both use it, moving data between a raw data lake and a curated warehouse. For finance reporting, compare loaded amounts with source ledgers, document lineage in language report users understand, and remember that a useful ETL process ends in trusted data that supports a decision, not merely a successful transfer message.
In practice
Real-world examples.
Example
A company combines till and online sales, converts timestamps to one time zone and loads a reporting table. Daily totals now match across channels. Managers see one sales figure instead of three that disagree.
Example
A job extracts 10,000 orders but loads 9,900; an exception report explains the 100 excluded records. The data team fixes the rule that rejected valid orders with blank phone numbers. The next run loads every valid order.
Example
A retry uses stable order IDs so it does not duplicate yesterday's sales. The job fails halfway through one night and is restarted in the morning. The reporting table shows each sale once.
Formula
Calculation
There is no universal ETL formula, but load reconciliation can use: Load completeness = Accepted target records / Expected source records x 100, with documented exclusions and value checks.
Worked example. A fictional retailer's nightly job extracts 10,000 orders and loads 9,900, with an exception report listing 100 rejected records.
- Load completeness = 9,900 / 10,000 x 100 = 99%.
- The 1% gap is acceptable only if each of the 100 exceptions is explained, for example invalid dates or missing customer keys.
- Value check: the source ledger shows $482,000 of sales and the loaded table shows $481,500, a variance of $482,000 - $481,500 = $500, or about 0.1% of source sales. Finance traces the $500 to the rejected records before signing off the report.
A matching count with the wrong total, or a matching total with missing rows, both show why reconciliation needs more than one check.Case study
Seen in the real world.
This entirely fictional case follows Harbour Retail. A new POS version changed a tax code and the daily report dropped some sales. A reconciliation alert showed a mismatch, and the team corrected the mapping and reprocessed the affected dates. They documented the code change for future releases. The case is invented.
The finance manager asked for the reconciliation to be shown beside the daily report, with the source total, the loaded total and the difference. Report users could then see at a glance whether the numbers were complete before using them in meetings. In the illustrative follow-up, the team also agreed that the point-of-sale team would warn the data team before changing any code values. The change process now includes a test run of the pipeline, so mapping errors are caught before they reach management reports.
Watch out
Common mistakes.
- Treating job success as proof the figures are correct.
- Replacing unknown values with zero without a rule.
- Retrying a partial load without checking for duplicates.
Questions
People also ask.
What is the difference between ETL and ELT?
In ETL, transformation precedes loading; in ELT, much transformation happens after loading.
Does ETL have to run nightly?
No. Frequency depends on the data and operational need.
Why reconcile?
To detect missing, duplicated or altered data after transfer.
From the founder's library
Take it further with the book.
Build your financial confidence beyond this definition. Shihan's full-length guide, Accounting Fundamentals, takes the same plain-English approach and turns it into a complete, practical playbook for non-finance managers, business owners and students - with chapter-end quiz answers and presentation slides included.
25% off with code MMHQ25, applied at checkout. Priced in USD - checkout may show the equivalent in your local currency.
View the book and save 25%