Caldera Health: A 9% Cache Hit Rate at 8 A.M.
Caldera Health serves 2,200 claims analysts with 40 MicroStrategy dossiers across six sources, including Snowflake and an on-premises claims warehouse. The result-cache hit rate is 9%, Snowflake spend is 210,000 dollars per month, and p95 dashboard load time at the 8-10 a.m. peak is 18 seconds. Three operational dossiers need data no older than 10 minutes. Thirty analytical dossiers can tolerate four hours of staleness. Seven dossiers use row-level security. Most dossiers prompt for a free-text member ID and a date range. A 15-minute ETL job purges the cache, Intelligent Cubes are rebuilt nightly, and five dossiers use live connect. Users complain about both slowness and stale data, and Finance wants the cloud bill reduced without breaking the operational SLA.
1. Diagnose the 9% hit rate. Which cache-key and design factors are most likely responsible?
Read the worked answer
The cache key includes the report definition, prompt answers, the user or security context, VLDB settings, connection, and cache version. A free-text member ID creates near-unique prompt answers: 2,200 analysts choosing different members can produce thousands of entries that never recur. Free-text date ranges compound that. If row-level security is applied per user and the cached result is user-specific, sharing collapses further.
The 15-minute purge is a second cause. Even a reusable entry is discarded before it can be hit, and the purge is tied to ETL completion rather than to changed partitions. Nightly cubes are too stale for the three operational dossiers but unnecessarily fresh for the thirty analytical ones. Live connect bypasses result cache for five dossiers. If those five are among the heaviest, they both raise cost and lower the measured hit rate.
Finally, non-deterministic filters such as current date or current timestamp can make each execution look new. The symptom is not one bad setting; it is a mix of high-cardinality prompts, aggressive invalidation, and mismatched freshness tiers. Measure hit rate by dossier and prompt cardinality before changing anything.
2. Design a tiered caching and freshness architecture. Be specific about what is cached, for how long, and how security is handled.
Read the worked answer
Tier 0, operational: the three dossiers get a narrow aggregate table or micro-cube refreshed every 5-10 minutes, with live connect only where the source can meet the SLA. Cache for 5 minutes and pre-warm before 8 a.m. Tier 1, analytical: the thirty dossiers use hourly or four-hour Intelligent Cubes, partitioned by date and region, with dynamic sourcing to the warehouse for detail. Pre-warm the common partitions before peak. Tier 2, ad hoc: allow result cache but replace free-text member ID with a governed selector or search prompt, and provide default date ranges.
Replace the fixed 15-minute full purge with event-based invalidation tied to the ETL partition. Do not invalidate the whole cube when one day changes. For row-level security, include the security dimension in the cube or apply security at query time, then test that no user can see another region. Do not assume a user-specific cached result can be shared.
Use multi-pass SQL or separate fact containers for unrelated facts. Set caching at the project and dossier level deliberately, and document the freshness SLA per dossier. Measure hit rate, p95, Snowflake credits, and freshness lag by tier.
3. How would you prove the redesign works and prevent the cache from becoming a security or cost problem?
Read the worked answer
Run a before-and-after scorecard by dossier: hit rate, prompt cardinality, p95 load time, Snowflake credits, cache entry count, and freshness lag against the stated SLA. Test at the 8-10 a.m. peak, not only off-peak. For the three operational dossiers, verify that the 10-minute freshness requirement is met during ETL and after a failed job. For the analytical tier, verify that the four-hour tolerance is real and that users understand it.
Security testing must include a row-level security user from each region attempting to reach another region through prompts, drill, export, and cube access. Test cached results after a security-group change. Do not cache PHI in a broadly accessible cube; aggregate or mask it, and log access.
Cost control needs ownership: a monthly review of the top ten dossiers by compute, a cap or alert on pre-warm jobs, and a rule that new live-connect dossiers require an SLA justification. Keep cache invalidation event-driven and monitored. If a dossier cannot meet its SLA without full-table scans, fix the model or the aggregate table rather than extending cache duration indefinitely.