Extract, Transform, Load: How ETL Works in a Warehouse

On Monday the revenue report shows yesterday at 3.4% above the same day last week. Nobody ran a promotion. Four hours later an analyst finds it: 412 order rows exist twice. Sunday night's job timed out partway through writing to the warehouse, the scheduler retried it, and the retry had no idea what the first attempt had committed. That is an extract transform load failure, and the most common one in the discipline. Not a broken connector, not a schema change, just a job safe to run once and unsafe to run twice.

This guide walks the three stages using one order table, then explains why modern warehouses inverted the order into ELT and what that changed about where transformation logic lives. After that: incremental loads, idempotency, and the mechanics that produce duplicate rows. The goal is that you can read a pipeline someone else wrote and predict how it will fail.

The one table that explains extract transform load

An ecommerce application stores orders in Postgres, roughly like this:

orders(id, customer_email, status, currency, amount_cents, discount_cents, tax_cents, shipping_cents, is_test, created_at, updated_at, deleted_at)

Every column is shaped for the application, not for analysis. amount_cents is in whatever currency the buyer paid. created_at is UTC, but finance closes the month in America/New_York. is_test is true for a few hundred QA rows. status moves through pending, paid, refunded, cancelled, overwritten in place, so the row remembers only its current state. Deletes are soft, except in one legacy code path where they are not.

What an analyst wants:

fct_orders(order_key, order_id, customer_key, order_date_local, gross_revenue_usd, discount_usd, net_revenue_usd, is_refunded, first_order_flag, loaded_at)

Every real argument about extract transform load is about how you get from the first shape to the second, and which machine does the work. The vendor categories, the orchestration tools, the ELT vs ETL debate all resolve to where and when that reshaping happens.

Here is the same order row moving through the three stages:

Every section below is about one of those three cells.

Extract: getting data out without breaking the source

Extraction sounds like the easy stage and produces the most silent data loss. Three ways to get rows out, with different guarantees:

Full extract. Read the entire table every run. Simple, obviously correct, fine up to a few million rows, and it catches hard deletes for free because anything absent from the new copy is gone. It stops being viable when the read locks the production database or a rate limit turns a full pull into a six-hour job.

Incremental extract by watermark. Store the maximum updated_at you have seen, and next run ask for everything greater than it. This is the default, and it carries three assumptions people forget to check. First, that every write path updates updated_at. A bulk UPDATE orders SET status = 'cancelled' WHERE ... run during an incident usually does not. Second, that clocks agree, since an application server running 400 milliseconds ahead of the database can write rows just below a watermark you have already passed. Third, that deletes are soft, because a watermark query can never see a row that no longer exists.

Change data capture. Read the database's own replication stream, the write-ahead log in Postgres or the binlog in MySQL, turning each committed change into an event. CDC catches hard deletes and bulk updates that bypass updated_at, with almost no query load on the source. It also adds a component that can fall behind, fill a disk with retained WAL segments, and needs care during failovers.

For SaaS sources you do not choose. You get whatever the API offers, usually cursor pagination over an unstable sort key, so a record modified mid-scan can be returned twice or skipped entirely. That is the origin of a large share of duplicate rows in pipelines pulling from commerce and billing platforms. The reconciliation habits in Stripe QuickBooks Integration: Reconciling Fees and Payouts exist precisely because payment data arrives in multiple passes with different totals, and the connector patterns for a storefront are covered in Shopify Integrations: Orders, Books, and Marketing in Sync.

One rule makes the rest manageable: extraction should be dumb. Land the source shape as it arrived, in an append-only raw zone, with an ingestion timestamp and batch identifier. Do not filter, rename, or cast. Once extraction contains business logic, you cannot rebuild history without re-extracting, and re-extracting is often impossible because APIs age out their data.

Transform: where the business logic actually lives

Transformation is not cleaning. Cleaning is the boring part. Transformation is where a company's definitions get encoded into SQL, and definitions are political.

The mechanical work is straightforward: cast types, convert currency at the rate on the transaction date rather than today's, shift timestamps to the reporting timezone, standardize join keys, generate surrogate keys, drop test records. All of it must be exactly right, because a timezone shift applied at the wrong stage moves transactions across a month boundary and changes a number finance already reported.

The interesting work is the decisions with no technically correct answer:

• Does revenue include shipping? Include tax? Both move the number by several percent.
• When a March order is refunded in May, does March's revenue drop, or does May carry a negative? Accounting says one thing, growth assumes the other, and the pipeline has to pick.
• Is a customer identified by email, by account id, or by a resolved identity merging the two addresses one person used?
• Does a cancelled order that was never charged appear at all?

Each becomes a line of SQL, and once it is there it is the company's answer whether or not anyone agreed to it. That is the argument for keeping transformation logic in version-controlled SQL rather than a graphical pipeline tool: a definition change should show up in a diff with an author and a reason. The query patterns in SQL for Data Analysis: The Queries Analysts Use Daily are the vocabulary this stage is written in.

