Data Warehousing & Analytics Engineering

A second database, on purpose

The first surprising thing you learn about analytics is that your application’s database is the wrong place to do it. The database that runs your product is exquisitely tuned for one job: small, fast operations on a few rows at a time. Add an order. Update an email. Read one cart. Ask it instead to sum three years of revenue grouped by country and it will grind, lock resources, and slow the app for the customers who are actually trying to buy something.

So you build a second database, on purpose, with the opposite priorities. It is columnar instead of row-oriented, so a query that needs one column out of forty reads only that column and skips the rest. It compresses aggressively because each column holds values of one type. It is built to scan billions of rows and return an aggregate, not to fetch a single record by key. This is the data warehouse, and the gap between it and the application database - between OLAP and OLTP - is the reason the whole discipline exists.

Shaping data for humans

A warehouse that merely copies the application’s tables is a missed opportunity. Raw operational schemas are designed for the app, not for analysis, and querying them directly is a slog of obscure joins and half-remembered business rules.

Dimensional modeling, the approach Ralph Kimball laid out decades ago, fixes this by reshaping data into a form that is both fast and legible. You split the world into facts - the events that happened, long and narrow tables of measurements and keys - and dimensions - the context around those events, the customers and products and dates you filter and group by. Arrange a fact in the center with its dimensions around it and you get a star schema: few joins, every one between a big table and a small one, and table names a businessperson can actually read.

The deceptively important part is grain - deciding, in plain language, exactly what one row of a fact table represents before you write a line of SQL. One row per order? Per order line? Per daily snapshot? Get it wrong and you will double-count revenue in ways that take days to debug. Get it right and the rest of the model almost designs itself. Decades on, this vocabulary - facts, dimensions, grain, slowly changing dimensions - is still the lingua franca of the field.

The economics that changed everything

For most of warehousing history, the warehouse was a single expensive machine where storage and compute were welded together. You sized it for your busiest hour and paid for it around the clock. Scarcity shaped every decision, including the order of operations: you transformed data on a separate server first and loaded only the clean result, because warehouse compute was too precious to waste and storage too dear to fill with raw junk. That was ETL.

Then cloud warehouses - Snowflake, BigQuery, Redshift - pulled storage and compute apart. Storage became almost free; compute became elastic and rentable by the second. The moment it cost nothing to keep raw data and you could summon huge compute for a few minutes, the old logic collapsed. The pattern flipped to ELT: extract, load the raw data immediately, and transform it inside the warehouse with SQL.

Reordering two letters does not sound like a revolution, but it was. Keeping the raw data means that when you find a bug in a transformation - and you always do - you fix the SQL and re-run, instead of scrambling to re-extract data that may no longer exist at the source. A new question from the business becomes new SQL over data you already have, not a new pipeline. The trade was a little storage cost for an enormous amount of flexibility, and it was not close.

SQL, written like software

What ELT created was a vacuum where a craft now lives. If transformation happens in the warehouse, in SQL, then someone has to own that SQL - and own it well, because the whole business will make decisions on its output. That someone is the analytics engineer, and the tool that made the role practical is dbt.

dbt’s genius is modest: it lets you write each transformation as a plain SELECT, reference other models by name, and from those references work out the build order itself. But around that simple core it imports the entire discipline of software engineering into a world that had been getting by on ad-hoc scripts and tribal knowledge. Models are layered from raw to staging to marts, so logic is written once and reused. Tests assert your assumptions - this key is unique, every foreign key points at a real parent - and fail the run when reality disagrees, so bad data never reaches a dashboard. Documentation lives beside the code and generates a lineage graph anyone can read. And the whole project sits in Git, which means data logic finally becomes something you review, branch, and trace through history.

That is the quiet transformation underneath all the tooling. The warehouse stopped being a place where data is parked and became a place where code is written, tested, and shipped. The skill that matters most now is not knowing the cleverest query - it is the unglamorous discipline of modeling data clearly, testing it relentlessly, and serving numbers people can trust. Build that well and nobody downstream ever has to wonder whether the dashboard is lying. That, in the end, is the entire job.