Analytical Query Performance and Optimization Questions

The predicate WHERE date = CAST(ts AS DATE) wraps the partition column's comparison in a per-row computation, so the planner cannot resolve which partitions could possibly match without first evaluating the cast for every row; wrapping a partition (or indexed) column in a function generally defeats the planner's ability to statically determine a bounded range of partitions or values to read, which is exactly what partition pruning depends on.

Why this specific form breaks pruning

Partition pruning works by comparing the query's predicate against each partition's known bounds before reading any data. That comparison has to be a simple, sargable form (a bare column compared to a constant or a range) so the planner can evaluate it against partition metadata alone. Once the column is inside a function or cast, the planner would have to apply that function to every row in every partition to know the answer, which defeats the entire point of pruning: it has no way to know in advance which partitions the transformed value could fall into.

The rewrite

The fix is to move the transformation to the constant side of the comparison instead of the column side, so the column itself appears bare:

-- fixture: 90 days of partitions, 1,000 rows/day
CREATE TABLE events AS
  SELECT (DATE '2024-01-01' + INTERVAL (i % 90) DAY) AS event_date, i AS id
  FROM range(90000) t(i);

-- unsargable: function wraps the column being compared
SELECT count(*) FROM events WHERE date_trunc('month', event_date) = DATE '2024-01-01';

-- sargable: bare column compared to a computed range
SELECT count(*) FROM events
WHERE event_date >= DATE '2024-01-01' AND event_date < DATE '2024-02-01';

Executed against a 90,000-row table spanning 90 daily partitions, both forms returned an identical row count (31,000), confirming the rewrite preserves correctness; the difference is entirely in what the planner can statically reason about, not in what the query means.

Other common patterns that silently disable pruning

• Implicit type casts: comparing a DATE partition column to a string literal in a way that forces an implicit cast on the column side rather than the literal side (engine-dependent, but a frequent source of surprise).
• Applying a function directly to the partition column, such as UPPER(country) = 'US' on a partition key, or date_trunc/EXTRACT on a date partition column, as above.
• OR-ing a partition-column predicate with a non-partition-column predicate, which forces the planner to consider partitions it could otherwise exclude, since either side of the OR could be true.

Trade-offs and pitfalls

Modern optimizers vary in how much of this they rewrite automatically; some engines will normalize a common date_trunc-to-range comparison into a sargable form on their own, which can mask the problem in development on a small table and only surface it in production on a partitioned one, or on a different engine that does not do the same rewrite. Do not rely on the optimizer catching this for you: write the range form directly, especially in code that has to run correctly and efficiently across more than one query engine.

Computing customer lifetime value from raw transaction-level data recomputes the true, fully accurate number every time by summing every transaction directly, at the cost of scanning and joining potentially the entire transaction history for every customer on every report run; computing it from pre-aggregated daily summaries reads a much smaller, already-summed table, at the cost of only being as accurate and current as the last summary refresh, and of losing the ability to drill into individual transactions from the same query.

Trade-offs

• Latency: pre-aggregated summaries are dramatically faster for a report covering long time horizons or many customers, since the expensive summation work is already done; raw data is slower in direct proportion to the transaction history's size.
• Accuracy: raw data is always exactly correct as of the moment the query runs; pre-aggregated summaries are only as accurate as their last refresh, and any bug in the aggregation logic silently propagates to every report that reads from it rather than being caught by an ad-hoc raw query that computes independently each time.
• Cost: raw-data computation repeats the same expensive scan-and-sum work on every report run, even if the underlying data has not changed since the last run; a pre-aggregated summary pays that cost once per refresh cycle regardless of how many times the report is subsequently viewed.
• Interactivity: pre-aggregated summaries support fast, interactive drill-downs and filter changes because each interaction is cheap; raw-data computation makes every filter change pay the full recomputation cost again.

Recommendation

For an interactive BI dashboard where users are actively filtering, slicing, and comparing customer-lifetime-value figures in real time, pre-aggregated daily summaries are the right choice: the interactivity and cost benefits dominate, and a daily refresh cadence is normally granular enough that the accuracy trade-off is invisible to the audience the dashboard serves (nobody expects a lifetime-value dashboard to reflect a transaction from the last five minutes). For a monthly finance report, where accuracy and auditability matter more than interactive speed and the report is generated once rather than viewed repeatedly, computing directly from raw transaction-level data is more defensible, since it produces a number finance can trace transaction-by-transaction if questioned, without a summarization layer's logic sitting between the raw ledger and the reported figure.

Pitfalls

A common failure mode is using pre-aggregated summaries for a monthly finance report anyway (because the dashboard version already exists and is convenient) without verifying that the summarization logic matches finance's exact definition of lifetime value, refund handling, and currency conversion, and only discovering the discrepancy when the finance number and the dashboard number disagree publicly. Keep the raw-data path available and periodically reconciled against the summary path specifically because these two use cases have genuinely different correctness requirements.

