Transactional downstream updates using Kafka
View as MarkdownWhen you load messages from Kafka into another data store, such as Snowflake or ClickHouse, the loader writes messages as they arrive. A query against the downstream store can therefore see some, but not all, of the changes Materialize made at a given timestamp. Use this pattern when downstream readers must only see complete, transactionally consistent results.
This guide walks you through the pattern end to end, using Snowflake as the destination:
- Create a Kafka sink in Materialize.
- Load the sink’s data topic and progress topic into Snowflake.
- Build a table of trusted frontiers that Snowflake has fully loaded.
- Create Snowflake views that expose only changes below the trusted frontier.
The same approach works for any destination that stores Kafka headers and message keys alongside the message value.
How it works
Materialize writes each batch of sink messages to Kafka in a transaction, together with a progress record in the connection’s progress topic. The progress record says which timestamps the sink has fully committed. A downstream view can join the two topics and hide any row that is not yet covered by a progress record.
| Where | What to read | Value |
|---|---|---|
| Data topic | materialize-timestamp header |
The Materialize timestamp of the change, for example 1792700000000. |
| Data topic | materialize-sink-id header |
The sink’s ID, as shown in mz_sinks, for example u123. |
| Progress topic | Message key | mz-sink- followed by the sink’s ID, for example mz-sink-u123. |
| Progress topic | Message value | JSON of the form {"frontier":[1792700001000],"version":1}. |
All sinks on a Kafka connection share one progress topic, and each sink’s
records are keyed by mz-sink-<sink_id>. Joining on the materialize-sink-id
header is what ties each data message to its own sink’s frontier.
The frontier is exclusive: the sink has committed every change with a
timestamp less than the frontier. The pattern has two parts:
- Build a trusted frontier. For each sink, record a frontier only once the loader has confirmed that every data message below it has landed in the destination. The latest frontier in the progress topic is only a candidate; see Step 3.
- Gate views on the trusted frontier. Keep the data messages whose
materialize-timestampis less than the trusted frontier for their sink.
Before you begin
- A Kafka or Redpanda cluster that Materialize can write to.
- A Kafka Connect cluster with the Snowflake connector for Kafka installed.
- A Snowflake database and schema, and a Snowflake user that the connector can authenticate as. See Installing and configuring the Kafka connector.
- A table, view, or materialized view in Materialize to sink. This guide uses a
materialized view named
orderswith a unique key columnid.
Step 1. Create a Kafka sink in Materialize
-
Create a connection to your Kafka cluster. You can optionally name the progress topic with the
PROGRESS TOPICoption; otherwise, Materialize uses a default name.CREATE SECRET kafka_password AS '<BROKER_PASSWORD>'; CREATE CONNECTION kafka_connection TO KAFKA ( BROKER '<BROKER_URL>', SASL MECHANISMS = 'SCRAM-SHA-256', SASL USERNAME = '<BROKER_USER>', SASL PASSWORD = SECRET kafka_password ); -
Create the sink. This guide uses the JSON format and the upsert envelope.
CREATE SINK orders_sink FROM orders INTO KAFKA CONNECTION kafka_connection (TOPIC 'orders') KEY (id) FORMAT JSON ENVELOPE UPSERT;Materialize adds the
materialize-timestampandmaterialize-sink-idheaders to every message automatically. You don’t need to set any options. -
Look up the sink’s ID and the name of its progress topic. You need the progress topic name to configure the Snowflake connector.
SELECT s.id AS sink_id, c.sink_progress_topic FROM mz_sinks AS s JOIN mz_kafka_connections AS c ON s.connection_id = c.id WHERE s.name = 'orders_sink';sink_id | sink_progress_topic ---------+-------------------------------------- u123 | _materialize-progress-<env>-<conn>
Step 2. Load both topics into Snowflake
Configure the Snowflake connector to load the data topic into a table named
raw_orders and the progress topic into a table named raw_progress. This
guide uses version 3 of the connector with Snowpipe Streaming, which loads
each partition through its own channel. For example:
{
"name": "materialize_orders",
"config": {
"connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
"snowflake.ingestion.method": "SNOWPIPE_STREAMING",
"topics": "orders,<sink_progress_topic>",
"snowflake.topic2table.map": "orders:raw_orders,<sink_progress_topic>:raw_progress",
"snowflake.url.name": "<account>.snowflakecomputing.com:443",
"snowflake.user.name": "<user>",
"snowflake.private.key": "<private_key>",
"snowflake.database.name": "<database>",
"snowflake.schema.name": "<schema>",
"key.converter": "org.apache.kafka.connect.storage.StringConverter",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter.schemas.enable": "false",
"consumer.override.isolation.level": "read_committed"
}
}
Each Snowflake table gets two columns:
RECORD_CONTENT: the message value, as aVARIANT. For the progress topic, this is the JSON object containingfrontier.RECORD_METADATA: the message metadata, as aVARIANT. The message key is inRECORD_METADATA:keyand the headers are inRECORD_METADATA:headers.
These settings in this configuration matter for the pattern:
snowflake.ingestion.methodmust beSNOWPIPE_STREAMING. Each channel then records an offset token, which Step 3 relies on. Snowpipe Streaming does not support the Snowflake converters, so this example usesJsonConverterfor values.key.convertermust beorg.apache.kafka.connect.storage.StringConverter. Otherwise, the connector does not keep the progress topic’s message key, and you can’t tell which sink a progress record belongs to.consumer.override.isolation.levelmust beread_committed, so that the connector never loads messages from aborted Kafka transactions. Your Kafka Connect worker must allow client configuration overrides (connector.client.config.override.policy). Alternatively, setconsumer.isolation.level=read_committedin the worker configuration. This setting only excludes aborted transactions. It does not order the connector’s flushes across partitions; see Step 3.errors.toleranceandbehavior.on.null.valuesmust keep their defaults,noneandDEFAULT. With other values, the connector can skip a message but still advance its offset token past it, and Step 3 would then trust a frontier that is missing that message. WithDEFAULT, a deletion (a message with anullvalue) loads as a row with empty content.
Step 3. Build a trusted frontier table
The latest frontier in raw_progress is not enough to gate on. Kafka
transactions are atomic on write, not on read: the Snowflake connector loads
each topic partition through its own channel and flushes each one
independently. A progress record can land in Snowflake before every data
message below its frontier has landed. Before you trust a frontier, you must
confirm that the loader has consumed the data topic up to it.
This requires a loader that reports the offset it has committed for each
partition. With Snowpipe Streaming, the Snowflake connector loads each
partition through its own channel, and each channel’s offset token records the
last Kafka offset that channel has committed to Snowflake. To read the offset
tokens, run SHOW CHANNELS IN TABLE raw_orders and use the offset_token
column. The checks below compare those offset tokens to the data topic.
Create a table to hold the frontiers you have confirmed:
CREATE TABLE trusted_frontier (
sink_id STRING NOT NULL,
frontier NUMBER NOT NULL,
confirmed_at TIMESTAMP_LTZ NOT NULL
);
Then run a small routine outside Snowflake that appends a row each time it
confirms a new frontier for a sink. Views read the highest frontier for each
sink, the same way they would read raw_progress. For each sink, the routine
should:
- Read the candidate frontier for the sink from
raw_progress. - Record each data partition’s high watermark. The progress record is already in Snowflake, so every data message below the candidate frontier sits below these high watermarks.
- For each data partition, start a consumer with
read_committedisolation at the channel’s offset token + 1, or at the start of the partition if the channel has no offset token yet. Read until the consumer’s position reaches the recorded high watermark.read_committedskips transaction markers and aborted messages. - If no partition returns a message, Snowflake has loaded every committed
data message below the candidate frontier. Write the candidate frontier
to
trusted_frontier. - Otherwise, the connector is still loading. Wait, then repeat from step 3 with the same candidate frontier and high watermarks.
Keep the candidate fixed until you confirm it. If you pick a newer candidate on each attempt, the routine can keep chasing new data and never confirm any frontier. A production routine also needs to handle timeouts and retries, for example when a transaction stays open and holds the consumer below the high watermark.
Step 4. Create a view of committed changes in Snowflake
Create a view that keeps only the data messages below the sink’s trusted frontier:
CREATE VIEW committed_orders AS
WITH latest_trusted AS (
-- The latest trusted frontier for each sink.
SELECT sink_id, MAX(frontier) AS frontier
FROM trusted_frontier
GROUP BY 1
)
SELECT
o.RECORD_METADATA:headers:"materialize-timestamp"::number AS mz_timestamp,
o.RECORD_CONTENT AS order_record
FROM raw_orders AS o
-- Match each data message to the trusted frontier for its sink.
JOIN latest_trusted AS f
ON f.sink_id = o.RECORD_METADATA:headers:"materialize-sink-id"::string
-- Keep only the changes below the frontier.
WHERE o.RECORD_METADATA:headers:"materialize-timestamp"::number < f.frontier;
latest_trusted takes the highest trusted frontier for each sink. The join
uses the materialize-sink-id header, so each message is compared to its own
sink’s frontier. Messages at or above the frontier appear in the view
once the routine confirms they have loaded.
Step 5. Join changes from multiple sinks
If you sink several relations and create a view like committed_orders for
each, each view is gated on its own sink’s frontier. Joining two such views can
combine changes from different Materialize timestamps, which loses
consistency. Gate joined views on a common frontier instead. There are two
ways to do this:
-
One shared frontier. Gate every view on the lowest trusted frontier across all sinks. Any of the views can then be joined with any other. The tradeoff is freshness: every view is held back by the sink that is furthest behind.
CREATE VIEW shared_frontier AS WITH current_frontiers AS ( -- The latest trusted frontier for each live sink. SELECT sink_id, MAX(frontier) AS current_frontier FROM trusted_frontier WHERE sink_id IN ('u123', 'u456') GROUP BY sink_id ) SELECT MIN(current_frontier) AS frontier FROM current_frontiers;List the live sinks explicitly. A dropped sink’s frontier stops advancing, and a recreated sink gets a new ID, so update the list whenever you drop or recreate a sink. Otherwise, the shared frontier stalls.
-
One frontier per join. For each join, gate on the lowest trusted frontier of only the sinks that join reads. This keeps each join as fresh as its inputs allow, but each join needs its own gate that lists its sinks.
Step 6. Query the view
Point downstream readers at committed_orders instead of raw_orders:
SELECT * FROM committed_orders ORDER BY mz_timestamp DESC LIMIT 10;
Every row in the result belongs to a Materialize timestamp whose changes have
all been loaded into Snowflake, because the view gates on trusted_frontier
rather than on raw_progress directly.
Considerations
committed_orderscontains one row per change, not the current state oforders. With the upsert envelope, a deletion is a message with anullvalue (a tombstone), which loads as a row with empty content. To build the current state, take the latest committed row for each key and drop the keys whose latest row is a tombstone.- Materialize writes a progress record each time the sink’s frontier advances,
even when there is no new data. Expect
raw_progressto grow steadily. - Header keys starting with
materialize-are reserved for Materialize. - For other guarantees you need end to end, see End-to-end exactly-once processing.