One layering convention is close to standard: raw, staging, intermediate, marts. Staging is one model per source table doing nothing but renaming, casting, and light cleaning, with no joins and no business rules. Intermediate holds the joins and the messy logic. Marts hold the tables people query. Its value is that when a number is wrong you can bisect the pipeline instead of reading a thousand-line query.

Load: the stage that quietly creates duplicate rows

There are four common load strategies, and they differ mainly in what happens when you run them twice.

Append is the default in quick pipelines and the source of most duplicate rows. It is safe only when something downstream deduplicates, and that usually means a SELECT DISTINCT someone added once and nobody documented, hiding the real problem for months while doubling the cost of every query over that table.

Merge is the workhorse, and its correctness depends entirely on the key being unique in both the target and the incoming batch. If the batch contains the same order twice, because pagination returned it twice or two runs overlapped, engines either error or write both depending on dialect. Deduplicating the staging set first, keeping the row with the latest source updated_at, is not optional.

Insert overwrite by partition is the pattern that scales. The pipeline owns a date partition and rewrites it wholesale, so a second run replaces the first run's output instead of adding to it. The cost is needing a stable partition column, the event's business date rather than ingestion date, or you can never correct history.

ELT vs ETL: why warehouses flipped the order

The original order existed for a hardware reason. A warehouse in 1998 ran on a fixed appliance with capacity paid for in advance, so wasting it on cleaning was unthinkable. Transformation happened on a separate server running a dedicated tool, only conformed data was permitted to land, and the raw source was usually discarded. The ETL job was the product, and its logic lived in a proprietary graphical canvas.

Cloud warehouses broke every assumption in that paragraph. Storage separated from compute and got cheap enough that retaining raw data stopped being a decision. Compute became elastic, so a heavy transformation at 2am no longer competed with a dashboard query at 2pm. Columnar engines made transforming in place beat shuttling data elsewhere. The industry flipped to ELT: extract, load the raw data as it is, then transform inside the warehouse.

The consequences run deeper than the letter order:

Transformation logic moved into the warehouse as SQL. That put it in version control with code review, tests, and lineage, and made analytics engineering a distinct role.

Raw data became durable, so backfills became routine. With the source retained, a logic bug is fixed by rewriting a model and rerunning it, and rebuilding two years of a metric under a new definition is a compute cost rather than a project. Under classic ETL the correct data was never captured, so fixing it meant re-extracting from a source that had changed or expired.

The failure surface moved. Instead of one monolithic job that either finished or did not, you have a graph of dozens of models where an upstream schema change makes a downstream model silently produce fewer rows instead of erroring.

ELT is not free. Warehouse compute becomes your transformation bill, and an unoptimized DAG rebuilt hourly costs real money. Raw data lands unmasked, so personal data sits in the warehouse before any redaction, a real reason to keep a transform step before the load for regulated fields. And because loading is cheap, you accumulate tables nobody has queried in a year. Where that cost lands is worked through in Enterprise Data Warehouse: Concept, Examples, and Cost.

Most modern stacks are ELT with a little T in the middle: light masking during ingestion, everything else in the warehouse.

Extract transform load in a data warehouse: incremental loads

Once a fact table passes a few hundred million rows, rebuilding it from scratch every run stops being reasonable and you move to incremental processing. Two disciplines make that safe.

A lookback window. Never process only the newest data. A pipeline that transforms "everything since the last run" misses late arrivals: the mobile app that batched events offline, the payment that settled two days after authorization, the source correcting a record retroactively. Reprocess a trailing window, typically three to seven days, using insert overwrite so it replaces rather than adds. You pay slightly more compute and corrections land automatically.

Idempotency. A job is idempotent when running it twice produces the same result as running it once. In a distributed system, at-least-once delivery is what you get: retries happen, two schedulers occasionally fire the same job, a network partition makes a successful write look like a failure. You cannot prevent duplicate execution, so make it harmless.

Idempotency comes from three things: deterministic keys, so the same source record always produces the same row identity; replace semantics rather than append, via merge or partition overwrite; and atomic swaps, where the new version is built into a temporary table and swapped in as one transaction so readers never see a half-written state.

The failure modes that produce duplicate rows recur across every stack:

1. Retry after partial commit. The job wrote 60% of a batch, failed, and the retry appended the whole batch again.
2. Overlapping runs. An hourly job that takes 70 minutes eventually runs concurrently with itself, and both instances process the same range.
3. Watermark inclusivity. Using >= instead of > reimports the boundary row every single run.
4. Unstable pagination. The API returned the same record on page 3 and page 4 because it was modified mid-scan.
5. Non-unique merge key. Two systems both use integer order ids, they were unioned without a source prefix, and merge matches the wrong rows.
6. Timezone boundary drift. A partition defined in local time is rebuilt with a UTC filter, so an hour of records exists in two partitions.

Each is trivially preventable and none announces itself. The report just looks slightly high.

