What Is ETL? A Plain Guide for Operations Teams

It's Monday at 7:30. Your operations manager exports jobs from the field service app, invoices from QuickBooks, and leads from the CRM. She pastes them into one workbook, fixes the customer names that don't match, looks up job numbers by hand, and builds the pivot table the owner asks about at 9:00. Next Monday, she does it again.

That's ETL. ETL stands for extract, transform, load: pull data out of the systems where it lives, clean and reshape it so it lines up, and load it into one place where people can report on it. Most companies run it by hand long before anyone calls it a data pipeline.

This guide explains what ETL is in plain terms, walks through each step with the details that trip operators up, compares ETL with ELT, and shows when a weekly spreadsheet routine should become an automated pipeline.

What is ETL?

ETL is a data integration process that combines data from several sources, cleans it, and loads it into a destination such as a data warehouse. IBM's definition covers the full scope: combine, clean and organize data from multiple sources, then load it into a warehouse, database or other target. AWS traces the idea back to the rise of relational databases, and IBM dates it to the 1970s.

The three steps:

1. Extract. Copy the data you need out of each source system: an ERP, a CRM, an accounting package, a spreadsheet, an API, a folder of files.
2. Transform. Apply business rules so the data agrees: fix formats, remove duplicates, match records across systems, calculate fields, convert units.
3. Load. Write the cleaned result into the destination, usually on a schedule, either all at once or as changes since the last run.

For a 40 person contractor the destination might be a single Postgres database or a Power BI dataset. The steps are the same.

You're probably already running ETL in Excel

Here's that Monday routine mapped to the three steps:

Microsoft says it outright: Power Query, the engine built into Excel and Power BI, is a tool for the extract, transform and load processing of data. If your team uses "Get Data" in Excel, you're already using an ETL tool.

The question is whether the routine is worth automating. Put in your own numbers below to see what it costs.

The transform step usually takes the longest, and it's also where the errors creep in. A tired person matching customer names at 7:45 a.m. will make different calls from week to week. That inconsistency is what makes the owner stop trusting the report.

The transform step is where the business rules live

Extract and load are plumbing. Transform is where your knowledge of the business turns into code. IBM's list of transform work includes cleaning, filtering, deduplicating, validating, calculating, converting and formatting. For operators, the rules that matter most look like this:

• Record matching. Which customer in QuickBooks is the same customer in the CRM? Decide the matching keys (email, phone, billing address) and what happens on a near match.
• ID mapping. Which invoice belongs to which job? If the job number is only typed into a memo field, the transform has to parse it out, and someone has to decide what happens when it's missing.
• Definitions. What counts as a "completed" job, an "active" customer, "revenue" for the month (invoiced or collected)? Write these down once. Every report inherits them.
• Unit and format fixes. Dates in three formats, phone numbers with and without country codes, hours stored as decimals in one system and minutes in another. AWS's own example converts ingredient weights from kilograms to pounds.
• Exception handling. Rows that fail a rule go to a review list with a reason, where someone can fix them.

When we build pipelines, we write these rules as a short document before any code. The owner and the bookkeeper sign off on the definitions. That one page saves more rework than any tool choice.

ETL vs ELT

ELT swaps the last two steps: load the raw data into the destination first, then transform it there. AWS notes that ELT has become common with cloud warehouses, which have the processing power to do the transformation themselves.

For most mid market operators the honest answer is a mix. We land raw copies of each source in a database (so you can always rebuild), then run transforms there to produce the clean tables reports use. That's ELT in shape, with ETL style rules. The label matters far less than writing the rules down.

When to automate, and what it costs

Signs your manual ETL has outgrown the spreadsheet:

• The report takes more than an hour a week to assemble.
• Two people produce different numbers from the same exports.
• The owner asks a question the workbook can't answer without a new export.
• A source system changed a column and nobody noticed for a month.
• You want the numbers daily, and nobody has the time.

Your options, from lightest to heaviest:

1. Power Query in Excel or Power BI. Record the extract and transform steps once and refresh with a click. Good for a few sources with stable exports.
2. Managed connector tools. Services like Fivetran copy data from SaaS apps into a warehouse on a schedule. Fivetran's pricing page lists a free plan up to 500,000 monthly active rows and meters paid use by rows inserted or updated each month; its Standard plan table shows a lowest annual contract of $12,000.
3. A custom pipeline. Code that pulls from each API, applies your rules and loads a database, run on a schedule in the cloud. More setup, full control over matching and exceptions.

Connector tools handle extract and load well. They leave the transform step, your matching rules and definitions, to you. That's the part to budget for whichever option you choose.

What a production ETL pipeline looks like

On the New York Lottery data pipeline, we built AWS ingestion and batch processing for encrypted files moving between parties, plus the deployment automation around it. The pieces of that project apply at any size:

1. Secure extract. Files or API pulls land in one controlled place, encrypted, with a record of what arrived and when.
2. Validation before transform. Check row counts, required fields and file structure first. A bad file stops the run with a clear error before anything downstream breaks.
3. Repeatable transforms. The same rules run the same way every time, in code under version control.
4. Incremental loads. After the first full load, process only what changed. IBM describes this pattern: an initial full load, then periodic incremental loads, with occasional full refreshes.
5. Deployment automation. Changes to the pipeline go out through a tested process.
6. Monitoring. Someone gets alerted when a run fails or a row count looks wrong.

A small business version might be a nightly job that pulls Jobber, QuickBooks and HubSpot into Postgres and refreshes a Power BI report. The same six pieces still apply.

Turning the Monday export into a pipeline

ETL is the work of making data from different systems agree. You're already paying for it in someone's Monday morning. Automating it pays off when the rules are written down, the exceptions are visible, and the report refreshes before anyone asks.

If you'd like help turning a weekly export routine into a pipeline you can trust, show us the workflow. Our data integration page covers how we build the layer that sits between your systems and your reports.

Sources

• What is ETL (extract, transform, load)?, IBM Think, accessed Nov 2026
• What is ETL?, Amazon Web Services, accessed Nov 2026
• What is Power Query?, Microsoft Learn, accessed Nov 2026
• Fivetran pricing, Fivetran, accessed Nov 2026