12.1 Problem: 100 Cr Row पर Normal SQL क्यों Fail होता है?
तुम्हारा Laptop वाला MySQL:
SELECT COUNT(*) FROM orders WHERE order_date = ‘2026-07-01’;
100 Cr row पर = 40 मिनट लगेगा 😵
Reason:
1.Single Machine: 1 CPU, 16GB RAM
2.Full Table Scan: हर row पढ़ेगा
3.No Parallelism: 1 काम एक बार में
Solution: BigQuery + Spark = 1000 Machine एक साथ काम करेंगी = 30 सेकंड
12.2 Core Concept 1: BigQuery – Google का Data Warehouse
BigQuery = “Serverless + Columnar + Distributed”3 जादू जो इसे fast बनाते हैं:
1.Columnar Storage:
Normal DB: Row by row store करता है
BigQuery: Column by column store करता है
SELECT AVG(amount) करना है तो सिर्फ amount column पढ़ेगा। बाकी 20 column skip।
Speedup: 10x
2.Partitioning – Table को टुकड़े करो
— Wrong: पूरा table scan
SELECT * FROM orders WHERE order_date = ‘2026-07-01’;
— Right: Partitioned Table
CREATE TABLE orders
PARTITION BY order_date
AS SELECT * FROM orders_raw;
अब Query सिर्फ 1 दिन का data पढ़ेगी। Speedup: 365x अगर 1 साल का data है
3.Clustering – Partition के अंदर Sort करो
CREATE TABLE orders
PARTITION BY order_date
CLUSTER BY customer_id, country;
अब WHERE customer_id = 101 भी fast होगा। Speedup: 5x
12.3 Core Concept 2: Spark SQL – Distributed Processing
Spark = “Memory में काम + Parallel Processing
“Spark का Flow:
1.Driver: Plan बनाता है
2.Executor: 100 Machine पर काम बांटता है
3.DataFrame: Table जैसा, पर distributed
Spark SQL Query:
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName(“FeatureEng”).getOrCreate()
df = spark.read.parquet(“gs://bucket/orders_100cr.parquet”)
df.filter(“order_date = ‘2026-07-01′”) \
.groupBy(“customer_id”) \
.agg({“amount”: “sum”}) \
.write.parquet(“output/”)from pyspark.sql import SparkSession
spark = SparkSession.builder.appName(“FeatureEng”).getOrCreate()
df = spark.read.parquet(“gs://bucket/orders_100cr.parquet”)
df.filter(“order_date = ‘2026-07-01′”) \
.groupBy(“customer_id”) \
.agg({“amount”: “sum”}) \
.write.parquet(“output/”)from pyspark.sql import SparkSession
spark = SparkSession.builder.appName(“FeatureEng”).getOrCreate()
df = spark.read.parquet(“gs://bucket/orders_100cr.parquet”)
df.filter(“order_date = ‘2026-07-01′”) \
.groupBy(“customer_id”) \
.agg({“amount”: “sum”}) \
.write.parquet(“output/”)
ये 100 Cr row = 30-40 सेकंड। क्योंकि 200 core एक साथ चल रहे।
12.4 BigQuery vs Spark – कब क्या use करें?
| BigQuery | Spark | |
| Setup | 0 Code, Direct SQL | Cluster बनाना पड़ेगा |
| Cost | $5 per TB scan | VM का पैसा |
| Speed | 100 Cr = 15-30 sec | 100 Cr = 30-60 sec |
| Best For | Analytics, Dashboard | ML, ETL, Complex Logic |
| SQL | Standard SQL + ML | PySpark SQL |
Rule: Dashboard = BigQuery, ML Pipeline = Spark
12.5 100 Cr Row को 30 सेकंड में Process करने के 7 Rules
Rule 1: Table को Partition + Cluster करो – सबसे जरूरी
— BigQuery
CREATE TABLE project.dataset.orders
PARTITION BY DATE(order_timestamp)
CLUSTER BY customer_id, product_id
AS SELECT * FROM raw_orders;
— Spark
df.write.partitionBy(“order_date”).parquet(“path”)
Impact: 40 मिनट -> 45 सेकंड
Rule 2: SELECT * मत करो
— Wrong
SELECT * FROM orders
— Right
SELECT customer_id, SUM(amount) FROM orders GROUP BY 1
सिर्फ जरूरी column लो। Impact: 3x fast
Rule 3: JOIN से पहले Filter करो
— Wrong
SELECT * FROM A JOIN B ON A.id=B.id WHERE A.date=’2026-07-01′
— Right
SELECT * FROM (SELECT * FROM A WHERE date=’2026-07-01′) A
JOIN B ON A.id=B.id
पहले data छोटा करो फिर Join। Impact: 10x fast
Rule 4: Broadcast Join – छोटी table को हर machine पर भेज दो
— Spark
SELECT /*+ BROADCAST(products) */ *
FROM orders o JOIN products p ON o.product_id = p.id
10 Cr row + 1000 row = 20 सेकंड। Impact: 50x fast
Rule 5: Avoid Shuffle – GroupBy महंगा है
— Exact CountDistinct – Slow
SELECT COUNT(DISTINCT user_id) FROM orders
— Approximate – 100x Fast, 1% error
SELECT APPROX_COUNT_DISTINCT(user_id) FROM orders
Dashboard के लिए यही चाहिए।
Rule 7: Materialized View बनाओ
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT order_date, SUM(amount) FROM orders GROUP BY 1;
पहली बार 2 मिनट। बाद में हर बार 2 सेकंड।
12.6 Real Case Study: 100 Cr Click Data Process करना
Problem: हर दिन “Top 10 Products per Category” निकालना
Step 1: Table Design
CREATE TABLE clicks
PARTITION BY event_date
CLUSTER BY category, product_id
AS SELECT * FROM raw_clicks;
Step 2: Fast Query
WITH ranked AS (
SELECT
category,
product_id,
COUNT() AS clicks, RANK() OVER(PARTITION BY category ORDER BY COUNT() DESC) AS rnk
FROM clicks
WHERE event_date = CURRENT_DATE() — Partition Pruning
GROUP BY 1,2
)
SELECT * FROM ranked WHERE rnk <= 10;
Result: 100 Cr row = 28 सेकंड। Cost = $0.12
Spark में:
df.filter(df.event_date == current_date()) \
.groupBy(“category”, “product_id”).count() \
.withColumn(“rnk”, rank().over(Window.partitionBy(“category”).orderBy(desc(“count”)))) \
.filter(“rnk <= 10”)
12.7 BigQuery ML – SQL से ही Model बनाओ
Code लिखे बिना:
CREATE MODEL project.dataset.churn_model
OPTIONS(model_type=’logistic_reg’) AS
SELECT
days_since_last_order,
total_spend,
CASE WHEN days_since_last_order > 30 THEN 1 ELSE 0 END AS is_churned
FROM customer_features;
Predict:
SELECT * FROM ML.PREDICT(MODEL churn_model, TABLE new_users)
100 Cr row पर prediction = 1 मिनट।
12.8 Spark Optimization – Advanced
1. Caching
df = spark.read.parquet(“100cr.parquet”)
df.cache() — Memory में रख लो
df.filter().count()
df.groupBy().sum() — दूसरी बार instant
2.File Format: Parquet + Snappy
CSV = 500GB, Parquet = 50GB। 10x छोटा, 5x fast
3.Repartition
df.repartition(200, “customer_id”) — 200 task बनाओ
12.9 Cost Control – Bill कम कैसे करें
BigQuery $5/TB scan करता है।
1.Preview Table: LIMIT 100 पहले
2.Partition Filter जरूरी: WHERE date = … हमेशा डालो
3.Dry Run: Query चलाने से पहले कितना data scan होगा देखो
Example: 100 Cr row = 200GB = $1 per query
12.10 10 Interview Questions – BigQuery + Spark
Q1: Partition और Cluster में फर्क?
A: Partition = Folder बनता है। Cluster = Folder के अंदर sort।
Q2: Shuffle क्या है और क्यों बुरा?
A: Data को machine के बीच move करना। Network slow है।
Q3: Skewed Join कैसे handle करोगे?
A: Salted Key या Broadcast
Q4: Spark में collect() क्यों खतरनाक?
A: पूरा data Driver पर लाता है। OOM आएगा।
Q5: BigQuery Slot क्या है?
A: CPU की unit। ज्यादा Slot = ज्यादा parallel।
Q6: 100 Cr row dedup कैसे करोगे?
A: ROW_NUMBER() OVER(PARTITION BY id ORDER BY ts DESC)
Q7: Spark vs Hive?
A: Spark Memory में, Hive Disk पर।
Q8: Iceberg / Delta Lake क्या है?
A: Table format जो ACID देता है।
Q9: Slot Contention?
A: ज्यादा query एक साथ = slow।
Q:10 Explain Plan कैसे पढ़ते हैं?
A: EXPLAIN में देखो Stage कितने हैं।
12.11 Final Project: “Daily Sales Report in 30s”
Architecture:
Raw Logs -> GCS -> Spark Job -> BigQuery -> Dashboard
Spark Code:
spark.read.json(“gs://logs/”) \
.filter(“date = current_date”) \
.groupBy(“product_id”).agg(sum(“revenue”)) \
.write.mode(“overwrite”).saveAsTable(“daily_sales”)
Schedule करो Airflow से। रोज सुबह 8 बजे report ready।
12.12 Chapter 12 Cheat Sheet
| Problem | Solution | Speedup |
| Full Scan | Partition by Date | 365x |
| Slow GroupBy | Cluster by Group Key | 5x |
| Big + Small Join | Broadcast Join | 50x |
| Repeated Query | Materialized View | 100x |
| Large Data | Parquet + Columnar | 10x |
Next: Chapter 13 – Interview + Case Study Round
वहां FAANG के 20 SQL + System Design Q तोड़ेंगे।