Case Study · Data Platforms

Data Platforms

Problem: Wide Snowflake scans and unstable BI p95 during peak reporting windows.
Role: Modeled star-schema marts, clustered Snowflake tables on county keys, and orchestrated dbt, Spark, and Airflow pipelines.
Stack: Snowflake, dbt, Spark, Airflow
Impact: 75% fewer bytes on LA County star join · Q3 elapsed 1,086 ms → 475 ms
Links: GitHub · Live (private tenant)

Production data platforms that cut query cost, improve decision latency, and keep downstream ML reliable.

Operational guarantees: predictable data contracts, lower operational noise, and faster incident recovery.

Context: baseline captured pre-refactor, then re-measured weekly for 5 weeks under identical BI benchmark mixes.

Warehouse economics

Snowflake warehouse optimization for BI reliability

Context: Enterprise BI dashboards depended on daily Snowflake models with drill-down by customer, region, and time window.

Problem: Shared fact tables were not clustered for common filters, so analysts hit wide scans and unstable p95 dashboard load times.

What I built: Used dbt to split high-cardinality facts, added Snowflake clustering keys on date and tenant dimensions, replaced heavy joins with pre-aggregated marts, and used targeted Spark backfills for historical reprocessing without blocking fresh loads.

Impact: 75% fewer bytes scanned on the LA County 4-table star join (17.9 MB → 4.5 MB), 56% faster Q3 elapsed (1,086 ms → 475 ms), ~71% fewer fact rows scanned per table, and zero spill on benchmark paths.

Tradeoff I made: Accepted scheduled reclustering and model maintenance overhead to keep query performance predictable during reporting peaks.

75%

fewer bytes scanned

Baseline: 17.9 MB (Q3 star join)
After: 4.5 MB per query
Sample: LA County filter, 4-fact join

56%

lower Q3 elapsed (star join)

Baseline: 1,086 ms elapsed
After: 475 ms elapsed
Sample: California / LA County

Proof artifact · data contract

Breaking-change gate on mart_daily_revenue

# contracts/mart_daily_revenue.yml
version: 2
models:
  - name: mart_daily_revenue
    columns:
      - name: tenant_id
        data_type: uuid
        tests: [not_null, relationships]
      - name: revenue_usd
        data_type: numeric(18,2)
        meta:
          breaking_change: range_shift  # ← deploy gate on distribution shift

checks:
  - name: no_negative_revenue
    sql: |
      SELECT COUNT(*) FROM {{ ref('mart_daily_revenue') }}
      WHERE revenue_usd < 0
    config:
      severity: error
      fail_calc: count > 0  # ← blocks merge on any negative row

Architecture diagram

Source systems -> Airflow orchestration -> dbt/Spark transforms -> Snowflake marts -> BI dashboards + ML consumers

Before & After

Before

Wide scans, slower BI drill-downs, and rising warehouse spend under peak reporting windows.

p95 dashboard load > 4.0s

After

Targeted scans, faster dashboard interactions, and predictable query performance for engineering teams.

p95 down 56% · bytes scanned down 75%

Execution Footprint

Core stack and operating patterns used to deliver the outcomes above.

Tech stack

Techniques / models / operations

Dimensional Modeling ELT Pipelines Data Quality Clustering Keys

Data Platforms

Warehouse economics

Snowflake warehouse optimization for BI reliability

Architecture diagram

Before & After

Execution Footprint

More case studies

Data Platforms

Agent Benchmark Engineering

Inference & Evals

Fullstack product

Backend & Cloud

SENTINEL

Data Platforms

Agent Benchmark Engineering

Inference & Evals

Fullstack product

Backend & Cloud

SENTINEL