Monitoring a broken extract transform load, without alert fatigue

Four checks catch nearly everything that goes wrong with an etl database pipeline.

Freshness. What is the maximum loaded_at, and is it inside the interval the pipeline promises? A stale table is more dangerous than a missing one, because dashboards keep rendering.

Volume. How many rows landed today against the trailing average for the same weekday? A pipeline that drops 90% of records because a source renamed a field does not error, it just returns less.

Schema drift. Did a column change type, disappear, or start arriving null? Type changes are the quiet killer, since a numeric field arriving as a string often coerces to null rather than failing.

Reconciliation. Does the warehouse total match the system of record? Yesterday's warehouse order count against the storefront's, warehouse revenue against the payment processor's daily total. This is the only check that catches the duplicate row problem from the opening paragraph, and the one teams skip.

The problem is not knowing which checks to run. Every check becomes an alert, every alert becomes a Slack channel, and within a quarter the channel is muted, the same triage failure worked through in Bug Reporting Software: Tools and Triage That Works. An alert that fires every morning and is usually noise trains people to ignore the morning it is not.

Where Skopx fits, and where it does not

The honest thing first: Skopx does not run extract transform load jobs. It is not an ETL tool, not a data warehouse, not an orchestrator, not a dashboard builder. It will not extract your Postgres tables, run your models, manage a DAG, or deduplicate a fact table. If you need a pipeline, buy pipeline tooling.

What it does is watch for downstream symptoms of a broken load in the systems that hold the truth. It connects to nearly 1,000 tools a company already uses, including Stripe, Shopify, QuickBooks, HubSpot, Google Analytics, Gmail, and Slack, answers questions in chat with cited data from them, and can turn a repeated check into an automation you describe in plain language.

That matters for one part of this problem: reconciliation. The most useful ETL alarm is not a job status, it is a mismatch between two independent systems that should agree. Comparing the payment processor's settled total against what accounting recorded catches duplicate loads, dropped batches, and stalled jobs from outside the pipeline, which is where that check belongs.

Daily revenue reconciliation alert

Every weekday 09:00

Runs after overnight loads should have finished

Read yesterday's Stripe charges

Settled total and transaction count

Read QuickBooks deposits

Same date range, same currency

Compare totals and counts

Difference in amount and in record count

Gap above threshold?

Stay silent when the two agree

Post the variance to Slack

Both figures, the delta, and the date

Automations like that are built by describing them in chat rather than configuring nodes, which is what workflows covers. A morning brief summarizes what changed overnight across connected tools, and an insights engine surfaces anomalies without anyone asking. Skopx uses BYOK, so you bring your own AI key for any major model with zero markup, and plans are Solo at $5 per month and Team at $16 per seat per month on the pricing page. SOC 2 controls are in place.

Now the limits. Skopx cannot see inside your warehouse jobs. It does not read your orchestrator's task history, has no lineage graph, cannot say which model in a DAG failed, and cannot rerun a backfill. For recurring scheduled checks it is a useful monitoring surface, and the boundary between that and genuine analytical tooling is the subject of Automated Market Analysis Software: What Actually Works.

The division of labor: your pipeline owns correctness, a chat-built check owns noticing. Conflating them is how teams end up expecting an assistant to fix a load they never made idempotent.

Frequently asked questions

What are the ETL process steps, in order?

Extract from source systems, transform into the shape and definitions analysis requires, load into the destination. A fourth step belongs in the list: verify, meaning the freshness, volume, schema, and reconciliation checks that tell you whether the result can be trusted. A pipeline without it is not finished, just unmonitored.

What is the difference between ELT vs ETL in one sentence?

ETL transforms data on a separate machine before it reaches the warehouse, while ELT loads raw data into the warehouse first and transforms it there with the warehouse's own compute. The elt vs etl choice used to be about hardware cost and is now mostly about where transformation logic lives, in a pipeline tool or in version-controlled SQL.

Why does my pipeline produce duplicate rows?

Almost always because the load step appends instead of replacing, combined with something that processes the same batch twice: a retry after a partial write, overlapping runs of a job that outlasts its interval, an inclusive watermark comparison, or unstable API pagination. Fix it by making the load idempotent, through merge on a genuinely unique key or insert overwrite by partition, not by adding a distinct downstream.

Do I need extract transform load in a data warehouse if I only use a handful of SaaS tools?

Frequently not. If your questions are about current state, which invoices are overdue, what shipped yesterday, which deals slipped, those systems already hold the answer, and copying them adds a pipeline to maintain and a lag to explain. Build extract transform load in a data warehouse when you need history the source overwrites, joins the APIs cannot serve, or a governed definition finance will sign off on.

How often should etl data pipelines run?

As infrequently as the decisions allow. Hourly and daily cover most real reporting needs, and each increase in frequency multiplies compute cost, raises the chance of overlapping runs, and grows the late-arriving data you reprocess. Ask what someone would do differently with fresher numbers. If the answer is nothing before tomorrow, run it nightly.