After studying this material, you should be able to:

1. Explain why export workloads create disproportionate storage costs in cloud lakehouses
2. Describe how GCS auto-class tiering works and why export workloads break it
3. Understand how Apache Hudi column stats enable file pruning without touching data files
4. Explain why table sorting amplifies the effectiveness of column stats
5. Evaluate trade-offs between secondary indexes versus column stats + sorting
6. Apply this optimization pattern to real-world use cases across industries

---

Step-by-Step Teaching

Step 1: Understanding the Problem Context

What is a Lakehouse?

A lakehouse combines the scalability of a data lake with the query capabilities of a data warehouse. Uber stores its lakehouse data on Google Cloud Storage (GCS) using Apache Hudi as the table format.

What Are Export Workloads?

Think of export workloads as needle-in-a-haystack searches across massive historical data.

Concrete Example — DSAR Request:

A user says: "Show me all personal data Uber holds about me."

This translates to a query like:

SELECT *
FROM trip_table
WHERE rider_id = 'user_12345'

Key Characteristics of Export Workloads

Why This Is a Problem

Even though the output is tiny, the scan surface is enormous.

Output size:     ████ (small)
Data scanned:    ████████████████████████████████████ (massive)

---

Step 2: Understanding GCS Auto-Class Tiering

The Storage Tier System

GCS automatically moves data between storage classes based on how frequently files are accessed:

FREQUENTLY ACCESSED
        │
        ▼
┌─────────────────┐
│   STANDARD      │  ← Highest cost, fastest access
│   (Hot data)    │
└─────────────────┘
        │ (not accessed for 30 days)
        ▼
┌─────────────────┐
│   NEARLINE      │  ← Lower cost
└─────────────────┘
        │ (not accessed for 90 days)
        ▼
┌─────────────────┐
│   COLDLINE      │  ← Even lower cost
└─────────────────┘
        │ (not accessed for 365 days)
        ▼
┌─────────────────┐
│   ARCHIVE       │  ← Cheapest storage
│   (Cold data)   │
└─────────────────┘

Ideal vs. Reality Distribution

Ideal distribution (based on typical access patterns):

Standard:  60% ██████████████████████████████
Nearline:  15% ███████████████
Coldline:  10% ██████████
Archive:   15% ███████████████

Reality with export workloads:

Standard: 100% ████████████████████████████████████████████████████
Nearline:   0%
Coldline:   0%
Archive:    0%

Why Export Workloads Break Auto-Class Tiering

Auto-class tiering only works when files are left untouched long enough to go cold.

Export workloads repeatedly touch all files across all historical partitions. This resets the access timer on every file, keeping everything in the expensive Standard tier permanently.

Key insight: Even reading a file's metadata or footer counts as an access and keeps it hot.

---

Step 3: Contrasting Analytics vs. Export Workloads

This contrast is critical to understanding why export workloads are uniquely problematic.

Typical Analytics Query

SELECT city, COUNT(*) as trips, SUM(fare)
FROM trip_table
WHERE date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY city

Characteristics:

• Broad in data volume (aggregates many records)
• Narrow in time (only recent partitions)
• Older partitions go untouched → they go cold → auto-class saves money ✅

Typical Export Query

SELECT *
FROM trip_table
WHERE rider_id = 'user_12345'
-- No date filter — must search all history

Characteristics:

• Narrow in output (few records returned)
• Broad in time (searches all historical partitions)
• All partitions get touched repeatedly → nothing goes cold → no savings ❌

Visual Comparison of File Scanning

ANALYTICS QUERY:
Partition Jan ░░░░░░░░░░░░░░░░  (cold, untouched)
Partition Feb ░░░░░░░░░░░░░░░░  (cold, untouched)
Partition Mar ░░░░░░░░░░░░░░░░  (cold, untouched)
Partition Jun ████████████████  (scanned — recent)
Partition Jul ████████████████  (scanned — recent)

