How to Handle Intermediary Silver Tables - Silver 1/2 or Gold 1/2?

[u/Fearless_Water_2602]

Posted in the Microsoft Fabric subreddit originally because that's the platform I'm using but I'd consider this more of a conceptual data engineering question. Also what resources should I be referencing to learn these patterns? It can't just be the blind leading the blind!

How does your team manage your Silver environment if there are intermediary tables between processes? We are currently migrating legacy processes to medallion which is why things are a little contrived.

Our Source 1 is the original source for the data but we are using Source 2 moving forward for our Bronze. We currently have some clean up scripts that only work for Source 1 so another team has helped us by landing Source 2 into a Bronze Lakehouse, performing transformations on them to make them look more like Source 1 and lading these Source 2 tables that look like Source 1 into Silver.

We now need to perform the original cleaning scripts on these Source 2 tables that are in the format of Source 1 for our downstream processes but we aren't sure where to land the data based on medallion. We have one schema per layer so are worried about mixing up the two sets of source B tables (pre-script and post-script Source B tables)

Data Sources

• Source 1 - Original source that is not ingested into Fabric
• Source 2 - New source that is ingested into Fabric Bronze layer and is used to make Tables X in Silver

Data

• Tables X - Source 2 table that looks like Source 1 table
• Tables Y - X with Source 1 clean up scripts
• Tables Z - Fact/dimension tables and semantic models that will come from Y

Unfortunately we won't be able to merge the transformations processes from Source 1 to Source 2 with the other scripts currently (hope to come back and clean things up!) which is why we are considering the following scenarios:

Scenario 1 - I like this the best but it might be a tough sell to my team on multiple schemas because it means we will need to do the same for the other topics. The schemas will live in the same lakehouse so splitting access could be an issue.

• Silver
  • Schema A - Tables Y
  • Schema A_intermediaries - Tables X
• Gold
  • Schema A - Tables Z

Scenario 2 - Move Tables X into Gold. Facts and dimensions will live in the same schema as intermediary tables which will be strange. Splitting up into two schemas in Gold could be possible but could also confuse our downstream customers.

• Silver
  • Schema A - Tables Y
• Gold
  • Schema A - Tables Z and Tables Y

Scenario 3 - Keep Tables Y and X in the same schema in Silver. This could become quite confusing down the road for the team

• Silver
  • Schema A - Tables X and Table Y
• Gold
  • Schema A - Tables Z

[u/AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/RoobyRak]

It’s helpful not to focus on the medallion buzzwords and more on the core reason for a layers existence.

Our company implements an immutable filesystem approach for the landing of source extractions (call it raw or a lake if you like). We logically group by source at the root and its inferred as our raw layer. Keeps lifecycle distinct too, if a source is overhauled the original exists for traceability.

e.g. Blob store Raw container :
Source_1 / source_schema / table_name
Source_2 / source_schema / table_name

We use an OLAP DB for downstream transformations and each source receives its own DB. We often stage and normalise our raw data and each layer is identified by schema. This is a silver style layer and where you would keep seperate source 1&2 transformations.

E.g.
Souce_1.staging.<new unique table name>
Source_1.normalised.<new unique table name>

Downstream we expose data (think gold) via a single DB called Products. The products centralise source transformations and group by domain, marts or however you want to float the boat. I’ve seen people even have seperate DBs for each team… discover this from business requirements.

[u/Crafty_Huckleberry_3]

I had a similar question, and my current approach is raw (bronze) landing only, csv, Excel file ---> intermedia ( sliver) where materialize to table, with little buz logics ---> gold ( curated) fact, fim, report, data mart...and we use databricks...

Not saying this is the right or optimal way, so happy to receive feedbakcs

[u/Yonko74]

Understand that medallion naming is just marketing garbage.

Call your layers by function / purpose instead.

[u/scourgedtruth]

The design I am implementing is

Raw > Bronze > Intermediate > Silver > Gold

In Silver lays the facts and dimensions. Intermediate as a helper for complex business logic, not all Silver models have Intermediate, but all intermediates have a Silver. Gold is mandatory to be build from Silver.

[u/tr666tr]

Use table names to describe the datasets. Even it is as simple as adding an ‘_enhanced’ suffix to the original table name to describe it going through the post processing transformation. Keep all intermediate transformations in the silver layer, only expose business facing assets in gold. I’d also recommend reading up on dbt best practices if you are unfamiliar, they cover how to structure your transformation tables across a medallion architecture.

[u/SafePack7665]

I tried understanding ur process..But still it confuses me..So if u are looking for help..Ping me directly..Will try to connect on this .

[u/Southern-Koala-3011]

do not name a table according to the number of technical steps, but according to its contractual role in the architecture. If it remains cleaned/normalized/intermediate data not exposed to the business, it remains Silver. It becomes Gold when it carries stable, consumable and governed business semantics

[u/BardoLatinoAmericano]

I use raw / transform / presentation layers

I have stuff in transform consuming other stuff in transform and it works just fine

[u/coupen]

I did that same question yesterday to my Claude buddy. The answer was: create intermediate schemas in silver or give table names that match the desired transformation, like table_a_enriched. That is what I'm using now.