In this guide 6 sections
ETL stands for extract, transform, load. A scheduled job reads records from your accounting or billing software, cleans and reshapes them, and writes them to a reporting database that your dashboard queries. Follow one invoice and every step becomes visible, from source row to chart figure.
Follow one invoice
Invoice 4471 is raised on Tuesday afternoon in your accounting package. It sits in the invoice header table with a customer code, a date, a net amount and a tax amount, plus a few lines in a detail table.
By Wednesday morning the same invoice is a small part of a bar on the sales dashboard. Three steps sit between those two moments.
ETL is the name for those three steps.
Extract: reading without touching
The job connects to the accounting database and asks for new and changed rows. It uses a login that can only read, so nothing in the live system is edited, locked or deleted by this step.
Read-only access matters for two reasons. It protects your books, and it lets the accounting vendor confirm the connection is safe. Ask for the account name and its permissions in writing.
Some packages offer no database access. Then the job reads a scheduled export file, or calls the vendor’s API within its rate limits.
- Database login with select rights only
- Scheduled CSV or Excel export dropped in a folder
- Vendor API with a token and a call limit
Transform: cleaning and matching
Raw rows rarely agree with each other. One system writes “Acme Pvt Ltd” while the CRM writes “ACME”. Dates arrive as text. A credit note carries a positive amount when it should reduce the total.
The transform step fixes these. It matches customer codes, converts dates, flips credit note signs, adds the month and the branch, and drops test records, following rules taken from your signed KPI definition sheet.
Rows that fail a check go to a rejects table with a reason. They never reach the dashboard.
Load: incremental and logged
The cleaned rows are written into the reporting database. A full reload copies every invoice every night, while an incremental load copies only rows changed since the last run, tracked by a modified date or a running id.
Incremental loads finish in minutes and put less pressure on the source. They need care with deletions and backdated edits, so many teams also run a weekly full reload as a cross-check.
Every run writes a log line with its start time, rows read, rows loaded, rows rejected, finish time and any error.
- Start and finish times
- Rows read, loaded and rejected
- Error text and the step that failed
- Last successful load shown on the dashboard
From reporting table to chart
The dashboard tool, such as Power BI or Metabase, queries the reporting database. Net sales for the month is a sum over the loaded invoice rows, grouped by the month and branch columns that the transform step added.
When a figure looks wrong, trace backwards. Check the chart filter, then the reporting table, then the reject log, then the source row. The path is short because every step leaves a record.
Ask your vendor to show you one real invoice travelling this path before you approve the project.
ETL trace worksheet
A worksheet that follows a single invoice from left to right. Boxes show the source table and field, the extract method, each transform rule applied, the reporting table and column, and the dashboard figure it feeds. Below sits a run log grid with columns for start, finish, rows read, rows loaded, rows rejected and error text.
Open a blank worksheet to printQuestions owners ask
Is ETL the same as a data migration?
No. A migration moves data once to a new system and then stops. ETL repeats on a schedule, copying and reshaping data from a live system into a reporting store.
How often should the job run?
Nightly suits most owner reports. Hourly or faster is worth the extra load on the source only when someone acts on the figures within the same working day.
What is ELT?
ELT loads raw data first and transforms it inside the reporting database. The outcome is the same, and the pattern is common with cloud warehouses.
Can ETL damage my accounting data?
Not when the job uses a read-only login. It copies rows out and cannot edit them. Confirm the account’s permissions with the accounting vendor before the first run.