ETL vs ELT: How Data Gets into Your Dashboards

ETL and ELT are two ways of moving data from source systems into a place where you can analyze it. In ETL (Extract, Transform, Load) data is cleaned and reshaped before it is loaded into the warehouse; in ELT (Extract, Load, Transform) raw data is loaded first and transformed inside the warehouse, usually with SQL. ELT has become the common pattern with cloud data warehouses, but ETL is still the right choice in plenty of situations.

The three steps: extract, transform, load

Every pipeline that feeds a dashboard does the same three jobs, whatever the order.

• Extract: read data from the sources. That could be an ERP or POS database, a CRM, a spreadsheet on SharePoint, a CSV export from a retailer portal, or an API like Shopify or Google Ads.
• Transform: make the data usable. Fix data types, standardize names ("FL", "Fla.", "Florida"), remove duplicates, join tables, calculate columns, and reshape everything into facts and dimensions.
• Load: write the data into its destination, usually a data warehouse, lakehouse or database that your BI tool connects to.

The difference between ETL and ELT is simply where and when the transform step happens.

How ETL works

In ETL, a separate tool or server pulls data from sources, transforms it in its own engine, and then loads only the finished, clean tables into the warehouse.

Sources → ETL tool (transform) → Warehouse → Dashboard

This was the standard approach for decades, when warehouse storage and compute were expensive and limited. Classic examples include SQL Server Integration Services (SSIS), Informatica and Talend. Power Query, the engine in Excel and Power BI, also follows the ETL pattern: it extracts, transforms in its own engine (or folds steps back to the source), and loads the result into your model.

Strengths of ETL:

• Only clean, approved data lands in the warehouse.
• Sensitive fields can be masked or dropped before they ever reach the destination, which can help with privacy requirements.
• Works well with on-premises databases that have limited compute.

Weaknesses: transformation logic lives in a separate tool, sometimes in a visual designer that is hard to review or version. If you need a field you didn't load, you have to change the pipeline and reload.

How ELT works

In ELT, you copy raw data into the warehouse as-is, then transform it there using the warehouse's own compute, usually with SQL.

Sources → Warehouse (raw) → Transform in SQL → Clean tables → Dashboard

Cloud warehouses such as Snowflake, BigQuery, Databricks and Microsoft Fabric separate storage from compute and can scale processing up when needed, which makes it practical to store raw data and transform it in place. A typical ELT stack uses a loading tool (for example Fivetran, Airbyte or Azure Data Factory) and a transformation tool like dbt, which manages SQL models, tests and documentation. Our introduction to dbt covers it in detail.

A simple ELT transformation might look like this, turning a raw orders table into a clean staging table:

CREATE OR REPLACE VIEW staging.stg_orders AS
SELECT
    CAST(order_id AS INT)                   AS order_id,
    CAST(order_date AS DATE)                AS order_date,
    UPPER(TRIM(store_code))                 AS store_code,
    CAST(quantity AS INT)                   AS quantity,
    CAST(unit_price AS DECIMAL(10, 2))      AS unit_price,
    CAST(quantity AS INT)
      * CAST(unit_price AS DECIMAL(10, 2))  AS revenue
FROM raw.orders
WHERE order_id IS NOT NULL;

(CREATE OR REPLACE VIEW works in PostgreSQL, Snowflake, BigQuery and Databricks; in SQL Server use CREATE OR ALTER VIEW.)

Strengths of ELT:

• Raw data is preserved, so you can rebuild or add new transformations without re-extracting.
• Transformations are plain SQL that analysts can read, test and keep in version control.
• Loading is fast and simple, since nothing heavy happens on the way in.

Weaknesses: you store more data, raw sensitive data lands in the warehouse (so access control matters), and poorly written transformations can run up compute costs on usage-based platforms.

ETL vs ELT side by side

In practice, most teams mix both. A pipeline might do light cleanup during extraction (ETL-style), load into a lakehouse, and do the heavy modeling with SQL (ELT-style). The labels matter less than knowing where each piece of logic lives. Microsoft Fabric is a good example of this blend: Dataflows Gen2 use the familiar Power Query interface for ETL-style steps, while warehouses and notebooks in the same workspace handle ELT-style SQL or Python transformations.

Which one should a small team choose?

For a small business or a solo analyst, the answer depends on where the data lives and how many sources there are.

1. One or two sources, modest data: Power Query in Excel or Power BI is already an ETL tool. Clean the data there, model it as a star schema, and schedule refresh. No warehouse required.
2. Several sources, data on a company server: load into a SQL Server or PostgreSQL database and build reporting views. This is a light ELT setup that many teams run for years.
3. Many SaaS sources, growing volume, multiple analysts: consider a cloud warehouse with a managed loading tool and dbt. Compare platforms in our guide to Snowflake vs BigQuery vs Databricks vs Fabric, and check each vendor's current pricing page, since costs depend heavily on usage.

No matter the approach, the end goal is the same: clean fact and dimension tables at a clearly defined grain. If those terms are new, read data warehouse basics first.

Common pipeline mistakes

• No monitoring. A pipeline that fails silently is worse than no pipeline. Set up alerts for failed loads and for row counts that drop unexpectedly.
• Full reloads forever. Reloading everything nightly is fine at first; as data grows, switch to incremental loads based on a date or change-tracking column.
• Undocumented logic. Write down where each transformation happens and why, so the next analyst can maintain it.
• Ignoring data quality. Add checks for duplicates, nulls in key columns and unexpected values right after loading.
• Mixing time zones. Sources often store timestamps in different time zones. Convert everything to one standard during transformation, or daily totals will not match the source reports.

Next steps

1. Draw your current data flow: list every source, where it is cleaned, and where it is loaded.
2. Identify any business rule calculated in more than one place and consolidate it.
3. Try writing one cleaning step as a SQL view instead of a Power Query step and compare.

Watch: video lessons

▶

▶

Frequently asked questions