Need advice on the Gold Layer

[dtran9936]

Hello,

I just need some guidance on the best practice on the medallion layer. Let me explain the scenario

Scenario is in Incremental ELT

Bronze (Lakehouse): Load delta data base on Watermark filed (Last modied date)

Silver (Lakehouse): Using Data pipeline to append the delta load with cleansing 

Note: if I have a record that is modified today, the will be two records in the Bronze and Silver Layer

At the Gold Layer (Warehouse), should I create view to make that specific table unique then build my SP query through a view? Or build it directly into the SP query.

I hope I explain it correctly.

Thank you Again.

[marcus_mck]

Hey there @dtran9936 ,

What will your SP queries do? Are your view tables in Gold simply coming from Silver and Bronze and that would result in unique tables (I'm assuming post processed cleaned data)?

Thanks,

[V-yubandi-msft]

Hi @dtran9936 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @dtran9936 ,

We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.

Thank You.

[V-yubandi-msft]

Hi @dtran9936 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[V-yubandi-msft]

Hi @dtran9936 ,

Best practice is to create a Gold View to manage deduplication first, followed by building Stored Procedures on top of this clean view. This approach has several advantages

1. Keeps business logic and data cleaning distinct for better clarity and organization.
2. Makes Stored Procedures more straightforward and easier to manage over time.
3. Allows seamless updates if deduplication rules need to be modified.
4. Facilitates testing and validation of the latest records independently.

Thank you for your valuble input @ucarbengisu .

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[ucarbengisu]

Hello @dtran9936,

I think you should start by building a Gold View that handles deduplication first. After that, Stored Procedures should simply query this clean, pre-processed Gold View.

By doing this, Stored Procedures stay simple and focused only on business logic, without having to deal with cleaning the data. If the deduplication rules change later, you only need to update the Gold View, not multiple Stored Procedures. This makes the system easier to maintain and more flexible.

The "latest record" logic is contained within the Gold View, which makes it easier to test and validate separately from the business logic. Since the Gold View handles deduplication and selects the most recent records, it can be tested on its own, without involving other parts of the system. This separation makes the whole system more organized and easier to manage.

As far as I know, this approach follows best practices for creating a clean, modular, and maintainable system. It keeps your business logic separate from data processing, which helps make the system more flexible and easier to work with over time.