BigQuery ও Redshift

কোনো absolute "best" নেই — workload + team + ecosystem-এর উপর নির্ভরশীল। Pathao-এর context-এ পাঁচটি মাত্রায় তুলনা।

(১) Pricing — মাসিক ৩০০ TB scanned:

• BigQuery on-demand: $৬.২৫ × ৩০০ = $১,৮৭৫। Predictable cap-এর জন্য slot reservation ($২,৯২০ for ১০০ slots) — slightly higher কিন্তু budget-known।
• Redshift: ৪× ra3.4xlarge ১/৩ utilization → ~$৩,৫০০ + storage। Higher fixed cost; underutilized off-hours।
• Snowflake: warehouse-based, multiple WH design। Likely $২,৫০০-৩,৫০০।
• Winner cost: BigQuery (Pathao-র workload bursty)।

(২) Operational overhead:

• BigQuery — কোনো cluster, vacuum, sortkey nei। ১ DE ১০০% ops handle করতে পারে।
• Redshift — VACUUM, ANALYZE, distkey rebalance, WLM tuning। ১-২ FTE।
• Snowflake — middle ground; warehouse design লাগে কিন্তু low maintenance।
• Winner ops: BigQuery।

(৩) Ecosystem fit:

• Pathao Google Maps API, Firebase, GA4 ব্যবহার করে — সব GCP-এ। BigQuery native।
• Redshift মানে S3, Lambda, Kinesis re-engineer।
• Snowflake multi-cloud, কিন্তু integration efforts।
• Winner ecosystem: BigQuery।

(৪) Real-time ingestion:

• Pathao real-time dashboard (driver location, ride status) চায়।
• BigQuery streaming insert ($০.০৫/GB) সহজ — Pub/Sub থেকে direct।
• Redshift streaming ingestion (Kinesis Data Streams) — robust কিন্তু setup কঠিন।
• Snowflake Snowpipe — micro-batch, latency ~১ মিনিট।
• Winner streaming: BigQuery।

(৫) ML/AI integration:

• BigQuery ML in-database; Vertex AI seamless।
• Redshift ML → SageMaker call।
• Snowflake Snowpark + Cortex (নতুন)।
• Winner ML: BigQuery (BD context)।

(৬) Risks ও lock-in:

• BigQuery — GCP lock-in। SQL standard ANSI, কিন্তু operational lock-in বেশি।
• Snowflake — multi-cloud freedom, vendor lock-in কিন্তু portability ভাল।
• Redshift — AWS lock-in।

সিদ্ধান্ত: Pathao-এর জন্য BigQuery। Bursty workload, ছোট DE team, GCP ecosystem, ML-heavy roadmap — সব dimension-এ winner। Risk hedge: dbt + Iceberg-export-এ portability রাখুন future migration-এর জন্য।

মূল উপলব্ধি: Cloud DW choice technical-only নয়। Team capacity, existing cloud, regulatory, vendor relationship — সব মিলে। BD startup-এ most common winner — BigQuery; legacy enterprise-এ Redshift; multi-cloud strategic — Snowflake।

Redshift slow join — বেশিরভাগ ক্ষেত্রে distribution + sort key মিস। Step-by-step diagnostic।

(১) Diagnose — STEP 1: EXPLAIN দেখা:

EXPLAIN
SELECT c.segment, SUM(t.amount)
FROM fact_transactions t
JOIN dim_customer c ON t.customer_id = c.customer_id
WHERE t.tx_date >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY 1;

খারাপ pattern: DS_DIST_BOTH বা DS_BCAST_INNER — দু'টোই full network shuffle। ভালো: DS_DIST_NONE বা DS_DIST_ALL_NONE।

(২) Diagnose — STEP 2: STL_DIST দেখা:

SELECT slice, num_values, ratio
FROM stl_dist
WHERE query = pg_last_query_id();

একটি slice-এ disproportionately বেশি rows = data skew।

(৩) সম্ভাব্য কারণ:

• fact_transactions DISTSTYLE EVEN — তাই join-এ shuffle।
• dim_customer DISTSTYLE EVEN — ছোট হলেও replicate-এ benefit।
• Sort key অনুপস্থিত — date filter zone map use করছে না।
• VACUUM/ANALYZE পুরনো — statistics stale, planner ভুল choice।

(৪) Fix:

-- fact: customer_id-এ co-locate, date-এ sort
CREATE TABLE fact_transactions_v2 (LIKE fact_transactions)
DISTSTYLE KEY DISTKEY (customer_id)
COMPOUND SORTKEY (tx_date);

INSERT INTO fact_transactions_v2 SELECT * FROM fact_transactions;

-- dim: ৫ M rows × ~১০০ B = ৫০০ MB → ALL distribute সম্ভব
CREATE TABLE dim_customer_v2 (LIKE dim_customer)
DISTSTYLE ALL;

INSERT INTO dim_customer_v2 SELECT * FROM dim_customer;

-- Statistics ও cleanup
ANALYZE fact_transactions_v2;
ANALYZE dim_customer_v2;
VACUUM REINDEX fact_transactions_v2;

(৫) যাচাই:

• EXPLAIN-এ DS_DIST_NONE দেখা চাই।
• SVL_QUERY_REPORT-এ "rows" per step উল্লেখযোগ্যভাবে কম।
• Query time ১০-১০০x কমার expectation।

(৬) Long-term hygiene:

• Weekly VACUUM (auto-vacuum-এ rely না করা)।
• Daily ANALYZE।
• WLM queue separation — short query, long ETL আলাদা।
• Concurrency Scaling enable — burst-এ extra cluster auto।

মূল উপলব্ধি: Redshift performance debugging — distribution + sort + statistics। BigQuery/Snowflake-এ এই concern অনেক কম, কিন্তু Redshift control-এর বিনিময়ে responsibility। UK fintech client-এ সঠিক distkey চয়ন = ১০x speedup, খরচ same।

Telco CDR — BigQuery-এর সবচেয়ে high-stakes use case। ১৫ TB/day × ৩৬৫ × ৩ = ১৬ PB। ভুল design = টানা billing surprise।

(১) Schema design:

CREATE TABLE gp_dwh.cdr (
  call_ts          TIMESTAMP NOT NULL,
  caller_msisdn    INT64     NOT NULL,
  callee_msisdn    INT64,
  duration_sec     INT64,
  cell_id          STRING,
  region           STRING,
  call_type        STRING,
  payload          STRING    -- raw record
)
PARTITION BY DATE(call_ts)
CLUSTER BY region, caller_msisdn
OPTIONS (
  partition_expiration_days = 1095,    -- ৩ বছর
  require_partition_filter  = TRUE,
  description = 'CDR raw — 15 TB/day'
);

(২) কেন এই design:

• PARTITION BY DATE: ৩৬৫ × ৩ = ১০৯৫ partition; BigQuery limit ৪০০০ within।
• CLUSTER BY region প্রথম: ৮ division — জনসংখ্যাগত query (Dhaka customers) সরাসরি।
• caller_msisdn দ্বিতীয়: investigation query ("এই number-এর last 7 days history") দ্রুত।
• require_partition_filter: Junior engineer "select * from cdr" → error, না bill suicide।
• partition_expiration_days: auto-drop ৩ বছর পর — storage cost control।

(৩) Storage cost:

• Active storage: $২০/TB/month × ১৫ TB × ৩০ = $৯,০০০ (last 30 days)।
• Long-term storage (৯০ days+): $১০/TB/month — auto। ১৬ PB × $১০ = $১৬০,০০০/মাস।
• BDT: ~১.৭৫ কোটি/মাস storage alone। GP-র scale অনুযায়ী acceptable।

(৪) Query cost — ৭ দিনের window:

• ৭ × ১৫ TB = ১০৫ TB। Cluster pruning (Dhaka-এর filter) → ~১৩ TB scan।
• On-demand: $৬.২৫ × ১৩ = $৮১/query। ১০০ analyst × ১০ query/day = ৮১ ক $৮১,০০০/day — অসঙ্গত।
• সমাধান: flat-rate slot reservation। ২,০০০ slots × $৬০/month/slot = $১২০,০০০/month। Predictable।

(৫) Query performance — ১০s target:

-- ৭ দিন, Dhaka, top 100 callers
SELECT caller_msisdn, COUNT(*) AS calls, SUM(duration_sec) AS total
FROM gp_dwh.cdr
WHERE DATE(call_ts) BETWEEN '2025-05-02' AND '2025-05-08'
  AND region = 'Dhaka'
GROUP BY 1
ORDER BY calls DESC
LIMIT 100;

