Materialize vs Snowflake for Live Agent Context

Operational databases hold the current state of a business. Snowflake's analytical engine, the warehouses and tables that dynamic and interactive tables run on, is well suited to historical analysis, large scans, backfills, training data, and other workloads that can tolerate delay.

Agents need something different when they are making operational decisions: current business objects they can retrieve quickly and combine without seeing contradictory versions of the underlying data.

This paper calls those business objects context products. A context product turns raw operational records into a maintained representation of something an agent needs to understand, such as a customer, account, order, shipment, or approval state. It may depend on one source table or many, across databases and systems. What matters is the business object it presents and the fact that it stays current as source data changes.

Snowflake can build and serve context products. A dynamic or interactive table runs its defining query on a schedule, no more often than every 60 seconds, and writes the result to storage; agents then read that result through a standard, adaptive, or interactive warehouse. In Snowflake the refresh is the transformation, and it runs on a timer. Materialize also maintains context products on one tier and serves them from another, but its transformation is continuous: it ingests the same operational change streams, updates each context product on a transform cluster as changes arrive, and serves indexed reads from dedicated serving clusters, with every read at one consistent logical timestamp. It exposes those context products to agents directly through a built-in MCP server or plain SQL, keeps external vector and search indexes current through its sinks, can push changes to agents, and can still write finished context products to Snowflake so operational and historical workloads use the same definitions.

A context product is a queryable business object derived from operational data and kept current as that data changes. For example, an account context product might combine the account record with open invoices, recent support activity, and current entitlements. An order data product might combine order state, inventory, payment status, and shipment events. An agent can retrieve one of these objects directly or combine several in a transaction.

A useful context product may come from a single table or from a much larger graph of joins, filters, aggregates, and window functions. The relevant questions are the end-to-end context latency, from a source commit to the moment an agent can read the result, whether related context products agree when the agent reads them, and what resources are required to serve many concurrent agents.

On Snowflake, a dynamic table or interactive table defines and maintains the context product. A standard warehouse performs the refresh, and TARGET_LAG specifies the desired staleness. The minimum target is 60 seconds. Snowflake treats that target as a goal rather than a guarantee, so applications must monitor actual lag and decide how to handle a miss.

Snowflake's operational stores do not change this path. Hybrid tables cannot be the base of a dynamic table or a stream, and interactive warehouses support them for surface compatibility only. Snowflake Postgres is a separate engine; its data reaches the warehouse side through data mirroring, in preview, at a refresh interval of 30 seconds or longer, or through a read-only Iceberg catalog integration. Either way the context product is a dynamic table on the analytical side, and the 60-second floor sits on top of whatever the operational store adds.

Refresh behavior depends on the SQL and the amount of source data that changes between runs. Incremental refresh is recommended when each cycle changes only a small portion of the input. Some operators, including EXCEPT, INTERSECT, and exact percentiles, require a full refresh. Stacked GROUP BY, DISTINCT, and window functions can also make a full refresh less expensive than an incremental one. ADAPTIVE refresh mode, generally available since July 30, 2026, can reinitialize after a large upstream change makes incremental work more expensive than rebuilding. When a definition uses Cortex AI functions, UDFs, or external functions, the heuristic typically skips reinitialization because re-executing those operators across every row would cost more than a fresh build.

Serving is a separate choice. Interactive warehouses are the closest fit for short, high-concurrency agent reads, so they are the fairest basis for this comparison. They change the serving behavior and cost, but they do not change the 60-second refresh floor of the maintained table beneath them.

In Materialize, the context product is an incrementally updated materialized view. The recommended production layout uses three tiers of clusters. Source clusters ingest operational change streams. Transform clusters maintain the materialized views and persist their results to durable storage. Serving clusters hold indexes on those views and answer reads from memory. A single cluster can do all three, but production deployments generally separate serving so that a heavy query or a rehydrating view cannot affect agent reads, and so that each tier scales independently. Unlike Snowflake's refresh and serving warehouses, the tiers share one logical timeline: a read on the serving cluster sees exactly the state the transform cluster has produced, and the transformation runs continuously rather than on a refresh schedule.

