পাঠ ০৪ · ২৯-এর মধ্যে · মডিউল ১

Home / AI Courses / ডেটা ইঞ্জিনিয়ারিং / ETL বনাম ELT

ETL বনাম ELT

ETL vs ELT — the order matters

৬ মিনিট পড়া শুরু · Beginner Pipeline pattern

এই পাঠে যা শিখবেন

ETL ও ELT-র মৌলিক পার্থক্য, ক্রম-এর প্রভাব
কেন cloud warehouse-এর উত্থান ETL-কে ELT-তে rewrite করেছে
dbt-র ভূমিকা ও modern data stack-এর কাঠামো
ETL ও ELT কোনটি কখন appropriate — বাস্তব সিদ্ধান্ত

১ · তিনটি অক্ষর — কিন্তু ক্রমে বড় পার্থক্য

ETLETL — Extract, Transform, Loadএকটি classic pipeline pattern: source থেকে data extract, একটি staging server-এ transform (cleaning/joining), তারপর warehouse-এ load। ১৯৭০-৯০-এর warehouse-এর সীমিত compute-এর প্রতিক্রিয়ায় তৈরি। ও ELTELT — Extract, Load, Transformআধুনিক pattern: data raw অবস্থায় warehouse-এ load, তারপর warehouse-এর elastic compute দিয়ে SQL transform। Cloud-যুগের default। — নাম প্রায় একই, কিন্তু philosophy পুরোটা ভিন্ন। শুধু "T" ও "L" স্থান-বদল — কিন্তু এতে cost, speed, agility, debugging সব বদলে যায়।

একই ব্যবসা-প্রশ্ন — "BD-র প্রতিটি জেলায় গত মাসে Daraz-এর গড় order value":

ETL approach: production DB থেকে দরকারি column extract → একটি Spark/Airflow worker-এ Python/Scala-তে transform → warehouse-এ "ready" table load। Dashboard সরাসরি এই table পড়ে।
ELT approach: production DB থেকে raw orders + addresses copy → warehouse-এ raw.orders, raw.addresses table load → warehouse-এ একটি SQL view/model "marts.daily_district_aov" তৈরি (dbt দিয়ে)। Dashboard এই mart পড়ে।

এক বাক্যে

ETL: "Transform বাইরে, warehouse-এ ঢোকার আগে।" 1990-এর pattern।
ELT: "Transform ভিতরে, warehouse-এর elastic compute দিয়ে।" 2015-এর modern pattern।
শুধু order বদলেছে — কিন্তু cost ও agility পুরোটাই বদল।

২ · কেন আগে ETL ছিল — ১৯৯০-এর কারণ

Teradata, Oracle, IBM DB2 যুগে warehouse-এর সীমাবদ্ধতা:

Compute expensive: warehouse-এর CPU/RAM প্রতি ঘণ্টায় খুব দামি। সেখানে heavy transformation চালানো যেত না।
Storage expensive: raw data রাখা mean ১০x cost। তাই শুধু "ready" data ঢুকত।
Schema rigid: warehouse-এ schema বদলানো ব্যথাদায়ক, তাই pre-validate।
Concurrency low: heavy ETL run মানে BI dashboard slow।

সমাধান — একটি আলাদা staging serverStaging ServerETL pipeline-এ একটি intermediate server (যেমন Informatica, Talend, SSIS) যেখানে raw data নিয়ে transformation চালানো হয়, তারপর warehouse-এ load। Cloud যুগের আগে ETL-এর কেন্দ্রবিন্দু। (Informatica, Talend, IBM DataStage)। সেখানে Python/Java-তে transformation, তারপর শুধু polished data warehouse-এ।

এই pattern ৩০ বছর dominant ছিল। ২০১২-র আগে "ETL" ছিল data engineering-এর synonym।

৩ · কেন cloud আসার পর ELT এসেছে

২০১২-এর পর Snowflake, BigQuery, Redshift এসে warehouse-এর constraint উল্টে দিল:

Compute সস্তা ও elastic: Snowflake "warehouse" auto-suspend, scale up/down সেকেন্ডে। যখন transformation চাই — চালান, তারপর shut।
Storage সস্তা: S3-backed। ১০x raw data রাখলেও cost negligible।
Compute & storage separated: Snowflake storage-এ আছে, যেকোনো warehouse compute চালাতে পারে।
SQL fast on huge data: column-store + vectorized engine।
Schema evolution easy: Snowflake-এ ALTER TABLE ADD COLUMN instant।

এই environment-এ — "transform বাইরে কেন? ভিতরেই ভাল!" — এই insight থেকে ELT।

১৯৯০ ETL = বাড়িতে রান্না করার জন্য সবজি বাজার থেকে কেটে-কুটে আনা; কারণ বাড়িতে stove ছোট, কাটার জায়গা নেই। ২০২০ ELT = বাড়িতে এখন full kitchen + induction; raw সবজি এনে যেভাবে চান কাটুন। কাটা সবজি কেনার মানে নেই — flexibility হারানো হয়।

৪ · Modern Data Stack — ELT-র বাস্তব রূপ

২০১৫-২০২৫-এ একটি stack pattern dominant — বলা হয় Modern Data StackModern Data StackCloud-native ELT-centric pipeline architecture: Fivetran/Airbyte (ingestion) + Snowflake/BigQuery (warehouse) + dbt (transformation) + Looker/Metabase (BI)। ২০১৫-এর পর প্রভাবশালী।।

Ingestion (E + L): Fivetran, Airbyte, Stitch, Hevo — ২০০+ source থেকে warehouse-এ raw load। কোনো transformation না।
Storage: Snowflake, BigQuery, Redshift, Databricks।
Transformation (T): dbtdbt — data build toolSQL-based transformation framework। warehouse-এ raw থেকে marts তৈরির জন্য version-controlled, tested, documented SQL model। Modern data stack-এর "T" layer-এর de-facto standard। (data build tool)। SQL দিয়ে raw → staging → marts।
Orchestration: Airflow, Dagster, Prefect — পুরো pipeline schedule।
BI: Looker, Metabase, Tableau, Power BI।
Reverse-ETL: Hightouch, Census — warehouse থেকে operational tool-এ data ফেরত (যেমন Salesforce-এ enriched user)।

ETL ও ELT-র সবচেয়ে বড় পার্থক্য — warehouse-এ raw data থাকছে কিনা। ELT-তে raw থাকে — ভবিষ্যতে নতুন প্রশ্ন এলে replay possible। ETL-তে সেই সুযোগ নেই।

৫ · ELT-র Practical সুবিধা

Replay-ability: Raw data warehouse-এ থাকে। ভবিষ্যতে নতুন business প্রশ্ন এলে — raw থেকে নতুন mart বানানো যায়। ETL-এ raw হারিয়ে যেত — নতুন প্রশ্ন মানে নতুন pipeline।
SQL ubiquity: Analyst, scientist, engineer — সবাই SQL জানে। Spark/Java specialist হিরে।
Version control: dbt model = git repo। PR review, test, CI/CD।
Lineage automatic: dbt graph — "এই table কোথা থেকে আসছে" instant।
Cost visibility: Warehouse-এ query cost transparent।
Speed of iteration: নতুন metric চাইলে — SQL লিখে deploy। ETL-এ Python deploy + Airflow rebuild + tester।

৬ · একটি dbt model — ELT-র মূল কোড

SQL · dbt model

-- models/marts/marts_daily_district_revenue.sql
-- বর্ণনা: প্রতিদিন প্রতি জেলায় Daraz-এর revenue
-- Layer: marts (final analytics-ready)

{{ config(
    materialized = 'incremental',
    unique_key   = ['order_date', 'district']
) }}

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
    {% if is_incremental() %}
      WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
    {% endif %}
),
addresses AS (
    SELECT * FROM {{ ref('stg_addresses') }}
)

