Data Warehousing and Data Lakes Questions

A data lakehouse is an architecture that adds warehouse-like reliability directly on top of a data lake's cheap, flexible storage, so you don't have to choose between the two or maintain both separately. It exists because neither pure lake nor pure warehouse fully served a team that needed both governed business intelligence (BI) reporting and large-scale ML work off the same data.

What it borrows from each side

From the data warehouse, a lakehouse borrows the guarantees that make data trustworthy for reporting: atomic writes (a query never sees a half-finished update), consistent snapshots you can query repeatably, and enough schema enforcement that a malformed record doesn't silently corrupt downstream numbers. These guarantees are commonly summarized as ACID (atomicity, consistency, isolation, durability), the same property a traditional warehouse or database provides.

From the data lake, it borrows the storage model: cheap, elastic object storage that can hold structured, semi-structured, and unstructured data at scale, with compute that's decoupled from storage so you're not paying warehouse-grade compute prices just to keep data sitting around.

The technical enabler that makes this combination possible is an open table format sitting on top of the raw files (this is a distinct, deeper topic on its own; the point here is only that some layer has to track which files currently make up a table's consistent state, so readers and writers agree on what 'the table' looks like at any moment, the same job a database's transaction log does).

Worked example

Before lakehouses, a team doing both business intelligence (BI) and ML typically ran two pipelines: raw events landed in a lake, and a separate ETL (Extract, Transform, Load) job copied a cleaned subset into a warehouse for reporting. Two copies of the data existed, and keeping them consistent, especially when correcting historical records, was manual and error-prone.

With a lakehouse, the same physical storage serves both: the BI team queries a curated, schema-enforced view of the data with the same reliability a warehouse would have offered, while the ML team can read the exact same underlying files at an earlier raw or intermediate stage for feature engineering, without waiting for a second copy to be built and synced.

Trade-offs and pitfalls

The main thing people get wrong is treating 'lakehouse' as a product you buy rather than a pattern you have to implement well: simply landing files in object storage with a table format on top doesn't automatically give you warehouse-grade query performance. You still need file-layout discipline (avoiding a huge number of tiny files, keeping tables compacted) to get the performance the pattern promises. It's also not a free upgrade over a mature managed warehouse for a team whose workload is purely well-known BI reporting; the benefit shows up specifically when you need both reliable reporting and large-scale, flexible ML work off the same underlying data.

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.

A data mart is a smaller, subject-oriented subset of data built for one business domain or team, like sales or finance, while an enterprise data warehouse is the broader, integrated store meant to be the consistent source of truth across the whole business. The trade-off is speed and autonomy for one team versus consistency across all of them.

When a mart makes sense

A mart is worth standing up when a specific team needs fast, tailored analytics that a shared, general-purpose warehouse schema doesn't serve well, for instance when that team's queries would otherwise compete for the same resources everyone else's dashboards depend on, or when their reporting needs a data shape (a specific set of pre-aggregated tables) that doesn't make sense to build into the general warehouse for every other team's benefit. It also makes sense when a team needs more autonomy over refresh cadence or schema changes than a centrally governed warehouse can reasonably offer everyone.

Worked example

Say the finance team needs a daily-refreshed set of tables tailored to close-of-month reporting, with definitions and aggregates specific to their workflow, while the rest of the company's dashboards run off the general warehouse on a different cadence. Standing up a finance-specific mart, refreshed from the same underlying warehouse data, lets finance move at their own pace and shape their schema around their own reporting needs, without every other team's warehouse queries competing with (or being reshaped by) finance-specific logic.

The risk this introduces: if the sales team separately builds their own mart, and both marts compute a metric like 'revenue' slightly differently (different date cutoffs, different handling of refunds), the business ends up with two different numbers for something that should be one number.

Trade-offs and pitfalls

The way to avoid that inconsistency is to make sure every mart pulls from the same canonical, governed source rather than each team independently reinventing the same aggregate logic: shared, agreed-upon definitions (a single definition of 'active customer' or 'revenue') should live in one place and flow into each mart, rather than being recomputed independently by each team. The most common pitfall is exactly the failure mode above: marts that grow independently, without a shared source of truth underneath them, until two departments show up to the same meeting with two different numbers for what should be the same metric, and nobody can say which one is right without tracing both all the way back to their source logic.

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.

Schema-on-write means you define and enforce a schema before data is loaded; anything that doesn't fit gets rejected or transformed at load time. Schema-on-read means you store the data as-is and only apply structure when a query actually reads it. The trade-off is agility and ingestion speed versus upfront quality guarantees.

What you gain and give up

Worked example

Say a new upstream system starts sending event data tomorrow, and nobody has fully agreed on its final field list yet. Under schema-on-write, you'd have to design a table schema, build and test a load job, and only then let anyone query it: useful once it's done, but it blocks any exploration until that's finished. Under schema-on-read, you land the raw events immediately and let an analyst start poking at them the same day, at the cost of not knowing yet whether every record is well-formed.

In practice, the two aren't mutually exclusive within one platform. A common pattern is to apply schema-on-read at ingestion (land raw data immediately, so nothing blocks exploration) and then promote a schema-on-write, validated version of the same data for anything the business depends on daily. That gives you the ingestion speed of one model and the quality guarantee of the other, on the same underlying data, at different points in its lifecycle.

Trade-offs and pitfalls

The most common mistake is picking one model as a blanket policy for an entire platform instead of matching it to the actual use case: forcing every new source through a heavyweight schema-on-write pipeline slows down legitimate exploration, while leaving everything permanently schema-on-read (never promoting anything to a validated layer) means business-critical reports are only as reliable as whatever query someone happened to write. The second pitfall is assuming schema-on-read has no cost: without a curated layer or documented conventions, different analysts querying the same raw data can apply subtly different parsing logic and land on different numbers for what should be the same metric.