Transform clusters are sized for the incoming change rate and the amount of maintained state. Serving clusters are sized for the indexes they hold and the read concurrency they absorb. Indexed reads do not add a per-query compute charge, so increasing the number of readers does not change the bill until the serving cluster needs to grow. In customer deployments, indexed serving clusters have handled thousands of reads per second while the underlying data changes, with tail latencies under 20 milliseconds on index reads.

Agents can reach these context products without a custom API layer. Materialize can serve context products directly using a SQL interface, and includes a built-in MCP server, currently in public preview, that runs inside the database and exposes each indexed view as a typed tool, using the index columns as the tool's lookup keys and the view's comments as its description. Materialize recommends a serving cluster and schema dedicated to agent traffic, with the agent role granted access to only those objects, so agent reads are isolated from other workloads. An agent connects to the endpoint, discovers the available context products, and calls them directly instead of generating SQL against raw tables.

The table compares the serving tier each system would use for agent reads: an interactive warehouse reading dynamic tables zero-copy on Snowflake, and an indexed serving cluster on Materialize.

End-to-end context latency starts when the operational database commits a change. Both systems must ingest that change, update the relevant context product, and serve the result. The difference is the number of stages, the refresh floor, and what happens when the system cannot meet its target.

In Snowflake's best case, end-to-end latency includes a few seconds of ingestion, at least 60 seconds of target lag, and the final read. Production latency may be longer when ingestion is batched or a refresh falls behind. In Materialize, end-to-end context latency is about one to two seconds at the default one-second source timestamp interval, and that interval can be lowered.

The more important distinction is enforceability. Snowflake reports whether a target lag was met, but an application should not assume that every read is within the target. Materialize can enforce a bounded-staleness requirement at query time through its bounded staleness isolation level. It returns data from a timestamp within the requested bound, never blocks waiting for inputs to catch up, and returns a serialization error if the bound cannot be met. The agent can then avoid acting on data of unknown age.

A one-minute Snowflake target also has serious cost implications. Under continuous CDC, the refresh warehouse always has new work and may never suspend. Cloud services check for changes on each cycle. Interactive cache warming must also keep up with the new micro-partitions produced by refreshes.

Snowflake and Materialize have different approaches to consistency; these choices ultimately impact agent effectiveness and token efficiency. Snowflake coordinates the inputs to a pipeline refresh. Materialize coordinates the context products returned by a query. For an agent choosing an action, the query-time guarantee is the one that determines whether the assembled context describes a single state of the business.

Within a Snowflake pipeline refresh, every table reads its upstream inputs at a coordinated data timestamp. This is stronger than independently scheduled streams and tasks. The guarantee ends with the refresh, however. An ad hoc query reads the latest committed version of each table. If two context products refresh on different cycles, an interactive warehouse can join versions that represent different points in time.

Pipeline boundaries add another qualification. A downstream dynamic table may consume stale data from an upstream object it does not control. The refresh can still report success. Snowflake recommends a boundary around views that a pipeline owner does not control, which is common when different teams publish shared context products. Repeated refresh failures are surfaced as increasing staleness, and a dynamic table is automatically suspended after five consecutive failures.

Materialize assigns logical time as changes enter the system and preserves that time through computation. A query that reads several context products sees all of them at one timestamp. This remains true across isolation levels; the isolation choice changes the tradeoff between recency and latency, not whether the query is internally consistent.

• Agents often write to an operational system and immediately read context to decide what to do next. A minute of lag can break that feedback loop and make it impossible to build interactive agents with humans in the loop.
• Stale data can cause an incorrect action, not merely an outdated display. A delayed dashboard is inconvenient; a refund approved against an old account state can be wrong.
• Agents combine several business objects at the moment of decision. Those objects need to describe the same point in time.
• An explicit freshness failure lets the application pause, retry, or escalate. A successful query that silently returns old data does not.
• Concurrency comes from many agents issuing small reads, rather than a smaller number of analysts running exploratory queries.
• Prepared context products reduce tokens as well as database work. They replace wide raw payloads, repeated text-to-SQL attempts, and polling with a direct call that returns only the context the agent needs.

Some agent architectures avoid polling to minimize reaction time and save tokens spent asking the same question multiple times. A change in the business, such as a payment failing or an order shipping, should wake the agent, and the agent should then read the rest of its context as of the same moment. That asks two things of the context layer: a way to deliver changes as they happen, and a way to tell the consumer when what it has received is complete.

