Data By Design: The Art of Building a Data Warehouse

architecture Apr 15, 2026

Building a data warehouse starts with design, not with tools. Every architecture I have worked on comes down to three pillars: sources on the left, insights on the right, and a central hub in the middle where data people spend nearly all of their time. Inside that hub there are five core components to settle first: an analytics database to consolidate the data, ingestion to load it, transformation to model it, reporting to expose it, and workflow to repeat the process reliably. Once you know what each component is for, choosing tools becomes mixing and matching rather than guessing. The teams that struggle do it in the opposite order: they pick the tools first and then try to work out where they are going with them.

Key takeaways

Design is the first lesson, not the last one. The layout of the pipeline decides how hard every later decision is.
The three pillars are sources, a central hub, and insights. The hub is the part you actually build.
Five core components fill that hub: analytics database, data ingestion, data transformation, reporting, and workflow.
Each component answers one plain question, starting with where the data will be consolidated.
Transformation is where most of the work lives. In my experience it is 80 to 90 percent of the effort.
Once the design is set, tools are interchangeable. Snowflake with Stitch, BigQuery with Airbyte, and Postgres with Fivetran all produce the same shape.

Why design comes first

I recently asked a group of more than 1,900 data engineers what they found most challenging about the work. The top three answers were best practices and conventions, data modeling, and overall strategy for how the pieces fit together.

None of those are tool problems. They are design problems, and they are what this post is about.

If you do not know where you are going, the details do not help you. That is why I start every architecture conversation at the highest possible level and work down, instead of starting with a product comparison.

The three pillars of data engineering

I have worked on dozens of teams and talked with a lot of engineers, and at the end of the day there are three things we are always dealing with.

Sources. The data the business runs on: APIs, internal databases, cloud applications and SaaS tools, and raw files like CSVs, spreadsheets and text exports.
Insights. The version of that data someone looks at to make a decision. Without this, none of the rest has a point.
The central hub. Everything in the middle that turns the first into the second.

Sources are what the business uses to run its operations. They were never designed for analysis, which is most of why the middle exists at all.

The hub is the part you design

Data people live in the central hub. Ingestion, modeling, workflow, all of the detailed work happens there.

So when you say you are designing a data warehouse, what you are really designing is that hub: the path from sources to insights and the components along it.

The five core components

You can add plenty more to an architecture, but these five are the ones I would start with. Each one answers a plain question.

There are plenty of other things you could add to an architecture. These are the ones I would get right before adding anything else.

1. Where will we consolidate the data?

This is the analytics database, the central place everything lands so you can build warehouse objects on top of it. Common choices are Snowflake and BigQuery, and some teams use Postgres.

2. How will the data get there?

This is data ingestion: taking source data and loading it into that storage in a specific, repeatable way. It is the first component people underestimate, because every source has its own quirks.

3. How will we clean it up?

This is data transformation, where the business logic and the custom code live. Honestly, this is about 80 to 90 percent of our work, and it is the hardest part for most teams.

The strategy I recommend here is a three layer pipeline, moving data through staging, a warehouse layer, and marts before anything reaches a report.

4. How will we analyze it?

This is reporting, sometimes called data visualization. It connects to the modeled data at the end of the pipeline, not to the raw source tables.

5. How will we repeat it consistently?

This is workflow, and it is the component teams skip. It covers version control and environments: the habits that let you change something without breaking production.

Put all five together and you have an end to end strategy, at least conceptually, before a single tool has been chosen.

What you end up with

Go back to the three pillars and the picture is complete. Sources flow into the hub, the five components carry the data across it, and insights come out the other side.

It is a lot to hold at once. It is also the thing you keep returning to every time someone proposes a change to the stack.

Three example stacks

Generic icons only go so far, so here are three implementations I have worked on. Same design each time, different products filling the slots.

Snowflake, Stitch, dbt and Power BI

This client used Snowflake for storage and a tool called Stitch to connect to sources and load into a dedicated database inside Snowflake.

Loading into a dedicated database inside the warehouse is worth noticing. Raw data stays separated from the modeled objects, which is the same isolation a landing zone gives you.

dbt held the transformation code and moved data from staging through warehouse to marts. Power BI connected to those marts, and GitHub handled version control and helped isolate logic across environments.

BigQuery, Airbyte, dbt and Tableau

The second example swaps the ends and keeps the middle. BigQuery for storage, Airbyte for ingestion, dbt and GitHub again, and Tableau connected to the marts.

Postgres, Fivetran, dbt, GitLab and Metabase

The third uses an open source database, and it changes one detail worth calling out. In Postgres you do not usually query across databases, so the separation that Snowflake gets from multiple databases has to come from somewhere else.

The answer is naming. Keep everything in one database, use schemas and prefixes to isolate the concepts, and the same pattern still shows through.

That stack was Postgres, Fivetran, dbt, GitLab for version control, and Metabase for reporting. Same strategy, different tools.

Strategy first, tools second

When the design is in place, tool decisions get easy. You are choosing what fills a slot you have already defined, and you know what the slot has to do.

What a lot of teams do instead is pick the tools and then work out what they want from them. That is how you end up with an expensive stack and no clear path from sources to insights.

Start with the picture. Sources, hub, insights, then the five components inside the hub. The rest is implementation.

Once that is settled, you can go component by component and work out the details of each one, knowing none of those details will contradict the others.

Key terms

Three pillars of data engineering

The highest level view of the work: source data on one side, insights on the other, and a central hub in the middle that connects them.

Central hub

Everything between sources and insights, which is where data teams spend nearly all of their time and the part a data warehouse design describes.

Analytics database

The central storage where source data is consolidated so warehouse objects can be built on top of it, such as Snowflake, BigQuery or Postgres.

Three layer pipeline

The transformation strategy I recommend, moving data through a staging layer, a warehouse layer and a marts layer rather than one giant step.

Workflow

The repeatable process around the pipeline, including version control and separate environments, so changes can be made without breaking production.

Common questions

What is the difference between a data architecture and a data warehouse?

The architecture is the end to end design: where data comes from, how it moves, and how it gets consumed. The warehouse is the central storage and the modeled objects inside it. In practice, designing the warehouse means designing the hub of the architecture around it.

Which component should a small team set up first?

Start with the analytics database, because every other component points at it. Once you know where data will land, ingestion and transformation decisions follow naturally from that choice.

Do I need all five components on day one?

You need all five eventually, but they do not arrive at the same time. Workflow is the one most teams defer, and deferring it is also what makes the first production mistake expensive.

Can I build this architecture on Postgres instead of a cloud warehouse?

Yes, and I have seen it work well for small teams. The one adjustment is separation: Postgres does not query comfortably across databases, so you use schemas and naming conventions inside one database to keep the layers distinct.

Why is transformation the hardest part?

Because it is the only component that encodes what your business actually means. Ingestion and reporting are mostly configuration, while transformation is custom logic that has to be designed, tested and maintained by your team.

Final takeaway

Every warehouse I have built with a small team started as this one diagram: sources, hub, insights, and five components in the middle. Draw it before you shop for tools, and the tool decisions stop feeling like decisions you might regret.

Additional Free Resources

Starter Guides & Checklists

Explore additional free resources built on the same patterns I use with real clients so you can build your own with structure and confidence. Topics include data architecture, modeling and more specifically for small data teams.

Browse Resources