topic Re: Need advice on the Gold Layer in Data Engineering

Need advice on the Gold Layer

dtran9936 — Mon, 28 Apr 2025 14:06:03 GMT

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.

Re: Need advice on the Gold Layer

ucarbengisu — Mon, 28 Apr 2025 16:04:34 GMT

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.

Re: Need advice on the Gold Layer

V-yubandi-msft — Tue, 29 Apr 2025 05:09:51 GMT

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

Keeps business logic and data cleaning distinct for better clarity and organization.
Makes Stored Procedures more straightforward and easier to manage over time.
Allows seamless updates if deduplication rules need to be modified.
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.

Re: Need advice on the Gold Layer

V-yubandi-msft — Fri, 02 May 2025 11:17:36 GMT

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.

Re: Need advice on the Gold Layer

V-yubandi-msft — Mon, 05 May 2025 11:56:48 GMT

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.

Re: Need advice on the Gold Layer

V-yubandi-msft — Thu, 08 May 2025 16:40:09 GMT

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.

Re: Need advice on the Gold Layer

marcus_mck — Fri, 09 May 2025 05:08:45 GMT

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,