Advanced SQL: Metric Monitoring, Anomaly Detection, and Data Correctness at Scale Questions

Direct answer

Compute the rate itself with a straightforward aggregate query, then test whether the week-over-week change is statistically significant with a two-proportion z-test rather than eyeballing the raw percentage difference. A seller with only a handful of orders can swing 10 points in a week from noise alone; a seller with hundreds of orders moving 2 points might be a real signal.

Structured elaboration

• Step 1, the rate: cancellation_rate = COUNT(CASE WHEN status = 'canceled' THEN 1 END) / COUNT(*), filtered to the last 90 days, grouped by seller_id, with sellers under 50 orders excluded (the corpus-level floor the question specifies, itself a coarse version of the same minimum-volume idea used to guard rate metrics elsewhere). As a runnable query:

SELECT seller_id,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) AS canceled_orders,
  ROUND(1.0 * SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) / COUNT(*), 4) AS cancellation_rate
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY seller_id
HAVING COUNT(*) >= 50
ORDER BY seller_id;

Run against synthetic data (a seller with 60 orders in the trailing 90 days and 6 cancellations, a seller with only 20 recent orders, and a seller with 60 orders that are all older than 90 days), this returns only the first seller, with cancellation_rate = 0.10; the other two are correctly dropped, one by the 50-order volume floor and one by the 90-day window.

• Step 2, is a week-over-week change significant: for two independent proportions $p_1 = x_1/n_1$ (this week) and $p_2 = x_2/n_2$ (last week), pool them into a single estimate under the null hypothesis that the true rate did not change:
  $\hat{p} = \frac{x_1 + x_2}{n_1 + n_2}$
  $SE = \sqrt{\hat{p}(1-\hat{p})\left(\frac{1}{n_1} + \frac{1}{n_2}\right)}$
  $z = \frac{p_1 - p_2}{SE}$
  In plain language, this test asks: if this seller's TRUE cancellation rate had not actually changed, how surprising would it be to see a gap this large purely from the random luck of which orders happened to land in each week's sample? That "how surprising" number is the p-value -- the smaller it is, the less plausible it is that pure chance alone produced the observed gap. The 1.96 cutoff comes from the standard normal distribution: under the null hypothesis (that the true rate did not change), $z$ is approximately normally distributed, and about 95% of that distribution's area falls between -1.96 and +1.96. So a $|z|$ beyond roughly 1.96 means the observed gap falls in the most extreme 5% of what pure chance alone would produce -- the conventional (if somewhat arbitrary) bar for calling a result "statistically significant", which is the same thing as saying it corresponds to a two-tailed p-value under 0.05.
• Why not just compare raw percentages: a seller going from 10% to 12% cancellations on 50 orders a week is well within normal sampling noise; the same 2-point move on 5,000 orders a week is very unlikely to be noise. The test naturally accounts for this because the standard error shrinks as $n$ grows.

Worked example

A seller has 200 orders and 20 cancellations this week ($p_1 = 0.10$), and 180 orders and 36 cancellations last week ($p_2 = 0.20$):
$\hat{p} = \frac{20+36}{200+180} = \frac{56}{380} \approx 0.1474$
$SE = \sqrt{0.1474 \times 0.8526 \times \left(\frac{1}{200}+\frac{1}{180}\right)} \approx 0.0364$
$z = \frac{0.10 - 0.20}{0.0364} \approx -2.75$
That corresponds to a two-tailed p-value of about 0.006, well under a typical 0.05 significance bar, so this seller's change is a genuine signal worth surfacing, not noise. By contrast, the same 10-point swing on a seller with only 50 orders each week ($x_1=5, x_2=10$) gives $\hat p \approx 0.15$, $SE \approx 0.071$, $z \approx -1.4$, which does not clear a 0.05 threshold and should not be surfaced as a real signal.

Trade-offs & pitfalls

• Multiple comparisons: running this test across every seller every week means many tests are being run simultaneously, and a 5% false-positive rate per test adds up to many false alarms across thousands of sellers. A stricter threshold, or a correction such as controlling the false discovery rate -- the fraction of all the sellers flagged as "significant" that are actually false alarms, once thousands of tests are running at once rather than just one -- is the honest answer once volume is high; don't present a bare $p<0.05$ per-seller as if it were the whole story at that scale.
• Independence assumption: the two-proportion test assumes each week's orders are independent draws, which breaks down if the same customers or the same handful of large orders dominate both weeks.
• Practical significance still matters: a statistically significant 0.5-point move on a huge seller may not be operationally meaningful even though the test says it's real; report both the p-value and the effect size.

