Data Storage Architectures

Data storage architecture is the choice of where and how data is physically stored — transactional databases, analytical warehouses, or raw data lakes — matched to how the data will actually be used.

At a glance

• What it is: Choosing among OLTP databases (row-oriented, low-latency writes for transactions), data warehouses/OLAP (columnar, optimized for aggregation), and data lakes/lakehouses (raw or semi-structured, schema-on-read, cheap at scale).
• Why it matters: Running analytical queries directly against a production transactional database is a classic way to slow down — or crash — the system that’s supposed to be handling live transactions.
• For developers: if a BI query is timing out or production database CPU spikes whenever someone runs a report, that’s a storage-architecture mismatch, not a code bug to patch around.
• When to use it: Whenever you’re designing a new workload — pick the storage pattern to match how the data will be read, not just how it will be written.
• When not to use it: Don’t reach for a data lake or warehouse for a small application’s transactional needs — that’s over-engineering a problem an OLTP database already solves well.
• Prerequisites: Data Modeling & Schema Design.

Mental model

graph LR
    OLTP["OLTP database\n(checkout, orders)"] -->|ETL / pipeline| DW["Data warehouse\n(OLAP, columnar)"]
    OLTP -->|raw ingestion| Lake["Data lake / lakehouse\n(schema-on-read)"]
    DW --> BI["BI & reporting"]
    Lake --> BI

Transactional systems and analytical systems have opposite performance goals — one is optimized for many small, fast writes; the other for scanning and aggregating large volumes of data. Trying to make one engine do both well is usually where storage-architecture problems start.

Step-by-step walkthrough

1. Classify the workload: is it transactional (many small reads/writes, needs consistency) or analytical (large scans, aggregation, historical data)?
2. For transactional workloads, choose an OLTP engine sized and indexed for the expected write/read pattern.
3. For analytical workloads, choose a data warehouse (structured, curated) or a data lake/lakehouse (raw, flexible, cheaper at scale).
4. If using a lake, define raw and curated zones so consumers don’t query unvalidated raw data directly.
5. Plan the pipeline that moves data from the transactional store into the analytical store — see Data Integration & Pipelines.

Worked example

Northwind’s finance team runs a month-end report directly against the production order database, and checkout latency spikes during the resulting table scans — right in the middle of a promotional sale.

• Input: one OLTP database serving both live checkout traffic and ad hoc analytical queries.
• Process: separate the workloads — stand up a data warehouse, load it via a nightly ETL pipeline from the OLTP database, and redirect finance’s reporting to the warehouse instead of production.
• Output: checkout latency stabilizes because production traffic no longer competes with analytical scans, and finance gets a system actually optimized for aggregation queries.
• On Azure: checkout stays on Azure SQL Database (OLTP); a nightly Azure Data Factory pipeline copies and reshapes the previous day’s orders into Azure Synapse Analytics (OLAP), which finance’s BI reports query instead of production.

For a system needing low-latency reads across continents (not just DR), the storage choice shifts to a globally distributed database rather than a single-region OLTP store with failover:

resource cosmos 'Microsoft.DocumentDB/databaseAccounts@2024-05-15' = {
  name: 'northwind-catalog'
  location: 'eastus'
  properties: {
    databaseAccountOfferType: 'Standard'
    enableMultipleWriteLocations: true
    locations: [
      { locationName: 'eastus', failoverPriority: 0, isZoneRedundant: false }
      { locationName: 'westeurope', failoverPriority: 1, isZoneRedundant: false }
    ]
    consistencyPolicy: { defaultConsistencyLevel: 'Session' }
  }
}

Product-catalog reads in Europe now hit the westeurope replica instead of round-tripping to eastus, at the cost of accepting Cosmos DB’s weaker (session-level, not strong) consistency guarantee across regions.

Common use cases

Use case	When to use	Notes
Checkout / order processing	Transactional workload with data integrity requirements	OLTP database, normalized schema
Finance / BI reporting	Aggregating large volumes of historical data	Data warehouse with a denormalized (e.g. star) schema
Raw clickstream / ML training data	High-volume, semi-structured, or exploratory data	Data lake or lakehouse, schema-on-read
Global low-latency catalog/session data	Reads and writes need to be fast from multiple continents	Azure Cosmos DB with multi-region writes; accept eventual/session consistency across regions

Advanced details

• Columnar vs. row storage: row storage is efficient for reading/writing whole records (OLTP); columnar storage is efficient for scanning specific columns across many rows (analytics).
• Schema-on-write vs. schema-on-read: warehouses enforce schema before data lands (schema-on-write); lakes accept raw data first and apply structure when it’s read (schema-on-read) — more flexible, but riskier without governance.
• Partitioning and cost tiers: partition large analytical tables by date or key access patterns, and use tiered storage (hot/warm/cold) to control cost as data ages — see Data Lifecycle Management.
• Replication and regions: OLTP engines replicate for both availability and read scaling. Azure SQL Database auto-failover groups replicate to a paired region for disaster recovery (near-zero data loss, automatic failover); Azure Database for PostgreSQL – Flexible Server read replicas offload read traffic to a secondary node, in-region or cross-region; Azure Cosmos DB goes further with multi-region writes — every region accepts writes and Cosmos DB resolves conflicts, trading strict consistency for global low-latency reads and writes. This is the same idea Martin Fowler catalogs as the Leader and Followers pattern, and the same problem ByteByteGo’s Database Middleware guide solves by routing reads to replicas transparently instead of hard-coding routing logic into every application.

Quick reference

Term	Meaning
OLTP	Online Transaction Processing — optimized for fast, frequent, small transactions
OLAP	Online Analytical Processing — optimized for aggregating large volumes of data
Data warehouse	Structured, curated store purpose-built for analytical queries
Data lake	Raw, often semi-structured store, schema-on-read, cheap at scale
Lakehouse	A hybrid combining data-lake flexibility with warehouse-like structure and performance
Read replica	A secondary copy of an OLTP database serving read traffic to reduce load on the primary
Failover group	A managed group of databases that fail over together to a paired region during an outage
Multi-region writes	A distributed database (e.g. Cosmos DB) accepting writes in more than one region simultaneously

Troubleshooting

Symptom	Likely cause	Fix
BI dashboard queries time out	Warehouse/lake schema not designed for the query pattern (missing partitioning/aggregation)	Redesign physical schema (e.g. star schema, partitioning) for the actual query pattern
Production database slows during reporting	Analytical queries running directly against the OLTP store	Move analytical workload to a warehouse/lake fed by a pipeline
Data lake becomes a “data swamp”	No curated zone or schema governance on raw data	Introduce raw vs. curated zones and catalog what’s stored where

Check your understanding

• Why is running a month-end aggregation report directly against a checkout database risky, even if the query itself is correct?
• For a system you know, is its current storage engine matched to how the data is actually queried, or was it just “whatever was already there”?

Related pages

Part of Introduction to Data Architecture. See also Data Integration & Pipelines for how data moves between these stores, Analytics & BI Architecture for what sits on top, Business Continuity & Disaster Recovery for the operational failover/DR mechanics behind these replication choices, and the Glossary for term definitions.