What Is ETL? Extract, Transform, Load Explained

ETL brings together three essential operations: extracting data from diverse sources, transforming it through cleaning and standardization, and loading it into a destination system for analysis. This article breaks down each stage, compares ETL to extract, load, transform (ELT) approaches, explores modern integration methods like change data capture (CDC) and streaming, and provides practical guidance for building pipelines that scale with your business.

Key takeaways

Here are the main points at a glance.

• ETL (Extract, Transform, Load) is the foundational process for moving data from multiple sources into a centralized system for analysis, turning fragmented information into a single source of truth.
• The three stages work together in sequence: extract pulls data from sources, transform cleans and standardizes it, and load delivers it to the destination.
• ETL differs from ELT in when transformation occurs. ETL transforms before loading (ideal for governance-heavy environments), while ELT transforms after (well suited to cloud-native scale).
• Modern ETL combines batch processing with streaming, CDC, and cloud-native tools to deliver more current data with lower latency than traditional approaches allowed.
• Choosing the right ETL approach depends on data volume, latency requirements, governance needs, and existing infrastructure.

What is ETL and why does it matter?

A quick note before diving in: "ETL" in a data context means Extract, Transform, Load. This is distinct from the Intertek "ETL Listed" product safety certification mark you might see on electrical equipment. This article focuses entirely on the data integration process.

ETL is the process of gathering data from multiple systems, cleaning and reshaping it, and loading it into a centralized destination such as a data warehouse, data lake, or analytics platform. By ensuring information is accurate, consistent, and well-structured, ETL turns scattered data into a single source of truth for confident, data-driven decisions.

At its core, ETL performs a specific set of operations on your data: clean, deduplicate, validate, standardize, enrich, filter, aggregate, format, join, mask, sort, and calculate. These transformation verbs describe what happens between extraction and loading. They're what make raw data actually useful for analysis.

Extract

The process begins by collecting raw data from various sources. ETL tools pull this dataregardless of format or locationso it can be processed and analyzed together.

Common source types include operational systems such as enterprise resource planning (ERP), customer relationship management (CRM), and human resources information system (HRIS) platforms, relational databases, SaaS APIs, flat files like CSVs and spreadsheets, event streams, and cloud storage platforms. The extraction method varies based on the source and requirements.

Three primary extraction approaches exist:

• Full extraction: Pulls all data from a source each time, useful for smaller datasets or initial loads
• Incremental extraction: Captures only records that changed since the last extraction, reducing processing time and resource usage
• Update notification: The source system signals when data changes, triggering extraction only when needed

Transform

Next, the data is prepared for analysis. This step involves reshaping and enriching information to make it consistent, meaningful, and reliable. Common transformations include:

• Cleansing: Fixing errors, removing duplicates, and filling in missing values so reports aren't skewed by bad data
• Standardization: Aligning formats such as dates, currencies, and naming conventions across systems
• Data enrichment: Adding context from external sourceslike demographic or regional datato enhance insights
• Calculations and business logic: Aggregating metrics, calculating ratios, and applying rules that match how your business measures performance
• Validation: Ensuring final datasets are accurate, compliant, and ready for use in dashboards, analytics, or AI models

Business rule application is a critical part of transformation. This includes revenue recognition logic that determines when a sale counts toward quarterly numbers, fiscal calendar normalization that aligns data across regions with different reporting periods, and currency standardization that converts transactions to a common currency for accurate comparison.

These transformations also shape data for dimensional modeling. The output feeds into fact tables (containing measurable events like sales or clicks), dimension tables (containing descriptive attributes like customer names or product categories), and surrogate keys that maintain referential integrity across your data warehouse.

Load

Finally, the transformed data is loaded into a centralized system, making it easily accessible for reporting, advanced analytics, and decision-making. Common destinations include data warehouses (optimized for structured queries), data lakes (flexible storage for structured and unstructured data), lakehouses (combining warehouse structure with lake flexibility), and data marts (focused subsets for specific teams or use cases).