SELECT
    DATE(o.created_at AT TIME ZONE 'Asia/Dhaka') AS order_date,
    a.district,
    COUNT(*)                                     AS orders,
    COUNT(DISTINCT o.user_id)                    AS unique_buyers,
    SUM(o.amount_bdt)                            AS gross_revenue,
    AVG(o.amount_bdt)                            AS avg_ticket
FROM orders o
JOIN addresses a USING (user_id)
WHERE o.status = 'completed'
GROUP BY 1, 2

এই dbt model-এর তিনটি বৈশিষ্ট্য পুরোটা দৃশ্যমান — (১) ref() দিয়ে dependency declare (lineage auto), (২) {{ config }} দিয়ে incremental run (শুধু new rows process), (৩) Jinja template-এ environment-aware logic। dbt run command দিলেই warehouse-এ এই table তৈরি/refresh।

৭ · Python ETL উদাহরণ — legacy pattern

Python · pandas ETL

import pandas as pd

# (E)xtract — production DB থেকে raw
orders   = pd.DataFrame({
    "order_id":   [1, 2, 3, 4, 5],
    "user_id":    [101, 102, 103, 102, 104],
    "amount_usd": [5, 12, 8, 15, 3],
    "status":     ["completed", "completed", "pending", "completed", "completed"],
})
addresses = pd.DataFrame({
    "user_id":  [101, 102, 103, 104],
    "district": ["Dhaka", "Chattogram", "Sylhet", "Khulna"],
})

# (T)ransform — staging-এ heavy lifting
USD_TO_BDT = 117.5
clean = (orders
    .query("status == 'completed'")
    .assign(amount_bdt=lambda d: d.amount_usd * USD_TO_BDT)
    .merge(addresses, on="user_id")
)

mart = (clean
    .groupby("district", as_index=False)
    .agg(orders=("order_id", "count"),
         revenue_bdt=("amount_bdt", "sum"))
)

# (L)oad — only the polished mart goes in
print(mart)
# warehouse-এ load: mart.to_sql("marts.district_revenue", engine, ...)

এই pattern এখনো বাস্তব — বিশেষ করে যেখানে Python data scientist কাজ করে। কিন্তু ELT-র কাছে দু'টি বড় হার: (১) raw warehouse-এ নেই — নতুন প্রশ্নে rerun, (২) version-control / test / lineage manually maintain।

৮ · কখন ETL এখনও appropriate

ELT default হলেও — চারটি scenario-তে ETL ভাল:

Sensitive PII: NID, ফোন, NID-এর photo — এগুলো warehouse-এ load করার আগেই hash/mask করা compliance-required। ELT-তে raw PII warehouse-এ থাকে — data engineer-এর সবার access।
Real-time / streaming: Pathao-র surge pricing — ৫ সেকেন্ডের মধ্যে decision। Warehouse SQL-এ এটা সম্ভব না; in-stream Flink/Spark transform।
Compute-constrained warehouse: Old on-prem Teradata-তে ELT চালালে cluster crash। Pre-transform ছাড়া উপায় নেই।
Heavy non-SQL transformation: Image resize, video encoding, ML feature extraction — SQL-এ inelegant। Spark/Beam-এ ETL-style ভাল।

Hybrid pattern প্রায়ই dominate — EtLT: Extract, light Transform (PII mask), Load, heavy Transform (in warehouse)। এটি ২০২৪-এ সবচেয়ে practical।

৯ · বাংলাদেশী stack-এর উদাহরণ

একটি SaaS startup (১০ জন): Airbyte (E+L) + Snowflake + dbt + Metabase। মাসে $200।
Daraz-সদৃশ e-commerce: Fivetran + Snowflake + dbt + Looker। ML-এর জন্য parallel Spark on Databricks।
bKash fintech: ETL pre-processing (PII mask) + ELT in warehouse + streaming Kafka+Flink (real-time fraud)। Hybrid stack।
Sonali Bank: Legacy on-prem ETL (Informatica) মূল — কারণ regulatory ও legacy constraint। Newer projects-এ ELT।
Pathao surge pricing: Pure streaming — "ETL" বললে মূলত Flink stream operators।