A warehouse-level result cache, BigQuery's automatic result cache is the canonical example, stores the exact output of a previously executed query, keyed by the query's text (and, in most implementations, the state of the underlying tables at execution time), and serves that stored result instead of re-running the query the next time an identical query is issued, as long as none of the tables it read have changed since the cached result was produced.

How it interacts with table updates

The cache is invalidated per-query the moment any table the original query touched changes, and it separately carries its own upper-bound time-to-live independent of table changes: BigQuery's automatic result cache, for example, holds a cached result for roughly 24 hours and also invalidates it immediately if any referenced table changes before that window closes, whichever happens first. A new insert, update, or delete to any dependency invalidates every cached result that depended on that table, even a result that was otherwise perfectly fine, and even a result over tables that never change still eventually falls out of cache once the TTL elapses. This means result-cache freshness is exact (you will never see a result that is definitely stale relative to the table's current state) but its hit rate on a table that updates frequently, even in ways unrelated to what a specific dashboard displays, can be much lower than expected.

Designing to benefit from it

• Keep dashboard queries byte-for-byte identical across users and page loads where possible (same column order, same literal formatting, same filter representation), since most result-cache implementations key on the query text, not its semantic meaning, so two queries that return the same rows but are phrased differently will not share a cache entry.
• Separate frequently-updated tables from the tables a dashboard's expensive aggregation actually depends on, where the data model allows it, so that an unrelated update elsewhere in the warehouse does not needlessly invalidate a cached dashboard result.
• Combine with a materialized view or pre-aggregated table for the truly high-traffic dashboards, so the result cache is caching a cheap read of an already-small pre-aggregated table rather than a full scan-and-aggregate, which keeps even a cache miss relatively cheap.

Trade-offs and pitfalls

Relying on the result cache alone for freshness guarantees is fragile: it silently serves a fresh answer when nothing changed and silently falls back to a full, potentially expensive recomputation the instant anything changes, with no visible degradation warning to whoever is watching dashboard latency. Do not treat the cache hit rate as something you can directly control; design the query patterns and table update cadence around it instead, and treat it as a free accelerant on top of a materialized-view strategy, not a substitute for one.

Given events(id, user_id, amount, event_time) and a materialized daily_user_totals(date, user_id, total_amount), the incremental update needs to fold only the dates present in staging_events into the existing totals, adding new activity to matching (date, user_id) rows and inserting fresh rows for combinations that do not exist yet, all without holding a lock on the whole target table for longer than necessary.

Approach

1. Aggregate staging_events down to one row per (date, user_id) first, so the merge step only ever has to reconcile one summarized delta row per key, not many raw event rows.
2. Upsert that delta into daily_user_totals: insert a new row where the key does not exist, and add the delta's amount onto the existing total where it does.
3. For deletes (a correction that removes a previously-counted event), apply the negative of that row's amount as a delta in the same way, rather than trying to recompute the whole day from scratch.

Code (executed and verified)

-- fixture: existing totals plus a new staging batch
CREATE TABLE daily_user_totals (date DATE, user_id INT, total_amount DECIMAL(12,2), PRIMARY KEY(date, user_id));
INSERT INTO daily_user_totals VALUES (DATE '2024-01-01', 1, 100.00), (DATE '2024-01-01', 2, 50.00);

CREATE TABLE staging_events (id INT, user_id INT, amount DECIMAL(12,2), event_time TIMESTAMP);
INSERT INTO staging_events VALUES
  (100, 1, 25.00, '2024-01-01 10:00:00'),
  (101, 3, 40.00, '2024-01-01 11:00:00'),
  (102, 1, 5.00,  '2024-01-01 12:00:00');

-- 1. summarize new/changed activity to one row per (date, user_id)
CREATE TABLE staging_daily AS
SELECT CAST(event_time AS DATE) AS date, user_id, sum(amount) AS delta_amount
FROM staging_events
GROUP BY 1, 2;

-- 2. upsert the delta into the materialized target
INSERT INTO daily_user_totals (date, user_id, total_amount)
SELECT date, user_id, delta_amount FROM staging_daily
ON CONFLICT (date, user_id)
DO UPDATE SET total_amount = daily_user_totals.total_amount + EXCLUDED.total_amount;

Executed against a target table pre-seeded with (2024-01-01, user 1, 100.00) and (2024-01-01, user 2, 50.00), and a staging batch containing two new events for user 1 (25.00 and 5.00) and one new event for user 3 (40.00): the result was user 1 = 130.00 (100 + 25 + 5, correctly accumulated), user 2 = 50.00 (untouched, as expected since no new activity existed for them), and user 3 = 40.00 (a new row inserted). All three outcomes matched the expected arithmetic exactly.

Handling deletes and idempotency

