Data Warehousing and Data Lakes Questions

The medallion architecture organizes a lakehouse or warehouse into three progressive layers: bronze holds raw data close to how it arrived, silver holds cleaned and conformed data, and gold holds business-ready, aggregated data. Each promotion step adds quality and structure, and the layering makes it possible to trace a bad number in a report back to the exact stage where it went wrong.

What lives in each layer

Bronze (raw). Data as it arrived from the source, with minimal changes: maybe a timestamp added, but no real cleaning, deduplication, or business logic applied. It's kept close to raw specifically so you can always reprocess it later if downstream logic changes. A data scientist would come here to inspect what the source actually looks like, or to debug why a downstream number seems wrong.

Silver (cleaned and conformed). Deduplicated, validated, typed data with obvious errors filtered out or flagged, and different sources joined into a consistent, entity-centric shape (one clean 'customer' table instead of three inconsistent ones). This is usually where a data scientist does most exploratory work and feature engineering, because it's reliable enough to trust but not yet flattened into business-specific aggregates.

Gold (curated, business-ready). Aggregated, denormalized tables built for a specific business purpose: a metric, a dashboard, a feature table for a production model. This is what business intelligence (BI) tools and dashboards query directly, and what a data scientist would use to validate that a model's output lines up with the business's own numbers.

Worked example: what happens at each promotion

Going from bronze to silver typically involves the validations that catch structural problems: rejecting or quarantining rows with impossible values, deduplicating records that arrived twice, and resolving type mismatches. Going from silver to gold applies business logic: computing the aggregates and joins a specific report or model actually needs, which is where a subtle bug (a wrong join key, double-counting a category) would surface as a wrong number in a dashboard.

Because each layer's boundary is explicit, when a gold-layer number looks wrong, you can check whether the problem is in the gold aggregation logic, in the silver cleaning logic, or in the bronze data itself, rather than having to untangle one monolithic transformation.

Trade-offs and pitfalls

The most common mistake is treating the layer names as a rigid rulebook instead of a communication convention: not every pipeline genuinely needs three distinct physical layers, and forcing a small dataset through all three stages for the sake of following the pattern adds pipeline complexity without adding real quality. The second pitfall is skipping the boundary discipline the pattern is meant to provide: if the same job does raw ingestion, cleaning, and business aggregation all at once with no clear layer boundary, you lose the exact debugging benefit (isolating which stage broke) that's the whole point of adopting the pattern in the first place.

The decision mostly comes down to two questions: does this workload need managed, low-operational-overhead concurrency for live dashboards, or does it need flexibility and control for heavy, less predictable ad hoc analysis? Different compute engines are built to answer those two questions differently.

What actually drives the decision