"ELT মানেই raw data সবার সাথে শেয়ার" — এটা ভুল implementation। Warehouse-এ row-level security, masking policy, তিন-স্তরের privilege (raw → curated → marts) অপরিহার্য। ELT compliance-friendly হতে পারে — কিন্তু work করতে হয়।

ভাবনার প্রশ্ন

প্রতিটি প্রশ্ন নিজে কিছুক্ষণ ভাবুন — তারপর "→ উত্তর" চাপুন।

প্র ০১ একটি old enterprise এ ১৫ বছর-পুরোনো Informatica ETL pipeline চলছে। CEO চান modern data stack-এ migrate করবেন। কী step-গুলো নেবেন? কোন risks ও কীভাবে phased migration করবেন?

Legacy migration data engineer-এর কঠিনতম প্রজেক্ট। "Big bang" migration প্রায়ই fail; phased ও parallel approach success-এর key।

Phase 0 — Discovery (২-৪ সপ্তাহ):

Pipeline inventory: কতটি Informatica job? কোন source-destination?
Critical path: কোনগুলো nightly regulatory report-এ feed?
SLA mapping: কোনটি 9 AM-এ finished must, কোনটি best-effort।
Stakeholder interview: প্রতিটি downstream consumer কে।
Cost baseline: Informatica license + server cost কত।

Phase 1 — Foundation (১-২ মাস):

Cloud warehouse setup (Snowflake/BigQuery)।
VPN/network connectivity legacy DB-এর সাথে।
CDC tool (Fivetran, Airbyte, AWS DMS) setup।
dbt project skeleton।
Orchestrator (Airflow) setup।
Identity, access, audit log।

Phase 2 — Parallel run (৩-৬ মাস):

Pilot pick: একটি non-critical mart (e.g., marketing dashboard) দিয়ে শুরু।
Informatica job continue চলছে; new dbt model parallel।
প্রতিদিন output compare — row count, sum, sample row।
Discrepancy investigate, gap বোঝা।
Stakeholder confidence build।

Phase 3 — Cutover (per pipeline ১-২ সপ্তাহ):

Confidence high হলে — downstream consumer-কে নতুন source-এ point।
Old pipeline ১-২ সপ্তাহ disabled-but-deployable।
Issues নাই — old pipeline decommission।
প্রতি সপ্তাহে ২-৩টি cutover।

Phase 4 — Cleanup (১-২ মাস):

Informatica server decommission।
License terminate।
Documentation update।
Postmortem।

Risks ও mitigation:

Hidden dependency: "এই Informatica job-এর output আবার যে csv তৈরি করে — finance team excel-এ open করে।" → Discovery phase-এ detail interview।
Subtle data difference: NULL handling, time zone, rounding। → Comparison test daily।
Skill gap: Informatica engineer dbt জানে না। → Training + parallel hire।
Vendor lock-in fear: CFO Snowflake bill নিয়ে চিন্তিত। → Cost monitoring dashboard।
Compliance: Audit trail break হলে BB-এর কাছে সমস্যা। → Comprehensive logging।

BD context:

BB regulation 7-year retention — old data lake parallel preserve।
Vendor (Informatica reseller in Dhaka) থেকে decommission contract carefully।
Skilled hire scarcer in Dhaka — budget-এ training।

মূল উপলব্ধি: Migration একটি ১২-১৮ মাসের project, ৩-৬ মাসের না। CEO-কে timeline upfront communicate করা key। যিনি phased plan ধৈর্য নিয়ে execute করেন — তিনি successful architect।

প্র ০২ "ELT-তে raw data warehouse-এ — PII security risk। Senior data engineer তো সব দেখতে পারবেন।" এই compliance challenge কীভাবে handle হয়?

