Direct Query Mode — Data Platform Architecture Pattern

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 centralized and fresh.

Cloud Paradigm

Query Pushdown (predicate and aggregation delegation)
Separation of Storage and Compute
Semantic Layer Abstraction
Centralized 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 parameterized 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 leverage.

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, centralized 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.

Real-World Example

A national grocery retailer runs a store-operations dashboard on a cloud data warehouse holding billions of point-of-sale rows. Regional managers filter by store and category to see live shelf availability; each interaction issues Direct Query SQL that returns aggregated stock levels within seconds. Because nothing is extracted, a manager in one region can never load another region's raw transactions—row-level security is enforced by the warehouse, and pricing changes made minutes ago appear immediately without a scheduled refresh.

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 normalize 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.