পাঠ ০৬ · ২৯-এর মধ্যে · মডিউল ১
Home / AI Courses / Data Engineering / SQL for DE

SQL refresher — DE-এর জন্য

SQL fundamentals for data engineers
৮ মিনিট পড়া মাঝারি · Intermediate SQL কোডসহ

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

  • SELECT, WHERE, GROUP BY, HAVING — সঠিক ক্রম ও semantic
  • JOIN-এর ৪ ধরন — set-theoretic ভাবনা ও bug-prone case
  • CTE ও subquery — কখন কোনটি, readability ও performance trade-off
  • EXPLAIN ANALYZE পড়া ও index-এর প্রভাব বোঝা

১ · কেন DE-তে SQL এত গুরুত্বপূর্ণ

একজন data engineer প্রতিদিন গড়ে ৫০-১০০ লাইন SQL লেখেন — pipeline-এ data transformation, dbt model, Airflow task, ad-hoc debugging। আধুনিক tooling SQL-কে আরও কেন্দ্রে এনেছে: Spark SQL, BigQuery SQL, dbt, ksqlDB, Materialize — সবগুলোতে SQL সর্বোচ্চ-level interface। Python ভালো, কিন্তু ৭০-৮০% production transformation শেষমেশ SQL।

এই পাঠ — Window function ছাড়া (পরের পাঠে) — DE-র core SQL ক্ষমতা refresh করবে।

SQL execution order (যা লিখি তার বিপরীত)

লেখা: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
চলে: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
এই কারণে WHERE-এ alias ব্যবহার করা যায় না (তখনো SELECT চলে নি), কিন্তু HAVING ও ORDER BY-তে যায়।

২ · SELECT, WHERE, GROUP BY — সঠিক বুনন

একটি বাস্তব Daraz query — division-অনুযায়ী মাসিক revenue:

SQL · GROUP BY 
SELECT  c.division,
        DATE_TRUNC('month', o.order_ts) AS order_month,
        COUNT(*)             AS orders,
        SUM(o.revenue_bdt)   AS total_revenue,
        AVG(o.revenue_bdt)   AS avg_order_value
FROM    fact_orders o
JOIN    dim_customer c USING (customer_key)
WHERE   o.order_ts >= '2025-01-01'
  AND   o.order_ts <  '2025-04-01'
  AND   o.status   = 'delivered'
GROUP BY c.division, DATE_TRUNC('month', o.order_ts)
HAVING  SUM(o.revenue_bdt) > 1000000   -- ১০ লাখের বেশি
ORDER BY order_month, total_revenue DESC;

    


    

      মূল pattern: filter আগে (WHERE), aggregate (GROUP BY), aggregate-এর উপর filter (HAVING), শেষে sort। এই ক্রম mastery — DE-র দৈনন্দিন কাজের ৪০%।
    


    

      সবচেয়ে সাধারণ bug: SELECT-এ এমন column লিখলেন যা GROUP BY-তে নেই — Postgres error দেবে, কিন্তু MySQL silent ভুল answer দিতে পারে। সব non-aggregate column অবশ্যই GROUP BY-তে থাকতে হবে।
    


    
৩ · JOIN — ৪ প্রকার ও set-theoretic ভাবনা


    
Two table A, B। JOIN-এর ফলাফল কোন rows আসবে:

    
    
      
  • INNER JOIN: দু'টোতেই matched key — A ∩ B।
    • 
      
  • LEFT JOIN: A-র সব row + B থেকে matched (না হলে NULL)।
    • 
      
  • RIGHT JOIN: উল্টো — B-র সব row। Practice-এ rare; LEFT-এ rewrite।
    • 
      
  • FULL OUTER JOIN: দু'দিকে সব row — match হলে পাশাপাশি, না হলে NULL।
    • 
    


    

      

        
        SQL · JOIN types
      

      
-- gold-tier customer-দের মধ্যে যাদের কোনো order নেই (LEFT JOIN + IS NULL)
SELECT  c.customer_id, c.name, c.signup_date
FROM    dim_customer c
LEFT JOIN fact_orders o
  ON    c.customer_key = o.customer_key
  AND   o.order_ts >= CURRENT_DATE - INTERVAL '90 days'
