Approach (short): For a heavy-read dashboard, prefer pre-aggregation (materialized views or summary tables) over on-the-fly queries. Pre-aggregations reduce query latency and cost. Choose between a true materialized view (if your DB supports fast incremental/FAST refresh) or maintain a pre-aggregated summary table with an incremental refresh/CDC pipeline when the DB’s MV refresh is expensive or limited.Example: materialized-view-like summary table for monthly user metrics (new users, active users, retention). This example uses a summary table + incremental MERGE pattern (works on Snowflake/BigQuery/Redshift/Synapse with slight syntax changes). It assumes events table has event_time (timestamp), user_id, event_type, and users.created_at.SQL to create base summary table (first full load):sql
CREATE TABLE monthly_user_metrics AS
SELECT
DATE_TRUNC('month', event_time) AS month,
COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) AS new_users,
COUNT(DISTINCT CASE WHEN event_type IN ('page_view','login') THEN user_id END) AS active_users,
COUNT(DISTINCT user_id) AS total_distinct_users,
CURRENT_TIMESTAMP() AS last_updated
FROM events
WHERE event_time >= DATE_TRUNC('month', DATEADD(month, -24, CURRENT_DATE())) -- optional lookback
GROUP BY 1;
sql
-- 1) compute staging aggregates since last watermark
WITH watermark AS (SELECT last_processed_event_time FROM refresh_control WHERE name='monthly_user_metrics'),
staging AS (
SELECT
DATE_TRUNC('month', event_time) AS month,
COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) AS new_users_delta,
COUNT(DISTINCT CASE WHEN event_type IN ('page_view','login') THEN user_id END) AS active_users_delta,
COUNT(DISTINCT user_id) AS distinct_users_delta
FROM events
WHERE event_time > (SELECT last_processed_event_time FROM watermark)
GROUP BY 1
)
-- 2) merge into summary table
MERGE INTO monthly_user_metrics AS target
USING staging AS src
ON target.month = src.month
WHEN MATCHED THEN
UPDATE SET
new_users = target.new_users + src.new_users_delta,
active_users = target.active_users + src.active_users_delta,
total_distinct_users = target.total_distinct_users + src.distinct_users_delta,
last_updated = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
INSERT (month, new_users, active_users, total_distinct_users, last_updated)
VALUES (src.month, src.new_users_delta, src.active_users_delta, src.distinct_users_delta, CURRENT_TIMESTAMP());
-- 3) advance watermark (transactionally)
UPDATE refresh_control SET last_processed_event_time = (SELECT MAX(event_time) FROM events WHERE event_time > (SELECT last_processed_event_time FROM watermark)) WHERE name='monthly_user_metrics';
