Extract, transform, load: ETL and ELT workflow boundaries

By Ai Flowi · Published 10 October 2026

Extract, transform, load (ETL) is a data pipeline that reads records from a source, changes them into an agreed representation and writes the accepted result to a destination. ELT changes the order: extracted records are loaded before transformation happens in the destination environment. The useful boundary is where validation, rejected records and recovery are handled, not simply which tool moves the data.

What the three stages mean

Extraction identifies which source records belong to a batch and reads them without silently changing their meaning. A CSV export, a database query or an API response can supply that batch. Preserve its source reference and the selection rule so a later operator can explain why a record was included.

Transformation applies the destination contract: field names, accepted types, units, required values and rejection rules. An identifier containing leading zeros is text even if it contains only digits. Converting it to a number can destroy information before the load has started. Keep the raw value beside any revised value when the change matters to an audit or correction.

Loading writes accepted records and checks what the destination actually retained. A request being sent is weaker evidence than a confirmed write. If the response disappears after a write, the outcome is unknown until the destination is inspected; repeating the request immediately can create duplicates.

One fictional batch, with decisions attached

Synthetic teaching example; these rows and counts are not a recorded workflow result. Imagine an inventory export whose contract requires a text item ID and a non-negative integer quantity. The batch contains four rows. Each source row receives a stable reference independent of its item ID.

The extraction count is four; the transformation decision count is two accepted plus two rejected. If only A is confirmed written, loading is incomplete: one accepted row is confirmed and the other remains pending or uncertain. Do not report two loaded rows merely because transformation produced two candidates.

A rejection artifact should retain the source reference, raw values and a specific reason. Correcting C creates a new reviewed decision linked to C; it should not erase evidence of the first attempt. For a detailed transfer configuration, follow CSV to Google Sheets.

What is the difference between ETL and ELT using the same records?

ELT is not permission to publish unvalidated data. The staging area needs appropriate access and a clear distinction from the table used for reporting. ETL can keep unsuitable records out of a target, but it still needs somewhere safe to preserve rejections. Neither ordering establishes transaction support or safe replay by itself.

Related: Reverse ETL: understand operational data activation and write boundaries.

Checkpoints, watermarks and replay

A watermark describes progress through a changing source, such as the last reviewed update position. A checkpoint records completed work at a defined boundary. Recording extraction progress before confirming the corresponding load can skip records after a failure. Keep source-selection progress separate from destination confirmation.

When several records share the same update time, a timestamp alone may not identify a complete boundary. Define a stable tie-breaker or an overlapping read with deliberate duplicate handling. Late changes need their own reconciliation policy; a newer watermark does not prove older records will never change.

For replay, identify the batch and each source record. Decide whether a repeat should overwrite, merge, be ignored or be reviewed. An append action cannot establish that policy on its own. If a partial load fails, inspect confirmed destination records and retry only through the chosen recovery rule.

Representing the inventory boundaries in the builder

Use the Load CSV, JSON Operations and Google Sheets write references for the read, mapping and destination interfaces in this batch illustration.

The pipeline-specific decision is when a source row becomes eligible for a write. A and B qualify under the inventory contract; C and D remain in the rejection ledger. Do not send all four rows to the writer and assume that destination formatting will perform validation. For this proposed inventory pipeline, Load CSV, JSON Operations and Append Rows are documented in the catalogue; not production-tested by Ai Flowi. The combined read, mapping and write configuration is untested.

example configuration, not a recorded test

1. Supply the fictional export through Load CSV.csv_file; inspect its JSON rows before selecting fields.
2. For a reviewed mapping trial, set JSON Operations.data to inspected source JSON and choose "JQ Expression" in operations. Put the reviewed validation and row-mapping expression in query; retain rejected records separately before selecting load candidates.
3. Append Rows receives the approved arrays in values; choose "RAW" for value_input_option to retain the reviewed cell representations during this proposed write. Bind range_name to the inventory area, spreadsheet_id to its document and google_account to the authorised connection.
4. Compare the written values and accepted-row ledger before marking the batch complete.

Reconciliation needs row identities as well as totals. A destination containing A twice has two rows, but it has not loaded A and B correctly. Compare the accepted source-reference set with the confirmed destination-reference set, then inspect quantities against the reviewed candidates. Keep B pending if its write cannot be confirmed. A batch total may balance while one item is missing and another is duplicated.

Use the CSV-to-sheet setup guide for account and connection prerequisites; bring a permitted inventory export to the builder for this batch illustration. The inventory contract uses explicit rules without a model call; any provider charges sit outside the Flowi subscription.

Schema drift and limits

If a source column disappears, changes meaning or starts mixing units, stop at the affected contract boundary. A field rename is safe only when its meaning remains known. Keep a schema version and reject unrecognised changes rather than allowing a blank column to pass as valid data.

This batch needs three separate artifacts: accepted records, a rejection ledger and evidence of destination writes. Native change-data capture, atomic multi-stage loading and exactly-once delivery are not established by these catalogue entries. Use a system with verified transaction and recovery behaviour when those requirements govern the task. Browse documented use cases for the scope of existing recipes.

Related terms

Data cleaning explains corrections and unresolved values within transformation. API integration concerns the service boundary through which extraction or loading may occur.