Analytical store vs transactional store
Verdict: The transactional store is row-based and schema-agnostic. The analytical store is column-major, schematized, and auto-synced one way from the transactional store. There is no reverse sync.
| Criterion | Transactional store | Analytical store |
|---|---|---|
| Layout | Row-major, schema-agnostic | Column-major, schematized; optimised for scans |
| RU impact | Point reads and writes consume RUs | Synapse queries do not consume the container's RUs |
| Sync | Source of truth | Auto-synced one way from transactional; no reverse sync |
| Versioning | n/a | Does not create prior versions on overwrite |
Rules
- Analytical store is column-major and schematized; the transactional store is row-based and schema-agnostic.
- Analytical store auto-syncs one way from the transactional store; write back to the transactional store with the Cosmos DB OLTP SDK from Spark.
- Analytical store does not create prior versions on overwrite; snapshot-plus-delta has lower storage cost than full-copy versioning.
Traps
- There is no reverse sync from analytical to transactional; you cannot write directly to the analytical store from Spark.
- Analytical-store queries via Synapse do not consume the container's provisioned RUs.
- Analytical store does not itself version items on overwrite; use event sourcing or snapshot-plus-delta for history.