Loading into these pre-processed destinations rather than querying operational systems directly provides two key benefits: performance (analytical queries don't slow down transactional systems) and governance (data quality rules are applied before anyone can access the data).

Two primary load patterns exist:

• Full load: Replaces all existing data with the new dataset, useful for smaller tables or when complete refreshes are needed
• Incremental load: Adds only new or changed records, which can happen in batches (hourly, daily) or via streaming for near-real-time updates

Why ETL matters

ETL transforms raw, fragmented information into actionable insights. By automating and standardizing how data is prepared, ETL helps businesses:

• Integrate data from multiple sources to create a unified view across departments
• Improve data quality through cleansing, normalization, and validation
• Power analytics and AI with structured, analysis-ready data
• Streamline workflows and reduce the manual effort spent on data preparation
• Eliminate data silos for greater collaboration and transparency
• Support a modern data strategy that enables scalability, innovation, and quicker decision-making
• Maintain historical tracking and compliance by creating a reliable audit trail that supports trend analysis, regulatory reporting, and data lineage requirements

How ETL works step by step

Understanding the individual stages is one thing. Seeing how they connect as a complete pipeline? That's where it gets interesting.

The journey starts at your data sources: operational databases, SaaS applications, file drops, or event streams. From there, an extraction method (API calls, CDC listeners, or scheduled batch pulls) moves data into a landing or staging area. This staging zone holds raw data temporarily before any transformation occurs.

Transformation logic kicks in next. Business rules are applied, data quality tests run, and the data is reshaped into its target format. Once validated, the data moves to the load phase, where it's either appended to existing tables or merged using update logic that handles both new and changed records.

Orchestration ties everything together. Schedulers manage job dependencies, ensuring extracts complete before transforms begin and transforms finish before loads start. Monitoring dashboards track pipeline health, flag anomalies, and alert teams when something breaks.

Failure handling matters here. Well-designed ETL pipelines include retry logic for transient errors, checkpointing so jobs can resume from where they failed, and alerting that notifies the right people when intervention is needed. A successful job run does not mean the data is correct. Always validate row counts and data quality downstream, not just job completion status.

In modern data architectures, this flow often maps to a layered model: a raw layer (data as it arrived), a staged layer (cleaned and validated), and a curated "gold" layer (business-ready, trusted data).

ETL in SQL: what it looks like in practice

SQL plays a central role in the Transform and Load steps of most ETL pipelines. Here's what that typically looks like:

Data lands in a staging table, often created with a simple CREATE TABLE or loaded via bulk insert. A SQL transformation then applies business rules: deduplication using ROWNUMBER() window functions, data type conversions with CAST or CONVERT, and calculations that derive new columns from existing ones.

The Create Table As Select (CTAS) pattern is common for building transformed datasets. You write a SELECT statement with all your transformation logic, and the result becomes a new table ready for analysis.

For incremental loads, MERGE statements (sometimes called UPSERT operations) handle the complexity of updating existing records while inserting new ones. The statement compares incoming data against the target table using a key, then decides whether to update, insert, or sometimes delete. Without proper indexing on the join keys, these operations can become painfully slow or lock tables for extended periods. Teams often learn this the hard way.

Window functions like LAG, LEAD, and running totals help with calculations that depend on row order, such as identifying the most recent transaction per customer or calculating cumulative revenue.

This SQL-based approach works well when your transformation logic is straightforward and your data warehouse has strong compute capabilities.

{{custom-cta-1}}

ETL vs ELT: what's the difference

Where does transformation happen? That's the core distinction. In ETL, data is transformed into the required format before being loaded into its final destination. ELT flips the sequence, loading data into the target storage system first and performing transformations as needed.

At a glance, ETL is ideal for scenarios where data transformation is a known and necessary step before storage. ELT suits cases requiring on-demand data transformation, often using modern cloud-based systems.

Advantages of ETL

ETL offers a few clear strengths.

• Ensures high data quality by transforming data before loading it into the destination
• Compatible with on-premises systems
• Produces clean, structured datasets ready for immediate use

Disadvantages of ETL

ETL also comes with a few tradeoffs.

• The preprocessing step can slow down operations
• Transformation processes demand significant computational resources
• Less flexible in adapting to evolving data requirements

Advantages of ELT

ELT has a few strengths worth noting.

• Highly scalable due to reliance on cloud storage and computing
• Supports near-real-time data processing for more timely insights
• Eliminates the need for lengthy preprocessing, speeding up workflows

Disadvantages of ELT

ELT also has a few limitations to keep in mind.

• Storing raw data can lead to higher costs
• Requires a cloud infrastructure with enough capacity
• The transformation process can be more complex to implement

Both ETL and ELT offer unique benefits depending on your data integration needs. ETL prioritizes structure and quality upfront, while ELT emphasizes flexibility, scalability, and speed.

When ETL is the right choice: a decision framework

Knowing the differences between ETL and ELT is helpful. Knowing which to choose for your specific situation is more useful.

Here are three common scenarios and the approach that fits each:

• Regulated data with personally identifiable information (PII): When you're handling sensitive information that must be masked or anonymized before it touches your warehouse, ETL is the safer choice. Transforming before load means sensitive data never enters your analytical environment in raw form, simplifying compliance with the General Data Protection Regulation (GDPR), the Health Insurance Portability and Accountability Act (HIPAA), or internal policies.
• High-volume semi-structured data with cloud compute: When you're ingesting large volumes of JSON, logs, or event data and your destination warehouse (like Snowflake, BigQuery, or Databricks) has strong compute capabilities, ELT makes more sense. Load the data quickly, then push transformation logic into the warehouse where compute scales elastically.
• Near-real-time operational metrics: When dashboards need to reflect changes within minutes rather than hours, consider CDC (change data capture) combined with micro-batch processing. This hybrid approach captures changes as they happen and processes them in small, frequent batches rather than waiting for a nightly ETL run.

The right answer often involves combining approaches.

Modern data integration methods and when to use them

What happens when traditional ETL isn't enough? Modern data integration goes beyond the basics. Today's data ecosystems use a mix of methods (ETL, ELT, CDC, streaming, APIs, and virtualization) to move and manage information efficiently. Each plays a distinct role depending on how often data changes, where it lives, and how quickly insights are needed.

ETL and ELT

ETL (Extract, Transform, Load) remains the go-to process for ensuring data quality and consistency before it enters a data warehouse or analytics platform. It's ideal when governance, structure, and accuracy are top priorities.

ELT (Extract, Load, Transform) flips the sequence, loading data first and transforming it inside the target system. This approach works best for cloud-native environments where compute power can handle transformations at scale.

When to use each:

• Choose ETL when you need controlled, compliant datasets ready for immediate analysis or when operating in on-prem or hybrid environments
• Choose ELT when speed, flexibility, and scalability are key, especially in cloud-first architectures
• Combine both by using ELT for fast ingestion and ETL for refined, production-grade data pipelines

Change data capture (CDC)

CDC captures only what's changed since the last data load. Efficient. It keeps downstream systems in sync and enables near-real-time analytics without constantly reprocessing entire datasets.

Streaming data integration (SDI)

Streaming ETL and event-based integration continuously ingest and transform live data from sources like sensors, apps, or user activity. It's ideal for scenarios that require instant insight: fraud detection, personalization, performance monitoring.

API-led and event-driven integration (EAI)

APIs and event streams connect applications and workflows in real time. Use API-led integration to trigger data movement based on business eventslike a new order or shipment, and to embed analytics directly into operational processes. Paired with ETL/ELT, this approach combines transactional speed with analytical depth.

Data virtualization and federation

These methods give people a unified view of data across multiple systems without physically moving it.

• Data virtualization allows teams to query diverse sources in real time for ad-hoc analysis
• Data federation aggregates queries across databases through a virtual layer, enabling quick, cross-system answers without building pipelines

Putting it all together

Most modern architectures blend these methods:

• CDC and streaming keep data fresh and synchronized
• ETL and ELT curate trusted, analysis-ready datasets
• APIs, virtualization, and federation provide flexible, near-instant access for exploration and collaboration

What makes a good ETL workflow?

A strong ETL workflow does more than move data. It ensures the process is efficient, secure, and built for scale. A well-designed ETL pipeline automates extraction, transformation, and loading across systems while maintaining data quality, compliance, and performance from end to end.

Key components

A modern ETL architecture includes several interconnected layers:

• Data sources: Where it all begins, including databases, APIs, files, and streaming platforms
• Ingestion layer: The mechanisms that extract data from sources (batch pulls, CDC listeners, API connectors)
• Staging/landing zone: Temporary storage for raw data before transformation
• Transformation engine: The tools and logic that clean, standardize, and reshape data
• Orchestration layer: Schedulers and workflow managers that coordinate job dependencies and timing
• Data quality and testing layer: Validation rules, schema checks, and automated tests that catch problems early
• Storage targets: The final destinations, whether warehouse, lake, lakehouse, or mart
• Lineage and catalog layer: Metadata management that tracks where data came from and how it changed
• Observability and alerting: Dashboards and notifications that surface pipeline health and failures
• Access control layer: Role-based permissions and encryption that protect sensitive data

ETL tools automate every stage of the process, from extracting data from diverse sources to loading it into your final destination. The best tools reduce manual effort, improve consistency, and include features like encryption, regulatory compliance (for example, GDPR or HIPAA), and scalability to handle growing data volumes.

Scheduling and monitoring

Effective orchestration keeps pipelines reliable and predictable.

• Scheduling: Use automated schedulers to manage dependencies, retries, and data refresh cycles
• Monitoring: Track pipeline performance with dashboards that flag anomalies, schema drift, or failed jobs
• Quality checks: Implement validation rules, row-count checks, and threshold alerts to ensure data accuracy

Specific monitoring practices that catch problems before they reach people include:

• Freshness checks: Is the data current, or did a job silently fail?
• Row-count reconciliation: Did all expected records arrive, or did some get dropped?
• Referential integrity tests: Do foreign keys resolve correctly across tables?
• Anomaly detection: Are values within expected ranges, or did something unusual slip through?
• Failure alerting: When jobs fail, who gets notified and what's the escalation path?

Metadata and lineage

Metadata management provides visibility into how data moves through your ecosystem. A good workflow tracks:

• Source systems and data owners
• Transformation logic and dependencies
• Audit trails and version control

Clear lineage helps teams trust their data, troubleshoot issues more quickly, and stay compliant with governance requirements.

Security and governance

Governance is not an afterthought. It's built into the workflow.

• Apply role-based access controls and encryption for data in motion and at rest
• Mask or anonymize sensitive information such as PII
• Embed compliance checks to meet internal and external standards

Performance and cost optimization

Efficient ETL workflows balance performance with cost. Optimize compute resources, prune unused data, cache intermediate results, and partition large datasets to keep processing fast and cost-effectivewithout compromising data freshness or accuracy.

Modern ETL: from traditional to cloud

ETL emerged alongside relational databases to transform raw, transactional data into structured, analyzable formats. Early ETL tools converted data from transactional systems (designed for frequent reads and writes) into relational formats using interconnected tables, enabling analysts to uncover trends and patterns.

How ETL evolved (quick timeline)

1970s–1980s: Early data consolidation

Organizations moved from many transactional systems to centralized stores. Teams began extracting data, standardizing formats, and loading it into analytic environments to answer cross-functional questions.

1990s–2000s: Data warehousing and BI

Relational databases and star/snowflake schemas popularized ETL as the standard path from messy operational data to clean, query-ready tables for reporting and business intelligence.

2010s: Big data and the cloud

Volume, velocity, and variety expanded with web, mobile, and internet of things (IoT) data. Cloud data warehouses and lakes made storage and compute elastic, and ELT emerged as an alternative when loading first and transforming inside the warehouse takes less time.

Today: Real-time and AI-ready

Modern stacks blend batch ETL, ELT, and streaming pipelines. Change data capture, event streams, and automation deliver fresher data to dashboards, machine learning (ML) models, and applicationssafely and at scale.

Data warehouses

A data warehouse serves as a central repository for structured data, organized into tables and columns. It integrates with various storage hardware, such as solid-state drives (SSDs), hard drives, and cloud storage, to optimize data processing, making it ideal for analytics.

Data lakes

A data lake stores structured and unstructured data in one scalable location. Unlike warehouses, data lakes let you store raw data without pre-structuring. This provides flexibility to run diverse analytics, such as SQL queries, machine learning, and real-time insights.

Common ETL challenges and how to fix them

Scalability

ETL processes work well for small to medium-sized data batches but face challenges with large-scale datasets. These operations are resource-intensive and require significant computational power, limiting their efficiency in big data environments.

Processing latency

Complex data transformations can lead to longer processing times. When working with large datasets that require intricate transformations, delays can impact real-time analytics and time-sensitive applications.

Data quality issues

Integrating data from multiple sources often introduces inconsistencies, incomplete records, or errors. Differences in data formatting, missing values, or conflicting information create unreliable datasets, ultimately affecting decision-making and data integrity.

Strategies to overcome ETL challenges

Organizations can address scalability challenges by using cloud-based ETL tools. These solutions handle large and complex datasets efficiently, allowing businesses to scale their data operations seamlessly. When it comes to latency in preprocessing, selecting ETL tools with built-in transformation features can significantly reduce processing time. To ensure data quality, implement thorough quality checks and validation processes at every stage of the ETL pipeline, especially before the loading phase.

Automate workflows

Use ETL tools to automate recurring tasks. This reduces manual effort and minimizes the risk of human error.

Document processes

Maintain detailed documentation of your ETL workflows, including steps, tools, and configurations. Proper documentation ensures transparency, simplifies troubleshooting, and supports knowledge transfer within teams.

Monitor and optimize performance

Track ETL performance metrics regularly to identify bottlenecks and areas for improvement. Updating tools and configurations as business needs evolve ensures optimal performance over time.

Adhere to data governance standards

Implement data governance policies to ensure data security, compliance, and consistency throughout the ETL process. This includes managing access controls, maintaining data lineage, and meeting regulatory requirements.

Best practices for ETL success

Getting ETL right isn't just about moving data. It's about creating a foundation for more informed decisions.

1. Start with the business need: Define the questions you're trying to answer before building pipelines. This keeps your efforts focused and avoids wasted work.
2. Automate wherever possible: Manual data prep is slow and error-prone. Use modern ETL or ELT tools with automation features to schedule jobs, monitor performance, and reduce hands-on effort.
3. Keep transformations transparent: Document your rules and calculations so stakeholders can see how numbers are derived. Transparency builds trust in your data.
4. Design for idempotency: ETL jobs should produce the same result when re-run after a failure, without duplicating data. This means using merge logic instead of simple inserts and designing jobs that can be safely retried. Plan for backfill scenarios and late-arriving data from the start. Many teams learn this lesson the hard way after a failed job creates thousands of duplicate records.
5. Plan for scale and flexibility: As your data grows, so should your infrastructure. Cloud-native ETL solutions like Domo Data Integration make it easy to scale and adapt as new sources are added.
6. Prioritize governance and security: Build access controls, audit trails, and compliance checks into your pipelines from the start, rather than bolting them on later.
7. Use vendor-neutral selection criteria: When evaluating ETL tools, assess connector coverage (does it connect to your sources?), incremental and CDC support, observability integrations, cost model (compute-based vs. flat fee), and governance features. This framework helps you choose tools that fit your architecture rather than forcing your architecture to fit a tool.

ETL in action: use cases by industry

Retail: streamlining sales and inventory management

In the retail industry, consolidating sales data from multiple store locations and online channels is key to gaining a comprehensive view of overall performance. Analyzing this data helps identify sales trends, optimize inventory management to reduce overstock or stockouts, and accurately forecast demand. Additionally, these insights enable the creation of targeted marketing strategies that resonate with specific customer segments.

Finance: enhancing risk assessment and fraud prevention

The finance sector relies heavily on data integration to make informed decisions. Merging customer information from various sources (transaction history, credit scores, demographic details) enables more accurate risk assessments. Financial institutions can then offer personalized financial products, such as tailored loan options or investment plans, that align with individual customer profiles. Integrating data also enhances fraud detection systems.

Healthcare: improving patient care and compliance

In healthcare, integrating data from Electronic Health Records (EHR), billing systems, and laboratory databases creates a unified view of patient care. This comprehensive approach improves the accuracy of diagnoses by providing healthcare providers with all necessary patient information in one place. It also enhances treatment planning, allowing for more personalized and effective care. Furthermore, integrated data helps streamline regulatory reporting and compliance processes.

A worked ETL example: from source to warehouse

To make this concrete, here's what an ETL process looks like for a retail company consolidating daily sales data:

Source data arrives from three systems: a point-of-sale database with transaction records, an e-commerce platform API with online orders, and a flat file export from a third-party marketplace.

During extraction, the ETL tool pulls yesterday's transactions from each source. The point-of-sale (POS) data includes fields like transactionid, storeid, productsku, quantity, saleamount, and transactiontimestamp. The e-commerce data has similar fields but uses different naming conventions (orderid instead of transactionid, itemcode instead of productsku).

Transformation applies several rules:

• Field standardization: Rename e-commerce fields to match the target schema (orderid becomes transactionid, itemcode becomes productsku)
• Currency normalization: Convert all saleamount values to USD using the exchange rate from the transaction date
• Deduplication: Use a composite key of transactionid plus sourcesystem to identify and remove duplicate records that might appear in multiple extracts
• Derived calculations: Add a grossmargin field by joining to a product dimension table and applying the formula (saleamount - productcost) / saleamount

The load step merges this transformed data into a salesfact table in the warehouse. New transactions are inserted; any corrections to existing transactions (identified by matching transactionid and sourcesystem) update the existing rows.

The result: analysts query a single salesfact table that contains clean, standardized, deduplicated sales data from all three channels.

4 major types of ETL tools

Rather than listing specific vendors, it helps to understand ETL tools by what they do. Modern data stacks typically include tools from four functional categories:

Ingestion tools focus on getting data out of source systems and into your data platform. They handle connector management, scheduling, and the mechanics of extraction. Look for broad connector coverage, support for incremental loads and CDC, and reliability features like automatic retries.

Transformation tools apply business logic to your data. Some operate on data in transit (before it reaches the warehouse), while others push transformation logic into the warehouse itself. Key considerations include support for SQL and code-based transformations, version control integration, and testing capabilities.

Orchestration tools coordinate the entire pipeline: scheduling jobs, managing dependencies, handling failures, and providing visibility into what's running. Evaluate based on DAG (directed acyclic graph) support, alerting integrations, and ease of debugging failed runs.

BI endpoint tools sit at the end of the pipeline, making transformed data accessible for analysis. While not strictly ETL, they often include light transformation capabilities and semantic layers that shape how business people interact with data.

When evaluating tools in any category, consider these criteria:

• Connector breadth: Does it connect to the sources and destinations you need?
• Incremental load and CDC support: Can it efficiently handle changing data without full reloads?
• Built-in observability: Does it provide monitoring, logging, and alerting out of the box?
• Governance integrations: Does it support lineage tracking, access controls, and audit logging?
• Total cost of ownership: What's the pricing model, and how does it scale with your data volume?

{{custom-cta-2}}

Integration of ETL with data warehouses and data lakes

Data warehouses:

ETL plays a crucial role in populating data warehouses by extracting, transforming, and loading data into a structured format optimized for querying and analysis. Data warehouses support business intelligence (BI) tools, enabling organizations to derive insights from historical and transactional data. This structured approach makes them ideal for reporting, trend analysis, and decision-making processes.

Data lakes:

ETL can also feed data lakes, which store vast amounts of raw, unstructured, and semi-structured data. Unlike data warehouses, data lakes handle diverse data types, from text and images to log files and streaming data. Loading and transforming data in a data lake supports advanced analytics, machine learning models, and exploratory data science projects.

Domo makes data transformation possible for more people. With Domo's drag-and-drop ETL tool, you can extract data from multiple sources, transform it, and load it into Domo. No need to code in SQL. Learn more about how it works.