StackLift
All terms

Glossary

ETL (extract, transform, load)

A pattern that extracts data, cleans and reshapes it, then loads it into a target system.

ETL stands for extract, transform, load. Data is pulled from source systems, cleaned and reshaped in a separate processing step, and then loaded into a destination such as a data warehouse. The closely related ELT pattern loads the raw data first and transforms it inside the destination, which is common on modern cloud platforms.

ETL still fits when data must be filtered, masked or standardized before it lands, for example to keep sensitive fields out of the warehouse. ELT fits when the platform has cheap storage and strong compute, so raw data can be kept and reshaped later.

Change data capture is a separate choice about how changes are picked up from a source database. Orchestration, meaning scheduling, retries and dependencies, is a third. Treat the three as separate decisions.

Start in 60 seconds

Put this to work on your project.

Get an AI-assisted, architect-reviewed scope, cost range, and timeline. Free, no login.

The complete guide

ETL (extract, transform, load): the complete guide

Updated November 18, 2026 ยท Published by , Founder, StackLift AI. Written with AI assistance; how we make content.

Short answer

ETL stands for extract, transform, load. Data is pulled from source systems, cleaned and reshaped in a separate processing step, then loaded into a destination such as a data warehouse. In ELT the raw data is loaded first and transformed inside the destination, which is common on modern cloud platforms.

What is ETL?

In plain terms, ETL is how a business gets data out of the places where it is created and into the place where it can be analyzed. Orders sit in a shop database, payments in a processor, leads in a CRM. ETL collects them, makes them consistent and puts them side by side.

More precisely, ETL is a pattern for building a data pipeline. Extract means reading data from a source such as a database, an API or files. Transform means cleaning, converting, joining and calculating. Load means writing the result to the target.

The pattern dates from when warehouses had limited storage and compute, so data was reduced and shaped before loading. Cloud platforms changed that balance, which is why the order of the steps is now a real choice rather than a given.

Why does ETL matter for a business?

Most reporting problems are data problems. Two dashboards show different revenue because one counts refunds and the other does not. A sales report is stale because someone exports a spreadsheet by hand each Monday. ETL gives every number a single, repeatable path from source to screen.

  • One definition of each metric, written in code and kept in version control.
  • Fresh data without manual exports, on a schedule or as changes arrive.
  • A history you can audit: what ran, when, and how many rows moved.
  • A clean base for AI agents and RAG systems, which are only as good as the data they read.
  • Sensitive fields masked or dropped before they spread to more systems.

For a software project, ETL is usually the quiet foundation under dashboards, billing reconciliation, search indexes and machine learning features. If it is flaky, everything built on it is flaky too.

How does ETL work, step by step?

  1. Extract data from each source, either in full or only what changed since the last run.
  2. Land it in a staging area, untouched, so you can always see what the source sent.
  3. Validate it: data types, required fields, duplicate keys, impossible dates.
  4. Transform it: standardize formats, join sources, calculate fields, mask sensitive values.
  5. Load it into the target tables, replacing or merging rows so a rerun does not create duplicates.
  6. Log the run with row counts, and alert someone when it fails or when counts look wrong.

Each step can fail on its own, which is why good pipelines keep them separate. When the transform has a bug, you want to fix it and rerun from staging, not go back to the source.

ETL vs. ELT: what is the difference?

ETL and ELT compared
PointETLELT
Where transformation runsIn a separate processing layer, before loadingInside the warehouse or lakehouse, after loading
Raw data kept?Often notYes, so you can re-transform later
Good forMasking or filtering before data lands, legacy targetsCloud platforms with strong compute, analytics that change often
ToolsIntegration engines, custom codeManaged loaders plus SQL tools such as dbt
Main riskRework when logic changesRaw sensitive data sits in the warehouse unless controlled

ELT is the common default on data warehouses and lakehouses. Storage is cheap, compute scales, and analysts can change a model in SQL without asking an engineer to rebuild a pipeline. ETL remains a good fit when data must be cleaned or masked before it reaches the destination.

Many real systems mix the two. A small step before loading removes personal fields, and the heavy modeling happens afterward in the warehouse.

What kinds of ETL are there?

Common ways to run extract, transform, load
ApproachWhat it isWhen to use it
BatchRuns on a schedule, such as hourly or nightlyReporting where yesterday's data is fine
Micro-batchRuns every few minutes on small slicesDashboards that should feel current without true streaming
StreamingProcesses events as they arriveAlerts, fraud signals, live inventory
Change data captureReads a database's change log and passes along inserts, updates and deletesKeeping a warehouse close to an operational database without heavy queries
Reverse ETLSends modeled warehouse data back into operational toolsPutting a customer score into the CRM so sales can act on it

Change data capture reads a database's change log, such as PostgreSQL's write-ahead log, to pass along every insert, update and delete. It is a way to extract, and works with either ETL or ELT.

What are real-world examples of ETL?

  • An online store combines orders, ad spend and refunds into one revenue table for finance.
  • A clinic group pulls appointment data from several scheduling systems into one reporting model, with patient names masked before loading.
  • A logistics company merges carrier status feeds into a single shipment history for a customer portal.
  • A SaaS company loads product events and billing data together to see which features drive renewals.
  • A legacy modernization project moves records from an old database into a new schema, transforming codes and cleaning duplicates on the way.
  • A support team indexes tickets and knowledge articles nightly so an AI assistant can search them.

