Analytical Query Performance and Optimization Questions

An efficient partition-aware query for a common analytical pattern like daily active users needs two things working together: a predicate that lets the engine prune to only the relevant partitions, and a query shape that computes the answer in a single pass rather than repeated full scans.

Approach

Accept the target date as a parameter, filter directly on the partition column with a bare comparison (never wrapped in a function, which would defeat the engine's ability to push the predicate down and prune non-matching partitions), and aggregate in one pass.

Code (executed and verified)

-- fixture: 1,000 users, 5 days
CREATE TABLE events (user_id INT, event_time TIMESTAMP, event_type VARCHAR);
INSERT INTO events
  SELECT (i % 1000), TIMESTAMP '2024-01-01 00:00:00' + INTERVAL (i % 5) DAY + INTERVAL (i % 86400) SECOND, 'click'
  FROM range(200000) t(i);

SELECT CAST(event_time AS DATE) AS d, count(DISTINCT user_id) AS dau
FROM events
WHERE CAST(event_time AS DATE) = DATE '2024-01-03'
GROUP BY 1;

Executed against a 200,000-row synthetic events table spanning 5 days and 1,000 distinct users, this returned exactly 200 as the daily active user count (DAU) for 2024-01-03. A brute-force cross-check using an explicit timestamp range (event_time >= TIMESTAMP '2024-01-03' AND event_time < TIMESTAMP '2024-01-04') instead of casting the column returned the identical value, 200, confirming the result is correct and not an artifact of the specific predicate form used.

Note that in a genuinely partitioned production table (partitioned on a DATE column directly rather than derived from a TIMESTAMP at query time), the equivalent, fully sargable form is a direct comparison against the partition column itself, or an explicit half-open range on it, rather than casting a timestamp column inside the predicate:

-- if the table is genuinely partitioned on a DATE column already:
SELECT event_date AS d, count(DISTINCT user_id) AS dau
FROM events_partitioned_by_date
WHERE event_date = DATE '2024-01-03'
GROUP BY 1;

Supporting incremental computation

This same query shape, aggregating one partition (one day) at a time, is what makes DAU a natural fit for incremental or materialized computation: because count(DISTINCT user_id) per day is a discrete, per-partition unit of work, a materialized daily_active_users(date, dau) table can be populated one day at a time as each new day's partition closes, without ever re-scanning prior days, rather than a query re-deriving all history from scratch on every run.

Complexity and edge cases

Cost scales with the size of the single day's partition being scanned, not the table's full history, provided the predicate is genuinely sargable against the partitioning scheme. The count(DISTINCT ...) itself requires holding enough state to deduplicate user ids within the partition; for a partition small enough to filter to a single day this is rarely a concern, but the same pattern at a coarser grain (a full month in one query) can become the correct target for an approximate technique like HyperLogLog (an algorithm that estimates a distinct count using a small, fixed amount of memory instead of tracking every unique value seen, trading a small and bounded error for a large reduction in memory and shuffle cost) if the distinct-count state becomes the bottleneck.

Trade-offs and pitfalls

The most common mistake in this pattern is wrapping the partition or time column in a function for convenience (truncating a timestamp, extracting a date part) without checking that the specific engine can still push that predicate down to prune partitions; verify with EXPLAIN on the actual engine and table, since the safe, portable form is always a bare comparison directly on the column the table is physically partitioned by.

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.

An extract (or import) mode copies the data a BI tool needs into the tool's own storage at a point in time, so every subsequent query against it reads that local copy; a live connection sends every query straight through to the source warehouse in real time, with no local copy at all.

This distinction is what Tableau's Extract versus Live connection, Power BI's Import versus DirectQuery, and Looker's Persistent Derived Tables versus direct SQL exploration are all naming, three different products, the same underlying choice.

Trade-offs

• Latency: extracts are typically faster per-query, since the BI tool's storage engine is optimized for the exact query patterns its visualizations issue, while a live connection's latency is whatever the warehouse takes to run that specific query, every time.
• Freshness: a live connection is always as fresh as the warehouse itself; an extract is only as fresh as its last refresh, and users can be looking at data that is hours old without an obvious signal unless the tool surfaces the extract's last-refresh time clearly.
• Concurrency: an extract insulates the warehouse from query load entirely, since every viewer hits the BI tool's own storage, not the warehouse, which matters a great deal at high concurrency; a live connection sends every viewer's query straight to the warehouse, so concurrent viewers directly translate into concurrent warehouse load.
• Network dependency: a live connection needs a reliable, low-latency path to the warehouse for every single interaction (every filter change is a new round trip); an extract, once loaded, works even if that connection later degrades.

Deciding for a ~500 million row dataset feeding 200 daily dashboards

At that scale and concurrency, a pure live connection risks both slow interactive filtering (every click re-queries 500 million rows) and meaningful warehouse load from 200 dashboards' worth of concurrent users. A pure extract of the full 500 million rows may be too large or slow to refresh often enough for freshness needs. A practical hybrid: extract a pre-aggregated or filtered subset sized for interactive speed (the grain most dashboards actually need), and fall back to a live connection only for the small number of dashboards genuinely requiring row-level, real-time detail that an extract's aggregation would lose.

Trade-offs and pitfalls

The hybrid approach means maintaining two operational paths (extract refresh schedules and reliability, plus live-connection performance and warehouse load), which is more to own than either pure approach alone. It is worth it specifically because "200 dashboards" and "500 million rows" independently push toward extracts (for concurrency protection) and away from a single extract (for freshness and manageable extract size), so a one-size-fits-all answer under-serves one requirement or the other; segment dashboards by their actual freshness need rather than defaulting every one of them to the same mode.

The core move is to change the query patterns and physical data layout so that ad-hoc queries scan less data by default, then back that up with policy so analysts have visibility into cost and a reason to keep scans small, since a purely technical fix with no feedback loop tends to regress as new analysts join and old habits creep back in.

Technical strategies

• Partition and cluster tables on the columns analysts actually filter by (typically a date column, sometimes a high-traffic entity id), so a query that filters to "last 7 days" scans days, not the whole table's history.
• Materialize common aggregates: if the same rollup (revenue by day and category, for example) is recomputed by many ad-hoc queries, pre-computing it once and pointing analysts at the smaller table avoids repeated full scans of raw data.
• Encourage or default to sampling for exploratory work: a table sample (TABLESAMPLE or an engine equivalent) is often good enough for a first-pass exploration and can be swapped for the full table once the query is validated.
• Add a dry-run cost estimate to the query workflow, so an analyst sees "this will scan 4TB / cost roughly $20" before running it, which turns an invisible cost into a visible one at the moment it matters most.

Policy strategies

• Per-user or per-team monthly quotas with a soft warning before a hard block, so runaway cost is caught early without blocking legitimate work outright.
• A lightweight cost dashboard visible to the team, showing who is driving spend and on what, which is often enough on its own to change behavior once people can see the numbers.
• Brief, recurring education on the cheap technical levers above (partition filters, sampling, using the pre-aggregated tables), aimed specifically at new analysts, since the most expensive queries are usually written by people who do not yet know the pre-aggregated tables exist.

Trade-offs and pitfalls

Quotas and hard limits protect the budget but can genuinely block legitimate, one-off deep-dive analysis if set too aggressively; pair any hard limit with an easy escalation path (a temporary quota bump on request) rather than a wall with no way through. The technical levers (partitioning, pre-aggregation) require someone to actually build and maintain them, and a materialized aggregate that goes stale or gets forgotten becomes a trap: analysts trust a number that is quietly no longer being refreshed. Treat the pre-aggregated tables as a product with an owner, not a one-time optimization.

Sub-second interactive filtering over a billions-of-rows fact table is achievable only by moving most of the query cost off the hot path, so the design question is really which levers you combine and in what order you try them for an existing slow dashboard, not any single silver-bullet technique.

The available levers

• Materialized views or rollups at multiple granularities: pre-compute the aggregations most filters actually need (daily, weekly, by top dimension combinations) so an interactive filter change reads a small pre-aggregated table instead of scanning the fact table fresh each time.
• An online-analytical-processing (OLAP) cube pre-aggregation layer: a more structured, exhaustive version of the same idea, pre-computing aggregates across many dimension combinations at once so a wide range of ad-hoc filter combinations can be served without a bespoke rollup for each one, at the cost of higher storage and refresh complexity than a handful of targeted rollups.
• Result or dashboard-layer caching: for the specific filter combinations users repeat often (a default view, a popular segment), caching avoids recomputation entirely for the second and later requests.
• Approximate aggregation for the heaviest metrics: for the small number of metrics that remain expensive even after pre-aggregation (a high-cardinality distinct count, for example), an approximate technique trades a small, bounded error for a large cost reduction.

Trade-offs of combining them

More pre-aggregation and more caching both trade storage and refresh complexity for query speed; the OLAP-cube approach in particular can multiply storage substantially if it pre-computes many dimension combinations that turn out to be rarely queried, so it is worth auditing actual filter usage before committing to a full cube versus a handful of targeted rollups matching the filters people actually use. Caching adds staleness risk on top of whatever staleness the underlying rollup already has. Approximate aggregation is the narrowest tool, appropriate only for the specific metrics where exactness is not actually required by the business.

Prioritizing for an existing slow dashboard

Start by checking whether the current queries hit the fact table directly with no pre-aggregation at all; if so, a small number of targeted rollups matching the dashboard's actual filter patterns is almost always the highest-leverage first step, cheaper to build and reason about than a full cube. Add caching next for the specific filter combinations traffic data shows are repeated most. Reach for a full OLAP cube or approximate techniques only once targeted rollups and caching are in place and specific remaining metrics are still too slow, since both add meaningfully more operational complexity than the first two levers.

Pitfalls

A design that pre-aggregates everything up front, before knowing actual usage patterns, routinely wastes storage and refresh budget on combinations nobody queries while still missing the specific pattern a real user actually needs; instrument the dashboard's real filter usage first, and let that data drive which rollups and cache entries are worth building.