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

Home / AI Courses / Data Engineering / Schema design

Schema design — star ও snowflake

Schema design — dimensional modeling for analytics

৭ মিনিট পড়া মাঝারি · Intermediate SQL উদাহরণসহ

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

Fact ও dimension টেবিলের পার্থক্য — কোনটি কী ধরে রাখে
Star বনাম snowflake schema — কখন কোনটি বাছবেন
Grain — fact টেবিলের প্রতিটি সারি কী represent করে
SCD Type 1, 2, 3 — dimension-এ ইতিহাস ট্র্যাকিং

১ · Dimensional modeling কী এবং কেন

OLTP ডাটাবেস (যেমন Daraz-এর order system) সাধারণত 3NFThird Normal FormCodd-এর normalization ধাপ — repeated data বাদ, প্রতিটি fact একবার, transactional integrity। OLTP-তে আদর্শ; analytics-এ JOIN-heavy ও ধীর।-এ normalized — duplicate কম, integrity বেশি। কিন্তু analytics-এ আমরা প্রশ্ন করি "গত মাসে চট্টগ্রামে fashion বিভাগে কত টাকার বিক্রি হয়েছে?" — এই প্রশ্নে ১০-১৫টা টেবিল JOIN করতে হয়, যা slow ও দুর্বোধ্য।

Ralph Kimball ১৯৯৬-এ The Data Warehouse Toolkit-এ ভিন্ন পথ প্রস্তাব করলেন — dimensional modelingDimensional Modelinganalytics-এর জন্য optimized data model — পরিমাপ (fact) ও বর্ণনা (dimension) আলাদা টেবিলে। JOIN কম, business user-friendly।। ডেটাকে দু'ভাগে ভাগ — কী ঘটেছে (fact) আর কে/কোথায়/কখন/কোন পণ্যে (dimensions)।

দু'ধরনের টেবিল

Fact table: ঘটনা ও পরিমাপ — order_amount, units_sold, revenue_bdt। সারি অনেক, column কম।
Dimension table: বর্ণনামূলক context — customer_name, district, product_category, date। সারি কম, column অনেক।

২ · Grain — সবচেয়ে গুরুত্বপূর্ণ সিদ্ধান্ত

GrainGrainfact টেবিলের একটি সারি ঠিক কী represent করে। Schema design-এর প্রথম ও সবচেয়ে গুরুত্বপূর্ণ সিদ্ধান্ত। ভুল grain = পুরো warehouse ভুল। মানে — fact টেবিলের একটি সারি ঠিক কী represent করে। Daraz-এর জন্য সম্ভাব্য grain:

Order-level: এক সারি = একটি order। সবচেয়ে coarse।
Order-line-level: এক সারি = order-এর একটি লাইন (এক order-এ ৫টা পণ্য থাকলে ৫টি সারি)। সবচেয়ে সাধারণ।
Daily snapshot: এক সারি = প্রতি গ্রাহকের প্রতিদিনের balance/state।

Kimball-এর নিয়ম: সবচেয়ে atomic grain বাছুন। coarse grain-এ পরে aggregation যোগ করা সহজ, কিন্তু aggregated থেকে drill-down অসম্ভব।

একই fact টেবিলে দু'রকম grain mix করবেন না — একই টেবিলে যদি কিছু সারি order-level আর কিছু line-level হয়, sum() ভুল answer দেবে। double-counting bug সবচেয়ে কঠিন bug।

৩ · Star schema — সরল, দ্রুত

Star schemaStar Schemaএক fact টেবিল কেন্দ্রে, চারপাশে সরাসরি যুক্ত denormalized dimension টেবিল। দেখতে তারার মতো। analytics-এ default পছন্দ।-এ এক fact_sales কেন্দ্রে; চারপাশে dim_customer, dim_product, dim_date, dim_store — সরাসরি foreign key দিয়ে যুক্ত। প্রতিটি dimension denormalized — অর্থাৎ একই dimension-এ সব context (যেমন dim_product-এ category, subcategory, brand সব এক টেবিলে)।

Daraz-এর জন্য star schema:

SQL · DDL