Direct answer

Linear attribution splits each conversion's value equally across every touchpoint that falls inside a bounded conversion window before it: find the conversion window for each converting user, count the touches inside it, and divide the conversion's revenue by that count. At scale, the two things that determine whether this stays fast are the join key (partition and cluster by user_id so a user's touches and conversion live together) and whether the window filter is applied before or after the count, since counting first and filtering second silently attributes revenue to touches outside the intended window.

Structured elaboration

Approach. Three steps, expressed as common table expressions (CTEs), which are standard American National Standards Institute (ANSI) SQL and portable across engines:

1. conversions: pull every row where is_conversion = 1, giving each converting user's conversion timestamp and revenue.
2. window_touches: join every non-conversion touch back to that user's conversion, keeping only touches whose elapsed time before the conversion falls inside a fixed conversion window (a business-defined parameter, 30 days below).
3. touch_counts and the final aggregation: count touches per (user, conversion) pair, then for each surviving touch, credit revenue / touch_count to its channel, and sum across all conversions grouped by channel.

Performance at scale. Three concerns dominate once the touches table is large:

• Join and partition strategy. The join between touches and conversions is keyed on user_id; on a distributed warehouse (a columnar engine such as Snowflake, BigQuery, or Redshift), clustering or partitioning the physical table by user_id (or a date-bucketed user_id) keeps this join local to each partition instead of triggering a full shuffle across the cluster.
• Window functions over self-joins where the engine supports it. The self-join pattern above is correct and portable, but on very large tables a window-function form (COUNT(*) OVER (PARTITION BY user_id, conversion_id) computed directly on a pre-filtered, already-windowed row set) avoids materializing an intermediate join result twice, since the count and the later per-row division can share a single pass over the same partitioned data. This is a real optimization worth reaching for on a large warehouse table, described here as a design recommendation, not something separately benchmarked in this answer.
• Filter before count, always. The date-window predicate must be applied inside the join that produces window_touches, never as a HAVING or post-aggregation filter; applying it too late would count touches outside the window into touch_counts while still excluding their revenue from the numerator, silently understating every channel's credit. The edge-case check in the Worked example below verifies this ordering is correct, not just assumed.

Worked example

Schema and pinned synthetic data (four users: a 3-touch conversion, a 1-touch conversion as a sanity check, a 5-touch conversion to make the equal-split arithmetic non-trivial, and a 3-touch user where two touches fall outside a 30-day conversion window and must be excluded), executed with Python's stdlib sqlite3:

CREATE TABLE touches (
    user_id       TEXT NOT NULL,
    touch_id      INTEGER NOT NULL,
    channel       TEXT NOT NULL,
    occurred_at   TIMESTAMP NOT NULL,
    is_conversion INTEGER NOT NULL DEFAULT 0,  -- 1 marks the conversion row itself
    revenue       DECIMAL(10,2)                -- non-NULL only on the conversion row
);

INSERT INTO touches VALUES
 -- user_1: 3-touch conversion, revenue $90 (each touch worth $30)
 ('user_1',1,'paid-search','2024-04-20 09:00:00',0,NULL),
 ('user_1',2,'email','2024-04-30 09:00:00',0,NULL),
 ('user_1',3,'organic-search','2024-05-05 09:00:00',0,NULL),
 ('user_1',4,'conversion','2024-05-10 09:00:00',1,90.00),
 -- user_2: 1-touch conversion, sanity check, revenue $60
 ('user_2',1,'social','2024-05-09 09:00:00',0,NULL),
 ('user_2',2,'conversion','2024-05-10 09:00:00',1,60.00),
 -- user_3: 5-touch conversion, revenue $250 (each touch worth $50); paid-search at touch 1 and touch 5
 ('user_3',1,'paid-search','2024-04-15 09:00:00',0,NULL),
 ('user_3',2,'social','2024-04-20 09:00:00',0,NULL),
 ('user_3',3,'display','2024-04-25 09:00:00',0,NULL),
 ('user_3',4,'email','2024-04-30 09:00:00',0,NULL),
 ('user_3',5,'paid-search','2024-05-08 09:00:00',0,NULL),
 ('user_3',6,'conversion','2024-05-10 09:00:00',1,250.00),
 -- user_4: 3 raw touches, 2 outside the 30-day window (email, display), 1 inside (paid-search), revenue $120
 ('user_4',1,'email','2024-03-31 09:00:00',0,NULL),
 ('user_4',2,'display','2024-04-05 09:00:00',0,NULL),
 ('user_4',3,'paid-search','2024-04-25 09:00:00',0,NULL),
 ('user_4',4,'conversion','2024-05-10 09:00:00',1,120.00);

Linear attribution query (the date-difference expression below uses SQLite's julianday() so this exact query is directly runnable; on a production warehouse, swap only that expression for the engine's own day-difference function, DATEDIFF, date_diff, or interval subtraction, the surrounding ANSI SQL structure, CTEs, joins, and aggregation, is unchanged):

WITH conversions AS (
    SELECT user_id, touch_id AS conversion_touch_id, occurred_at AS conversion_at, revenue
    FROM touches
    WHERE is_conversion = 1
),
window_touches AS (
    SELECT t.user_id, c.conversion_touch_id, t.touch_id, t.channel, c.revenue
    FROM touches t
    JOIN conversions c
        ON c.user_id = t.user_id
       AND t.is_conversion = 0
       AND julianday(c.conversion_at) - julianday(t.occurred_at) BETWEEN 0 AND 30
),
touch_counts AS (
    SELECT user_id, conversion_touch_id, COUNT(*) AS n_touches
    FROM window_touches
    GROUP BY user_id, conversion_touch_id
)
SELECT wt.channel, ROUND(SUM(wt.revenue * 1.0 / tc.n_touches), 4) AS attributed_revenue
FROM window_touches wt
JOIN touch_counts tc ON tc.user_id = wt.user_id AND tc.conversion_touch_id = wt.conversion_touch_id
GROUP BY wt.channel
ORDER BY attributed_revenue DESC;

Output (actually executed with python3, sqlite3 stdlib module):

-- Linear attribution, per channel --
('paid-search', 250.0)
('social', 110.0)
('email', 80.0)
('display', 50.0)
('organic-search', 30.0)

sum(attributed_revenue) = 520.0
sum(conversion revenue) = 520.0

By hand: the $250 5-touch conversion splits into $50 per touch, and paid-search appears twice in that one path (touch 1 and touch 5), so it alone carries $100 of that conversion; added to $30 from the 3-touch conversion and $120 from the single in-window touch of the 30-day-filtered conversion, paid-search totals $250, matching the query exactly.

A second useful query: last-touch vs. linear, side by side, for the same conversions. Built by adding a ROW_NUMBER() OVER (PARTITION BY user_id, conversion_touch_id ORDER BY occurred_at DESC, touch_id DESC) ranking to the same window_touches CTE, taking rn = 1 as the last touch, and joining the two credit tables with a FULL OUTER JOIN on channel so a channel that only ever appears in one model still shows a 0 in the other rather than being silently dropped:

last_touch_rank AS (
    SELECT wt.*, ROW_NUMBER() OVER (
        PARTITION BY wt.user_id, wt.conversion_touch_id
        ORDER BY wt.occurred_at DESC, wt.touch_id DESC
    ) AS rn
    FROM window_touches wt
),
last_touch_credit AS (
    SELECT channel, SUM(revenue) AS last_touch_revenue
    FROM last_touch_rank WHERE rn = 1 GROUP BY channel
)
SELECT COALESCE(l.channel, lt.channel) AS channel,
       ROUND(COALESCE(l.linear_revenue, 0), 4)      AS linear_revenue,
       ROUND(COALESCE(lt.last_touch_revenue, 0), 4) AS last_touch_revenue
FROM linear_credit l
FULL OUTER JOIN last_touch_credit lt ON lt.channel = l.channel
ORDER BY channel;

Output (actually executed):

('display', 50.0, 0.0)
('email', 80.0, 0.0)
('organic-search', 30.0, 90.0)
('paid-search', 250.0, 370.0)
('social', 110.0, 60.0)

sum(linear_revenue) = 520.0
sum(last_touch_revenue) = 520.0
total conversion revenue = 520.0

Both columns independently sum to the full $520 of conversion revenue, since each model fully partitions every conversion on its own; they simply disagree about WHICH channel gets the credit. display and email never happen to be a last touch across these four conversions, so they read $0 under last-touch despite earning real linear credit, a concrete illustration of why the two models can tell very different stories about the same underlying data.

Complexity

• The core query is one join (touches to conversions, both filtered), one GROUP BY for counts, and one more aggregation: $O(T)$ where $T$ is the number of touch rows within the conversion window, assuming the join uses an index or a partition-local hash join on user_id rather than a full cross-table scan.
• The window-function form (ROW_NUMBER() OVER (...)) used for the last-touch comparison adds a sort within each (user_id, conversion_touch_id) partition; on a columnar warehouse this is typically implemented as a partitioned sort, $O(T \log k)$ where $k$ is the average touches per conversion, which is small (single digits to low tens) even when $T$ is enormous, so this stays cheap in practice.
• Space: the intermediate window_touches CTE materializes at most one row per (touch, conversion) pair inside the window, bounded by $T$, not by the full unfiltered touch table, provided the window predicate is pushed into the join as shown rather than applied afterward.

Edge cases

• Single-touch conversion (user_2 above): linear and last-touch degenerate to the same answer, verified directly and in isolation: [('social', 1, 60.0)], one touch, full $60 credited, which both models must agree on by construction.
• Conversion window filter must actually exclude stale touches, not just compile. user_4 has three raw touches but only one within 30 days of its conversion; verified directly: all raw touches for user_4: [(1, 'email', ...), (2, 'display', ...), (3, 'paid-search', ...)] versus touches surviving the 30-day window filter: [(3, 'paid-search')], confirming exactly two of three were correctly excluded.
• The same channel appearing more than once in one conversion path (user_3's two paid-search touches): each occurrence is still one of the five equally-weighted touches, so the channel's total credit for that conversion is $2 \times \frac{250}{5} = \$100$, not capped at a single share; this is a deliberate property of linear attribution, not a bug.
• A user with touches but no conversion never enters conversions at all, so none of their touches are ever counted or attributed, correctly excluding non-converting activity from revenue attribution entirely.
• NULL revenue only ever appears on non-conversion rows in this schema; the query never reads revenue from a non-conversion row, so a NULL there is inert by construction rather than something the query needs to guard against separately.

Trade-offs and pitfalls

• Linear attribution assumes every touchpoint contributed equally, which is rarely true. A touch two minutes before conversion and a touch three weeks before conversion get identical credit here; a time-decay or position-based model (more credit to first and last touch, as one common alternative) captures that intuition, linear does not, and that is a modeling choice to make explicitly, not an oversight to fix inside this query.
• Common mistake: filtering the conversion window after computing touch_counts instead of before. Doing the filter late would count out-of-window touches into the denominator while still excluding their revenue share from ever being attributed, quietly shrinking every in-window touch's credit for no principled reason; the CTE order above avoids this specifically by filtering inside window_touches, the very first step.
• Common mistake: assuming every conversion has at least one touch. If instrumentation gaps mean some conversions genuinely have zero touches in the window, that conversion's revenue is silently excluded from sum(attributed_revenue), which is why validating that the attributed total equals the true conversion-revenue total (done explicitly above) is not optional, it is the only way to catch this kind of silent data loss.
• FULL OUTER JOIN support is not universal. It rendered correctly in this environment's SQLite build, but some engines and older SQLite builds do not support it directly; the portable fallback is a UNION of two LEFT JOINs (one anchored on each side), which produces the identical result set and is worth knowing as the fallback pattern rather than assuming FULL OUTER JOIN is always available.

Direct answer

Compute each user's own historical mean and standard deviation of transaction amount using a window function, then flag a transaction when it exceeds mean + 3*stddev. The two judgment calls that separate a correct answer from a naive one: exclude the transaction being tested from its own baseline (otherwise a huge outlier drags its own mean and standard deviation up and can mask itself), and require a minimum history size before flagging anything, since one or two prior transactions produce a meaningless standard deviation.

Structured elaboration

• Baseline window: PARTITION BY user_id ORDER BY occurred_at RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING. The AND INTERVAL 1 DAY PRECEDING upper bound is what excludes the current transaction from its own baseline.
• Minimum-history guard: require COUNT(*) OVER (...) >= 5 (or whatever floor the business accepts) before evaluating the threshold at all. Below that, STDDEV_SAMP is either NULL (fewer than 2 rows) or wildly noisy.
• Threshold: 3 standard deviations is a common default (roughly the 99.7th percentile under a normal assumption), but transaction amounts are usually right-skewed, not normal, so this is a heuristic guardrail, not a calibrated p-value. State that assumption explicitly rather than presenting the 3-sigma cutoff as statistically rigorous.

Worked example

SELECT
  transaction_id, user_id, amount, occurred_at,
  baseline_mean, baseline_stddev, n_hist,
  (n_hist >= 5 AND baseline_stddev > 0
   AND amount > baseline_mean + 3 * baseline_stddev) AS is_anomalous
FROM (
  SELECT
    transaction_id, user_id, amount, occurred_at,
    AVG(amount) OVER w AS baseline_mean,
    STDDEV_SAMP(amount) OVER w AS baseline_stddev,
    COUNT(*) OVER w AS n_hist
  FROM transactions
  WINDOW w AS (
    PARTITION BY user_id ORDER BY occurred_at
    RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
  )
) t;

Run against a concrete history for one user (amounts $100, $105, $95, $102, $98, then $900):

The $900 transaction is compared against a baseline of mean $100.00 / standard deviation $3.81 built entirely from the five transactions BEFORE it, not including itself: $900 > 100 + 3(3.81) = 111.4$, so it is flagged. The first four rows are correctly never flagged even though some of them could, in isolation, look unusual, because n_hist < 5 blocks the rule from firing on a thin history.

Trade-offs & pitfalls

• Self-masking: if you instead compute the mean/stddev over a window that INCLUDES the current row, a genuine spike inflates its own baseline and can slip under the threshold, especially with a short history. This is the single most common mistake in a naive first draft of this query.
• Normality assumption: 3-sigma flags almost nothing on a heavy-tailed distribution and flags too much on a tight one; if the business needs a calibrated false-positive rate, a percentile-based threshold (e.g. "above this user's own 99th percentile") is more robust than a Gaussian-shaped rule.
• Cold-start users: the minimum-history guard means new or low-activity users are never protected by this check at all. Decide explicitly whether that gap is acceptable or needs a separate rule (e.g. compare against a global or cohort baseline until the user has enough history of their own).

Direct answer

Compute a rolling 90-day ARPU (average revenue per user) per region using a window function over daily aggregated purchases, being careful to count each user once per day even if they made multiple purchases that day, and to index/partition the underlying table so the rolling window doesn't force a full scan on every row.

Structured elaboration

• Avoiding double-counting same-day purchases: aggregate to one row per (user, day, region) first (summing that day's purchases), THEN apply the rolling window over days, rather than letting a naive per-transaction window silently weight active repeat-purchasers more heavily in the denominator than the numerator.
• The rolling window: SUM(daily_revenue) OVER (PARTITION BY region ORDER BY day ROWS BETWEEN 89 PRECEDING AND CURRENT ROW) / (rolling distinct active users over the same 90-day window). The distinct-user denominator over a rolling window is the more expensive half of this computation (a plain SUM over a frame is cheap; a rolling DISTINCT COUNT is not natively supported as a window aggregate in most engines and typically needs a separate approach -- in rough order of how often each is reached for in practice: a pre-aggregated daily distinct-user table combined with careful merge logic is usually the first thing to try; a self-join works for smaller data volumes but degrades quickly as the table grows; a full approximate-distinct sketch is worth its added complexity mainly once the table is too large for either of the other two to stay cheap).
• Indexing and partitioning for large datasets: partition or cluster the physical table by region and day so the engine can prune to the relevant date range and region without scanning unrelated partitions -- note this is a different sense of "partition" from the PARTITION BY region window-function clause used above: that clause tells the SQL engine how to group rows for computation, while this one is about how the table's data is physically laid out on disk before any query runs. A covering index on (region, day, user_id) supports both the daily aggregation step and the rolling-window step without needing to revisit the raw transaction table -- "covering" means the index already contains every column the query touches (region, day, user_id), so the engine can answer entirely from the index without jumping back to the base table's rows.

Worked example

Recall the definition of average revenue per user (ARPU) as total billed revenue over active subscriber count for a period: a single-month calculation computes that once for one fixed calendar month, while this version recomputes it for a 90-day window ENDING on every day, partitioned by region:

WITH daily AS (
  SELECT region, purchase_date AS day, user_id, SUM(amount) AS user_daily_amount
  FROM purchases
  GROUP BY region, purchase_date, user_id
)
SELECT region, day,
  SUM(user_daily_amount) OVER w AS rolling_90d_revenue,
  -- distinct active users over the rolling window needs a dedicated
  -- mechanism (approximate sketch or a maintained rolling-distinct table);
  -- shown here as a placeholder for that piece
  rolling_90d_distinct_users,
  ROUND(SUM(user_daily_amount) OVER w / NULLIF(rolling_90d_distinct_users, 0), 2) AS rolling_arpu
FROM daily
WINDOW w AS (PARTITION BY region ORDER BY day ROWS BETWEEN 89 PRECEDING AND CURRENT ROW);

The key correctness point demonstrated by the daily common table expression: a user who made three separate $50 purchases in one day contributes $150 to the numerator but must still only count as ONE active user that day in the denominator; grouping to (region, day, user_id) before the rolling window is what guarantees that.

Trade-offs & pitfalls

• The distinct-user denominator over a rolling window is the real engineering problem here, not the revenue numerator: a plain SUM over a frame is a cheap, well-supported window operation; a rolling 90-day DISTINCT COUNT is not, and reaching for an approximate-distinct sketch such as HyperLogLog (a compact, fixed-size probabilistic data structure that estimates the number of distinct values in a set within a small, well-characterized error, and that can be merged across time buckets without re-touching the original raw rows) is often the pragmatic answer once the table is large.
• Index/partition choice depends on query pattern: if most queries filter to a single region and a recent date range, partition by region first and cluster by day within it; if queries instead scan all regions for a fixed recent window, cluster primarily by day.
• A day with zero purchases in some region should still appear in the series with revenue 0, or a rolling average silently and incorrectly skips gaps; generate the full calendar range explicitly rather than relying only on the rows that happen to exist.

Direct answer

At the scale of roughly a billion entities and a hundred billion events a year, the design question shifts from "how do I write the query" to "how do I avoid ever computing the full thing from scratch": partition and cluster the storage to match the query pattern, prefer incremental refresh over full recompute, and choose pre-aggregation over raw window functions wherever the business tolerance for latency allows it.

Structured elaboration

• Storage and partitioning: partition by the natural time grain the metric refreshes on (commonly daily), and cluster within each partition by the entity key most queries filter or group by, so a query touches only the relevant slice of data rather than scanning the full history.
• Compute engine choice: a distributed, columnar engine (BigQuery, Spark, Snowflake, Redshift, or similar) is the right class of tool at this scale; the specific choice usually comes down to what the organization already operates, its cost model (on-demand per-byte-scanned vs. provisioned cluster time), and how well it supports incremental/merge operations, not a single universally-correct answer.
• Incremental refresh over full recompute: maintain the metric as a table that's updated only for the partitions affected by new (or corrected) data since the last run, rather than recomputing the entire history nightly; this is the same incremental-refresh principle used for any single running total, applied here at the level of an entire cohort/activity pipeline.
• Window functions vs. pre-aggregation trade-off: a window function computed over raw, unaggregated events at this scale is expensive because it has to sort and partition a massive row set; pre-aggregating to a coarser grain (for example, one row per entity per day) BEFORE applying any window logic dramatically shrinks the data the window function actually has to process, at the cost of losing sub-day granularity.
• Cost and monitoring: track bytes scanned (or compute-hours) per run as the primary cost signal, and alert on partition-level staleness (a partition that hasn't refreshed on schedule) as the primary correctness signal, since a stale partition silently serving old data is a much more common failure mode at this scale than an outright query error.

Worked example

A concrete before/after: at a scale of 100 billion events accumulated over a year, that averages 100,000,000,000 / 365, or roughly 274 million, new events per day. Recomputing a full 100-billion-event history every night to refresh a cohort or activity metric means every run's cost and duration scales with the ENTIRE accumulated history, growing worse every single day even if the actual NEW data volume per day stays constant at that ~274 million: by the end of year one, a full nightly recompute is reading 100,000,000,000 / 274,000,000, or roughly 365 times more data, than a run that only touches that day's new events -- and that ratio keeps climbing every additional day the full-recompute design stays in place. Restructuring so each day's run only reads and reprocesses that day's new (or corrected) partition (~274 million events), merges the result into an already-maintained summary table, and never re-touches settled historical partitions, means the run's cost and duration stay roughly constant at that ~274-million-event scale as the total history grows, instead of climbing toward the full 100 billion. This is the same incremental-merge-over-full-recompute principle scaled up from a single running total to an entire pipeline.

Trade-offs & pitfalls

• Pre-aggregating too early throws away information you can't get back: if the business later needs sub-day granularity (hourly retention, for example) and the design only ever stored daily pre-aggregates, that history is gone; decide the finest grain any FUTURE analysis might plausibly need before committing to a pre-aggregation level.
• "Incremental" designs are only as correct as their handling of corrections to old data: a correction landing against an old partition still requires replaying forward from the correction point rather than just patching the current total, exactly as with any incrementally-maintained running total, at greater cost here given the larger scale of what would need to be replayed.
• Cost monitoring needs to be a first-class part of the design, not an afterthought: at this data volume, an accidental full-table scan from a poorly-partitioned query can be enormously more expensive than the routine incremental job it was meant to support.