Failure Modes and Production Edge Cases

When Reality Gets Messy: Materialized views work well in theory, but production systems encounter several common failure modes that you must design for. Staleness and Lag Explosions: If refresh jobs fall behind due to load spikes or infrastructure issues, materialized views can become hours out of date. Imagine your pipeline expects 1 million events per second but a viral marketing campaign pushes traffic to 3 million events per second. The refresh job saturates available compute and falls further behind each cycle. Dashboards showing stale data are dangerous for operational monitoring. Engineers might miss ongoing incidents because metrics show everything normal from 2 hours ago. You need clear Service Level Indicators (SLIs) for freshness lag: track "time from event ingestion to appearance in aggregate table" and alert when p99 exceeds thresholds like 10 minutes. Consistency Bugs: If a refresh job partially fails or retries incorrectly, you can get duplicate aggregates, missing rows, or mismatched counts. A batch job might process a time window twice due to an off by one bug in window boundaries, leading to exactly doubled counts for that window. Or a streaming job with at least once delivery might reapply updates without idempotence. If your code does UPDATE SET count = count + delta instead of INSERT ON CONFLICT UPDATE with unique keys, replayed messages cause over counting. Late Arriving and Corrected Data: In real systems, events often arrive minutes or hours late due to mobile device connectivity or batch upload delays. If your materialized views only look at the current time window, late events never appear in historical aggregates. Your daily active user count for last Tuesday will be permanently understated. Handling this requires lookback windows in refresh logic. A common pattern is to recompute the past 7 days of aggregates on each run, allowing up to 7 days of late arrival. Older data becomes immutable. The trade off is higher compute cost: you reprocess some data repeatedly. GDPR deletions or data correction backfills add more complexity. Deleting a user means finding and updating all affected aggregate partitions. For systems at Uber or Meta scale with years of historical aggregates across hundreds of tables, this can require sophisticated lineage tracking and coordinated multi table updates. Schema Evolution: Adding a new dimension like user segment or pricing tier can invalidate past aggregates. If you need historical data with the new dimension, you must backfill months or years. For large datasets, full backfills can take days of cluster time and cost hundreds of thousands of dollars in compute. Mitigations include planning aggregation schemas carefully upfront, versioning aggregation tables to keep old and new side by side, and using semi structured columns (JSON, Variant types) for flexible dimensions that might change.