Self/Past dependencies

Self/Past Dependencies - Explained in Depth

This is one of Maxime's most important warnings! Let me break it down:

What Are Self/Past Dependencies?

When a partition depends on a previous partition from the same table.

BAD PATTERN (Self-dependency):

Day 1: dim_user (empty) → load → dim_user_day1
Day 2: dim_user_day1 → changes → dim_user_day2
Day 3: dim_user_day2 → changes → dim_user_day3
Day 4: dim_user_day3 → changes → dim_user_day4
...
Day 1000: dim_user_day999 → changes → dim_user_day1000

Each day depends on the previous day!

The Problem: Complexity Score

Maxime introduces "complexity score" - how many partitions are needed to compute a given partition?

COMPLEXITY SCORE EXPLOSION:

To compute dim_user_day1000:
┌─────────────────────────────────────┐
│ Need: dim_user_day999               │
│ Which needs: dim_user_day998        │
│ Which needs: dim_user_day997        │
│ ...                                 │
│ Which needs: dim_user_day1          │
│                                     │
│ Complexity Score: 1000!             │
└─────────────────────────────────────┘

vs.

GOOD PATTERN (No self-dependency):

To compute dim_user_day1000:
┌─────────────────────────────────────┐
│ Need: raw_user_data_day1000         │
│ Need: reference_data (maybe)        │
│                                     │
│ Complexity Score: 2!                │
└─────────────────────────────────────┘

Concrete Example

SCENARIO: Customer dimension with "total_rides" field

BAD APPROACH (self-dependency):
┌────────────────────────────────────────┐
│ Day 1:                                 │
│   dim_customer:                        │
│   Alice: total_rides = 5               │
│                                        │
│ Day 2 Logic:                           │
│   Take yesterday's dim_customer        │ ← DEPENDS ON PAST
│   Add today's new rides                │
│   Alice: total_rides = 5 + 3 = 8       │
└────────────────────────────────────────┘

Problem: To backfill Day 500, you need Days 1-499!

Visual Representation of the Problem

LINEAR DEPENDENCY CHAIN (can't parallelize):

Day1 → Day2 → Day3 → Day4 → ... → Day1000
  ↓      ↓      ↓      ↓             ↓
  ✓    wait   wait   wait          wait

Must run sequentially!
Takes 1000x the time!


GOOD PATTERN (independent partitions):

Source → Day1
      ↘ Day2
      ↘ Day3     All can run
      ↘ Day4     in parallel!
      ↘ ...
      ↘ Day1000

Can run concurrently!
Same total time regardless of history!

Why This Matters for Backfills

BACKFILL SCENARIO:

Bug discovered in Day 500's logic
Need to reprocess Days 500-1000

With self-dependency:
┌────────────────────────────────────┐
│ Must reprocess sequentially:       │
│ Day500 → Day501 → Day502 → ...     │
│ → Day999 → Day1000                 │
│                                    │
│ Time: 500 sequential jobs          │
│ Parallelization: None              │
└────────────────────────────────────┘

Without self-dependency:
┌────────────────────────────────────┐
│ Can reprocess in parallel:         │
│ Day500 ┐                           │
│ Day501 ├─ All at once              │
│ Day502 │                           │
│ ...    │                           │
│ Day1000┘                           │
│                                    │
│ Time: 1 job (parallelized)         │
└────────────────────────────────────┘

The Common Culprit: Cumulative Metrics

Why people create self-dependencies:

"I want customer.lifetime_value in my dimension"
"I want customer.total_purchases"
"I want customer.days_since_first_visit"

These are CUMULATIVE METRICS that tempt you to do:
  today = yesterday + today's_changes

Maxime's advice: DON'T DO THIS IN DIMENSIONS!

Solutions

Solution 1: Recompute from scratch each time

RECOMPUTE PATTERN:

Every day, scan ALL history:
┌────────────────────────────────────┐
│ dim_customer_day1000:              │
│                                    │
│ SELECT                             │
│   customer_id,                     │
│   name,                            │
│   SUM(amount) as lifetime_value    │
│ FROM fact_purchases               │
│ WHERE date <= '2024-01-01'        │ ← Scan everything
│ GROUP BY customer_id, name         │
└────────────────────────────────────┘

Pros:
✓ No self-dependency
✓ Can backfill any day independently
✓ Reproducible

Cons:
✗ Scans grow over time
✗ More compute needed

Solution 2: Don't put cumulative metrics in dimensions

SEPARATE CONCERNS:

Dimension (slow-changing attributes):
┌────────────────────────────────────┐
│ dim_customer:                      │
│ - customer_id                      │
│ - name                             │
│ - email                            │
│ - region                           │
│ - signup_date                      │
└────────────────────────────────────┘
Snapshot daily, no dependencies

Metrics (cumulative data):
┌────────────────────────────────────┐
│ fact_customer_metrics:             │
│ - customer_id                      │
│ - date                             │
│ - lifetime_value                   │
│ - total_rides                      │
│ - days_active                      │
└────────────────────────────────────┘
Use specialized framework

Solution 3: Use a specialized cumulative framework

SPECIALIZED FRAMEWORK:

Instead of daily partitions with dependencies,
use a framework optimized for cumulative metrics:

┌────────────────────────────────────┐
│ Incremental Materialized Views     │
│ Change Data Capture (CDC)          │
│ Streaming aggregations             │
│ Pre-aggregation tables             │
└────────────────────────────────────┘

Maxime mentions this is "out of scope" but
acknowledges it's a common pattern at scale

Real-World Impact

At Facebook/Airbnb scale:

WITHOUT self-dependencies:
├─ Any partition fails? Rerun it
├─ Bug in logic? Backfill in parallel
├─ New analyst? Easy to understand
└─ 3 years of history? No problem

WITH self-dependencies:
├─ One partition fails? May need to rerun hundreds
├─ Bug in logic? Sequential backfill takes days
├─ New analyst? Hard to trace dependencies
└─ 3 years of history? Complexity nightmare

Key Takeaway

┌────────────────────────────────────────┐
│ MAXIME'S RULE:                         │
│                                        │
│ If partition N depends on partition    │
│ N-1 from the same table, you're       │
│ creating a COMPLEXITY DEBT that        │
│ grows linearly with time.              │
│                                        │
│ Avoid at all costs!                    │
└────────────────────────────────────────┘

The essence: Each partition should be independently computable from source data, not dependent on previous versions of itself. This keeps your complexity constant regardless of how much history you have, and enables parallelization and easy backfills.