Pre-Calculated Metrics vs On-Demand Query Architectures

Pre-calculated metrics and on-demand queries make different bets about who will consume data. For recurring numbers shared by people and AI agents, governed pre-calculation is the stronger default because it binds speed and cost control to a shared definition. Dreambase applies this approach to Supabase and Postgres teams, while on-demand querying remains appropriate for exploration and infrequent questions.

Why the pre-calculated vs. on-demand choice is not a performance question

Most teams inherited on-demand querying because it was the simplest way to get an answer. A BI tool connected to a database, someone wrote SQL, and the database returned a result. Human consumers could wait a few seconds, tolerate some data age, and interpret an ambiguous number. That made query latency a reasonable focus when comparing architectures. It was often the visible constraint.

AI agents expose the assumptions behind that arrangement. They make concurrent requests, interpret results literally, and cannot be relied on to recognize a semantically wrong answer before using it. A faster query does not resolve any of those problems by itself. The architecture must also constrain what consumers can access and establish what each result means. Trust therefore depends on people and agents receiving numbers they can act on without reconstructing their meaning.

How each architecture actually works and where each breaks

On-demand querying aggregates raw or lightly transformed tables when a request arrives. It reflects the latest data available to the query, but complex aggregations over large tables can take seconds to tens of seconds. In a scan-based system, asking again can mean scanning the same bytes again. With direct table access, consumers can also calculate different answers from any columns their credentials let them use. Neither a correct SQL statement nor a fast response establishes that the resulting metric matches the business definition.

Pre-calculation moves that work ahead of the request. Results are computed on a schedule and stored, so consumers retrieve an existing result without repeating the underlying aggregation. Its failure modes are the reverse of on-demand execution. Freshness is bounded by the refresh interval, and a question not modeled in advance cannot be answered instantly from the prepared result. On-demand execution accepts repeated computation for flexibility, while pre-calculation accepts bounded freshness and scope for predictable retrieval.

A semantic layer addresses a separate problem by defining business logic centrally. It can govern either execution model, so it is not another name for pre-calculation. Without it, even stored metrics can encode competing definitions of the same number. With pre-modeled datasets, as in Dreambase’s approach, the semantic layer gives people and agents metrics that have already been defined, validated, and centrally maintained. The distinction matters because storing an answer does not, by itself, make that answer trustworthy.

What scan-based cost structures mean for teams that run dashboards at volume

When dashboards run again and again, scan pricing affects how the system should be built as well as what the occasional query costs. BigQuery’s on-demand pricing illustrates the mechanism because it bills by bytes scanned. A poorly scoped query touching a large table incurs its scan cost on each execution. Running it a hundred times per day multiplies that cost proportionally, even if every request asks the same question. The same dynamic seen in BigQuery applies to other scan-based on-demand systems.

Materialized views counter this repetition by storing computed results and, where supported, allowing incremental refresh. Consumers that ask again then get the prepared answer without paying for the same full-table aggregation each time. Reserved warehouse capacity offers a different form of predictability through planned capacity spending, but it does not itself remove repeated computation. Pre-calculation is an architectural response because it changes the work being performed.

Agent workloads make this distinction more important because a single workflow can issue multiple concurrent requests. Materialized views reduce redundant work, but they do not determine which results an agent may access. A governed data factory approach, including Dreambase’s, prepares metrics and limits which datasets agents can use. That addresses both repeated scans and the permission creep that comes from granting raw table access.

Why AI agents break on-demand query architectures in ways human users do not

Human-centered querying first assumes that consumers can question a result. A trained analyst may notice that an answer is implausible and investigate its definition. An agent can instead use a semantically wrong result as the premise for its next step. Referencing a reviewed metric by name removes the need for the agent to invent that metric from raw tables. It fixes the definition at the point of access.

The second assumption concerns the pace of requests. Human analysis often proceeds through individual queries, with time spent interpreting each answer. An agent workflow can combine vector searches, SQL queries, and key-value lookups concurrently. That pattern multiplies scan costs and contention without adding human pauses between requests.

The third assumption is that a human-oriented permission model provides a sufficient boundary. An agent with database credentials can reach whatever those credentials allow, even if it is told to be cautious. The Replit incident shows how production privileges that stay in place differ from a code freeze given in words. A governed dataset narrows the technical access surface from the underlying table to the metric. Because agents use structured output rather than browse dashboards, a defined API or MCP tool should enforce that boundary.

What MCP actually provides — and what it does not — as a data access layer for agents

MCP standardizes how agents call tools and access resources. It gives agents a structured interface to databases, APIs, and internal systems, so each agent does not need its own custom integration. That makes it useful as an access layer, but the interface does not determine the quality of what sits behind it. A tool that exposes raw database access still exposes the underlying query and permission problems.

The MCP protocol revision on July 28, 2026 removed protocol-level session tracking and made MCP stateless at the protocol layer. That separation reinforces the need to implement identity, permissions, and audit in the platform behind the interface. MCP is not a substitute for semantic grounding, lineage, query performance, or cost controls. The NSA’s May 2026 Cybersecurity Information Sheet warned that MCP’s rapid adoption had outpaced the development of its security model. Deploying an MCP server is therefore not, on its own, a governance strategy.

