Operational and analytical workloads pull a schema in opposite directions. Understand why you separate them, and why analytics databases store data by column instead of by row.
OLTP (online transaction processing) handles the day-to-day writes of an application — place an order, update a profile — with many tiny, concurrent transactions. OLAP (online analytical processing) answers big questions over history — revenue by region by month — scanning millions of rows. Their access patterns are so different that forcing both onto one database makes both slow, so data engineers move data from the OLTP source into a separate analytical store.
OLTP (the app's database) OLAP (the warehouse)
Typical query "get/insert THIS order" "sum revenue by month, 3yrs"
Rows touched one / a few millions
Writes constant, small bulk loads / appends
Users app + customers analysts + dashboards
Optimized for fast single-row read/write fast large aggregation
Shape normalized (3NF) denormalized (star)
Running heavy analytics on the OLTP DB slows the app for real users.
So you REPLICATE data into an OLAP store built for scans. That copy job
is data engineering.The deepest reason OLAP systems are fast is physical: they store data by column, not by row. Analytical queries read a few columns across many rows, so keeping each column together means the engine reads only what it needs and compresses it heavily. Row stores, ideal for “fetch this whole record,” would read every column of every row for the same query.
Query: SELECT avg(amount) FROM sales; (needs ONE column, all rows)
ROW store (OLTP) — rows kept together:
[id,date,region,amount][id,date,region,amount]...
-> must read id,date,region too, just to reach amount. Wasteful for scans.
COLUMNAR store (OLAP) — each column kept together:
id: [1,2,3,...]
amount: [40,20,99,...] <- read ONLY this contiguous block
-> reads just 'amount', and similar values compress ~10x.
Columnar = the single biggest reason warehouses scan billions of rows fast.The movement itself is a core data-engineering responsibility. Historically this was ETL (extract, transform, then load); modern cloud warehouses are powerful enough to flip it to ELT (load raw, then transform in the warehouse with SQL). Either way, the source stays operational and the warehouse holds the modeled, query-ready copy.
Source (OLTP) --extract--> Warehouse (OLAP)
|
ETL: transform BEFORE load (in a pipeline) -- older, when warehouses were weak
ELT: load raw, transform INSIDE the warehouse with SQL -- modern default
Modern stack: ingest raw -> ELT with dbt (see the dbt course) ->
star-schema marts for BI. This course models the target; the dbt and
Data Engineering courses build the movement.