WHERE   c.customer_tier = 'gold'
  AND   o.order_id IS NULL;   -- key trick: anti-join via LEFT + NULL

-- Daraz-এ যেসব product বিক্রি কখনো হয়নি (anti-join)
SELECT  p.product_id, p.name
FROM    dim_product p
LEFT JOIN fact_orders o USING (product_key)
WHERE   o.product_key IS NULL;

      

        
        
      

      

    


    

      LEFT JOIN ... WHERE right.col IS NULL = "A-তে আছে কিন্তু B-তে নেই" — anti-join। DE-তে অসংখ্যবার লাগে: missing record খোঁজা, data quality check।
    


    

      JOIN-কে দু'টি Excel sheet পাশাপাশি বসানোর মতো ভাবুন। INNER = শুধু সেই row যেখানে দু'sheet-এ key মেলে। LEFT = বাঁ sheet-এর সব row, ডান sheet থেকে যা মেলে; না মিললে blank cell। উপরে SQL-এ "blank cell"-গুলো খোঁজা মানে — যেসব customer-এর কোনো order নেই, তাদের তালিকা।
    


    
৪ · JOIN-এর সাধারণ bug — fan-out


    

      সবচেয়ে subtle DE bug — fan-outFan-outJOIN-এর পর row সংখ্যা প্রত্যাশার চেয়ে অনেক বেড়ে যাওয়া — কারণ "one" দিকে duplicate বা multi-match row। SUM/AVG ভুল হয়।। মনে করুন, fact_orders-এ ১ লাখ order, fact_returns-এ ১০ হাজার return। JOIN করলে আশা ১ লাখ। কিন্তু এক order-এ যদি multiple return থাকে — row বেড়ে গেল, এবং SUM(revenue_bdt) double-counted।
    


    

      

        
        SQL · Fan-out fix
      

      
-- ভুল: fan-out — return row বেশি হলে revenue double-counted
SELECT  o.customer_key, SUM(o.revenue_bdt) AS revenue, COUNT(r.return_id) AS returns
FROM    fact_orders o
LEFT JOIN fact_returns r ON o.order_id = r.order_id
GROUP BY o.customer_key;
-- ভুল answer!

-- সঠিক: aggregate আগে, JOIN পরে
SELECT  o.customer_key,
        SUM(o.revenue_bdt) AS revenue,
        COALESCE(r.return_count, 0) AS returns
FROM    fact_orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS return_count
  FROM   fact_returns
  GROUP BY order_id
) r ON o.order_id = r.order_id
GROUP BY o.customer_key, r.return_count;

      

        
        
      

      

    


    

      নিয়ম: যখনই "one-to-many" JOIN, এবং "one"-side-এ aggregation দরকার — আগে "many"-side aggregate (subquery বা CTE), তারপর JOIN।
    


    
৫ · CTE (WITH) — readable, modular SQL


    

      CTECommon Table ExpressionWITH clause দিয়ে এক query-র মধ্যে temporary "named subquery" তৈরি — পাঠযোগ্যতা বহুগুণ বাড়ায়। modern SQL-এ chain করা যায়। = WITH ব্যবহার করে নাম দেওয়া step। জটিল query-কে ছোট ছোট পদে ভেঙে — প্রতিটি একটি পরিষ্কার "চিন্তা"।
    


    

      

        
        SQL · CTE chain
      

      
-- bKash: গত ৩০ দিনে এমন গ্রাহক যাদের গড় daily transaction ১০০০-এর বেশি
WITH daily_tx AS (
  SELECT  customer_key,
          DATE(transaction_ts) AS tx_date,
          SUM(amount_bdt)      AS daily_amount
  FROM    fact_transaction
  WHERE   transaction_ts >= CURRENT_DATE - INTERVAL '30 days'
    AND   status = 'success'
  GROUP BY customer_key, DATE(transaction_ts)
),
customer_avg AS (
  SELECT  customer_key,
          AVG(daily_amount) AS avg_daily_bdt,
          COUNT(*)          AS active_days
  FROM    daily_tx
  GROUP BY customer_key
  HAVING  AVG(daily_amount) > 1000
)
SELECT  c.msisdn, c.customer_tier, ca.avg_daily_bdt, ca.active_days
FROM    customer_avg ca
JOIN    dim_customer c USING (customer_key)
ORDER BY ca.avg_daily_bdt DESC
LIMIT   100;

      

        
        
      

      

    


    

      প্রতিটি CTE — এক logical step। এই same query subquery-তে লিখলে nested ৩-৪ স্তর — পড়তে দুঃস্বপ্ন। CTE = SQL-এ "function"।
    


    

      পুরোনো PostgreSQL (১২-এর আগে) CTE optimization fence ছিল — অর্থাৎ planner CTE-র ভেতরে predicate push করতে পারত না। আধুনিক Postgres (১২+), BigQuery, Snowflake — সবাই CTE inline করে। তাই readability-র জন্য নির্দ্বিধায় CTE ব্যবহার করুন।
    


    
