Cloud & Automation
Pre-Rendered Reports vs Live Queries
Choose pre-rendered, live, or hybrid report execution using freshness, repetition, variability, cost and security constraints.
On this page
Short answer: pre-render a report when the same result is requested repeatedly and the data changes on a known schedule; run it live when the filters vary too much to reuse results or users need data newer than the last load. Most report engines end up hybrid: serve a stored result while it matches the current data version, query live on a miss. Decide per report class, not once for the whole engine.
Pre-rendered
Pre-rendered results fit repeated known reports, expensive historical queries, and predictable date ranges. They move work away from request time and provide stable latency, but require storage, freshness metadata, invalidation, secure tenant separation, and lifecycle cleanup. A pre-rendered file must still be authorized at download.
Live query
Live execution fits fresh data, highly variable filters, low-latency query engines, and interactive exploration. It avoids stale artifacts but exposes the data platform to concurrency and unpredictable request cost. Apply validated templates, timeouts, cancellation, row limits, quotas, and load shedding.
Hybrid
- RequestValidated, tenant resolved
- Cache key
tenantreportparametersformatversion - Look upIn
report-cache, under{tenant}/{report}/… - Cached and fresh?Within the report’s TTL
Yes Cache hit
- Authorize again for this caller
- Read the stored output
- Return result
No Cache miss
- Query Fabric (approved template)
- Build output: JSON, CSV or Parquet
- Store under the tenant’s path, with a TTL
- Return result
Tenant-aware: same report, same parameters, different tenants
- contoso
contoso · sales-by-region · 2026-09 · csv · v3report-cache/contoso/sales-by-region/… - fabrikam
fabrikam · sales-by-region · 2026-09 · csv · v3report-cache/fabrikam/sales-by-region/…
Two keys, two folders: never shared.
A hybrid engine checks a tenant-aware cache first and executes a fresh query on a safe miss. Cache keys include tenant, report type, date range, dimensions, filters, format, and report version. TTL follows freshness requirements. Invalidation can be event-driven after a data refresh or time-based when exact coupling is impractical.
Decision
There is no universal winner. Choose per report class using freshness objective, repetition, parameter variability, execution cost, result size, concurrency, and failure tolerance. Measure cache hit rate and query duration, but also correctness, stale-result incidents, output security, and storage growth.
Deciding per report class
| Report class | Example | Typical choice | Reason |
|---|---|---|---|
| Fixed period, fixed parameters, many readers | Monthly statement per tenant | Pre-render after the monthly load | Same output read many times; data will not change |
| Common parameters, daily data | “Last 30 days by channel” | Hybrid, keyed by data version | High reuse until the next load, then naturally invalid |
| Free combinations of filters and dimensions | Ad-hoc breakdowns from an API | Live, with strict limits | Reuse is rare, so stored results mostly waste storage |
| Near-real-time operational view | Orders in the last 15 minutes | Live, or a small precomputed table refreshed by the pipeline | Freshness matters more than repeat latency |
| Very large exports | Full-year detail for a tenant | Asynchronous job that writes a file | Neither a cache hit nor a synchronous query fits |
If you cannot name the class of a report, measure before choosing: log the normalized request key for a few weeks and count how often each key repeats between data loads. A key that never repeats before the data changes is a poor cache candidate however expensive it is.
Key the cache on the data version, not only on time
A time-based TTL guesses when data changes. When your pipeline records a load identifier, put it in the key and let invalidation happen by itself:
{tenant}/{report}/{report_version}/{data_version}/{sha256(normalized parameters)}.{format}
contoso/revenue-by-channel/v3/load-2026-11-06-0200/9f2c….csv
- tenant first: a key or path that can be built without the tenant can be served to the wrong tenant;
- report_version: changing the query or the output shape must not serve old files;
- data_version: the identifier of the last successful load for the source tables, read from your execution log; a new load produces new keys and old entries simply stop being read;
- normalized parameters: sorted, defaulted and typed, so equivalent requests share one key.
Keep a TTL as a safety net and for storage cleanup, not as the freshness mechanism.
Failure modes specific to caching
- Stampede after a load. Every key becomes a miss at the same moment and the warehouse receives the whole morning’s traffic at once. Coalesce identical in-flight misses so one query fills the entry while other callers wait, or pre-render the most-requested keys as the last step of the load.
- Late-arriving data. A correction lands after the load that the data version points to. Either bump the data version for corrections or exclude reports that tolerate no correction window from caching.
- Partial writes. A result is returned to the caller but the cache write fails, or the write half-completes. Write to a temporary name and rename, and treat an unreadable entry as a miss rather than an error.
- Storage that only grows. Pre-rendered files nobody opens. Record reads per entry and stop pre-rendering keys that are not read.
What to measure
Hit rate per report class (not overall), the age of the data at the moment a result is served, live query P95 and the share of misses that exceed it, storage bytes per tenant, and reads per stored entry. A rising hit rate with a rising served-data age is a warning, not a success.
Related
- What is a report engine?
- Report engine architecture: request to result
- Building a multi-tenant report engine on Azure and Microsoft Fabric, section 9 (caching and report artifacts)
- Designing execution logs for data pipelines: where the data version comes from
Sources
- Azure Architecture Center: Cache-Aside pattern, Materialized View pattern
- Microsoft Learn: Shared access signatures (SAS) overview, for short-lived download access to stored results
Checked November 2026. Thresholds here are design guidance, not measured results from a specific system.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind High CPU QueriesIdentify SQL Server queries consuming the most CPU now and historically, distinguish total CPU from CPU per execution, and choose the right next tuning step.Open cheat sheet
- ArticleDesigning a Report Engine for UI and API ConsumersDesign one governed report backend for interactive UI pages, external APIs, downloads and large asynchronous exports.Read article
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.