Data Platform / Consumption
Data PlatformConsumptionBIAnalytics
Direct Query Mode
David TirabassiUpdated
Problem
Copying entire datasets into BI tool memory yields stale snapshots, hits memory limits, and scatters sensitive data outside the governed warehouse. Refreshing these extracts strains pipelines, dashboards diverge from reality, and audit gaps widen as copies proliferate beyond central controls.
Solution
Configure BI tools to use Direct Query Mode, pushing SQL execution down to the Cloud Data Warehouse in real-time, ensuring data remains centralised and fresh.
Cloud Paradigm
- Query Pushdown (predicate and aggregation delegation)
- Separation of Storage and Compute
- Semantic Layer Abstraction
- Centralised Data Governance
- Identity Propagation (row-level security passthrough)
- Live Query Federation
Solution Flow
- A business analyst opens a dashboard and interacts with a visual (filter, drill-down, or slice), triggering a query request rather than loading a cached extract.
- The BI semantic layer translates the visual interaction and any row-level security context into a parameterised SQL statement using the defined data model and relationships.
- The BI connector pushes that SQL down over an encrypted connection to the cloud data warehouse, passing the user's identity or a mapped service principal.
- The cloud data warehouse executes the query against live tables, applying its own compute scaling, result caching, and governance policies before returning only the aggregated result set.
- The BI rendering engine receives the compact result, renders the visual, and holds nothing but the on-screen aggregate—no bulk row storage in tool memory.
- The platform team monitors query load, tunes materialized views and warehouse cache, and enforces concurrency and cost guardrails on the shared compute.
When to Use
- Datasets are too large to fit in BI engine memory or exceed extract row limits.
- Dashboards must reflect near-real-time operational data (inventory, fraud, live pipelines).
- Regulatory or residency rules require data to stay inside the warehouse boundary.
- The warehouse already provides elastic compute and result caching you want to use.
When NOT to Use
- High-concurrency dashboards where per-interaction queries would overwhelm or over-spend on warehouse compute.
- Latency-sensitive executive dashboards over slow or complex joins that an extract would render instantly.
- Sources without a performant SQL engine (flat files, small CSV exports).
- Offline or intermittently connected reporting scenarios.
Trade-offs
- Always-fresh, centralised data vs. per-interaction query latency and warehouse cost exposure.
- No bulk data duplication in the BI tier vs. dependency on warehouse uptime and connection stability.
- Governance and RLS enforced at source vs. tighter coupling between dashboard design and SQL performance tuning.
- Smaller memory footprint vs. reduced benefit from BI-native in-memory acceleration.
Illustrative Scenario
A hypothetical scenario showing how the pattern applies. Not a client case study.
Imagine a national equipment-hire company whose depot managers use a utilisation dashboard backed by a cloud data warehouse holding billions of rental-transaction and telemetry rows. A manager filters by depot and machine category to see which excavators, light towers and generators are on hire, idle or overdue, and each interaction issues Direct Query SQL that returns aggregated utilisation within seconds. Because nothing is extracted into the BI tool, row-level security stays in the warehouse, so a manager in one region cannot load another region's raw contract data. A rate change made minutes ago also shows up immediately, with no scheduled refresh to wait for.
Additional Details
- Query storms from interactions: Each filter, cross-filter, and drill-down fires fresh SQL; a single dashboard load can spawn dozens of concurrent queries per user. Cap visuals per page, disable auto-apply on slicers, and batch filter selections before execution.
- Cache layering: Distinguish warehouse result cache (keyed on exact SQL text) from BI-side query cache. Parameter churn and per-user RLS predicates fragment the cache key and drive cache-miss rates up—monitor hit ratio and normalise generated SQL to improve reuse.
- Identity passthrough: Verify that user identity or mapped principal reaches the warehouse on every query so RLS and column masking apply; a shared service account silently collapses per-user governance. Test that connection pooling does not leak one user's context to another.
- Cost drivers: The bill scales with compute-seconds per interaction, not data volume at rest. Pre-aggregate with materialized views, set statement timeouts and per-user concurrency limits, and route dashboards to a right-sized, auto-suspending warehouse to avoid runaway spend.
- Model-schema coupling: Renamed columns, changed grain, or dropped tables break live visuals immediately with no extract buffer. Version the semantic model against warehouse DDL and stage schema changes behind views to preserve backward compatibility.
- Observability: Tag queries with dashboard, visual, and user IDs so warehouse query history maps back to specific tiles. Instrument p95 latency, queue/spill time, timeout rate, and per-dashboard credit consumption to catch expensive joins early.
Security Controls
- Row-level security passthrough: User identity is forwarded so the warehouse enforces per-role row and column filters at query time.
- No data at rest in BI tier: Only on-screen aggregates are held in the tool, eliminating extract files as a data-exfiltration surface.
- Encrypted transport: All connector traffic uses TLS with mutual authentication between the BI service and the warehouse.
- Least-privilege service principals: Connections use scoped credentials mapped to read-only, query-only warehouse roles.
- Query auditing: Every pushed-down SQL statement is logged in the warehouse for full lineage and access review.
- Concurrency and cost guardrails: Resource classes and query timeouts prevent a runaway dashboard from exhausting shared compute.