#057: Data Architecture 101: The Modern Data Warehouse

The modern data warehouse is the default end to end architecture for most small and mid size data teams. Sources are extracted and loaded on a batch schedule, once an hour or once a day, into a data lake that holds the raw data exactly as it arrived. A transformation step, usually dbt or plain SQL, turns that raw data into structured, related tables inside the warehouse. From there you build data models shaped for a specific department or reporting need, and the analytics tools read only those. The thing that makes it modern rather than traditional is that the raw landing area is never cleared: it keeps growing as a historical log, which is affordable now that storage is cheap.

Strategy first, tools second

In the modern data landscape it is the tools and the technologies that grab all of the headlines. That is what everyone talks about, and it is the easiest thing to argue about.

The decision that actually matters is your strategy. By strategy I mean the high level approach you take to move data from your sources into something useful for analytics, and everything in between.

There are plenty of approaches. This one is the stereotypical modern data warehouse, and it is the one I see working for most teams.

What the modern data warehouse looks like

The flow has four stops. Each one has a single job, and the handoff between them is what keeps the architecture understandable.

• Sources. Your business applications, databases and files.
• Batch extract and load. On a set cadence, you pull from every source and drop it in one place.
• Data lake. The central landing zone for that raw, unformatted data.
• Warehouse and data models. Transformations that add structure and relationships, then models built for reporting.

The word model here just means a table or a view. These are customized for certain departments or certain reporting needs, and the analytics layer pulls from them.

So the chain reads backwards cleanly. Reports come from data models, which come from the warehouse, which is a clean version of the data lake, which was batch loaded from your sources.

Pick the cadence first

Once an hour, once a day, once a week: the cadence is a business question before it is a technical one. It sets what every report downstream can promise.

Everything else in this architecture works the same no matter which you pick, which is part of why it is easy to live with.

How it differs from a traditional pipeline

If this sounds a lot like a traditional pipeline, it is. The components rhyme, and the difference is narrower than people expect.

The difference is what happens to the landing zone. In a traditional pipeline, the temporary landing area gets cleared with every run. Each batch starts from an empty slate.

In the modern data warehouse, you load into the data lake and leave it there. It is a historical log that keeps growing, and transformations pull from it as needed for whatever day or hour they are processing.

Why that changed

Two things. There is far more data being created and loaded now, and storage has become cheap.

In the past, keeping every raw load around was a real cost, so clearing it out each day was the sensible priority. That constraint mostly went away, and the architecture followed.

What a data lake actually is

The term data lake is a concept and an approach more than a specific technology. Some platforms and vendors use the name, but the idea is storing everything raw in one central location.

There are two ways I see teams build it.

Option 1: a database

Allocate a separate schema, or a separate database entirely, to hold all of your raw loads. Then a second database handles the warehouse, and maybe a third holds the data models.

This is the simplest version. Everything is in one platform and there is nothing extra to query across.

Option 2: cloud object storage

Use something like Amazon S3 or Azure Blob Storage as the lake. This makes sense when you have objects you cannot put directly in a warehouse, such as video files or images, but still want everything landing in one location.

Either way, the point is the same. One central place for raw data, clearly separated from the structured layers downstream.

Example tool stacks

These are examples, not recommendations. I am also leaving out version control, orchestration and containers on purpose, so the core architecture stays visible.

Airbyte into Snowflake, with dbt

Use Airbyte to batch load a handful of sources and business applications. In this version the lake, the warehouse and the data models all live inside a Snowflake cloud database, separated by database or schema.

Then dbt runs the SQL that builds and updates tables and views in the warehouse layer. A third layer turns those warehouse models into user facing models, also with dbt, and Tableau reads from there.

The same stack with S3 in front

Same concept, but instead of landing directly in Snowflake you load into Amazon S3. Snowflake has built in connectors to copy that data into the warehouse, and everything downstream stays the same.

A Microsoft stack with no dbt

Use Azure Data Factory to connect to the sources and load them into Blob Storage. Then use Data Factory again to transform that into SQL style database tables, and connect Power BI on top.

Different vendors, same shape. That is the whole point of thinking in architecture rather than in tools.

Why this is my default recommendation

This is the most common approach I have seen, and the simplest one to get started with. For most small and mid size companies establishing an architecture for the first time, it is the right place to begin.

It is also worth remembering that most companies are not big data enterprises and do not need overly complex systems. Avoid the urge to keep up with big tech companies if it does not apply to you, which is probably the case.

I would take simplicity and clarity over complexity any day.

What to add once the core is working

The examples above leave out several things on purpose, so the core path stays visible. They are not optional forever, just later.

• Version control. History of every change to your transformation code, and the base for automated testing.
• Orchestration. Coordinating the load and the transformation runs once the dependencies between them matter.
• Containers and environments. Giving developers a safe place to work that matches production.

Each one slots alongside the four stops rather than changing them. That is the benefit of settling the architecture before the tooling.

Related reading

• Data Architecture 101: The 5 Key Questions
• Data Architecture 101: Lambda Strategy (Stream + Batch)
• Data Architecture 101: Kappa (Real-Time Data)
• Data Warehouse vs Data Lake, Explained

Final takeaway

When I come into a small data team that is starting from scratch or cleaning up a sprawl of tools, this is the architecture I put in place first. Get the four stops clear and separated, then argue about tools.