Data and analytics: from OLTP to lakehouse

Somebody ran a heavy report against the application database and checkout latency doubled. Somebody presented a revenue number and somebody else presented a different one. Both situations come from the same place: analytical data and transactional data were treated as if they were the same thing.

OLTP and OLAP: two worlds#

OLTP is the application database. Many small transactions, each touching few rows, reads and writes mixed together, millisecond latency. Row storage, because you almost always want the whole row.

OLAP is the analytics world. Few queries, each scanning millions of rows and aggregating, and you tolerate seconds. Column storage.

Why columnar changes everything: if you want the sum of one column over a hundred million rows, in row format you read everything, including the forty columns you do not care about. In columnar, you read only that column.

And because values in the same column are similar, they compress far better: ten times is common. Fewer bytes read is less time and, in the cloud, literally less money, because billing is frequently per byte processed.

That is why running a report against the application database is bad in two ways: it is slow, because the format is wrong; and it is dangerous, because it consumes the same resource that serves the paying customer.

The names you will run into: Parquet and ORC as columnar file formats; DuckDB for local analysis, which is surprisingly efficient; ClickHouse for real-time analytics; BigQuery, Snowflake and Redshift as managed warehouses.

OLTP: the application database

• ·Many small transactions, few rows each
• ·Row storage: you want the whole row
• ·Millisecond latency, reads and writes mixed
• ·Normalised: each fact lives in one place

OLAP: the analytics world

• ·Few queries scanning millions of rows
• ·Column storage: reads only the column you need
• ·Compresses ten times better, and in the cloud that is money
• ·Denormalised on purpose: the star schema

Modelling: normalise or denormalise#

In OLTP you normalise. Each fact lives in one place, and you avoid update anomalies. Third normal form solves nearly everything.

Two frequent traps:

Soft deletes. A deleted column looks harmless and contaminates every query forever. Somebody will forget the filter, and the bug will be subtle.

Storing a mutable value only by reference. The product price changed, and the two year old order now shows the wrong amount. An order stores the price that was charged, always. That is not improper denormalisation: it is a historical record.

In OLAP you denormalise on purpose. The model is the star schema: at the centre, the fact table, with one row per event, the numeric measures and the keys. Around it, the dimensions: customer, product, time, store, each wide and descriptive.

It works because the analyst writes a simple join and the engine optimises it well. You trade normalisation for speed and clarity, and in a system where nobody updates rows, duplication creates no anomalies.

And the concept that confuses people: the slowly changing dimension, or SCD. The customer moved city.

Type 1: you overwrite and lose the history, and last year's report now shows the new city.

Type 2: you create a new row with start and end dates, and keep the history, and last year's report stays correct.

Type 2 is more work and is what you want when somebody asks "how much did we sell in São Paulo in 2023".

How the data gets there#

ETL vs ELT. ETL is the classic order: extract, transform outside, load ready. It made sense when the destination was expensive and limited.

ELT flips it: extract, load raw, and transform inside the destination using its power. It is the standard today, and the practical advantage is large: if the transformation rule was wrong, you reprocess from the raw data that is already there, without extracting from the source again.

Batch vs streaming. Batch processes in windows: hourly, daily. Simple, cheap, easy to reprocess. Streaming processes event by event, with seconds of latency, and it is far more expensive in complexity: late events, time windows, state.

The right question before choosing streaming: will anybody make a different decision because they know this now instead of in an hour? Most of the time, no.

CDC (change data capture) is how data leaves the transactional database today. Instead of a "select everything" every night (which is heavy, misses deletes and misses intermediate states), you read the database's own replication log. You receive every insert, update and delete as an event.

The caution with CDC on Postgres: the replication slot. If the consumer stops, the database holds the WAL for it, and the disk fills up. It has taken down plenty of production databases. Monitor slot lag.

Warehouse, lake and lakehouse#

Warehouse: structured and governed, with schema on write. Great for consumption, rigid for new data.

Lake: raw files on cheap storage, with schema on read. Flexible, and it turns into a swamp if nobody governs it.

Lakehouse: the synthesis. Open files on cheap storage plus a metadata layer (Iceberg, Delta, Hudi) that brings transactions, time travel and schema evolution.

Today, starting from scratch, it is the default I would pick.

And the layered organisation: bronze is the raw data, exactly as it arrived, immutable: it is your safety net for reprocessing. Silver is clean, typed, deduplicated. Gold is aggregated and ready for the business.

It looks like bureaucracy until the day a rule comes out wrong. Then it saves your week, because you reprocess from bronze.

Trust in the data#

The most common way a pipeline breaks is not a bug: it is somebody on the application team renaming a column without knowing that ten dashboards depended on it.

A data contract is the explicit agreement between producer and consumer: schema, meaning of each field, freshness and volume guarantees, and the change process. With a contract, breaking it becomes a detectable violation in the producer's pipeline, not a surprise on Friday.

Data testing is different from code testing: you test the data that came through, not the function:

→ Schema: are the columns and types there?

→ Volume: is today's row count in the expected range?

→ Distribution: are averages and null counts similar to yesterday?

→ Business rules: nothing negative, nothing in the future, keys line up.

Freshness is the most important metric and the most forgotten: when was this table last updated? Stale data served as current is worse than missing data, because everybody notices missing data.

Idempotency is the property that separates a professional pipeline from an artisanal one: running it twice gives the same result. The standard technique is overwrite by partition: instead of inserting, you rewrite that day's partition entirely. Reprocessing becomes trivial, and you will reprocess far more often than you think.

Events, partitions and the single metric#

If you are an application developer, your biggest contribution to the data team is emitting good events: a past tense name (OrderConfirmed, not ConfirmOrder), a unique identifier for deduplication, the instant it happened separate from the instant it was processed, a schema version, and the data that describes the fact, not references that will change later.

And use the outbox pattern, so the event and the write are atomic.

Partitioning on the analytical side: files sit in folders by date, and the query that filters by date reads only those folders. That is partition pruning, and it is the difference between scanning two gigabytes and two terabytes, which in the cloud is the difference between cents and hundreds of dollars on the same query.

Watch out for the opposite extreme: partitioning by something high cardinality generates millions of tiny files, and then the cost becomes opening files. The target is files of a few hundred megabytes.

And the most political problem: what is this month's revenue? Marketing has one number, finance has another. Neither is wrong. They defined it differently: with or without tax, with or without cancellations, on the order date or the payment date.

The solution is a semantic layer: the metric is defined once, in code, versioned, and everybody consumes from there. Tooling helps; what is essential is that somebody owns the definition.

Against the hype#

For most companies, the right stack is: a managed warehouse, dbt for transformation in versioned SQL, a simple orchestrator and a BI tool. Full stop.

You do not need Kafka, Spark and a data mesh for thirty gigabytes of data. Thirty gigabytes fit comfortably in DuckDB on your laptop, and answer in seconds.

Data mesh, specifically, is an organisational model: domains owning their own data as a product. Without the organisational change, adopting the tools just distributes the mess.