৬ · Subquery — কখন CTE-র চেয়ে ভালো


    
    
      
  • Scalar subquery: WHERE-এ "average-এর চেয়ে বেশি"-র মতো single value compare।
    • 
      
  • Correlated subquery: outer row-এর reference ব্যবহার করে — যেমন "এই customer-এর last order"।
    • 
      
  • EXISTS / NOT EXISTS: existence check — JOIN-এর চেয়ে fast ও fan-out free।
    • 
    


    

      

        
        SQL · EXISTS
      

      
-- যেসব customer-এর last 7 দিনে অন্তত একটি delivered order আছে
SELECT  c.customer_id, c.name
FROM    dim_customer c
WHERE   EXISTS (
  SELECT 1
  FROM   fact_orders o
  WHERE  o.customer_key = c.customer_key
    AND  o.order_ts >= CURRENT_DATE - INTERVAL '7 days'
    AND  o.status = 'delivered'
);

      

        
        
      

      

    


    

      EXISTS match পেলেই থামে (semi-join) — তাই বড় টেবিলে IN/JOIN-এর চেয়ে অনেক fast। DE-এর দৈনন্দিন hammer।
    


    
৭ · Query plan ও EXPLAIN ANALYZE


    

      একই query কখনো ১ সেকেন্ডে শেষ, কখনো ১০ মিনিট — পার্থক্য query planQuery Plandatabase optimizer যে ক্রমে JOIN ও filter execute করবে — সেই execution graph। EXPLAIN দিয়ে দেখা যায়, EXPLAIN ANALYZE বাস্তবে চালিয়ে timing দেখায়।-এ। DE-কে query plan পড়তে জানতেই হবে।
    


    

      

        
        SQL · EXPLAIN ANALYZE
      

      
EXPLAIN ANALYZE
SELECT c.division, SUM(o.revenue_bdt)
FROM   fact_orders o
JOIN   dim_customer c USING (customer_key)
WHERE  o.order_ts >= '2025-04-01'
GROUP BY c.division;

-- নমুনা output (Postgres):
-- HashAggregate  (cost=12450..12455 rows=8 width=42) (actual time=320..320)
--   ->  Hash Join  (cost=850..11200 rows=250000)    (actual time=18..280)
--         Hash Cond: (o.customer_key = c.customer_key)
--         ->  Index Scan on fact_orders_order_ts_idx (cost=0..9800 rows=250000) (actual time=0.1..150)
--               Index Cond: (order_ts >= '2025-04-01')
--         ->  Hash  (cost=750..750 rows=8000) (actual time=15..15)
--               ->  Seq Scan on dim_customer (cost=0..750 rows=8000)
-- Planning Time: 0.3 ms
-- Execution Time: 322 ms

      

        
        
      

      

    


    

      পড়ার ক্রম: ভেতর থেকে বাইরে। Index Scan (ভালো) বনাম Seq Scan (যদি ছোট টেবিল হয় ঠিক, বড় হলে red flag)। Hash Join — equi-JOIN-এ best। Nested Loop — ছোট outer-এ ঠিক, বড়তে disaster। actual time ও rows-এ estimate বনাম reality-র mismatch দেখুন — বড় mismatch মানে stats outdated।
    


    
