Databricks & Snowflake · Chapter 16 of 16
Streaming, and what 'exactly once' actually guarantees
Checkpoints, the key-value store that makes file ingestion idempotent, channels and offset tokens — and the preconditions every exactly-once claim quietly depends on.
Every streaming system in this space claims exactly-once. Every claim is conditional, and naming the preconditions is the whole lesson. This chapter is what each mechanism actually does and where each guarantee stops.
Structured Streaming checkpoints, in four parts
- Offsets — which source offsets were processed in each micro-batch, so a restart resumes exactly where it stopped.
- Commits — which micro-batches were committed to the sink. This is the record that makes exactly-once possible: on restart, a batch present in offsets but absent from commits is replayed.
- State — the contents and schema of stateful operators.
- Metadata — the query ID.
What you may change across a restart: adding or removing filters and rate limits, changing the trigger interval, changing the logic inside a stateful mapping function.
What you may not change: the schema of any stateful operation — aggregations, stream-stream joins, deduplication — the number or type of input sources, the topic subscription or ingestion path, or the sink type. These are the constraints that turn ‘just change the aggregation’ into a migration with a new checkpoint and a backfill.
And the caveat that undoes the headline guarantee for a great many pipelines: the foreachBatch sink provides at-least-once, not exactly-once. It is documented, and it is widely missed, because foreachBatch is what everyone reaches for as soon as they need to write anywhere the built-in sinks do not cover.
If you use it, your logic must be idempotent. Usually that means a merge keyed on a natural or synthetic identifier rather than an append, or writing the batch ID alongside the data so a replay can be detected.
This is the single most common way a pipeline that everyone believes is exactly-once is not.
Auto Loader: idempotence through a key-value store
File-based ingestion at scale has one hard problem: knowing which files you have already processed when there are billions of them. The mechanism is specific and worth knowing: file metadata is persisted in an embedded key-value store inside the checkpoint location, and that store is what guarantees each file is processed once.
Two consequences follow directly, and they explain most operational surprises:
- The deduplication state has no other home. Delete or lose the checkpoint and you reprocess everything, because there is nowhere else the system could look to find out what it has already seen.
- The guarantee is file-level, not record-level. A file rewritten at the same path is a different question from a file arriving at a new path, and the answer depends on your detection mode.
On detection modes: directory listing is the default and needs no extra permissions, but listing a bucket with millions of objects is the expensive operation. File notification uses the cloud's own notification and queue services and is substantially faster at scale — the system can configure those resources itself. And an explicit non-guarantee worth carrying: file discovery and processing order is not guaranteed in either mode, so any pipeline that assumes files arrive in order is relying on an accident.
Declarative pipelines, and the three dataset types
The declarative pipeline framework separates three things that people otherwise conflate:
| Type | Semantics | When to use it |
|---|---|---|
| Streaming table | Each record processed exactly once, assuming an append-only source | Ingestion and append-only transformation |
| Materialized view | Recomputed as needed to reflect current state; processes only new or changed source data where it can | Aggregations and joins that must reflect updates and deletes |
| View | Evaluated on demand, never persisted | Intermediate logic you do not want to store |
The runtime analyses declared dependencies, executes in order with maximum parallelism, and retries progressively — task, then flow, then the whole pipeline. Expectations are the quality mechanism: boolean constraints with three violation modes — warn and continue, drop the record, or fail the update. Choosing between those three is a data-contract decision rather than a technical one, and it is worth making explicitly with whoever consumes the table.
There is also a change-data flow type that handles out-of-order change events and both slowly changing dimension patterns, which is the piece that removes the most hand-written code from a typical CDC (change data capture) pipeline.
Snowpipe and streaming ingestion
The distinction is file versus row. Classic pipe ingestion is file-based — if your upstream already produces files in blob storage, that is the right tool. Streaming ingestion is row-based with no staged files at all.
- Channels are the key abstraction: they preserve insertion order within a channel and map naturally onto source partitions, such as a message-queue partition. Classic file ingestion has no equivalent, which is why ordering guarantees are one of the real reasons to move.
- Exactly-once works through offset tokens. The application tracks committed offsets and replays from the last committed position on recovery. Which means — and this is the point — the guarantee is a contract with your code, not a property of the service. If your application does not persist and replay offsets correctly, you do not have exactly-once, and nothing will tell you.
- The two generations bill differently: the newer architecture charges per uncompressed gigabyte, while the older one charges compute plus a per-client-instance hourly charge — so an idle client still costs money, which is a genuine trap for a fleet of always-on connectors.
Every exactly-once claim, with its preconditions attached
Structured Streaming — end-to-end exactly-once only when the source is replayable by offset, the sink is transactional or idempotent, and the checkpoint is intact and unmodified. Break any one and you have at-least-once.
foreachBatch — at-least-once, documented. Your logic must be idempotent.
Auto Loader — file-level deduplication via a key-value store in the checkpoint. Not record-level, and gone if the checkpoint is.
Streaming ingestion with offset tokens — exactly-once per channel, and only if your client persists and replays offsets correctly.
Multi-writer upserts in a timeline-based format — only the upsert operation carries the guarantee; plain inserts may produce duplicates even with deduplication enabled.
The general rule: exactly-once in this space always means exactly-once given these preconditions. A candidate who states the preconditions unprompted is telling you they have operated one of these pipelines through a failure.
Where each platform genuinely wins, in 2026
A closing map, because the honest version is not a ranking:
- Databricks — heterogeneous workloads on one engine: distributed ETL, machine learning, agents; multi-cloud; anything that needs code rather than SQL.
- Snowflake — SQL-first analytics with the lowest operational surface, governance that applies uniformly because everything runs in one engine, and frictionless sharing within the platform's population.
- The Microsoft stack — organisations whose gravity is already there and whose destination is a Microsoft BI tool.
- Column-store serving engines — sub-second, high-concurrency queries on append-heavy event data: observability, product analytics, user-facing dashboards. A different latency profile rather than a cheaper warehouse.
- Single-node engines — anything under roughly a hundred gigabytes, where the distributed tax exceeds the work. This is the genuinely under-appreciated one: a great deal of ‘big data’ fits comfortably on one modern machine, and the startup cost of a cluster can exceed the query.
The common thread is that storage stopped being differentiating — everyone reads the same open table formats now. Competition moved to the catalog, to the engine's latency profile, and to the AI runtime bolted on top. Both vendors also acquired or built managed transactional Postgres within a year of each other, on the shared thesis that agentic applications need a low-latency system of record beside the data they reason over, and that no analytical warehouse serves single-row lookups well. That symmetry is more instructive than either product.
Check yourself