The legacy case is a one-time migration that behaves like ETL. See the data migration plan and legacy data audit playbooks for how those are planned.

When is classic ETL still the right choice?

  • Personal or regulated fields must be masked or dropped before storage.
  • The target is a system that cannot run heavy transformations.
  • Source data needs heavy cleanup that is easier in code than in SQL.
  • You move data between operational systems, where iPaaS tools are built for it.

A regulation may limit where certain data can be stored or processed. The plan should cover what it requires, and your compliance owner should confirm the details.

Should you build ETL yourself or use a tool?

Standard sources, such as popular SaaS tools and databases, are usually better handled with a managed connector than custom code, because connectors track API changes for you. Custom code is justified for unusual sources, special logic or tight control.

Options for building pipelines
OptionStrengthWatch out for
Managed connectors (Fivetran, Airbyte)Fast setup for common sourcesCost grows with volume, limited custom logic
SQL modeling (dbt)Transformations as tested, versioned SQLNeeds a warehouse and discipline in naming
Orchestrators (Airflow, Dagster, Prefect)Scheduling, retries, dependenciesSomeone has to run and upgrade them
Custom code (Python, Node.js)Full control, odd sourcesYou own every API change and failure
iPaaSApp-to-app flows with low codeLess suited to heavy analytics loads

Whichever you choose, keep transformations in version control, test them, and document the definitions behind each metric.

What makes an ETL job reliable?

Reliable jobs are boring. They can run twice without creating duplicates, restart from where they failed, and tell someone when something is wrong.

  • Make loads idempotent, so a rerun gives the same result.
  • Load incrementally where possible, and keep a watermark of what was last read.
  • Keep credentials in a secrets manager, never in code.
  • Log each run with counts in and out.
  • Keep a way to backfill a date range after a bug fix.
  • Treat schema changes at the source as an expected event, with an alert, not a surprise.

These habits matter more than the choice of tool, and they are part of the delivery plan from the first sprint.

What are the most common ETL mistakes?

Mistakes and fixes
MistakeWhat happensFix
Transforming in the sourceLogic is scattered and cannot be rerunLand raw data first, transform in one place
No tests on the outputWrong numbers reach a dashboard unnoticedCheck counts, nulls and key uniqueness on every run
Full reloads foreverRuns slow down as data growsMove to incremental loads or change data capture
Silent failuresA job stops and nobody knows for daysAlert on failure and on unusually low row counts
Undefined metricsTeams argue over whose number is rightWrite each definition once and link it to the model
Hard-coded secretsCredentials leak through the repositoryUse a secrets manager and rotate keys

How do you know your ETL is working?

Watch three families of signals. Freshness tells you how old the newest data in a table is compared with what users expect. Completeness compares row counts and totals with the source. Reliability covers how often runs succeed on the first try and how quickly failures are fixed.

A good dashboard for the pipeline itself shows the last successful run per table, failures by cause, and the time each stage takes. Add a small set of reconciliation checks, such as total order value in the warehouse against the source, and review them on a schedule. This is the same thinking as observability for applications.

What drives the cost and timeline of ETL work?

Effort follows the number of sources, how messy each one is, how many transformed models you need, and how fresh the data must be. Streaming and change data capture cost more to build and run than nightly batches. Data quality problems in the source are the usual surprise, because someone has to decide what the correct value is.

Run costs include connector fees, warehouse compute and storage, and the time to maintain the pipelines. For ranges by project type, see cost to build a data dashboard and the data analytics cost page. The free estimate scopes sources and models feature by feature.

How StackLift approaches ETL work

StackLift starts with structured discovery: which sources exist, who owns them, what each metric means and how fresh the data must be. A senior architect reviews the scope, and ETL or ELT is scoped as deliverables per source and per model, priced by complexity and not by hours.

Agents and engineers work on feature branches, never on production secrets. CI/CD, observability and rollback are in the plan from sprint one, and the client portal shows live project state. See system integration, workflow automation and the free estimate.

Questions people ask

Is ELT replacing ETL?

ELT is the default on many cloud platforms, but ETL remains useful when data must be changed before it lands. Many teams use both in the same stack.

What does ETL stand for?

Extract, transform, load: read from sources, reshape the data, and write it to a destination.

What is reverse ETL?

Moving modeled data from the warehouse back into operational tools such as a CRM, so teams can act on it where they already work.

Is ETL the same as data integration?

ETL is one technique for data integration. Integration also covers real-time APIs, events and app-to-app flows.

How do you test an ETL job?

Check row counts, null rates and key uniqueness, compare totals to the source, and run the job on a sample before a full load. Keep these checks in the pipeline so they run every time.

Do I need a data warehouse to do ETL?

No. ETL can load into any database or file store. A warehouse becomes worth it when analytics queries start to slow your operational database or when you combine many sources.

What is the difference between ETL and a data pipeline?

A data pipeline is any automated movement of data. ETL is a specific kind of pipeline with an extract, transform and load shape. Streaming and sync jobs are pipelines that are not classic ETL.

Can ETL handle unstructured data such as PDFs?

Yes, with extra steps. Text is extracted, split into chunks and often turned into embeddings for search. See the chunking and embeddings terms for how that fits into AI systems.

Sources

Sources and further reading

The standards, rules, research and official documentation this page draws on.