-- Fact: প্রতিটি order line একটি সারি
CREATE TABLE fact_sales (
  sales_key       BIGSERIAL PRIMARY KEY,
  date_key        INT  NOT NULL REFERENCES dim_date(date_key),
  customer_key    INT  NOT NULL REFERENCES dim_customer(customer_key),
  product_key     INT  NOT NULL REFERENCES dim_product(product_key),
  store_key       INT  NOT NULL REFERENCES dim_store(store_key),
  units_sold      INT  NOT NULL,
  unit_price_bdt  NUMERIC(10,2) NOT NULL,
  discount_bdt    NUMERIC(10,2) DEFAULT 0,
  revenue_bdt     NUMERIC(12,2) NOT NULL  -- pre-computed measure
);

-- Dimension: গ্রাহক — denormalized, সব context এক টেবিলে
CREATE TABLE dim_customer (
  customer_key    INT PRIMARY KEY,
  customer_id     VARCHAR(20) NOT NULL,   -- natural key (Daraz user id)
  name            VARCHAR(100),
  district        VARCHAR(50),            -- ঢাকা, চট্টগ্রাম...
  division        VARCHAR(50),            -- ঢাকা division, চট্টগ্রাম division
  customer_tier   VARCHAR(20),            -- silver, gold, platinum
  signup_date     DATE
);

লক্ষ করুন dim_customer-এ district ও division দু'টোই আছে — যদিও division district থেকে derive করা যায়। এটাই denormalization — query সহজ ও দ্রুত করার জন্য একই তথ্য redundant রাখা।

৪ · Snowflake schema — normalized dimensions

Star schema-তে dim_product-এ category, brand, supplier — সবই inline। Snowflake schemaSnowflake Schemadimension-এর ভেতরে আবার sub-dimension — উদাহরণ dim_product থেকে dim_category, dim_brand আলাদা। দেখতে snowflake-এর মতো শাখাযুক্ত।-এ এই hierarchy আবার ভাগ — dim_product → dim_category → dim_department, এভাবে multi-level।

কখন বাছবেন কোনটি?

Star — analytics এর default. BigQuery, Snowflake, Redshift — সব columnar warehouse-এ JOIN cheap, denormalization-এর storage cost সামান্য। dashboard ও BI tool-এ user-friendly।
Snowflake — যখন একই dimension অনেক fact-এ share হয় (dim_currency, dim_country) ও update consistency জরুরি। অথবা storage-constrained system-এ।

বাস্তব production-এ pure star বা pure snowflake বিরল — সাধারণত hybrid। বড় বড় hierarchy-যুক্ত dimension snowflake করা হয়, ছোট গুলো denormalized থাকে।

Star — denormalized dimension, JOIN কম। Snowflake — normalized dimension, storage কম কিন্তু query-তে আরও JOIN।

৫ · Slowly Changing Dimensions (SCD)

একজন গ্রাহকের ঠিকানা ঢাকা থেকে চট্টগ্রামে বদলালে — dim_customer-এ কী করব? ৩টি classical pattern:

Type 1 — Overwrite: নতুন value দিয়ে পুরোনো overwrite। ইতিহাস হারিয়ে যায়। typo-correction-এর জন্য ভালো।
Type 2 — Add new row: পুরোনো সারি valid_to দিয়ে close করুন, নতুন সারি valid_from দিয়ে খুলুন। পূর্ণ ইতিহাস। এটাই default best practice।
Type 3 — Add column: current_district ও previous_district দু'টা column। সীমিত — শুধু last change track করে।

SQL · SCD Type 2

-- SCD Type 2 — পূর্ণ ইতিহাস
CREATE TABLE dim_customer_scd2 (
  customer_key   BIGSERIAL PRIMARY KEY,   -- surrogate key (changes per version)
  customer_id    VARCHAR(20) NOT NULL,    -- natural key (stable)
  name           VARCHAR(100),
  district       VARCHAR(50),
  customer_tier  VARCHAR(20),
  valid_from     TIMESTAMP NOT NULL,
  valid_to       TIMESTAMP,                -- NULL = current
  is_current     BOOLEAN NOT NULL DEFAULT TRUE
);

