The Query That Used to Be Fast

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

medium

Seniority

L5

Problem

Dashboards on a 2TB Snowflake fact table used to answer in seconds; now an analyst watches the same date- and merchant-filtered queries take minutes, far past the 30-second SLA. Design the fix: a served surface for those dashboards fed from the warehouse, plus a scheduled job that tracks p95 query duration and pages the team on regression.

Queries used to be fast. Something changed.

Business requirements

• Analysts run interactive dashboards and expect results in tens of seconds; today they're waiting minutes.
• The same date-filtered dashboards run hundreds of times a day and re-scan the whole table each time.
• When p95 query duration regresses past the interactive SLA, the team needs to be paged, not hear about it from analysts.

Worked solution and explanation

What this really is

This is a caching problem dressed up as warehouse tuning. The 2TB table is not the problem; the access pattern is. A few hundred identical date-and-merchant dashboards scan the whole fact table every day, each one paying for the same answer. Everyone reaches for a bigger warehouse or a new cluster key. That buys seconds, and every rerun of every dashboard still pays for the scan. The real move is to compute the answer once per data arrival and let dashboards read the small result. The second half is quieter: p95 crept from seconds to minutes over months and nobody noticed, because nothing was measuring it.

Step 1: Find the shape the dashboards actually ask for

Ask what the dashboards read before touching the table. Here it is daily totals and counts per merchant, filtered by date, and new rows land hourly. That means the full answer to every dashboard is a few million aggregate rows that change once an hour, not 2TB that must be scanned on demand.

Step 2: Serve them from materialized_aggregates

Build the merchant-by-day aggregate from snowflake_fact_table and point analyst_dashboards at it, not at the fact table. The repeated queries now hit a surface thousands of times smaller, so they answer in seconds without resizing anything. The fact table stays where it is for anyone who truly needs row-level detail.

Step 3: Measure p95 on a schedule and page on regression

p95_query_monitor is an Airflow job that reads the warehouse's query history on a schedule and computes p95 duration for the dashboard queries. Its alert pages the team when p95 crosses the 30-second SLA, so the next slow drift is caught in a day, not reported by analysts months later.

The reference design

Bigger warehouse or new cluster key

Every dashboard load still scans the fact table, just faster or with better pruning. Cost grows with query volume, a cluster key favors one filter over the others, and the gain decays as the table grows by about 5GB a day.

Pre-computed aggregate surface

The scan happens once per hourly load. Hundreds of dashboard loads read a tiny merchant-by-day table, so latency stays flat as the fact table grows and spend follows refreshes, not clicks.

Common follow-up questions

• Data scientists start running ad-hoc row-level queries against `snowflake_fact_table` and they are slow too. What changes? (Tests whether the candidate keeps the dashboard surface separate and treats ad-hoc row-level access as its own problem (layout or pruning on the fact table), rather than reshaping the aggregate for a second audience.)
• p95 is healthy but p99 is ten times worse. Where do you look? (Tests whether the candidate profiles the slow tail to find a different query class, such as a filter the aggregate does not cover, before upsizing compute.)