POWER BI | Inside the Golden Dataset: Building a Maintainable ETL Pipeline

The Story Continues

In the previous article, I explained why I stopped building individual reports and started building a Golden Dataset instead.

The goal wasn’t to create yet another dashboard.

It was to create a single source of truth that every report could trust.

But that naturally leads to the next question.

How do you build a dataset that can survive years of change?

Not just new reports.

New business requirements.

New data sources.

New systems.

Even organisational changes.

This article is the story behind that foundation.

The Real Challenge Was Never Power Query

When people hear “ETL”, they often imagine importing data from one system into another.

That wasn’t my reality.

The reporting solution combined information from many different business domains.

Claims.

ERP exports.

Project management systems.

Organisational structures.

Business services.

Order management.

Historical monthly snapshots.

Each system spoke its own language.

Each contained only part of the story.

None of them were useful on their own.

The challenge wasn’t loading data.

The challenge was creating one consistent business model from many independent sources.

Every Report Started From Scratch

At the beginning, every new dashboard followed the same pattern.

Import the monthly files.

Connect to the project data.

Load organisational information.

Merge business mappings.

Transform the data.

Create business rules.

Validate the result.

Then build the visuals.

The first report was exciting.

The second one was manageable.

By the third or fourth report, I realised I was solving exactly the same problem again and again.

Every dashboard repeated the same expensive preparation process.

The same transformations.

The same business logic.

The same joins.

The same validations.

Not only was this time-consuming to develop.

It also meant every report refreshed independently, processing the same large datasets over and over again.

As the number of reports grew, so did the computational cost.

That wasn’t sustainable.

Designing for Reuse Instead of Repetition

That was the moment I changed my perspective.

Instead of asking:

“How do I build the next report?”

I started asking:

“How do I build this only once?”

The answer became the Golden Dataset.

One ETL process.

One business model.

One place where every transformation happens.

Every report simply consumes the result.

From that moment on, adding a new dashboard no longer meant rebuilding the entire data preparation process.

It meant reusing a trusted foundation.

Building a Pipeline That Could Grow

Creating that foundation required much more than connecting a few files.

The reporting process relied on monthly snapshots collected over several years.

Those files shared a common structure, but they represented different reporting periods and continued to grow month after month.

I wanted a solution that could process all historical files automatically while remaining ready for every new monthly export.

Instead of creating a separate query for every file, I built a reusable ingestion pattern.

Using Power Query’s Parameter, Sample File and Transform File pattern, I separated the common processing steps from the business-specific transformations.

Every incoming file first passed through the same reusable preparation process.

Only after the common structure had been established did the individual business logic begin.

That simple architectural decision eliminated a huge amount of duplicated work and made future maintenance dramatically easier.

Optimising More Than Just Development Time

Performance was just as important as maintainability.

Throughout the ETL process I continuously looked for opportunities to reduce unnecessary processing.

Only the required columns were kept.

Data types were assigned as early as possible.

Business mappings were centralised instead of duplicated.

Intermediate staging queries remained hidden and were not loaded into the data model.

Only the final curated dataset became part of the published semantic model.

These may seem like small implementation details.

Together they significantly reduced refresh complexity and created a solution that remained efficient even as the data continued to grow.

Then Everything Changed

Ironically, the real proof that the architecture worked came much later.

The organisation changed.

Business processes evolved.

Enterprise systems were replaced.

Data structures changed.

Historical identifiers had to be mapped to new ones.

New business requirements kept arriving.

Yet the reporting platform survived.

Not because nothing changed.

But because change had already been considered during the design.

The ETL pipeline wasn’t built around individual files.

It was built around business concepts.

That distinction made all the difference.

Making the Solution Maintainable

Another design decision turned out to be surprisingly valuable over time.

The report included a dedicated Sources page.

Rather than hiding where the data came from, every important source was documented and linked directly from the report.

Anyone with the appropriate permissions could immediately navigate to the original location.

For me, maintainability isn’t only about writing clean queries.

It’s about making solutions understandable for the next developer as well.

If someone needed to take over the project in the future, they wouldn’t have to reverse engineer the architecture.

The report already documented itself.

What I learned

People often think the difficult part of Business Intelligence is designing dashboards.

My experience has been very different.

Dashboards change.

Business questions change.

Technology changes.

What lasts is the architecture underneath.

Looking back, the Golden Dataset wasn’t simply a Power BI dataset.

It became a reusable enterprise data platform that allowed new reports, new business requirements and new systems to be integrated without rebuilding everything from scratch.

That turned out to be the most valuable design decision of the entire project.

Next in the series

In the next article, I’ll take a closer look at one of the most challenging parts of the project: integrating multiple enterprise systems into a single business model.

We’ll explore how information from ERP systems, project management tools, organisational structures and business mappings came together to create one consistent view of the business.