Retail data warehouse — Bronze/Silver/Gold medallion on Databricks Lakehouse

Context

The analytical question came from an actual cross-department disagreement: was the Back-to-School Tech & Essentials marketing campaign genuinely successful, or did every operational sub-system count campaign success differently? Point-of-sale, payment gateway, marketplace, classifieds, and returns each had their own answer, and none of them lined up.

Problem

Five sub-systems each producing their own version of “campaign success” is an information-architecture problem dressed as a reporting problem. Without conformed dimensions enforcing a single source of truth, every cross-department analytical question gets five answers and a Slack argument.

Approach

A Bronze / Silver / Gold medallion architecture on a Databricks Lakehouse with Unity Catalog Volumes for storage. The Gold layer is a Kimball dimensional model with 13 conformed dimensions and 14 facts, so cross-sub-system questions traverse one shared model rather than five duplicated ones. Idempotent transformations end-to-end with SHA-256 surrogate keys for deterministic re-derivation.

Solution

• Bronze landing zone for raw transaction, classified-listing, point-of-sale, payment-gateway, marketplace, and seller data.
• Silver layer with idempotent transformations and schema-aware cleanup; marketplace + classified-listing Silver layer owned end-to-end.
• Shared silver_template notebook every Silver notebook inherits from, with consistent anomaly handling and validation patterns across owners.
• Gold layer with 13 conformed dimensions and 14 facts in Kimball star-schema form.
• SHA-256 surrogate-key generation for deterministic identity.
• Snowflake integrated as a read/write target via the snowflake-connector-python adapter, replacing the spark-snowflake Maven JAR in a serverless-only environment.
• Platform-adaptation work documented end-to-end: migrated from a deprecated Databricks Community-Edition assumption to Free Edition (serverless compute, Unity Catalog Volumes, widgets-for-secrets, Python Snowflake adapter).
• Supporting SQL artifacts under a dedicated sql/ folder for downstream BI consumption.

Outcome

Above. The Gold layer answers the Back-to-School-campaign question consistently across operational sources rather than producing five competing answers; that is the warehouse’s operating value. Idempotent Silver + Gold transformations + SHA-256 surrogate keys make the pipeline safe to re-run from any layer without duplicate keys or fact drift.