৮ · Index — কখন কোন কোন column-এ


    
    
      
  • Foreign keys: JOIN column-এ অবশ্যই index। customer_key, product_key।
    • 
      
  • WHERE-এর filter column: order_ts, status — যদি high cardinality থাকে।
    • 
      
  • Composite index: (customer_key, order_ts) — leftmost prefix rule।
    • 
      
  • Partial index: WHERE status = 'pending' — production-এ pending row কম, lookup fast।
    • 
      
  • না দিন: খুব low cardinality (boolean), constantly-updated column-এ overhead বেশি।
    • 
    


    

      Index ফ্রি না — write 2-5x slow, storage বাড়ে। OLTP-তে balance, OLAP warehouse-এ (BigQuery, Snowflake) traditional index নেই — partition + cluster এর কাজ করে।
    


    
    

      
        
          
            
          
        

        SQL execution pipeline — যা লিখি, যেভাবে চলে

        
        📝 যা লিখি
        ⚙️ যেভাবে চলে

        
        
        SELECT cols

        
        FROM tables

        
        WHERE filter

        
        GROUP BY

        
        HAVING

        
        ORDER BY · LIMIT

        
        
        optimizer reorders

        
        
        1. FROM (load)

        
        2. WHERE (filter rows)

        
        3. GROUP BY

        
        4. HAVING

        
        5. SELECT (compute)

        
        6. ORDER BY · LIMIT

        
        
        ⚡ filter-আগে row কমাও → পরের সব ধাপ দ্রুত
      
      
