How do point-write OLTP workloads and scan-heavy analytics workloads pull a schema in opposite directions?
answer
- how many rows does each query touch?
- and how many columns of them?
- writes rewrite indexes; scans do not
- join count is the analytics cost
basics
~20 sOLTP touches few rows but most of their columns and writes constantly, which rewards narrow single-purpose tables. Analytics scans millions of rows across a handful of columns and never updates them, which rewards fewer, wider tables and fewer joins.
solid answer
~50 sThink about what one query touches. An operational query fetches a single order and most of its columns, or updates one row's status; its cost is dominated by lookups and by how much has to be rewritten. That workload rewards **narrow tables and one place per fact**, because a wide row with copied attributes makes every small write rewrite more data, touch more indexes and hold locks longer. An analytic query reads hundreds of millions of rows but only three or four columns of each, aggregates them, and writes nothing. Its cost is dominated by rows scanned and by join fan-out across large tables. That workload rewards **wide tables and few joins**: extra descriptive columns are close to free for a reader that never asks for them, while an extra join on a huge table is not. So the same data pulls in opposite directions — and the deciding factor is the access pattern, not the data itself.
code
sql · 10 lines-- Operational: one row, most of its columns, thousands per second
SELECT * FROM orders WHERE order_id = 91827;
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 91827;
-- Analytic: hundreds of millions of rows, three columns, read-only
SELECT d.region_name, t.month_name, SUM(f.amount) AS revenue
FROM fct_order_line f
JOIN dim_customer d ON d.customer_key = f.customer_key
JOIN dim_date t ON t.date_key = f.order_date_key
GROUP BY d.region_name, t.month_name;go deeper
Be able to describe the two typical queries: one fetches or updates a single row with most of its columns, the other scans millions of rows across a few columns and aggregates them.
Explain both costs. Say why width hurts the write path — more bytes, more index maintenance, longer locks, more writers of the same fact — and why join count hurts the read path.
Bring in join fan-out as a correctness risk, not just a cost, and be able to argue when a specific attribute is worth flattening based on who pays for it and who benefits.
Frame the decision as workload characterization rather than preference: state which system serves needle queries, which serves haystack queries, and refuse to let one schema be asked to do both without an explicit account of what each side loses.
## Start from what a single query touches The cleanest way to see why the two schemas diverge is to describe one representative statement from each workload along two axes: **how many rows** it touches, and **how many of each row's columns**. - Operational: `SELECT * FROM orders WHERE order_id = 91827` and `UPDATE orders SET status = 'SHIPPED' WHERE order_id = 91827`. One row, most of its columns, thousands of times a second, with writes interleaved. - Analytic: revenue by region by month across three years. Hundreds of millions of fact rows, three or four columns, run a few hundred times a day, read-only. Almost every schema difference between the two worlds falls out of that contrast. ## Why the write path punishes width In a transactional system, denormalizing has a cost that has nothing to do with reads. If you copy a customer's region name onto every order row, then: - Every insert writes more bytes, and every index over the copied column has to be maintained. - A change to the underlying value becomes a bulk update over many rows — a long transaction holding locks that block the very traffic the system exists to serve. - Several independent code paths can now write the same logical fact, which is how copies start disagreeing. So the transactional shape is narrow tables joined by keys, with indexes tuned for selective lookups. The engine's job is to find one row fast and change it with minimal collateral work. ## Why the read path punishes joins In the analytic system there is no write path to protect — only bulk loads. The costs that remain are: - **Rows scanned.** Aggregations are proportional to the volume passing through them. - **Joins over large inputs.** Each join adds cost, and each one is an opportunity to fan out rows and inflate a `SUM` if the join is not at the grain the author assumed. This is a *correctness* risk as much as a performance one. - **Human cost.** A question requiring seven joins is a question most analysts will get subtly wrong at least once. Meanwhile the cost of carrying extra columns is low: analytic engines are built to read only the columns a query names, so twenty unused descriptive attributes on a dimension are mostly a storage line item rather than a query tax. (Exactly *how* the engine achieves that is the analytical-engine topic; for modelling purposes the relevant fact is simply that unread columns are cheap.) Put those together and the analytic shape is few, wide tables: measures at a declared grain, descriptive attributes flattened alongside the keys that filter them. ## Selectivity versus volume Another way to phrase the split is that OLTP is a **needle** workload and analytics is a **haystack** workload. Needle queries want structures that let the engine skip almost everything and land on one row; haystack queries expect to touch a large fraction of the table and care about how efficiently they can stream and reduce it. A schema optimized for needles — many small tables, many indexes, high normalization — makes the haystack query assemble its rows from pieces. A schema optimized for haystacks — few tables, wide rows, redundant attributes — makes the needle update rewrite far more than it needed to. ## Concurrency and freshness pull too Two more forces run in the same direction: - **Concurrency profile.** OLTP has many concurrent short transactions competing for the same rows; contention design (short transactions, small footprints) shapes the schema. Analytics has few concurrent long queries and no row-level contention worth speaking of. - **Mutability.** Operational rows are updated in place; analytic tables are appended to or rebuilt. That is why analytics can model change as new rows rather than as edits — an option OLTP cannot afford at the same scale. ## The practical consequence for a modeller When you are deciding whether to flatten an attribute into a wide table, ask who pays. If the payer is a write path with many concurrent small transactions, flattening is expensive and probably wrong. If the payer is a scheduled bulk load and the beneficiary is every reader of the table, flattening is usually right. The data is the same; the answer changes with the workload — which is precisely why the same business entity is modelled twice, once in each system. ## Answering this in an interview Give the two representative statements, name rows-times-columns for each, then name the cost each shape avoids: write amplification and copy divergence on one side, join count and scanned volume on the other. Candidates who reduce it to "analytics has more data" miss the point — volume alone would not change the shape if the access pattern were the same.
- Isn't the real difference just that analytics has more data?No — volume amplifies the difference but does not create it. Even at modest size, a scan-and-aggregate query wants few joins and a filterable wide row, while a point update wants a narrow row it can rewrite cheaply. Change only the access pattern, hold volume constant, and the preferred shape still flips.
- Why is fan-out from an extra join a correctness problem and not only a performance one?Joining a fact to something that is not one row per fact key multiplies the fact rows, and every subsequent SUM counts the same measure several times. The report looks plausible and is simply wrong. Fewer joins, at a grain the model declares, removes whole classes of that mistake.
- Does this mean indexes are pointless in an analytics model?Not pointless, but far less central. Analytic access is dominated by scanning and reducing large row sets rather than seeking single rows, so the shape of the model and the grain of its tables do more for query cost than secondary indexes do. Physical tuning is an engine topic, and it comes after the model is right.
saying these in an interview costs you the question
- Argues the only difference is data volume
- Claims wide tables are always faster in every system
- Ignores write amplification when denormalizing an OLTP table
- Thinks join cost is the sole difference between the workloads
- Assumes unused columns cost as much to read as used ones