Caching
The result and metadata caching layer that makes repeated or near-identical queries return instantly instead of re-executing the full plan every time.
High-Level Design
Caching is why the tenth person to open the same dashboard doesn't wait as long as the first.
💼 Business Context
- Most dashboard traffic is many people asking the same or nearly the same question — caching turns that traffic pattern into a cost and latency win rather than repeated full-cost execution
- Protects the lakehouse from being overloaded by popular-dashboard traffic during business hours
- Owned by Analytics Engineering / Platform Engineering
🔌 Technical Overview
Query results are cached in Azure Cache for Redis, keyed on a normalized representation of the compiled semantic-layer query plus its parameters, with a TTL tuned per mart to the underlying dbt refresh schedule so a cache entry never outlives the data it was computed from. Rollups & Aggregations' batch jobs actively invalidate the relevant cache keys on completion — packaged as part of the same .NET Core scheduler that orchestrates dbt runs — rather than relying purely on TTL expiry, keeping the cache from serving stale results after a fresh batch lands.
Cache Layers
💾 Cache Key Structure
cache:query:{metric_hash}:{dimension_filter_hash}:{time_grain}
TTL: aligned to marts.order_fact's dbt refresh schedule (hourly)
Invalidated on: rollups_aggregations job completion
🔗 Integration Points
- Azure Cache for Redis — the caching implementation
- Rollups & Aggregations — actively invalidates cache entries on batch completion
- Query Optimizer — checks the cache before planning a fresh execution
- AI Copilot — benefits from cache hits on the common questions it fields repeatedly
🧰 Services Consumed
- Owning microservice —
Cxos.Intelligence.Api(see the Full Application Service Map) - Database — Azure Database for PostgreSQL (semantic layer) + Azure Cache for Redis (query cache)
⚠️ Non-Functional Considerations
- Scale: cache sizing tracks distinct-query cardinality among popular dashboards, not total data volume
- Latency: a cache hit returns in single-digit milliseconds versus hundreds of milliseconds to seconds for a fresh execution
- Reliability: active invalidation on batch completion, not TTL alone, is what prevents a stale-cache correctness bug after a fix lands upstream
- Security/Privacy: cache entries are scoped per caller entitlement context, not shared blindly across users with different access levels
🎯 Enterprise Example
During a Monday morning traffic spike, 200 people open the same executive revenue dashboard within 10 minutes. Only the first query executes a full plan against the lakehouse; the remaining 199 are served from cache in milliseconds, keeping the underlying query engine's load flat.