-- উদাহরণ: গ্রাহক C1042 ঢাকা → চট্টগ্রাম (২০২৫-০৩-১৫-এ)
INSERT INTO dim_customer_scd2 VALUES
 (1, 'C1042', 'রহিম মিয়া', 'ঢাকা',     'silver', '2024-01-10', '2025-03-15', FALSE),
 (2, 'C1042', 'রহিম মিয়া', 'চট্টগ্রাম', 'silver', '2025-03-15', NULL,        TRUE);

-- "মার্চ ১৫-এর আগের order কোন district-এ ছিল?" — সঠিক ইতিহাস পাওয়া যায়
SELECT f.revenue_bdt, c.district
FROM   fact_sales f
JOIN   dim_customer_scd2 c
  ON   f.customer_key = c.customer_key   -- surrogate key দিয়ে JOIN
WHERE  f.date_key < 20250315;

মূল চমক — fact_sales-এ customer_key store হয় (surrogate key, ভিন্ন version-এর জন্য ভিন্ন), customer_id নয়। ফলে সেই সময়ের সঠিক attribute রিকভার হয়।

৬ · Surrogate key বনাম natural key

Surrogate keySurrogate Keywarehouse-এ generated meaningless integer key (যেমন BIGSERIAL)। SCD Type 2-এর জন্য অপরিহার্য — same business id কিন্তু ভিন্ন version-এর জন্য ভিন্ন key। = warehouse-এ generated integer (customer_key = 1, 2, 3...)। Natural key = source system-এর id (customer_id = 'C1042')। warehouse-এ সব JOIN surrogate দিয়ে — কারণ:

Source system বদলালে (Daraz user-id format change) warehouse অক্ষুণ্ন থাকে।
SCD Type 2-এ same business entity-র multiple version আলাদা key পায়।
Integer JOIN string JOIN-এর চেয়ে দ্রুত।
Multi-source dedup — bKash + Nagad-এ same user হলে warehouse-এ এক surrogate।

৭ · Fact-এর প্রকার — যা গণনা করি

Transaction fact: সবচেয়ে সাধারণ। প্রতি event এক সারি — order, payment, ride। Daraz, Pathao-র sales fact।
Periodic snapshot: প্রতি period (দিন/মাস)-এর end-state। উদাহরণ — bKash-এ প্রতি গ্রাহকের দিনশেষে balance।
Accumulating snapshot: এক process-এর multiple milestone। যেমন order: placed → packed → shipped → delivered — প্রতিটি timestamp এক সারিতে।

৮ · Daraz-এর জন্য বাস্তব schema

কল্পনা করুন Daraz-এর CFO প্রশ্ন করলেন — "গত quarter-এ চট্টগ্রাম division-এ Samsung-এর mobile category-তে gold-tier customer-দের গড় order value কত?" star schema-তে এটি একটি SQL — চারটি simple JOIN, এক GROUP BY, ৫ সেকেন্ডে answer। 3NF OLTP-তে ১২+ JOIN, ৫ মিনিট, প্রতিবার dashboard লোড করতে production DB চাপে পড়বে।

SQL · Analytics query

-- Daraz: Q1 2025, Chittagong, Samsung mobile, gold-tier — গড় order value
SELECT  d.year_quarter,
        ROUND(AVG(f.revenue_bdt), 2) AS avg_order_bdt,
        COUNT(*) AS order_lines
FROM    fact_sales f
JOIN    dim_customer c ON f.customer_key = c.customer_key
JOIN    dim_product  p ON f.product_key  = p.product_key
JOIN    dim_date     d ON f.date_key     = d.date_key
WHERE   d.year_quarter   = '2025-Q1'
  AND   c.division       = 'চট্টগ্রাম'
  AND   c.customer_tier  = 'gold'
  AND   p.brand          = 'Samsung'
  AND   p.category       = 'Mobile'
GROUP BY d.year_quarter;

৪টি JOIN, plain WHERE, এক GROUP BY — যেকোনো analyst পড়তে ও লিখতে পারে। এটাই dimensional model-এর শক্তি — analytics-এর জন্য optimized, business user-friendly।