EXPORT QUERY:
Partition Jan ████████████████  (scanned — all history)
Partition Feb ████████████████  (scanned — all history)
Partition Mar ████████████████  (scanned — all history)
Partition Jun ████████████████  (scanned — all history)
Partition Jul ████████████████  (scanned — all history)

---

Step 4: Why the Obvious Solution (Secondary Index) Doesn't Work

What Is a Secondary Index?

A secondary index maps non-primary-key column values to the files that contain them. It's designed to help the query engine skip irrelevant files.

Why It Fails Here

The problem is data distribution. If records for a given rider_id are scattered randomly across many files, the secondary index still maps that ID to most of the files.

rider_id = 'user_12345' → files: [f1, f3, f7, f12, f18, f24, f31...]
                                   ↑ Still a large file set to scan

Additional problems with secondary indexes:

• Higher metadata storage overhead
• Must maintain lookup mappings for every indexed value
• Doesn't reduce scan cost enough to justify the complexity

Core insight: A secondary index tells you where records are, but if they're everywhere, it doesn't help much.

---

Step 5: The Solution — Column Stats + Table Sorting

The Two-Part Solution

┌─────────────────────────────────────────────────────┐
│  SOLUTION = Hudi Column Stats + Table Sorting        │
│                                                      │
│  Column Stats → Pruning MECHANISM                    │
│  Table Sorting → Pruning EFFECTIVENESS               │
└─────────────────────────────────────────────────────┘

---

Part A: Hudi Column Stats

What Are Column Stats?

Hudi maintains a metadata table that stores statistics for each data file:

How File Pruning Works

When a query arrives with WHERE rider_id = 'user_12345', the engine checks the metadata table:

File 001: rider_id min='user_00001', max='user_09999'  → MIGHT contain target ✓
File 002: rider_id min='user_10000', max='user_19999'  → SKIP ✗
File 003: rider_id min='user_20000', max='user_29999'  → SKIP ✗
File 004: rider_id min='user_10000', max='user_15000'  → SKIP ✗

Only files where min ≤ target ≤ max need to be scanned.

Why Not Just Use Parquet Footer Stats?

Parquet files also store column statistics, but they live inside the file footer.

PARQUET FOOTER APPROACH:
Query → Open File 001 → Read footer → Check stats → Maybe scan
      → Open File 002 → Read footer → Check stats → Skip
      → Open File 003 → Read footer → Check stats → Skip
      ↑ Every file is "touched" — keeps all files HOT in GCS

HUDI COLUMN STATS APPROACH:
Query → Read metadata table → Identify relevant files → Scan only those
      ↑ Cold files are NEVER touched — auto-class tiering preserved ✓

This is the critical difference: Hudi column stats enable pruning without touching the actual data files.

---

Part B: Table Sorting

The Problem Column Stats Alone Can't Solve

Even with column stats, if records for rider_id = 'user_12345' are scattered across many files, the min/max ranges of those files will overlap with the target value.

Unsorted table — wide min/max ranges:

File 001: min='user_00001', max='user_99999'  → Must scan (range includes target)
File 002: min='user_00002', max='user_99998'  → Must scan (range includes target)
File 003: min='user_00001', max='user_99997'  → Must scan (range includes target)
... (almost every file has overlapping range)

How Sorting Fixes This

When the table is sorted by rider_id, similar values are physically clustered together in the same files. This tightens the min/max ranges:

Sorted table — tight min/max ranges:

File 001: min='user_00001', max='user_01000'  → SKIP ✗
File 002: min='user_01001', max='user_02000'  → SKIP ✗
File 003: min='user_02001', max='user_03000'  → SKIP ✗
...
File 012: min='user_12001', max='user_13000'  → SCAN ✓ (contains user_12345)
File 013: min='user_13001', max='user_14000'  → SKIP ✗
...

Now only 1 file needs to be scanned instead of hundreds!

Sorting Also Improves Compression

When identical or similar values are stored together, compression algorithms work more efficiently:

UNSORTED:  user_99, user_01, user_45, user_12, user_78...  → Poor compression
SORTED:    user_01, user_02, user_03, user_04, user_05...  → Excellent compression

This reduces total storage footprint as an additional benefit.

---

Step 6: Benchmark Results

The Uber team validated this approach with real data:

Files Touched: Sorted vs. Unsorted

For a query with 100 predicates (user IDs):

Unsorted: ████████████████████████████████████████ ~80% of files touched
Sorted:   ████ ~5% of files touched

As the number of predicates increases, the sorted table still touches far fewer files proportionally.

Overall Efficiency Gains (Single Partition)

---

Step 7: Decision Framework — Choosing the Right Approach

Use this matrix to decide which approach to take:

                    │ Table UNSORTED │ Table SORTED
────────────────────┼────────────────┼──────────────────
Secondary Index     │ Moderate help  │ Overkill + heavy
                    │ (still many    │ metadata overhead
                    │  files mapped) │
────────────────────┼────────────────┼──────────────────
Hudi Column Stats   │ Limited help   │ ✅ OPTIMAL
                    │ (wide ranges)  │ (tight ranges +
                    │                │  lightweight meta)

Conclusion: Sorting + Column Stats is the winning combination because:

• Sorting provides physical clustering (tight min/max ranges)
• Column stats provide lightweight pruning (no data file touches)
• Together they eliminate the need for a heavier secondary index

---

Step 8: Broader Applicability

This pattern applies anywhere you have narrow lookups over large historical datasets:

The common pattern: subject-oriented lookup over long retention window

---

Step 9: Key Lessons Summary

┌─────────────────────────────────────────────────────────────┐
│                    KEY LESSONS                               │
├─────────────────────────────────────────────────────────────┤
│ 1. Access pattern drives storage cost                        │
│    → Narrow + history-spanning = expensive if unoptimized   │
│                                                             │
│ 2. File layout matters as much as tiering                   │
│    → Auto-class only saves money when files go cold         │
│                                                             │
│ 3. Metadata-driven pruning is critical                      │
│    → Parquet footers still touch files; Hudi stats don't    │
│                                                             │
│ 4. One-time rewrite → long-term savings                     │
│    → Upfront sort cost pays off repeatedly over time        │
│                                                             │
│ 5. End-to-end design required                               │
│    → Storage + metadata + query + orchestration must align  │
└─────────────────────────────────────────────────────────────┘

---

Step 10: What's Next — Advanced Concepts to Explore

Once you've mastered this material, the next frontier involves:

Multi-Column Lookup Problem

Single-column sorting only helps when queries always filter on the same column. What if different clients query by rider_id, driver_id, or courier_id?

Solution being explored: Z-order curves

Z-order (also called Morton code) maps multi-dimensional data to a single dimension while preserving locality across multiple columns simultaneously:

Single sort:  Good for rider_id queries, poor for driver_id queries
Z-order sort: Reasonably good for BOTH rider_id AND driver_id queries

Automated Pattern Detection

Building a system that:

1. Detects needle-in-a-haystack access patterns automatically
2. Calculates ROI of sorting vs. query savings
3. Triggers optimization pipelines only when justified

---

Quick Reference Cheat Sheet

PROBLEM:
  Export workloads → full table scans → all files stay hot
  → 100% Standard tier → maximum storage cost

ROOT CAUSE:
  Records scattered across files → wide min/max ranges
  → column stats can't prune effectively
  → Parquet footer reads keep files hot anyway

SOLUTION:
  Sort table by predicate column
    → Records clustered → tight min/max ranges
  + Enable Hudi column stats
    → Prune files using metadata (no data file touches)
  = Only relevant files scanned
    → Cold files stay cold → auto-class tiering works
    → Lower storage + egress + compute costs

WHY NOT SECONDARY INDEX:
  Records still map to many files (before sorting)
  Heavier metadata overhead than column stats
  After sorting, column stats are sufficient