How does query performance change with data source type?

The perennial debate around BI tool performance often centers on engine architecture (in-memory vs. direct query) and visualization rendering. However, a more foundational and frequently overlooked variable is the *data source type* itself. The query performance profile of a dashboard is intrinsically tied to the characteristics and capabilities of the underlying data system, often to a greater degree than the BI tool's own optimizations.

I propose a framework for evaluating this, breaking down the data source into three critical dimensions that directly govern latency, concurrency, and cost:

1. **Query Interface & Protocol:** The wire protocol and query language the BI tool uses to communicate with the source (e.g., JDBC/ODBC, REST API, GraphQL, native drivers).
2. **Execution Model:** Where and how the computational work is performed (e.g., in the source database's engine, in an intermediate semantic layer, or pulled into the BI tool's memory).
3. **Underlying System Performance:** The inherent performance profile of the source system (OLAP vs. OLTP, columnar vs. row storage, indexing, partitioning, resource allocation).

To make this concrete, consider a simple aggregation dashboard (`SELECT region, SUM(revenue) FROM sales GROUP BY region`) against three different source types:

* **Direct Query to Cloud Data Warehouse (e.g., Snowflake, BigQuery):**
* The BI tool submits SQL via an ODBC driver.
* Execution is 100% on the cloud warehouse's compute. Performance is governed by its cluster size, caching, and warehouse optimization. The BI tool is merely a client. Latency is network RTT + warehouse execution time. Cost is directly tied to cloud warehouse compute consumption.
```sql
-- The BI tool generates and sends this. Performance hinges on the warehouse.
SELECT region, SUM(revenue) FROM sales
WHERE date >= '2024-01-01'
GROUP BY region
ORDER BY SUM(revenue) DESC;
```

* **Live Connection to a Production OLTP Database (e.g., PostgreSQL RDS):**
* The BI tool submits SQL via PostgreSQL's native protocol.
* Execution is on the OLTP database, competing with transactional workload. Lack of columnar storage and aggressive indexing for reporting queries often leads to full table scans and lock contention. Dashboard load can severely impact production operations. Performance is erratic and concurrency is low.

* **Import into BI Tool's In-Memory Engine (e.g., Tableau Extract, Power BI Import Mode):**
* Data is pre-aggregated or fully ingested into the BI tool's proprietary, compressed, in-memory columnar store.
* Execution is within the BI tool's process, leveraging its RAM and CPU. Query latency is extremely low and consistent for filtered scans and aggregations. However, refresh cycles are stale, and scalability is limited by the user's desktop or server hardware. Cost is shifted to provisioning robust BI servers.

The performance trade-offs are stark. Choosing a "Live Connection" to an unoptimized MySQL instance for a complex, self-service dashboard is a recipe for failure, regardless of the BI tool's brand. Conversely, using Import Mode against a rapidly changing transactional source renders the data useless for operational decisions.

I am currently modeling the cost-performance curve for a 5TB dataset across Amazon Redshift, Athena, and a Power BI Premium capacity with DirectQuery. Initial results suggest the inflection point where direct query latency becomes unacceptable is reached much sooner with Athena (due to its per-query scan model) than with a provisioned Redshift cluster, but the cost structure is entirely different.

What specific data source combinations have you benchmarked? I'm particularly interested in observed latency differences for concurrent dashboard loads when using tools like Metabase or Superset against traditional databases versus modern cloud platforms.

-ek