SQL — declarative ভাষা। আপনি বলেন "কী চাই", optimizer ঠিক করে "কীভাবে আনবে"। execution order জানা = performance debug-এর প্রথম শর্ত।

    


    
৯ · DE-specific SQL pattern

    
    
      
  • Idempotent UPSERT: daily pipeline-এ rerun করলেও duplicate না হয় — INSERT ... ON CONFLICT।
    • 
      
  • Date dimension JOIN: তারিখ-ভিত্তিক যেকোনো analytics-এ dim_date JOIN — calendar-aware reporting।
    • 
      
  • NULL safety: COALESCE, NULLIF — pipeline-এ NULL leakage সবচেয়ে সাধারণ data quality bug।
    • 
      
  • CAST সাবধানে: '২০২৫'::int Bangla digit-এ fail করবে — input cleaning জরুরি।
    • 
    


  


  

    
    
  


  
  

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

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


    

      
        প্র ০১
        আপনার একটি Daraz query ৩০ মিনিট চলছে — production dashboard আটকে। EXPLAIN ANALYZE-এ কী দেখবেন, এবং কোন কোন স্পষ্ট red flag খুঁজবেন?
      
      

        

          Production query timeout — DE-র সবচেয়ে stressful situation। সঠিক diagnosis order:
        

        

          (১) প্রথমে EXPLAIN (without ANALYZE):
        

        
    
          
  • ANALYZE আসলে query চালায় — যদি ৩০ মিনিট চলেই, আবার চালানো বিপজ্জনক। শুধু plan দেখুন।
    • 
          
  • cost number (top node) huge হলে plan-ই খারাপ।
    • 
          
  • estimated rows বাস্তবের সাথে wildly mismatch হলে statistics outdated।
    • 
        

        

          (২) Red flag: Sequential Scan on big table:
        

        
    
          
  • Seq Scan on fact_orders — যদি filter আছে কিন্তু scan সব row পড়ে, index missing।
    • 
          
  • Solution: order_ts বা customer_key-এ index।
    • 
          
  • Exception: টেবিল ছোট (~1000 rows) হলে seq scan ঠিক, optimizer বুদ্ধিমান।
    • 
        

        

          (৩) Nested Loop Join on huge tables:
        

        
    
          
  • Outer ৫ লাখ × inner ১০ লাখ-এ ৫ ট্রিলিয়ন lookup — অসম্ভব।
    • 
          
  • Optimizer-এর Hash Join বা Merge Join বাছার কথা। যদি Nested Loop বাছছে, statistics ভুল হিসাব দিচ্ছে।
    • 
          
  • Fix: ANALYZE table_name চালান, statistics refresh।
    • 
        

        

          (৪) Estimated vs actual rows mismatch:
        

        
    
          
  • Estimated 100 rows, actual 5,000,000 — এই ৫০,০০০x mismatch optimizer-কে completely ভুল pick করায়।
    • 
          
  • Cause: stale stats, skewed data, complex filter expression।
    • 
          
  • Fix: ANALYZE, increase default_statistics_target, query rewrite।
    • 
        

        

          (৫) JOIN order সমস্যা:
        

        
    
          
  • বড় টেবিল × বড় টেবিল প্রথমে JOIN, তারপর small dim — explosion।
    • 
          
  • Fix: filter আগে apply, ছোট টেবিল আগে।
    • 
          
  • Sometimes LEADING hint ব্যবহার (Oracle) বা CTE দিয়ে force order।
    • 
        

        

          (৬) Sort/Hash spill to disk:
        

        
    
          
  • "external merge Disk: 500MB" — work_mem ছাড়িয়েছে, disk-এ spill।
    • 
          
  • Fix: SET work_mem = '256MB' session-এ; বা DISTINCT/ORDER BY-এর আগে rows কমান।
    • 
        

        

          (৭) Cartesian product (CROSS JOIN accident):
        

        
    
          
  • JOIN condition ভুলে গেলে — ১ লাখ × ১ লাখ = ১ ট্রিলিয়ন row।
    • 
          
  • plan-এ "Nested Loop" without "Join Cond" big warning।
    • 
        

        

          Production debugging triage:
        

        
    
          
  • Kill the long-running query — production-এ priority।
    • 
          
  • Lower environment-এ EXPLAIN দেখুন।
    • 
          
  • Index, statistics, query rewrite — order-এ চেষ্টা।
    • 
          
  • Persistent? — partition strategy বা materialized view consider করুন।
    • 
        

        

          মূল উপলব্ধি: Performance debugging intuition আসে অভিজ্ঞতা থেকে। প্রতি new project-এ সবচেয়ে important ৫টা query-র plan নিজে পড়ুন — production-এ surprise কম।
        

      

    


    

      
        প্র ০২
        একই query — CTE বনাম subquery বনাম temp table দিয়ে লেখা যায়। কোন situation-এ কোনটি বাছবেন?
      
      

        

          সঠিক উত্তর situation-dependent, এবং SQL engine-ভেদে আলাদা।
        

        

          CTE (WITH) — default পছন্দ:
        

        
    
          
  • Readability — অনেক step-এর pipeline-এ unmatched।
    • 
          
  • Same logic একাধিকবার reference (modern engines auto-cache, পুরোনো-তে duplicate compute)।
    • 
          
  • Recursive query (পরের পাঠ) শুধু CTE-তে possible।
    • 
          
  • dbt model — পুরোটাই CTE chain।
    • 
          
  • Limitation: Postgres ১১-এ optimization fence ছিল, ১২+-এ inlined।
    • 
        

        

          Subquery — যখন কম্প্যাক্ট better:
        

        
    
          
  • Single-use, simple — SELECT *, (SELECT COUNT(*) FROM orders WHERE ...) AS cnt।
    • 
          
  • Correlated subquery — outer-এর row reference।
    • 
          
  • EXISTS / NOT EXISTS-এ semi-join performance।
    • 
          
  • সাবধান: nested ৩+ স্তর হলে CTE-তে refactor — পড়া অসম্ভব।
    • 
        

        

          Temp table — heavy intermediate-এ:
        

        
    
          
  • একই intermediate result অনেকবার লাগবে — CTE inline হলে recompute, temp table এক বার compute।
    • 
          
  • Temp table-এ index দেওয়া যায় — CTE-তে সম্ভব না।
    • 
          
  • Statistics generate হয় — optimizer better plan বাছে next step-এ।
    • 
          
  • Cross-session sharing — session-scoped অথবা real table।
    • 
          
  • Cost: disk write, transaction overhead, cleanup management।
    • 
        

        

          Materialized CTE (Postgres 12+): WITH x AS MATERIALIZED (...) — CTE-কে temp-table-এর মতো force। যখন optimizer ভুল decision নেয়।
        

        

          Decision tree:
        

        
    
          
  • One-time query, ছোট — subquery/CTE indifferent।
    • 
          
  • Multi-step, readable — CTE।
    • 
          
  • Same intermediate ৩+ বার — temp table বা MATERIALIZED CTE।
    • 
          
  • Recursive — must CTE।
    • 
          
  • Production pipeline (Airflow, dbt) — CTE for code, table materialization for compute reuse।
    • 
        

        

          BD context উদাহরণ:
        

        
    
          
  • Daraz daily revenue dashboard — subquery enough।
    • 
          
  • bKash fraud detection (multi-step suspicious pattern) — CTE chain।
    • 
          
  • Pathao monthly settlement (intermediate ride totals reused for driver bonus, area metrics, fraud check) — temp table।
    • 
        

        

          মূল কথা: "Best" বলে কিছু নেই। Readability + measured performance — দু'টোই বিবেচ্য। শুরুতে CTE; bottleneck হলে EXPLAIN দেখে উপযুক্ত alternative।
        

      

    


    

      
        প্র ০৩
        OLTP database (PostgreSQL) ও OLAP warehouse (BigQuery) — দু'টোতেই আমরা SQL লিখি। index strategy ও query optimization কিভাবে ভিন্ন?
      
      

        

          এক ভাষা — দু'টা ভিন্ন জগৎ। DE-র জন্য এই পার্থক্য বোঝা critical।
        

        

          OLTP (Postgres, MySQL) — row-store, transaction-optimized:
        

        
    
          
  • প্রতিটি row একসাথে disk-এ — point lookup (single row read) cheap।
    • 
          
  • B-tree index — primary tool, foreign key, status, timestamp-এ।
    • 
          
  • Workload: ছোট transaction (Daraz checkout — ১০-২০ row read/write)।
    • 
          
  • Optimization: ACID, lock contention, replication lag।
    • 
          
  • Index rule: high cardinality, frequent filter → index।
    • 
        

        

          OLAP (BigQuery, Snowflake, Redshift) — columnar, analytics-optimized:
        

        
    
          
  • প্রতিটি column আলাদা compressed file। ১০০ column-এর টেবিলে শুধু ৩ column read করলে ৯৭% I/O save।
    • 
          
  • Traditional B-tree index নেই — partition + cluster এর কাজ করে।
    • 
          
  • Workload: full-table aggregation (১ বছরের ১০০ মিলিয়ন order summarize)।
    • 
          
  • Optimization: partition pruning, column projection, predicate pushdown, broadcast vs shuffle JOIN।
    • 
          
  • "Index" rule: partition by date (hot column), cluster by high-selectivity filter (customer_id, region)।
    • 
        

        

          Concrete example — same query, different optimization:
        

        

          Query: "গত ৭ দিনে চট্টগ্রামে fashion category-তে total revenue।"
        

        
    
          
  • Postgres:
            
      
              
    • idx_orders_ts_status — order_ts range scan।
      • 
              
    • idx_orders_customer_key — JOIN-এর জন্য।
      • 
              
    • idx_customer_division — partial index WHERE division = 'চট্টগ্রাম'।
      • 
              
    • ৭ দিনের data — ১-২ লাখ row, query ১-২ সেকেন্ডে।
      • 
            
    
          
    • 
          
  • BigQuery:
            
      
              
    • PARTITION BY DATE(order_ts) — শুধু ৭টি partition scan।
      • 
              
    • CLUSTER BY customer_id, product_category — block-level pruning।
      • 
              
    • কোনো traditional index নেই, কিন্তু ১ বিলিয়ন row-এ ৩-৫ সেকেন্ডে।
      • 
              
    • Cost: bytes processed-এর ভিত্তিতে — partition pruning সরাসরি bill কমায়।
      • 
            
    
          
    • 
        

        

          Other key differences:
        

        
    
          
  • Update/Delete: OLTP cheap, OLAP costly (immutable storage, MERGE rewrites partition)।
    • 
          
  • Concurrency: OLTP — হাজার concurrent connection। OLAP — ১০-১০০, কিন্তু প্রতিটি massive।
    • 
          
  • JOIN: OLTP — Hash/Nested Loop। OLAP — distributed shuffle vs broadcast (small dim broadcast to all nodes)।
    • 
          
  • Cost model: OLTP — row count। OLAP — bytes scanned (BigQuery), credit-seconds (Snowflake)।
    • 
          
  • Latency: OLTP — ms। OLAP — sec to min, optimized for throughput not latency।
    • 
        

        

          DE practical advice:
        

        
    
          
  • OLTP read replica থেকে analytics — ছোট scale-এ কাজ চলে, বড় হলে OLAP লাগবেই।
    • 
          
  • BigQuery-তে SELECT * করলে cost বিস্ফোরণ — শুধু দরকারি column।
    • 
          
  • Snowflake warehouse size পরীক্ষা — auto-suspend ৫ মিনিট, না হলে credit বার্ন।
    • 
          
  • Mixed workload — OLTP-এ analytic query চালালে production lock — read replica বা CDC দিয়ে warehouse-এ পাঠান।
    • 
        

        

          মূল উপলব্ধি: SQL syntax একই, কিন্তু performance mental model সম্পূর্ণ আলাদা। DE-কে দু'টা সিস্টেমেই comfortable হতে হবে।
        

      

    


    

      
        প্র ০৪
        Grameenphone-এর CDR table-এ "৪G data session গত ১ ঘণ্টায় সবচেয়ে বেশি use করা ১০০ subscriber" query — billion-row scale-এ আপনি কীভাবে লিখবেন?
      
      

        

          এই scale-এ "naive SQL" আর "production-grade SQL"-এর পার্থক্য বিরাট। বছরে ১৮০+ বিলিয়ন CDR row।
        

        

          Naive (যা শুরুতে লেখা হয় — কাজ করে না):
        

        
