How to Build Modern Data Architectures Faster

The fastest way I know to build a data architecture is to stop building it all at once. Pick one deliverable the business actually uses, plan the pipeline backwards from it, then build forwards from the source tables to that deliverable. You end up with one complete pipeline instead of twelve partial ones, and that pipeline carries your layers, your naming conventions, your testing and your core data model. I call this output led engineering, and the point is not speed for its own sake. Narrowing to a single output is what keeps the scope from running away while still giving the business something real early.

The wasted effort problem

A big moment for any company is deciding to really invest in a data architecture. Maybe it is greenfield, maybe there is something in place that needs an overhaul.

Either way it is exciting and a little overwhelming. There are dozens of sources to set up, expectations from stakeholders, and everything in the middle about how to model it and wire up an end to end pipeline.

Divide and conquer, and what it produces

The common response is to do it all at once. Split the sources across the team, build in parallel, and report progress as the number of models or pipelines built.

That is activity, not production. There is a John Wooden line I come back to here: never mistake activity for achievement.

Doing a lot of things does not mean the things are useful. With no consistent approach underneath, you mostly hurry up to make a mess.

The gap that opens up is between what you built and what is actually used. New stakeholder requests keep arriving, everyone keeps moving, and nobody has time to think about why any of it is being built.

Killing 2,000 reports

An example from my own experience, and the number is not a typo.

At the first company I worked at we had roughly 4,000 SSRS reports. SSRS is Microsoft's reporting tool, and like most reporting tools it makes creating a report very easy.

I could not believe we were using all of them, so I built a report on reports. It identified over 2,000 inactive SSRS reports.

Half of them or more were not used at all anymore, or used once a quarter, once a year, and a huge number were duplicates of each other. All I could think about was the time spent building them.

What we did instead

We decided to build a new data warehouse from scratch, to get away from the ad hoc, one off query habit that produced those 4,000 reports.

We began with the most used and impactful reports. We identified what the business was genuinely relying on and worked backwards from it, rather than aimlessly building from whatever source data happened to exist.

We did not do it all at once. We built the foundations, added on over time, and ended up with something robust that fed a lot of reports people actually opened.

The foundational pipeline

The alternative to scattering is to build one pipeline that goes all the way through, which is what I mean by a foundational pipeline and a minimum viable data model.

You still hit plenty of problems. You just hit them all in one place, and you solve them once:

• The layers of your pipeline and how they relate
• Automation and testing
• Naming conventions across the project
• The core data model: the keys, the relationships, the shape you want

It obviously cannot cover everything. It covers this scope, from beginning to end, which is far more than you get from poking at several pipelines at once.

Then you rinse and repeat. With one working example, the team can run in parallel against a pattern everyone has agreed on, and move much faster than they would have at the start.

Step 1: Narrow the scope to one deliverable

Output led data engineering is just a way to narrow scope by identifying a single deliverable that guides development.

It is not abandoning data modeling or process to rush out a result. The deliverable exists so the work does not get out of control in week one.

Deliverables that work well

• An existing report, dashboard or spreadsheet. The most commonly used one, like the report we started from at that first company.
• A grouping of similar metrics. With one client we picked three related metrics they were actively using and built around them.
• An overly manual process. Something the team does by hand today is a good candidate to pull into the new pipeline.
• A desired data set you do not have. Something the business wants that has to be built from scratch.

Do not overcommit

When you discuss this with stakeholders, resist the temptation to promise more than the one thing. Especially at the start.

The goal is to build the process and the pipeline. The report is how you scope that work, not the finish line.

Step 2: Reverse engineer the deliverable

Now plan right to left. Start at the deliverable on the right and walk backwards. Nothing is being built yet.

The mart

Ask what flat mart or presentation table would deliver that output. What columns do you want, and what are the ways you would want to slice it?

Try to shape it so it is not strictly one to one with this report. If the output is a sales report, what other reports or data sets could read from the same mart?

The fact

Next, what is the main fact behind that mart? By fact I mean the business action: the thing that actually happened.

This is where the star schema approach comes in, and the action you pick is the grain everything else hangs off.

The dimensions

Then the context around that action. Which dimensions do you need to slice the fact the way the mart requires?

Note the dimensions you can imagine adding later too. They are not needed for this deliverable, and seeing where they would plug in tells you the model is sound.

Staging and sources

Staging is one to one with the raw table, so these last two go together. Which source tables do you need to produce everything above?

Identify as few as possible. Only what this deliverable requires, which is the whole reason the scope stays manageable.

This is also where you set naming conventions and the landing zone separation, before anything loads. I cover that groundwork in Fix Your Data Pipeline From The Start.

A worked example

Here is the playground example I use when teaching this. The output is a daily NBA game summary report.

deliverable  daily NBA game summary report
  mart       nba_games_detail      -- also feeds other game level reports
  fact       fct_games             -- the action: a game played
  dims       dim_teams, dim_date   -- context around the action
  staging    stg_nba__games, stg_nba__teams
  sources    only the raw tables those staging models read

The staging convention there is stg, the source name, two underscores, the table name. It tells you immediately that it is a staging model, which source it came from and which table it maps to.

At the end of this step you have a game plan in front of you and you have not written a line of code.

Step 3: Implement left to right

Now reverse direction and build forward:

• Load only the source tables you identified into the landing zone, applying the naming conventions you chose.
• Build the staging layer on top of them.
• Set up the development environment and the workflow you want the team to follow.
• Create the warehouse tables, the fact and dimensions from your plan.
• Build the mart, then the final deliverable on top of it.

By the end you have a full pipeline and an example of more than the modeling. You have development environments, version control, automation and testing, all established by one deliverable.

Then you go back and do more with it. That is the minimum viable data model you rinse and repeat for everything that follows.

Related reading

• The First Step in a Data Architecture (Ingestion)
• How to Create a 3 Layer Data Model Pipeline
• The True Value of a Data Presentation Layer
• Managing the "End" of a Data Pipeline

Final takeaway

This is the approach I take with clients because it answers the two questions that stall a new architecture: where to start and when to stop. Choose one output the business already uses, plan backwards from it, build forwards to it, and let that pipeline become the template for the rest.