For a genuine delete of a previously-processed event, apply it as a negative delta through the same staging_daily aggregation path (summing a negative amount for a deleted row) rather than a separate code path, so the merge logic only has one shape to reason about. Concretely, using the fixture above: if event id 100 (user 1, amount = 25.00) later turns out to be a duplicate that needs to be removed, a later staging batch carries a compensating row, (user_id=1, amount=-25.00, event_time='2024-01-01 13:00:00'), rather than deleting anything from staging_events. That row flows through the same step-1 GROUP BY and sum(amount), contributing -25.00 to staging_daily for (2024-01-01, user 1), and the step-2 upsert applies it exactly like any other delta: total_amount = daily_user_totals.total_amount + EXCLUDED.total_amount becomes 130.00 + (-25.00) = 105.00, which is exactly the total user 1 would have had if the erroneous 25.00 event had never been staged (the 100.00 seed plus the remaining 5.00 event). The negative amount is not produced by a special delete code path; it is simply the deleted row's own original amount, sign-flipped, staged like any other event. For idempotency (the same staging batch being replayed, for example after a job retry), the delta must be computed from events not yet applied, not from all events in the staging table unconditionally; track a processed-flag or a watermark on staging_events and only aggregate unprocessed rows, or the same delta will be double-applied on a retry.

Complexity and edge cases

The aggregation step is a single pass over the staging batch, and the upsert is bounded by the number of distinct (date, user_id) pairs touched, not the full target table, so cost scales with the size of the incoming batch rather than the table's total history. Edge cases worth naming explicitly: a staging_events batch that spans more than one date needs the GROUP BY to include date (as written above) so events do not get folded into the wrong day; and a batch containing both an insert and a same-key correction in the same run must net out correctly before the upsert, which the sum(amount) in step 1 already handles since it aggregates all of a key's staged rows together before touching the target.

Trade-offs and pitfalls

A blind INSERT ... ON CONFLICT without first deduplicating and aggregating the staging batch (skipping step 1) would apply each raw event row as its own tiny update, which is both slower (many small writes instead of one aggregated write per key) and more exposed to a partial-batch retry double-applying individual rows rather than one clean delta per key.

Design a refresh strategy in three layers: how each refresh mode actually keeps data current, how you avoid two refreshes stepping on each other, and how you recover cleanly when something arrives late or a refresh fails outright.

Comparing refresh modes

• Full refresh recomputes the entire materialized view from scratch on every run. It is the simplest to reason about and always correct, but its cost scales with the full dataset size regardless of how little actually changed, which quickly becomes too slow or expensive for an hourly cadence over a large fact table.
• Fast (log-based) refresh applies only the changes captured since the last refresh (a change log the warehouse or a change-data-capture (CDC) mechanism maintains), and can be much cheaper, but depends on that change log being complete and correctly ordered; a gap in the log produces a silently wrong result with no error raised.
• Incremental (watermark/CDC) refresh is the general form of fast refresh you build yourself: track a high-watermark (a timestamp or monotonically increasing id) of what has already been processed, and each run only reads and applies rows past that watermark. It gives you explicit control over correctness at the cost of building and maintaining the watermark logic yourself.

Incremental refresh, worked through

-- 1. find the current watermark already reflected in the view
SELECT last_watermark FROM refresh_state WHERE view_name = 'hourly_fact_summary';

-- 2. pull only rows newer than the watermark
CREATE TEMP TABLE delta AS
SELECT * FROM source_fact WHERE updated_at > (SELECT last_watermark FROM refresh_state WHERE view_name = 'hourly_fact_summary');

-- 3. merge the delta into the materialized aggregate (upsert, not blind insert)
INSERT INTO hourly_fact_summary (dim_key, hour, metric)
SELECT dim_key, date_trunc('hour', event_time), sum(amount) FROM delta GROUP BY 1,2
ON CONFLICT (dim_key, hour) DO UPDATE SET metric = hourly_fact_summary.metric + EXCLUDED.metric;

-- 4. advance the watermark only after the merge commits successfully
UPDATE refresh_state SET last_watermark = (SELECT max(updated_at) FROM delta) WHERE view_name = 'hourly_fact_summary';

Concurrency control against double-refresh

Take an advisory lock (or an equivalent "refresh in progress" row with a status flag) keyed on the view name before step 2, and release it only after the watermark advances in step 4. A second refresh attempt that starts while the lock is held should skip its run entirely rather than proceed, since running two incremental refreshes concurrently against the same watermark risks double-applying the same delta.

Fallback and late data

If an incremental run fails partway (for example, the merge succeeds but the watermark update does not), the lock/status row should record that as a failed run so the next attempt retries from the last confirmed watermark rather than assuming success; a periodic full refresh (nightly, say) as a fallback catches any incremental drift that accumulates from edge cases the incremental logic did not anticipate, including data arriving up to 48 hours late, which a pure watermark-based approach would otherwise miss if a row's updated_at value is in the past by the time it lands.

Trade-offs and pitfalls

Incremental refresh trades simplicity for efficiency, and every piece of that complexity is a place correctness can quietly break: a delta query that misses late-arriving rows, a lock that is not actually held atomically, or a watermark advanced before the merge is truly durable. Test the failure paths explicitly (what happens if the job dies between steps 3 and 4) rather than only the happy path, since an incremental pipeline that has never been tested against a mid-run failure will eventually hit one in production.