Discover the key differences between ETL and ELT data pipeline architectures. Learn when to use each, explore real-world examples, and choose the right approach for your analytics workflow.
This is a continuation of previous post on explaining the concepts of ETL vs. ELT
Here’s a Jupyter Notebook demo concept for comparing ETL vs. ELT using a small dataset. This notebook simulates:
- A simple ETL process: extract → transform in Python → load to SQLite
- A simple ELT process: extract → load to SQLite → transform with SQL
✅ Notebook: ETL vs. ELT – A Hands-On Comparison
Sections:
📦 1. Import Libraries
import pandas as pd
import sqlite3
📤 2. Simulate Raw Data (CSV Extract)
# Simulated extract from source
data = {
'customer_id': [1, 2, 3, 4],
'name': ['Alice', 'Bob', 'Charlie', 'David'],
'purchase_amount': [250, 300, 150, 400],
'region': ['North', 'South', 'East', 'West']
}
df_raw = pd.DataFrame(data)
df_raw.to_csv('raw_customers.csv', index=False)
df_raw.head()
⚙️ 3. ETL Process (Transform before Load)
# ETL: Transform in Python
df_etl = pd.read_csv('raw_customers.csv')
df_etl['purchase_category'] = df_etl['purchase_amount'].apply(lambda x: 'High' if x >= 300 else 'Low')
# Load into SQLite
conn = sqlite3.connect('etl_pipeline.db')
df_etl.to_sql('etl_customers', conn, if_exists='replace', index=False)
pd.read_sql('SELECT * FROM etl_customers', conn)
🚀 4. ELT Process (Load first, Transform in SQL)
# ELT: Load raw, then transform in SQL
df_elt = pd.read_csv('raw_customers.csv')
df_elt.to_sql('elt_customers_raw', conn, if_exists='replace', index=False)
# SQL Transformation
query = '''
CREATE TABLE elt_customers_transformed AS
SELECT *,
CASE WHEN purchase_amount >= 300 THEN 'High' ELSE 'Low' END AS purchase_category
FROM elt_customers_raw
'''
conn.execute('DROP TABLE IF EXISTS elt_customers_transformed')
conn.execute(query)
pd.read_sql('SELECT * FROM elt_customers_transformed', conn)
📊 5. Compare ETL vs. ELT Outputs
etl_result = pd.read_sql('SELECT * FROM etl_customers', conn)
elt_result = pd.read_sql('SELECT * FROM elt_customers_transformed', conn)
print("ETL Output:")
print(etl_result)
print("\nELT Output:")
print(elt_result)
✅ Conclusion
This notebook demonstrates:
- ETL performs transformations externally before loading.
- ELT loads raw data into the system first and transforms using native tools (e.g., SQL).