SELECT msisdn, SUM(bytes_used) AS total_bytes
FROM   fact_cdr
WHERE  service_type = '4G_DATA'
  AND  session_ts >= NOW() - INTERVAL '1 hour'
GROUP BY msisdn
ORDER BY total_bytes DESC
LIMIT 100;

        

          সমস্যা: partition pruning কাজ করছে কিনা নিশ্চিত না, full hour-এ ৪-৫ কোটি row scan, GROUP BY সব key-এ shuffle।
        

        

          Production-grade approach:
        

        

          (১) Partition pruning নিশ্চিত করুন:
        

        
    
          
  • Table-এ PARTITION BY DATE(session_ts) থাকা ধরা।
    • 
          
  • Filter explicit literal-এ — NOW() dynamic, কিছু engine partition prune করতে পারে না।
    • 
          
  • Better: session_ts BETWEEN '2025-05-09 14:00' AND '2025-05-09 15:00'।
    • 
        

        

          (২) Pre-aggregated table ব্যবহার:
        

        
    
          
  • Real-time pipeline-এ agg_subscriber_hourly_data — প্রতি ঘণ্টা auto-refresh।
    • 
          
  • Naive query raw fact থেকে ৪ কোটি row → agg-এ ৫-৬ লাখ row।
    • 
          
  • Latency drop: ৩০ সেকেন্ড → ১ সেকেন্ড।
    • 
        

        

          (৩) Approximate top-K (cheap):
        

        
    
          
  • BigQuery: APPROX_TOP_COUNT(msisdn, 100) — sketches ব্যবহার, ১০x fast।
    • 
          
  • Top-100-এ ranking exactly accurate না হলেও বাস্তবে identical (একই গ্রাহক)।
    • 
          
  • Alarming/billing-এ ব্যবহার করবেন না, dashboard-এ perfect।
    • 
        

        

          (৪) Cluster key strategically:
        

        
    
          
  • CLUSTER BY service_type, msisdn — service_type filter-এ block prune।
    • 
          
  • Snowflake micro-partition / BigQuery clustering — billion row-এ ১০-৫০x speedup।
    • 
        

        

          (৫) Streaming alternative:
        

        
    
          
  • Real-time top-K-এর জন্য Kafka + Flink/ksqlDB — sliding window, sub-second update।
    • 
          
  • Warehouse query — ৫-১৫ মিনিট latency acceptable হলে ঠিক, real-time alarming-এ না।
    • 
        

        

          (৬) Materialized view:
        

        
    
          
  • CREATE MATERIALIZED VIEW agg_data_5min — incremental refresh।
    • 
          
  • Snowflake dynamic table, BigQuery materialized view — auto-maintenance।
    • 
          
  • Storage cost বাড়ে, কিন্তু query cost ৯৫% নামে।
    • 
        

        

          Final production query:
        

        
