OLTP vs OLAP

Classify the workload first

An order checkout and a five-year sales report may read the same business data. They ask the database to do different work.

Online transaction processing (OLTP) handles transactions such as recording an order, updating inventory, or changing a customer account. AWS describes these systems as optimized for transactional processing and real-time updates. The operations are usually small and frequent.

Online analytical processing (OLAP) supports reports and analysis over large volumes of data. Its queries group, summarize, and compare historical data. A sales dashboard might run this query:

SELECT region, SUM(revenue)
FROM sales
WHERE sold_at >= DATE '2025-01-01'
GROUP BY region;

The access pattern determines whether this is transactional or analytical; both workloads can use SQL.

Wrong "OLTP means SQL, while OLAP means NoSQL."

Right Both terms describe processing workloads. AWS documents OLAP systems that use relational databases as well as multidimensional models. OLTP commonly uses relational tables too.

Why applications and reports pull in opposite directions

A transactional application needs to keep accepting small reads and writes. An analytical store is designed for read-heavy analysis over large data volumes. Batch loading is common in warehouses, and some analytical systems also ingest continuously.

Running a large report against the production transaction store can make the two workloads compete for database compute and I/O capacity. A common architecture feeds a separate analytical store from the OLTP system. The engine, query load, and freshness requirements determine whether that separation is useful.

In that architecture, a pipeline copies and reshapes operational data for reports. The analytical store can also combine the OLTP data with other sources.

Wrong "A database that can execute GROUP BY is already the right place for every dashboard."

Right SQL feature support and workload suitability are separate questions. Some products support both OLTP and complex OLAP work. Whether to separate them depends on the engine and the workload they must handle together.

Classify the operation before naming a technology. Recording one order is transactional. Grouping years of orders by several dimensions is analytical.

The bytes a query must read

Suppose sales has fifty columns, but a report uses only sold_at, region, and revenue:

SELECT region, SUM(revenue)
FROM sales
WHERE sold_at >= DATE '2025-01-01'
GROUP BY region;

In a row-oriented layout, the fields of each row are kept together. That fits operations that fetch or change a complete record. In a columnar layout, values from each column are kept together. Systems such as ClickHouse divide those values into compressed blocks, so the analytical query can read the referenced columns without reading every field from every row.

ClickHouse documents two related benefits of columnar storage. A query can skip unreferenced columns, and values of the same type stored together compress well. Block metadata can also let an engine skip blocks that cannot match a filter. These are properties of the documented columnar read path, not a promise that every analytical query avoids a scan.

Wrong "Columnar means every aggregate uses an index."

Right Columnar storage reduces the data read when a query touches a subset of columns. Indexing, block skipping, sorting, and the query plan remain separate concerns.

Normalized operations and dimensional analysis

Normalization stores data in a way that reduces repetition. An operational sales table can hold a product_id rather than repeating the product's name, category, and other attributes on every sale.

Analytical models commonly arrange data as facts and dimensions. A conventional fact table stores observations or events, dimension keys, and numeric measures. A dimension table describes an entity used to filter or group the facts. Microsoft gives products, people, places, and time as dimension examples.

Before drawing tables, state the fact grain:

-- Grain: one row per completed order line
CREATE TABLE fact_sales (
  sold_date_key INTEGER,
  product_key INTEGER,
  region_key INTEGER,
  quantity INTEGER,
  revenue DECIMAL(18, 2)
);

dim_date, dim_product, and dim_region surround that central fact table in a star schema. The dimension tables provide fields for filtering and grouping; the fact table provides values to summarize.

A snowflake schema normalizes a dimension across related tables. Product, subcategory, and category might become three tables instead of one product dimension. A star still has fact and dimension tables. Snowflake describes a dimensional schema shape, not a separate workload class.

Wrong "OLTP is normalized and OLAP is denormalized, with no exceptions."

Right Those are common design directions, not definitions. Microsoft documents normalized fact and dimension tables in star models, normalized snowflake dimensions, and cases where analytical systems benefit from further denormalization.

Storage layout and logical schema solve different problems. Row versus columnar changes how values are physically grouped for access. Normalization versus star or snowflake changes how business facts and descriptive attributes are modeled.

A shared database has more than one conflict

Saying only that reports are slow misses the storage, schema, and processing conflicts.

AWS defines online transaction processing (OLTP) around transactional updates and online analytical processing (OLAP) around complex analysis and reporting. ClickHouse documents columnar storage as a layout for queries that scan a few columns across many rows, while row layout keeps a record's fields together. Microsoft documents star models around fact tables for summarization and dimension tables for filtering and grouping.

Those choices line up around different workload shapes. An application path tends toward frequent small reads and writes against an operational schema. An analytical path tends toward scans and aggregations against data loaded into an analytical schema.

One product may support both paths. AWS lists Aurora as able to run OLTP and complex OLAP workloads. The engine and the combined workload determine whether sharing a deployment is a good fit.

Wrong "One database can never serve both OLTP and OLAP."

Right Treat separation as an architectural response to competing access patterns, schema needs, and processing load. Evaluate the chosen engine and workload. Do not turn a common architecture into a universal prohibition.

ETL and ELT differ at the transformation boundary

A warehouse pipeline extracts source data, transforms it into a useful analytical model, and loads it into the target. The two common orderings differ in where transformation occurs.

With ETL, a separate transformation engine applies business rules before the target load. Microsoft lists filtering, sorting, aggregation, joining, cleaning, deduplication, and validation among possible transformations. With ELT, source data is loaded first, and the target data store uses its own processing capabilities to transform that data.

Microsoft recommends choosing between them from requirements. ETL can offload transformation from a constrained target or use a specialized engine. ELT fits a sufficiently capable target when transformations benefit from its native processing or raw data must be retained.

Wrong "ETL means batch, while ELT means real time."

Right The names specify the position of transformation relative to loading. Microsoft treats real-time streaming as another processing architecture, separate from the ETL versus ELT distinction.

The pipeline does not choose the dimensional model

Neither ETL nor ELT automatically produces a star schema. The transformation logic has to establish the model, including a consistent fact grain.

For sales analysis, decide whether one fact row represents an order, an order line, a daily product total, or another observation. Microsoft defines fact granularity from the dimension key values, and it recommends loading fact tables at a consistent grain. After that choice, measures and dimension keys have precise meaning.

A star keeps each dimension directly related to the fact. A snowflake normalizes one dimension into multiple related tables. Both can represent analytical data. The target system and the queries it must support inform the choice.

Wrong "Adding a read replica turns the operational model into a warehouse."

Right Amazon RDS defines a read replica as a read-only copy that can take queries from the primary. Its documented operation uses the database engine's replication features and copies the source databases. That operation includes no columnar conversion or dimensional remodeling.

Keep the distinctions separate during design review: workload is OLTP or OLAP, physical layout may be row or columnar, logical modeling may be normalized or dimensional, and ETL versus ELT says where transformation executes.