Guide

Operational and analytical data

For most apps the hard part is data, not CPU: its size, its changes, and keeping it right through failures.

Backends and data users

The frontend runs on the user's device and holds one user's data. The backend holds everyone's data, so the hard problems are there. Backend code is usually stateless: it forgets each request, so anything that must last goes into databases, caches, or queues. Engineers build features on it, analysts make reports, and data scientists build things like recommendations.

OLTP vs OLAP

OLTP (online transaction processing) serves users: look up a few records by key (a point query) and change single records. Its queries are fixed in the app code. OLAP (online analytical processing) serves analysts: scan millions of rows to compute totals and averages. Its queries are ad hoc and cover history, not just the latest state.

Data warehouse and ETL

Analysts should not query live databases. The data is split up, laid out for single records, big scans slow users, and access may be blocked. So companies copy it into a separate warehouse with ETL: extract, transform, load. ELT loads first and transforms inside the warehouse. HTAP does both in one system, but suits single apps, not a company-wide warehouse.

Data lakes and beyond

Data scientists often use Python, R, or Spark, since SQL handles text, images, and model features poorly. A data lake holds raw files of any kind, with no fixed schema, so each team shapes the data its own way. It often sits on cheap object storage. Streams give results in seconds; reverse ETL pushes results, like a model, back into the app.

Systems of record and derived data

The system of record holds the true version of the data. Caches, indexes, and models are derived: you can rebuild them, but something must keep them up to date.

Remember

  • OLTP: few rows, fixed queries. OLAP: many rows, open questions.
  • Analytics runs on a separate copy fed by ETL.
  • Lakes keep raw files; reverse ETL feeds results back.
  • If a cache and its source disagree, the source wins.