Skip to main content

ELT vs ETL — Why ELT on Snowflake

For decades, the standard way to move data from a source system into a data warehouse was ETL (Extract, Transform, Load). In the cloud era, this pattern has fundamentally shifted to ELT (Extract, Load, Transform).

Snowflake's entire architecture is optimized for the ELT pattern. Understanding the difference is critical for designing scalable data pipelines.

Topics Covered​

  • Traditional ETL Limitations
  • The ELT Paradigm Shift
  • Why Snowflake is Built for ELT
  • Real-World Example

1. Traditional ETL (Extract, Transform, Load)​

In the ETL pattern:

  1. Extract: Data is pulled from the source system (e.g., Salesforce).
  2. Transform: An intermediate server (e.g., Informatica, Talend, or a massive Spark cluster) sits in the middle. It processes the data, cleans it, and applies business logic in memory.
  3. Load: The clean, transformed data is finally pushed into the legacy Data Warehouse.

The Problem with ETL​

Legacy on-premise databases (like Oracle or SQL Server) had tightly coupled storage and compute. They couldn't handle the massive CPU overhead of transforming terabytes of raw JSON while simultaneously serving analytical queries to business users. Therefore, the heavy lifting had to be done by a separate server in the middle.

This creates a bottleneck: if your data volume doubles, your intermediate ETL server runs out of memory and crashes.

2. The ELT Paradigm Shift​

In the ELT pattern:

  1. Extract: Data is pulled from the source.
  2. Load: The raw, messy, untransformed data is dumped directly into the Data Warehouse.
  3. Transform: The Data Warehouse itself uses its own massively parallel compute engine to clean and structure the data.

Why ELT is Superior in the Cloud​

With ELT, you don't need a massive intermediate server. You use lightweight ingestion tools (like Fivetran, Airbyte, or Snowpipe) whose only job is to move raw data from Point A to Point B as fast as possible. All the heavy lifting is done inside the database using SQL.

Why Snowflake is Built for ELT​

Snowflake's architecture makes ELT not just possible, but the optimal pattern.

  1. Separation of Compute and Storage: In Snowflake, you can have one Virtual Warehouse loading raw data, while a completely separate Virtual Warehouse transforms it. They do not compete for resources.
  2. Native JSON Support: Snowflake can load raw, deeply nested JSON into VARIANT columns directly, without requiring a pre-defined schema. You parse the JSON after it's loaded using SQL.
  3. Infinite Elasticity: If your transformation job needs to process 10 Terabytes, you simply scale the warehouse to X-Large for 5 minutes, run the SQL transformation, and scale it back down. You only pay for what you use.

Hands-On: A Real-World ELT Example​

Imagine an e-commerce startup in Delhi. They receive raw web traffic logs as JSON files. Let's see how an ELT pipeline handles this entirely within Snowflake.

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_ECOMMERCE_DB;
USE DATABASE BEEMS_ECOMMERCE_DB;
CREATE SCHEMA IF NOT EXISTS ELT_LAB;
USE SCHEMA ELT_LAB;

2. The "Load" Step (Schema-on-Read)​

Instead of flattening the JSON in Python before loading, we load it completely raw.

-- 1. Create a table to hold the raw JSON
CREATE OR REPLACE TABLE RAW_WEB_TRAFFIC (
LOG_DATA VARIANT
);

-- 2. Simulate Snowpipe loading raw JSON directly into the table
INSERT INTO RAW_WEB_TRAFFIC (LOG_DATA)
SELECT PARSE_JSON(column1) FROM VALUES
('{"user_id": "U-100", "location": "Delhi", "action": "click", "timestamp": "2023-10-25T10:15:00Z"}'),
('{"user_id": "U-101", "location": "Mumbai", "action": "add_to_cart", "timestamp": "2023-10-25T10:16:00Z"}'),
('{"user_id": "U-100", "location": "Delhi", "action": "checkout", "timestamp": "2023-10-25T10:18:00Z"}');

3. The "Transform" Step (SQL in Snowflake)​

Now that the raw data is safely inside Snowflake, we use a Snowflake Virtual Warehouse to transform it into a clean, structured table for analysts.

(To verify which role is performing this transformation, run SELECT CURRENT_ROLE();)

-- Create a clean table using a CTAS (Create Table As Select) statement
CREATE OR REPLACE TABLE CLEAN_WEB_TRAFFIC AS
SELECT
LOG_DATA:user_id::STRING AS USER_ID,
UPPER(LOG_DATA:location::STRING) AS CITY,
LOG_DATA:action::STRING AS EVENT_TYPE,
LOG_DATA:timestamp::TIMESTAMP AS EVENT_TIME
FROM RAW_WEB_TRAFFIC
WHERE LOG_DATA:action::STRING != 'click'; -- Business rule: filter out raw clicks

-- The analysts now query this clean table
SELECT * FROM CLEAN_WEB_TRAFFIC;

Summary​

  • ETL relies on an external server to transform data before it hits the database.
  • ELT dumps raw data into Snowflake and uses Snowflake's massive compute to transform it via SQL.
  • ELT is significantly faster, infinitely scalable, and reduces the complexity of managing middle-tier ETL servers.
  • Snowflake's Separation of Compute and Storage and VARIANT data type are the core reasons ELT is the standard pattern.