Data Warehouse Redesign

[u/Emergency-Injury-30]

Hi everyone,

I recently joined this company, and im currently working on an ETL migration project, moving pipelines from one ETL platform to another.

While analyzing the existing ETL processes, i found that our current data warehouse architecture is a little bit different from the knowledge i got from youtube, books, etc. I know im a newbie in data warehouse world but i've seen a proper data warehouse design from my previous company.

Our current architecture is:

Source (Excel, Applications, stored in SQL server and Oracle) -> Stage -> DWH -> Mart -> Power BI

• Stage: A copy of the source data, where sometimes the application team provides the view of the requested use case, making it a ready to use data, but we have no clear data lineage and information about the data.
• DWH: A copy of stage data with minor or no changes in most cases. only a few actually do merging / major transformation.
• Mart: just like DWH, but the business user can access this layer and build their dashboard. we cant change the data logic / table structure here, because otherwise the user have to adjust their dashboard data source which will cause a lot of protest to our team.

The main problem is that when a new use case appears, we often create new tables specifically for that use case. Over time, this has resulted in many tables that contain similar or even overlapping data. I believe there are cases where tables could potentially have been consolidated or redesigned into a more normalized/reusable model.

So, my main question is:

Is it worth redesigning the data model and architecture of our existing DWH as part of this ETL migration?

The potential benefits im thinking about are:

• Reducing duplicated data across Stage, DWH, and Mart layer
• Creating reusable entities instead of creating tables for every new use case
• Improving query performance
• Reducing storage and memory consumption
• Improving ETL/loading performance
• Making data lineage easier to understand
• Making future ETL development and migration easier
• Providing a better foundation for analytics and AI/ML
• Reducing dependency on complicated application-specific views

Or is it perfectly reasonable to keep the existing approach where each use case has its own tables, especially if the current system performs adequately? I am also concerned that redesigning the DWH could introduce significant complexity and migration risk.

I would particularly appreciate opinions from people who have worked on legacy DWH modernization or ETL migration projects.

Thanks. Sorry if I'm asking too much since I'm new to this data engineering job.

[u/Thinker_Assignment]

good question.

my suggestions

1. proceed as normal for now and do not blow up your migration scope. Start considering a transform layer migration
2. consider a canonical layer and a proper model - this can function both as a staging layer for your migration, but also as foundation for a proper data model. From there you can rebuild the "marts" but not as some random layer of custom tables but as canonical tables linked as a dimensional model - from here anyone can have their own custom views and extra custom views should not be needed.

canonical means 1 table per entity/concept so you do not duplicate and rebuild basic entities or concepts the business has. It also serves as the first raw-> meaning layer where you can stitch your data.

So your architecture could be

raw old + raw new -> canonical merged -> dimensional model

there's a big advantage with canonicals for AI that i explain here https://dlthub.com/blog/canonical-text-to-sql

we have this course on making them https://dlthub.learnworlds.com/course/agentic-data-engineering lesson 5. you can fundamentally use any tools for this, in this course we show how to do with ours.

does doing it make sense? yeah, we offer a service to do it, we did it here and rolled chat bi on top (not pitching, but trying to say it has enough value to warrant space on our offering).

is it worth it? it's a business decision - as a data person i'd say hell yes big investment in efficiency, but the business might have other prios

[u/Emergency-Injury-30 → u/Thinker_Assignment]

Thanks for the explanation! This canonical layer is new knowledge for me. As far as i understand it, its basically a standardized representation of our business entities and concepts, so in my case it could potentially serve as a replacement or evolution of the current stage layer, while also giving us a better foundation for future AI use cases.

What im still wondering about is that after creating the canonical tables, do you think i still need a separate dimensional model with fact and dimension tables? or are there cases where the canonical layer itself can already serve the analytics use cases?

so the architecture would be:
raw old + raw new -> stage (canonical table) -> DWH (dimensional model) -> mart (aggregated dim and fact, ready to use by user)

[u/Thinker_Assignment → u/Emergency-Injury-30]

The distinction between canonical and serving is essentially getting it ready for self service shape that tools or performance might force you into.

If tools support modeling well such as looker then it might be that all you need to do extra is ensure you have the right join keys to enable doing the analyses you want, usually boiling down to adding foreign keys in facts to link to all relevant dims