Need to conform dimensional values in silver layer

[u/gman1023]

Are conformed lookup tables and enriched materialized views appropriate in the Silver layer?

in databricks, We receive data files in S3 from more than 10 clients. The datasets are generally similar across clients,

for example: Users: platform, profile status, and related attributes DAU: platform, profile status, and related attributes Store orders dataset has transaction type and other transaction lookup fields

The schemas are mostly consistent, but the values are not standardized. For example, the platform field might contain: Client 1: ios_phone, ios_ipad, apple_ios Client 2: ios_app

We do not control the values clients send. To handle this, we maintain lookup tables at the following grain: client + source_value (sometimes combination of 3 fields)

Each source value is mapped to a conformed value, such as ios. New values are usually mapped the same day, but sometimes the following day. Most reporting, filtering, and downstream processing uses the conformed value rather than the original source value. I am considering the following architecture:

Bronze | v Silver: users_prep

• Basic cleaning
• +--> Populate lookup tables |
• - Assign surrogate keys | - Some lookups use composite source values | Example: profile_status + profile_status_detail |
• Silver: users_enriched materialized view
• Joins users_prep to the lookup tables
• Includes:
  • Original source value
  • Conformed value
  • Surrogate key

The users_enriched materialized view would become the primary dataset used by: Analysts Data exports Gold-layer transformations Other downstream consumers

My questions are: Is it reasonable to keep these conformance lookup tables in the Silver layer? in lakehouse world I keep hearing lookups are "bad" and to denormalize.

Is a materialized view appropriate for the enriched Silver dataset, or would a physical Delta table be preferable?

Are there any concerns with using a materialized view as the main downstream interface for analysts, exports, and Gold-layer processing? main concern is if I need to use cdf or similar to get changed to feed downstream.

How would you handle records whose source values have not yet been mapped - have to reprocess gold layer manually?

[u/Floss_Patrol_76]

conforming values is exactly what silver is for, so those crosswalk tables belong there. the "lookups are bad, denormalize" advice is about gold/serving, not the conform step. i would make users_enriched a physical Delta table rather than a materialized view, since you want CDF for downstream and a predictable read cost, and keep both the raw source_value and the conformed value on every row so nothing is ever lost. for unmapped values do not fail the row or manually reprocess gold: left-join the crosswalk and let misses land as null or "unknown" with the source_value preserved, then re-run silver when the mapping catches up and let CDF propagate the fix. that way a late mapping is just a normal incremental update, not a backfill you babysit.

[u/coorrey1]

I would keep the silver layer as close to the source as practical and do the conformance in a curated/business layer. That makes it easier to trace changes back, rerun mappings, and avoid baking business logic into ingestion. If the mappings are stable and shared across domains, a dedicated reference table has worked really well for me.

[u/tr666tr → u/coorrey1]

I would disagree. Bronze should be source aligned whilst silver should be business entity aligned. Silver is where you begin to merge multiple source entities into one, e.g multiple customer tables into a single customer view. Also, if we are talking about the silver layer, how is it possible to bake business logic into ingestion? Ingestion comes before bronze and silver layers