Schema design একবার "ঠিক করে" পরিবর্তন করা কঠিন। downstream সব dashboard, ML feature, dbt model এর উপর depend করে। তাই প্রথম দিন থেকে grain ও SCD strategy পরিষ্কারভাবে document করুন।

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

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

প্র ০১ Pathao-এর জন্য একটি rides fact টেবিল ডিজাইন করছেন। সম্ভাব্য grain কী কী হতে পারে — এবং কোনটি বাছবেন? যুক্তি দিন।

Pathao-এর ride lifecycle-এ অনেকগুলো event ঘটে — ride request, driver acceptance, pickup, dropoff, payment, rating। প্রতিটির জন্য আলাদা grain সম্ভব।

সম্ভাব্য grain options:

One row per ride request: failed ও successful সব request, একটি সারি। Cancellation rate, search-to-booking funnel-এর জন্য আদর্শ।
One row per completed ride: শুধু সফল ride। revenue analysis-এ পরিষ্কার।
One row per ride event: request, accept, pickup, drop, pay — প্রতিটি event এক সারি। সবচেয়ে atomic, কিন্তু row count বিশাল।
Daily driver snapshot: প্রতি driver-এর প্রতিদিনের total — earnings, rides, online-hours।

Kimball recommendation: সবচেয়ে atomic grain — অর্থাৎ "one row per ride request"। কারণ:

Request থেকে completion-এর সব stage এক সারিতে track করা যায় (accumulating snapshot pattern)।
Cancellation, no-driver, surge — সব analyzable।
Aggregation (per-driver-day, per-area-hour) পরে যেকোনো level-এ derive করা যাবে।

Trade-off: daily 5-10 lakh request × 365 দিন = কয়েকশ' কোটি সারি প্রতি বছর। Storage cost ও query speed-এর জন্য partition by ride_date + cluster by city জরুরি। BigQuery বা Snowflake-এ এই scale ভালো handle হয়।

ভুল: dual grain। "request fact" আর "completed ride fact" একই টেবিলে mix করলে — sum(revenue) double-counting হবে। আলাদা টেবিল রাখা, অথবা request grain-এ completed_flag ও revenue_bdt NULL-able রাখা — দু'টোই valid।

পরামর্শ: production-এ fact_ride_request (সব request) + fact_ride_event (status change history, যদি দরকার) — এই দু'টো combo সবচেয়ে flexible।

প্র ০২ bKash-এ গ্রাহকের KYC tier পরিবর্তন হয় (basic → verified)। SCD Type 1, 2, 3 — কোনটি ব্যবহার করবেন এবং কেন?

bKash-এর KYC tier business-critically ও regulatory-critically গুরুত্বপূর্ণ — Bangladesh Bank ও AML compliance-এর কারণে। তাই ইতিহাস হারানো অগ্রহণযোগ্য।

সঠিক উত্তর: SCD Type 2 (পূর্ণ ইতিহাস)।

কেন Type 1 নয়:

Type 1-এ overwrite — পুরোনো tier হারিয়ে যায়।
Audit-এ "এই suspicious transaction-এর সময় এই গ্রাহক কী tier-এ ছিল" উত্তর দেওয়া যাবে না।
Bangladesh Bank-এর AML requirement violate হবে।

কেন Type 3 যথেষ্ট নয়:

Type 3-এ current_tier + previous_tier — শুধু last change।
basic → verified → enhanced → premium — multiple transition track হবে না।
fixed columns বাড়ানো অসম্ভব।

Type 2 implementation:

dim_bkash_customer-এ surrogate customer_key, natural msisdn।
প্রতিটি tier change-এ পুরোনো সারি valid_to set, নতুন সারি insert।
fact_transaction-এ transaction-এর সময়কার customer_key store — তখনকার tier permanently linked।

Hybrid approach (production-এ সাধারণ):

Type 2 — tier, district, account_status (regulated/audited fields)।
Type 1 — name, phone update (typo correction)।
Type 0 (immutable) — birth_date, NID (কখনো বদলায় না, error হলেও correct করা হয় Type 1 logic-এ)।

Performance consideration: Type 2 row count বাড়ায় (যত change, তত version)। কিন্তু bKash-এর ৭ কোটি গ্রাহকে গড়ে ২-৩ tier change/lifetime — মোট ১৫-২০ কোটি row, modern warehouse-এ trivial।

