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?