Concurrency and latency for live dashboards. Serverless or fully managed engines (a cloud warehouse's native engine, for example) are generally built to handle many simultaneous interactive queries with automatic scaling and little manual tuning, which is exactly what a live-dashboard workload needs.

Flexibility for heavy, varied ad hoc work. Open-source, cluster-based engines (Spark SQL or a distributed SQL engine like Presto or Trino) give you more control over how compute is sized and tuned, which pays off for large, irregular analytical jobs, but that control comes with the responsibility to actually tune cluster sizing and configuration yourself.

Cost model fit. A pay-per-query model suits bursty, unpredictable usage well, since you're not paying for idle capacity between spikes. A cluster-based, always-on model suits steady, predictable usage better, since a well-utilized cluster running constantly can be cheaper per query than paying for each one individually.

Integration with your table format. If your data already lives in a particular open table format, some engines have more mature, first-class support for it than others; a mismatch here means extra connector overhead or missing features, which matters more the more your workload depends on that table format's specific capabilities (like time travel or fine-grained schema evolution).

Worked example

A team serving both a set of executive dashboards refreshed throughout the day and a data science team running large, irregular exploratory queries would reasonably split these across two different engines rather than force both onto one: the dashboards onto a managed, serverless-style engine tuned for concurrency and low operational overhead, and the exploratory workload onto a cluster-based engine where the team can size compute up for an occasional large join without that cost or contention affecting the dashboards.

The underlying reason to split rather than pick one for everything is the same reason warehouse compute gets separated by workload type in general: a live dashboard's latency requirement and a data scientist's occasional huge, unpredictable query have fundamentally different resource profiles, and a single engine tuned well for one is rarely tuned well for the other by default.

Trade-offs and pitfalls

The most common mistake is picking a single engine for the whole organization based on whichever workload was loudest at the time (usually the dashboards) and then being surprised when the data science team's ad hoc queries are either painfully slow or expensive on an engine tuned for concurrency rather than flexibility. The opposite mistake, choosing a flexible cluster-based engine for everything to avoid maintaining two systems, usually means dashboards inherit tuning and operational overhead they shouldn't need, and inconsistent latency the business notices.

There's no universal winner here: a lakehouse consolidates a lake and a warehouse into one platform with transactional guarantees on top of cheap object storage, while keeping them separate lets each system specialize. The decision comes down to how much your organization actually needs unified governance and reproducible pipelines versus how much it benefits from best-of-breed, purpose-built tools on each side.

Where the trade-offs actually show up

Consistency and reliability. A lakehouse adds transactional guarantees (atomic commits, isolation between concurrent writers, versioned snapshots) directly on top of the lake's files, so the same storage that used to be 'append and hope' now behaves more like a database table. A separate lake plus warehouse gets this reliability only on the warehouse side; the lake itself is still just files, so anything reading raw lake data directly inherits the lake's weaker guarantees.

Operational complexity. Two systems means two things to operate, two places for metadata to drift apart, and an ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform) layer whose only job is keeping them in sync. A lakehouse removes that sync step, but it shifts the operational burden onto managing one more sophisticated system well: table maintenance, metadata scaling, and query-engine tuning become your responsibility (or your platform vendor's) instead of being split across two mature, narrower products.

Cost. Object storage underneath a lakehouse is cheap, and compute is decoupled from it, so storage cost stays low even as history accumulates. A separate warehouse usually costs more per byte stored because it's optimized for query performance, not just cheap persistence. Whether that matters depends on how much of your data is 'hot' (queried constantly, where warehouse performance pays for itself) versus 'cold' (rarely touched, where cheap lake storage wins).

Tooling and team skills. A managed warehouse's ecosystem (business intelligence, or BI, connectors, workload management, query optimizer) is mature and requires less specialized tuning. A lakehouse built on open table formats gives you more control and avoids vendor lock-in, but your team needs to actually understand things like compaction and file layout to get warehouse-like performance out of it.

Worked example: how you'd actually decide

A useful decision framework, applied to a mid-size company weighing a lakehouse against replacing (or supplementing) an existing warehouse: start from where the trade-offs show up, not from a platform preference.

• If most of your workload is well-known BI reporting on structured data, and your team is small, a managed warehouse alone is probably the pragmatic choice: you get performance and low operational overhead for the workload you actually have.
• If you increasingly need both governed BI numbers and large-scale ML feature engineering off the same raw history, and you're willing to invest in the operational skill to run it well, a lakehouse removes the duplicate-copy, duplicate-pipeline problem that a two-system setup creates.
• A common middle ground, and the one most organizations actually land on, is a hybrid: raw and semi-structured data lives in the lake (or lakehouse bronze layer), and a smaller, highly curated warehouse (or gold layer) serves the numbers the business depends on daily. This keeps the blast radius of 'the analytics platform is down' small and contained to the curated layer.

Trade-offs and pitfalls

The biggest pitfall is picking the lakehouse for its architectural elegance without budgeting for the operational skill it actually requires: an under-tuned lakehouse (poor file sizing, no compaction discipline) can be slower and less reliable than a plain warehouse, which erases the cost advantage once you factor in the engineering time spent firefighting. The second pitfall is the opposite: keeping two systems 'because that's how we've always done it' long after the sync overhead between them has become the single largest source of data inconsistency complaints.

Direct answer

Build a single conformed customer_dim, assembled by an identity-resolution and reconciliation process that merges records from CRM, support, billing, and marketing systems into one authoritative view keyed by a conformed surrogate key, owned by a single team, so every downstream fact table joins to the same customer identity regardless of which source system originated a given fact.

Structured elaboration

• Assembly process: each source system's customer records are matched (via shared identifiers where available, or matching logic like email/phone/name similarity where not) into a single conformed identity, similar in structure to product-dimension conformance but usually with higher identity-resolution complexity, since customer matching keys (email, phone) are noisier and more prone to typos or intentional variation than product SKUs.
• Governance as new sources onboard: define an intake process for any new source system that wants to contribute customer data: it must map its records into the conformed identity space (via the same matching pipeline), not create its own parallel customer notion. A governance body (or a designated owning team) reviews and approves new attribute additions to the shared dimension.
• Communicating breaking changes: maintain a change log and a deprecation process for any modification to the conformed dimension's schema or definitions (a segment definition changing, an attribute being renamed), with advance notice to every known downstream consumer (fact tables, dashboards, and any team known to query the dimension directly), since breaking it silently corrupts every team's reporting simultaneously.
• Ongoing reconciliation: run periodic checks comparing customer counts and key attribute distributions between the conformed dimension and each source system's own counts, to catch identity-resolution drift or a source system silently diverging from the conformed model.

Worked example

A customer signs up via the marketing site (captured in the marketing system), later contacts support (captured in the support system) using a slightly different email, and eventually becomes a paying customer (captured in billing). The customer-360 pipeline's identity-resolution matches all three records (via a combination of matched device ID, name similarity, and a shared phone number) into one conformed customer_key, so a single dashboard can show this customer's full marketing-to-support-to-billing journey without three separate, inconsistent customer identities.

Trade-offs and pitfalls

Identity resolution at this scope is genuinely hard and never perfect; the practical risk is either over-merging (falsely combining two different people who share some matching signal, like a shared household email) or under-merging (missing that two records are the same person due to noisy source data). Invest in a confidence-scored matching process with human review for ambiguous cases, and make match confidence visible to downstream consumers rather than presenting every merge as equally certain.

A data warehouse stores curated, structured data that's been cleaned and modeled ahead of time so business queries run fast and consistently. A data lake stores data closer to its raw form (structured, semi-structured, or unstructured) and defers structure until someone actually reads it. The practical consequence: a warehouse trades flexibility for speed and consistency; a lake trades speed and consistency for flexibility and scale.

The core comparison

The underlying reason for both approaches to exist is workload shape, not one being a strictly better version of the other. A warehouse is built around Online Analytical Processing, meaning many people running similar, well-known aggregation queries against a stable schema. A lake is built around the opposite assumption: you don't fully know the query shape yet, or the data doesn't have a stable shape to begin with.

Worked example

Say a company wants two things: a finance dashboard showing daily revenue by region, and a churn model trained on raw clickstream and support-ticket text.

The finance dashboard is Online Analytical Processing (OLAP) focused: the questions are known in advance ('revenue by region, by day'), the source data (orders, refunds) is already structured, and the business needs a single trustworthy number every day. That's a warehouse: model orders and refunds into a small star schema, enforce the schema so a bad refund record can't silently corrupt the total, and let the BI tool query modeled tables directly.

The churn model is ML-focused: the useful signal might be in raw event sequences or unstructured ticket text that nobody has modeled yet, the feature set will change every time someone retrains, and enforcing a rigid schema up front would throw away exactly the raw detail the model needs. That's a lake: land the raw events and ticket text as-is, and let the data science team iterate on feature extraction without waiting on a schema migration each time.

Trade-offs and pitfalls

The most common mistake is treating the lake as a substitute for governance rather than a different governance problem. Because a lake accepts anything, it's easy to end up with an ungoverned pile of files nobody trusts (sometimes called a 'data swamp'): if nobody owns metadata, lineage, or quality checks on the lake side, exploratory work slows down instead of speeding up.

The second common mistake is assuming a warehouse can't hold semi-structured data at all. Most modern warehouses support semi-structured columns (JSON or similar variant types), so the real dividing line isn't 'structured versus everything else,' it's whether the schema is enforced and stable versus deferred and evolving. Most real organizations end up running both: a lake for raw ingestion and exploration, and a warehouse (or a curated layer within a lakehouse) for the numbers the business depends on every day.