Snowflake delivers changes out of a context product as a batch feed from a stream. A stream on a dynamic table works only with incremental refresh, and a reinitialization delivers every row difference at once. A triggered task can consume the stream and call a notification integration. Triggered tasks fire at most every 30 seconds by default and every 10 seconds with a parameter change, and scheduled alerts run at most once a minute. The Openflow Connector for Snowflake to Kafka, in preview, consumes one stream per connector and emits change records to a topic without a schema and without schema evolution, on an Openflow runtime billed per second with a 60-second minimum, from 0.11 credits per hour for the smallest Snowflake-hosted node, plus a warehouse the connector uses to read the stream. Its setup page exposes no polling-interval parameter. There is no primitive by which a client subscribes to a changing query result. End to end, a push consumer learns of an operational change after ingestion, the 60-second refresh floor, and the trigger or poll interval, on the order of one and a half to two minutes.

Materialize delivers changes with SUBSCRIBE, which streams inserts and deletes to a view, materialized view, source, or ad hoc query over the PostgreSQL wire protocol. Each update carries a logical timestamp, and progress messages report when a timestamp is complete, so a consumer can act on a consistent frontier and distinguish a quiet period from a stall. For many consumers, a Kafka sink emits the same changes with an upsert or Debezium envelope and exactly-once delivery by default.

A practical routing rule is to use Materialize for named, maintained context products and Snowflake for novel analytical questions. If a context product already answers the question, the agent should call it instead of generating SQL against raw tables.

For Materialize reads that combine multiple products, define a composite context product or issue indexed reads inside one read-only transaction. The first statement in a read-only transaction fixes which schemas later statements can reference, so place products that agents read together in the same schema. An unindexed ad hoc join creates a one-time dataflow and has materially lower throughput.

Materialize and Snowflake can consume the same operational database and Kafka change streams. Materialize maintains the current business objects used by agents and operational applications. Snowflake retains history and serves analytical workloads. Rather than create two parallel pipelines, Materialize can deliver finished context products to Snowflake. There are two paths:

1. A native Iceberg sink writes exactly-once updates to Iceberg tables in Amazon S3 Tables, which Snowflake reads as external Iceberg tables.
2. A bulk export to Amazon S3 can be loaded with COPY INTO on a schedule.

Either allows batch scoring, business intelligence, and historical analysis to use the same definitions as the live agent path.

This design avoids maintaining separate transformation logic for live and historical systems. It also removes the need to copy Snowflake results back into an operational serving layer before an agent can use them. Both systems remain active, but they are active for different reasons: Materialize is sized for the rate of change and maintained state, while Snowflake runs the warehouses required to refresh and serve its copies.

Snowflake and Materialize charge for different parts of the workload. In Snowflake, the refresh warehouse is one bill and the serving engine is another. Serving cost depends on whether reads run on a standard warehouse, Adaptive Compute, or an interactive warehouse. Materialize charges per second for each cluster, so the bill is the transform and serving clusters that maintain the context products and serve their indexed results.

For a continuously changing context layer with a one-minute Snowflake target lag, the refresh warehouse is unlikely to suspend. That makes refresh a fixed hourly cost even if no agent reads the result. The serving line then behaves differently by engine:

• A standard warehouse adds capacity in steps. A continuously active reader can keep the warehouse running, and another cluster is required when concurrent queries exceed the capacity of the existing cluster.
• Adaptive Compute bills per query and has no idle charge, so cost rises with read volume. Snowflake does not publish a fixed per-query rate.
• An interactive warehouse is effectively a fixed hourly rental while active. It remains flat until another cluster is required, with a separate standard warehouse billed when a query exceeds the five-second limit.

Materialize's cost changes when source change volume or maintained state requires a larger transform cluster, or when read concurrency requires a larger serving cluster, not per query.

For the same interactive workload Materialize is typically less expensive, but the architectural differences in freshness and consistency matter more.

Snowflake can maintain and serve agent context, particularly when a team values platform consolidation and can tolerate at least a minute of best-effort refresh lag. Its interactive warehouse is the right Snowflake serving option for short, concurrent reads.

Materialize is the stronger fit when an agent needs context that reflects operational changes within seconds, when several business objects must agree at the moment they are read, or when the application must reject data that is too stale to support an action. A combined architecture lets Materialize serve that live context while Snowflake remains the system for history, exploration, and large-scale analytical work.