মূল কথা: SCD strategy ব্যবসায়িক ও regulatory requirement থেকে আসে — কখনো শুধু "easy implementation" থেকে নয়। ভুল choice মানে compliance failure।

প্র ০৩ আপনি BigQuery বা Snowflake-এ কাজ করছেন — যেখানে storage cheap ও JOIN fast। তবু কেন denormalized "One Big Table" (OBT) approach সবসময় সঠিক নয়, এবং star schema কেন এখনো relevant?

OBT (One Big Table) — সব dimension fact-এর সাথে inline merge করে একটি wide টেবিল। 2010-এর দশকে BigQuery ও Snowflake আসার পর কেউ কেউ যুক্তি দিল — "JOIN-এর দিন শেষ, সব এক টেবিলে রাখো।" কিন্তু practice-এ star schema এখনো dominant।

OBT-এর সমস্যা:

SCD Type 2 অসম্ভব হয়: dimension-এর version track করতে আলাদা টেবিল লাগে। OBT-তে শুধু "as-of-event" snapshot ধরা যায়।
Update overhead: dim_product-এ একটি description বদলালে — সব historical fact row update করতে হয়। billions of rows-এ costly।
Multi-fact reuse: dim_customer যদি fact_sales, fact_payment, fact_review সবার সাথে merge করা থাকে — ৩ জায়গায় একই গ্রাহকের তথ্য, inconsistency risk।
Schema evolution: dimension-এ নতুন column যোগ — OBT-তে সব fact টেবিল backfill করতে হয়।
Storage cost: "storage cheap" সত্য, কিন্তু dimension যদি ১০০ column হয়, ১০ বিলিয়ন fact row-তে multiply করলে — পেটাবাইট।

Star schema কেন এখনো জিতে:

Single source of truth: dim_customer একটি — সব fact-ে JOIN। গ্রাহকের district বদলালে এক জায়গায় update।
BI tool compatibility: Tableau, Power BI, Looker — সবাই star schema assume করে। OBT-তে drill-down, hierarchy navigation ভেঙে যায়।
Modern warehouse JOIN cheap: Snowflake, BigQuery, Redshift — broadcast join (small dim, big fact) microseconds-এ। denormalization-এর performance gain marginal।
Cognitive simplicity: analyst জানে — fact-এ measure, dim-এ context। ১০০-column wide table debug দুঃস্বপ্ন।

OBT কখন ভালো?

Reporting layer-এর শেষ ধাপে — dashboard-specific মaterialized view।
ML feature store — model-ready feature row।
Read-heavy, never-updated event log (যেমন application log analytics)।

Modern best practice (dbt-এর উদ্ভাবন):

Bronze: raw source data।
Silver/Staging: cleaned, conformed star schema (fact + dim)।
Gold/Marts: denormalized OBT — specific dashboard-এর জন্য, পুনর্নির্মাণযোগ্য।

মূল উপলব্ধি: Storage cheap → OBT trivially ঠিক — এই reasoning naive। design choice cost optimization-এর চেয়ে বেশি — maintainability, evolvability, audit-ability সব মাথায় রাখতে হয়। Kimball-এর star schema ৩০ বছর পরও standard, কারণ এই trade-off গুলো এখনো প্রাসঙ্গিক।

প্র ০৪ Grameenphone-এ আপনি call detail record (CDR) থেকে warehouse তৈরি করছেন — দিনে ৫০ কোটি call record। কোন কোন factor schema design-এ critical, কোন partition strategy ব্যবহার করবেন?

Telco CDR — data engineering-এর extreme scale পরীক্ষা। Grameenphone-এর ৮ কোটি+ গ্রাহক, প্রতিদিন ৫০ কোটি event (call, SMS, data session) — বছরে ১৮০+ বিলিয়ন row। এই scale-এ "কাজ চলে" আর "production-grade" এর মাঝে বিরাট পার্থক্য।

Grain ও schema:

Grain: "one row per call/session" — atomic, billing-grade accuracy।
fact_cdr: caller_key, callee_key, cell_tower_key, date_key, time_key, duration_seconds, bytes_transferred, charge_bdt।
dim_subscriber: SCD Type 2 — package change, prepaid→postpaid switch, ১৩৩ digit mapping।
dim_cell_tower: static-ish, location ও coverage hierarchy (district → division → BTRC region)।

Partition strategy:

Time-based partition (must): PARTITION BY DATE(call_timestamp)। ৯৯% query "last N days" — partition pruning ছাড়া অচল।
Sub-partition by hour: peak hour analytics-এ। কিন্তু partition explosion সাবধানে — Snowflake-এ micro-partition auto, BigQuery-তে max ৪০০০ partition।
Cluster by caller_id: per-customer billing query-তে nearly 100x speedup।

Storage tiering:

Hot (last 30 days): SSD, full speed। daily reporting।
Warm (1 year): standard storage। trend analysis।
Cold (7 years, BTRC retention compliance): archive (Glacier, Snowflake Time Travel)। audit-only।

Compression ও encoding:

Columnar format (Parquet, Iceberg) — CDR-এ ১০-২০x compression। ৫ বছরের ১ পেটাবাইট হয়ে যায় ৫০-১০০ TB।
Dictionary encoding — country_code, network_type-এ low cardinality, near-free।

Aggregation strategy:

Raw CDR কেউ user-facing dashboard-এ touch করে না।
Pre-aggregated tables: agg_subscriber_daily (per user per day), agg_tower_hourly।
৯৯% query agg table হিট করে — second-level latency। raw fact শুধু forensic, billing dispute-এ।

Compliance ও privacy:

BTRC ৭ বছর retention। ৭ বছর ১ দিন পর auto-delete।
PII hashing — calling number SHA-256 with salt, raw ফোন নম্বর শুধু legal-approved query-তে।
Row-level security — customer service rep নিজের assigned region-এর CDR দেখতে পারে।

Cost reality: এই scale-এ ভুল schema choice মানে মাসে কোটি টাকা cloud bill। আবার over-engineered solution শুরুতেই team-কে paralyze করে। iterative — প্রথমে partition + cluster, তারপর agg, তারপর tiering — এটাই telco DE-র wisdom।

অনুশীলন

Schema লিখুন: Pathao Food-এর জন্য fact ও dimension টেবিলের তালিকা ও primary measure বলুন।
fact_food_order (grain: one row per order):
- FK: date_key, customer_key, restaurant_key, rider_key, area_key
- Measures: subtotal_bdt, delivery_fee_bdt, discount_bdt, total_bdt, prep_minutes, delivery_minutes, customer_rating
Dimensions: dim_customer (SCD2), dim_restaurant (SCD2 — name, cuisine, area can change), dim_rider (SCD2), dim_area (district→thana hierarchy), dim_date।
SCD identify করুন: Daraz-এর dim_product-এ price, category, name, seller_rating — প্রতিটির জন্য কোন SCD type উপযুক্ত?
- price — Type 2। historical analysis-এ price-এর সময়কার সঠিক value দরকার।
- category — Type 2। category re-org হলে past sale তখনকার category-তে রিপোর্ট হবে।
- name — Type 1 (overwrite, typo correction)। Type 2 হতে পারে যদি rebranding track করতে চান।
- seller_rating — সাধারণত fact (daily snapshot fact)-এ, dimension নয়। কারণ এটি rapidly changing measure।
Star বনাম snowflake: dim_address-এ thana → district → division — তিন level hierarchy। কখন denormalize রাখবেন এক টেবিলে, কখন আলাদা?
Denormalized (star) — default: dim_address-এ thana, district, division তিন column-ই inline। analyst SELECT division, SUM(...) এক টেবিল থেকে। query সহজ, JOIN কম।

Snowflake — যদি:
- একই geography multiple fact-এ share — dim_division আলাদা থাকলে definition consistent।
- division-level extra attribute অনেক (population, GDP, area_sqkm) — dim_division আলাদা টেবিল cleaner।
- BTRC reporting-এ division/district পরিসংখ্যান একই data definition দরকার।
BD context-এ usually star যথেষ্ট — ৬৪ district, ৫০০ thana, ৮ division — denormalized টেবিল trivially small।