এটি ELT-র সবচেয়ে বড় valid concern। সমাধানের একটি pattern set আছে — কোনো single fix নেই।

Layer 1 — Source-side masking (best for highly sensitive):

Production DB থেকে copy করার সময়ই PII hash/encrypt।
Fivetran-এ "column block list", Airbyte-এ "hashing transformer"।
NID, full ফোন warehouse-এ পৌঁছায় না — পৌঁছায় nid_hash, phone_last_4।
Trade-off: Customer-specific lookup কঠিন।

Layer 2 — Tokenization vault:

Production থেকে raw copy — কিন্তু একটি আলাদা vault এ। Vault-এ access strictly limited।
Warehouse-এ শুধু token (e.g., cust_a8f2k3)।
Specific lookup চাইলে — vault-এ explicit request, audit।
Stripe, banks-এ এই pattern।

Layer 3 — Warehouse access control:

Schema-level: raw.* only senior engineer; analytics.* all analyst।
Column-level masking: Snowflake/BigQuery dynamic masking. Analyst query-তে SELECT phone FROM ... — auto-return ***-***-1234।
Row-level security: Sales team শুধু own region।
Tag-based policy: column-এ PII tag — auto-apply mask।

Layer 4 — Audit ও governance:

প্রতিটি query log।
"Who queried PII column when" দেখা যায়।
Anomaly detection — unusual access pattern alert।
Periodic access review।

Layer 5 — Encryption:

At rest (warehouse default)।
In transit (TLS)।
Customer-managed key (CMK) for highly regulated।

Layer 6 — Operational:

Production engineer-এর "raw" access read-only, time-limited (24 hr token)।
Sensitive column query-এ ticket required।
Quarterly compliance audit।

BD-specific:

Data Protection Act ২০২৩: consent ও lawful basis লাগে। NID specially sensitive।
BB: ব্যাংকিং tx data domestic-এ থাকা required (data residency)। Snowflake AWS Singapore violation possible — Mumbai/Singapore acceptability vary।
BTRC: CDR (call detail) sensitive, telco-specific rule।

Practical bKash-সদৃশ design:

Tokenization vault on-prem (BD)।
Warehouse Singapore — only tokens।
Analyst sees masked column (+88017****1234)।
Specific user investigation: vault access, full audit।
Sub-hour SLA for fraud team via streaming.

মূল উপলব্ধি: "ELT মানে compliance compromise" myth। Properly architected ELT compliance-friendlier — কারণ central audit এক জায়গায়। কিন্তু architecting ৬-১২ মাসের কাজ; shortcut নেই।

প্র ০৩ dbt কেন একটি SQL-only tool হয়েও এত প্রভাবশালী? কোন ৫টি innovation Snowflake-এর "stored procedure" থেকে আলাদা?

dbt (২০১৬ Fishtown Analytics, পরে dbt Labs) ২০২০-এর পর থেকে data engineering-এ revolutionary। SQL-ই — কিন্তু engineering practice আনল।

(১) Software-engineering practice SQL-এ:

প্রতিটি model = file in git repo।
PR review — code change peer review।
CI/CD — PR-এ auto-run dbt build & test।
Branching — analyst dev branch-এ experiment।
Stored procedure-এ এই কিছু নেই; যা নেই তা scale-এ chaos।

(২) Lineage automatically:

dbt-এ {{ ref('orders_clean') }} পড়লেই — dependency graph তৈরি।
dbt docs generate — visual graph: কোন model কোথা থেকে আসে, কোথায় যায়।
Production-এ "এই column-এ ভুল কোথায়?" trace করা সহজ।
Stored procedure-এ "find usages" manual; lineage chase-করা painful।

(৩) Built-in testing:

schema.yml-এ declarative test — "user_id should be unique, not null"।
dbt test command warehouse-এ assertion query চালায়।
Failed test = pipeline halt, alert।
Stored procedure-এ test লেখা manual; rarely done।