• Partition pruning: ১০৯৫ → ৭।
• Cluster pruning: region='Dhaka' → ~১২.৫% rows।
• Effective scan: ~১.৩ TB। ২০০০ slots-এ ~৬-৮s।

(৬) Aggregation table — daily roll-up:

-- Daily agg view দিয়ে ৯০% query আরো দ্রুত
CREATE MATERIALIZED VIEW gp_dwh.cdr_daily_region AS
SELECT DATE(call_ts) AS day, region, call_type,
       COUNT(*) AS calls, SUM(duration_sec) AS dur
FROM gp_dwh.cdr
GROUP BY 1, 2, 3;

(৭) Monitoring ও cost guard:

• Project-level maximum_bytes_billed = ১০ TB per query।
• BigQuery Reservation Insights — slot utilization weekly।
• Audit log: যে query ১০০+ TB scan করে — alert + review।

মূল উপলব্ধি: Telco-scale BigQuery-এ partition + cluster + reservation + materialized view — চারটি একসাথে দরকার। শুধু একটি বাদ গেলে cost বা performance ভাঙে। GP-র মতো operator-এ DE team-এর primary KPI = "predictable cost + sub-10s queries"।

BigQuery cost surprise — যেকোনো cloud DW-এর সবচেয়ে common incident। Investigation এবং remediation step-by-step।

(১) Investigation — INFORMATION_SCHEMA জিজ্ঞেস:

SELECT
  user_email, job_id, query,
  total_bytes_billed/POW(1024,4) AS tb_billed,
  total_slot_ms/3600000 AS slot_hours,
  creation_time
FROM `region-asia-southeast1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
ORDER BY total_bytes_billed DESC
LIMIT 50;

(২) সাধারণ root cause:

• Full-table scan: "SELECT * FROM huge_table" — partition filter মিস। দিনে ১০০ TB scan সহজ।
• SELECT * একটি wide table: ৫০ column × ১ TB → ৫০ TB scan, যদিও ৩ column চাই।
• Cartesian join: JOIN condition মিস → row explosion, intermediate scan বিশাল।
• Repeated subquery: CTE materialize হয় না; পাঁচবার একই subquery = পাঁচগুণ scan।
• Tableau "auto-refresh" dashboard: প্রতি ১৫ মিনিট একই query rerun, cache miss।
• External query bug: dbt model-এ partition filter বাদ গেছে।

(৩) Daraz-specific likely cause:

• Marketing analyst নতুন campaign launch report-এ "WHERE date" বাদ দিয়ে ১২ TB events table full scan, ৫০x/day।
• বা — dbt run accidentally হয়ে গেল table re-materialize, ৪ TB rebuild × ১০ models।
• বা — Looker dashboard auto-refresh ৫ মিনিটে — ৫০০ user × ৩০ days।

(৪) Immediate guardrails — ০-২৪ ঘণ্টা:

• Project-level maximum_bytes_billed = 1 TB per query।
• সব production tables-এ require_partition_filter = TRUE alter।
• User-level custom quota — daily TB cap।
• Looker auto-refresh disable; user-triggered শুধু।

(৫) Medium-term — ১-৭ দিন:

• Slot reservation switch — predictable cost (যেমন ৫০০ slots/$১৪,৬০০/mo flat)।
• Materialized view-এ frequent dashboard query precompute।
• BI Engine reservation — small datasets in-memory।
• dbt: --profile prod-cap with bytes_billed limit।
• Cost alert — Cloud Billing budget at 50%, 80%, 100%।

(৬) Long-term — culture:

• Onboarding training: SELECT * নিষিদ্ধ; partition filter mandatory।
• Code review: dbt PR-এ --dry-run bytes_billed check।
• Cost dashboard — daily, by user, by query family।
• Quarterly cost review — DE lead + finance।

(৭) BD-specific consideration:

• BDT exchange rate volatility — USD bill ১২x = BDT bill ১৫x (rate পরিবর্তনে আরও খারাপ)।
• CFO অনুমোদন cycle ২-৪ সপ্তাহ — runaway cost মাসের শেষে catastrophe।
• Bangladesh Bank-এর foreign exchange rules — USD payment delay possible।

মূল উপলব্ধি: Cloud DW cost engineering technical না — culture। Guardrails আগে বসান, surprise-এর পর নয়। Daraz-এর ক্ষেত্রে immediate fix + slot reservation + culture shift = ১২x bill ৩x-এ নামানো realistic।