-- agg table দিয়ে: ১ ঘণ্টা scan কয়েক লাখ row
SELECT  msisdn,
        SUM(bytes_used)  AS total_bytes,
        SUM(session_cnt) AS sessions
FROM    agg_subscriber_hourly_data
WHERE   hour_bucket = DATE_TRUNC('hour', NOW() - INTERVAL '1 hour')
  AND   service_type = '4G_DATA'
GROUP BY msisdn
ORDER BY total_bytes DESC
LIMIT   100;

        

          (৭) Cost ও SLA monitoring:
        

        
    
          
  • BigQuery — bytes scanned alert (e.g., কেউ ১TB+ scan করলে notify)।
    • 
          
  • Snowflake — credit consumption per query।
    • 
          
  • Slow query log + dashboard — DE-র ongoing duty।
    • 
        

        

          BD telco perspective: Grameenphone-এর peak hour-এ subscriber ranking — fraud detection, capacity planning, marketing — সবেতে লাগে। ভুল architecture মানে মাসে কোটি টাকা cloud bill। সঠিক pre-aggregation + clustering + materialized view stack — single most impactful skill telco DE-তে।
        

        

          মূল উপলব্ধি: Big-data scale-এ "SQL জানা" যথেষ্ট না — SQL + storage layout + query plan + cost economics একসাথে — এটাই senior DE।
        

      

    

  


  

    
 অনুশীলন

    
    
      
  1. 
        Anti-join লিখুন: Daraz-এ যেসব gold-tier customer-এর গত ৬ মাসে কোনো order নেই — তাদের তালিকা।
        
    
          
          
    
            
    SELECT c.customer_id, c.name, c.signup_date