(৪) Materializations:

একই SQL → different physical strategies: view, table, incremental, ephemeral।
Configuration change-এ — SQL untouched।
Stored procedure-এ এক pattern hard-coded।

(৫) Macros & Jinja templating:

Reusable SQL function ({{ get_division(district) }})।
Environment-aware ({% if target.name == 'prod' %}...)।
Loops, conditionals — pure SQL-এ impossible।
Code DRY।

আরও কারণ:

Documentation auto: column description-এ markdown। Site auto-generated।
Snapshots (SCD type 2): historical tracking declaratively।
Seeds: small reference data git-এ।
Packages: dbt_utils, dbt_artifacts — reusable modules।
Adapter ecosystem: Snowflake, BigQuery, Postgres, Databricks — same dbt code-base।

cultural impact:

"Analytics engineer" নতুন role — SQL + git + business।
Data team workflow আরো structured।
Documentation-as-code।

BD adoption:

Daraz, Pathao, bKash internal team-এ widely used।
Open-source core ফ্রি, dbt Cloud paid (small team-এ ফ্রি tier)।
Skill in Dhaka rising — ২০২২ থেকে job listing 5x।

মূল উপলব্ধি: dbt SQL changed করেনি — SQL-এর চারপাশ এ engineering practice এনেছে। সবচেয়ে পরিবর্তনশীল innovation প্রায়ই "নতুন কিছু" না — বিদ্যমানের চারপাশ-এ rigor।

প্র ০৪ Pathao surge pricing-এ পাঁচ সেকেন্ডে decision লাগে। ELT batch-এ সম্ভব না — কিন্তু "streaming ETL" ও আছে। এই use case-এ pipeline architecture কেমন হবে?

Real-time decisioning batch ELT-র সম্পূর্ণ ভিন্ন world। Architecture হার্ডওয়্যার, latency, ও business decision-এর intersection।

সমস্যা:

Rider/customer location প্রতি ৫ সেকেন্ডে।
"Demand spike হচ্ছে" detect ১০ সেকেন্ডে।
"Surge price activate" ১৫ সেকেন্ডে।
Batch warehouse-এ এই pace impossible।

Architecture — Lambda/Kappa pattern:

Hot path (real-time):

Mobile app → API → Kafka topic: rider_location, customer_request।
Kafka → Flink: sliding window 30 seconds:
- Per geohash demand count।
- Per geohash supply count।
- Demand/supply ratio।
Flink → Redis: per-geohash surge multiplier (1.0, 1.2, 1.5, 2.0)।
Booking API → Redis lookup: price = base × multiplier।
Total latency: rider GPS → surge active < ১৫ সেকেন্ড।

Cold path (analytics ELT):

Kafka → S3 sink: Avro/Parquet, partitioned by hour।
S3 → Iceberg lakehouse: raw history।
dbt models: daily demand patterns, regional trends।
Use: ML demand forecasting, postmortem, regulatory report।

Feature store bridge:

Cold path features (rider rating, historical earning) → online feature store।
Hot path joins online features with real-time demand।
ML model serve in 50ms।

Streaming ETL transformations:

Filter: invalid GPS (outside BD borders) drop।
Enrich: geohash from lat/long।
Aggregate: tumbling/sliding windows।
Join: rider_location + customer_request → matching candidates।
Pattern: CEP (complex event processing) — "5 cancellations in 2 minutes from same area" → suspicious।

Reliability concerns:

Out-of-order events: Watermark mechanism। 30-second tolerance।
Backpressure: Kafka partition count, Flink parallelism tuning।
State management: Flink checkpoints to S3 every 30 seconds।
Failover: active-passive Flink cluster across regions।

Cost reality:

Streaming infrastructure 5-10x batch।
৩-৪ engineer 24x7 oncall।
Bug-এর consequence — surge ভুল activate, customer rage।
Test environment realistic চাই।