The agent should get a named metric through an MCP tool call. The server retrieves that metric from a pre-modeled, governed dataset rather than handing the agent a production connection. The interface makes the data reachable, while the underlying layer makes access bounded and the result interpretable. Both parts are necessary for this access pattern to serve trusted numbers.

The governance gap that agent concurrency creates and why batch-audit tools cannot close it

Periodic audits were built around a pace at which people requested and reviewed data manually. Autonomous workflows can make many dependent calls before a later audit examines the first result. That makes batch review inadequate as the main operational control. Agent analytics needs observability that tracks data touchpoints, decision nodes, and API calls in real time. Governance here means knowing which numbers an agent used and whether their definitions remain dependable.

The controls start by letting agents use only a curated semantic layer with metrics that have been reviewed. The first version of a new agent-generated dashboard or report should receive human review. Where the system generates SQL, every prompt-to-SQL pair should be logged, a sample scored for faithfulness to the underlying data, and drift made visible through alerts. These controls treat analytics automation as a production LLM system.

Metric contracts make that governance concrete by recording what a number means, who owns it, and how it is calculated. A governed pre-calculated result can then be traced to a known computation time, dataset, and definition. The agent consumes an existing artifact instead of creating a novel calculation on every request. On-demand execution can also be audited, but doing so requires capturing and replaying the query. At agent concurrency levels, that creates a heavier operational burden than inspecting a shared, defined result.

When on-demand querying is still the right choice

On-demand querying remains appropriate when the question is genuinely new. A trained analyst exploring a warehouse needs room to look into relationships that no prepared dataset was built to cover. One-off work, including debugging an anomaly, validating a new model, or answering a nonrecurring board question, has the same requirement. Low-volume and unpredictable workloads can also make scan-based pricing reasonable because there is little repeated work to eliminate. Pre-calculating every possible question would limit what teams can ask without enough benefit to make up for it.

Freshness provides another legitimate constraint, but it must be defined by the decision. Fraud detection, real-time inventory, and live incident response can need data that is as fresh as possible. A weekly portfolio review does not have the same acceptable data age as an incident console. Where sub-second ingestion is required, streaming architectures address that requirement more directly than warehouse queries that add transformation and query latency. On-demand access should not be treated as synonymous with real time.

The practical test is recurrence and reuse. A founder’s morning metrics, an agent’s weekly KPIs, and numbers shown to every dashboard user are shared obligations. If the same metric is consumed more than once, by more than one consumer, on a recurring basis, pre-calculate it. If it is a novel question asked once by a trained analyst, on-demand querying is appropriate.

Why Postgres-native teams face a specific version of this problem

For Supabase and Postgres teams, the easiest analytics connection is often the application database itself. That creates problems beyond scan pricing. Row-level security policies built for application users can make BI tools and agents behave in ways that are hard to predict. Connection poolers sized for application traffic can become bottlenecks under analytical concurrency, while JSONB columns can be expensive and awkward to aggregate. Analytical queries also compete with application queries for compute, making the resulting latency visible to end users.

A read replica genuinely addresses that contention by separating analytical reads from the primary workload. Supabase recommends replicas for analytical workloads, but the separation has an infrastructure cost. A replica runs the same compute size as the primary and bills separately, with disk provisioned at 1.25× the primary’s disk size and billed at the same rate per GB. That can roughly double database spend without supplying metric definitions or agent-safe access. It creates a cleaner place to query.

Postgres materialized views are a built-in way to store calculations that are used again and again, and tools such as pg_cron can schedule their refresh. They still require schema design discipline and refresh orchestration, and they do not provide a governed API. Direct agent credentials also retain the access allowed by their database role.

What a governed pre-calculation layer looks like in practice for a Supabase team

The missing layer sits between production Postgres and everyone or everything that uses it for analytics. It prepares datasets with known calculation logic, refresh times, and ownership, then exposes those definitions through dashboards and agent interfaces. Governed Parquet datasets queryable via DuckDB let agents retrieve prepared context without a production connection string. The important separation is between preparing the data and consuming it. People and agents can use different interfaces without receiving different versions of the metric.

Dreambase is built for this pattern on Supabase and Postgres, with a shared data layer serving human teammates and AI agents. It provides pre-calculated metrics, governed datasets, live dashboards, and AI-generated reports without requiring ETL pipelines, schema changes, or new infrastructure to manage. Its metric dictionary, refresh schedule, and governance layer make the approach available to founders and growing teams without a dedicated data team. MCP gives agents access to prepared datasets, while metrics can reach people through inbox, Slack, and phone. Compared with assembling materialized views and separate access controls, the benefit is that these responsibilities belong to one layer.

The work still starts with a metric contract, which must be defined before agents get access. It establishes what each number means, who owns it, and how it is calculated. The dashboard should then present a small number of metrics readable in minutes, with other data available through governed datasets. The chart is an output of this architecture. The outcome is one definition of “active users” or “monthly revenue”, shared across the dashboard, the agent report, and the board update.