FROM   dim_customer c
WHERE  c.customer_tier = 'gold'
  AND  NOT EXISTS (
    SELECT 1 FROM fact_orders o
    WHERE o.customer_key = c.customer_key
      AND o.order_ts >= CURRENT_DATE - INTERVAL '6 months'
);
    
            
    Alternative: LEFT JOIN ... IS NULL pattern-ও কাজ করে; EXISTS সাধারণত fast।
    
          
    
        
    
      
    2. 

      
  2. 
        CTE chain লিখুন: Pathao-তে গত ৩০ দিনের প্রতি area-তে গড় ride duration বের করে — সেই গড় ৩০ মিনিটের বেশি যেসব area, তাদের লিস্ট দিন।
        
    
          
          
    
            
    WITH ride_30d AS (
  SELECT area_key, ride_duration_min
  FROM   fact_ride
  WHERE  ride_ts >= CURRENT_DATE - INTERVAL '30 days'
    AND  status = 'completed'
),
area_avg AS (
  SELECT area_key, AVG(ride_duration_min) AS avg_min,
         COUNT(*) AS rides
  FROM   ride_30d
  GROUP BY area_key
  HAVING AVG(ride_duration_min) > 30
)
SELECT a.area_name, a.district,
       ROUND(aa.avg_min, 1) AS avg_min,
       aa.rides
FROM   area_avg aa
JOIN   dim_area a USING (area_key)
ORDER BY aa.avg_min DESC;
    
          
    
        
    
      
    3. 

      
  3. 
        Fan-out fix করুন: fact_orders ও fact_returns JOIN করে per-customer revenue ও return count চান। সঠিক query লিখুন।
        
    
          
          
    
            
    WITH return_per_order AS (
  SELECT order_id, COUNT(*) AS return_cnt
  FROM   fact_returns
  GROUP BY order_id
)
SELECT  o.customer_key,
        SUM(o.revenue_bdt)            AS revenue,
        SUM(COALESCE(r.return_cnt,0)) AS returns
FROM    fact_orders o
LEFT JOIN return_per_order r ON o.order_id = r.order_id
GROUP BY o.customer_key;
    
            
    আগে return aggregate করে order-এ ১:১ JOIN — fan-out নেই, revenue accurate।
    
          
    
        
    
      
    4. 
    

  


  
  

    
 আরও পড়ুন · ABCL TECH-এ আপনার পরবর্তী পদক্ষেপ

    

    

      SQL practice কোথায়?
      Google Colab
      ব্যবহার করুন — DuckDB বা SQLite-এ এই query সব trivially চলবে। Postgres install করতে চাইলে docker-এ ১ command।
    

  








  
  
     এই পাঠে ভুল পেয়েছেন? জানান
  





  
    
 পূর্ববর্তী পাঠ

    
পাঠ ০৫ · Schema design