Data Warehousing and Data Lakes Questions

Allowing direct raw-zone access trades speed and flexibility for risk, and the right call is usually to allow it, but only with real controls in place rather than as an unmanaged free-for-all.

Advantages and risks

The advantage is real: analysts can explore data the same day it lands, without waiting on an Extract, Transform, Load (ETL) pipeline to be built, which matters most for genuinely new or exploratory questions nobody anticipated. The risk is also real: raw data can be inconsistent, duplicated, or contain sensitive fields nobody's reviewed, and if every analyst parses it slightly differently, two people can answer the same business question with two different numbers, each confident their number is right.

What I'd put in place

Access boundaries. Not every analyst needs unrestricted raw access by default; grant it deliberately, and mask or restrict fields that contain personally identifiable information (PII) so raw-zone access doesn't become a backdoor around the controls the curated layer already enforces.

Cost boundaries. Raw, unpartitioned data is expensive to scan broadly. Query caps or budget alerts per user or team keep one exploratory query from blowing through a month's compute budget, and encourage people to work off a sample or a time-bounded slice rather than the entire history by default.

Discoverability, so raw access doesn't become guesswork. A catalog entry describing what's actually in the raw zone, including known quality issues and who owns it, turns 'poke around and hope' into an informed starting point.

A path back to consistency. Raw access should be the exception for genuine exploration, not the default way people answer recurring business questions. Once a query or metric proves useful, the honest move is to promote it into the curated (gold) layer so everyone answering that same question afterward gets the same number, rather than everyone re-deriving it from raw data independently.

Worked example

An analyst investigating an unexpected metric drop wants to look at raw event logs directly, before anyone's had time to build a proper pipeline for whatever they're checking. With the controls above in place, they can query a time-bounded, masked slice of the raw zone the same day, using a documented catalog entry to understand what they're looking at, without needing to wait on an engineering ticket. If what they find turns out to matter on an ongoing basis, that becomes the trigger to formalize it into the curated layer, rather than everyone re-running the same ad hoc raw query indefinitely.

Trade-offs and pitfalls

The most common failure mode is treating raw access as permanently fine for recurring reporting because it worked once for exploration: without a deliberate path to promote a useful ad hoc query into the curated layer, raw-zone access quietly becomes the source of a business-critical number that nobody's actually validated, and different people's slightly different versions of it eventually surface as a trust problem in a meeting, not a data problem in a pipeline.

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.

I'd do both: land the raw JSON in a lake as-is for full fidelity, and separately parse and flatten a curated subset into warehouse tables for the analytics that actually run every day. Choosing only one of the two means giving something up you'll likely need later.

The trade-off

Keeping only raw JSON in a lake preserves every field, which matters because you genuinely don't know today which fields will turn out to matter for tomorrow's question. But raw, nested JSON is expensive and awkward to query directly at scale: every analyst who wants to use it has to know its parsing logic, and there's no shared, agreed definition of 'this field means X.'

Parsing and flattening everything into warehouse tables gives fast, predictable analytics and lets a business intelligence (BI) tool query it directly, but it's inherently lossy: whatever fields the flattening logic didn't anticipate get dropped, and if the source schema changes, the flattening job has to change too, or new fields silently disappear.

Worked example: the hybrid approach

A workable pattern: ingest the raw JSON into a lake, partitioned by arrival time, and keep it immutable and cataloged so anyone can find and inspect it later. Then build an automated job that extracts the commonly used fields (the ones that power today's dashboards and reports) into a proper warehouse table with an enforced schema, refreshed on whatever cadence the business needs.

For the fields that are rarely used or still evolving, rather than dropping them, keep them accessible as a semi-structured column (most modern warehouses support a JSON-like column type) alongside the flattened table. That way, an analyst doing routine reporting queries the fast, flattened columns, while someone who needs an unusual field can still get at it without going back to raw files in the lake.

The result: day-to-day analytics run fast against curated tables, while the full-fidelity raw data (and the less commonly used fields) stay reachable for the exploratory or forensic case where you need something the flattening logic didn't anticipate.

Trade-offs and pitfalls

The most common mistake is flattening too aggressively, dropping fields nobody's using today, only to need one of them six months later and discover it was never captured going forward. The opposite mistake is refusing to flatten anything and leaving every analyst to write their own JSON-parsing logic against the raw lake data, which reliably produces inconsistent numbers between teams asking what should be the same question.

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 staging (or landing) area is a raw, largely untransformed copy of source data that sits between extraction and the curated tables analysts actually query. You keep it separate for three reasons that all come back to the same idea: the raw copy is your safety net.

What lives there and what happens to it

• Data lands close to its source shape (same columns, minimal type coercion) so a transformation bug never destroys information you didn't capture anywhere else.
• Light operations happen here before anything moves further downstream: basic type casting, deduplication of exact source-level duplicates, and enrichment that has to happen once (attaching a load timestamp, a source system tag, a batch id).
• It is the recovery point. If a downstream transform is wrong, you re-derive the curated table from staging instead of re-extracting from the source system, which may be slow, rate-limited, or (for a point-in-time correction) no longer possible to reproduce exactly.

Retention
Staging data is usually cheap: raw storage, no indexes, no BI-facing service-level agreements (SLAs) to honor, so a common policy is to keep it far longer than the curated layer needs, often 30 to 90 days on a rolling window, sometimes indefinitely for regulated or audit-sensitive domains. The retention call is really a bet: how far back would you ever need to reprocess from raw, versus what the storage costs to keep that option open.

Trade-offs and pitfalls
Skipping staging (transforming directly from source into curated tables) is tempting because it looks like fewer moving parts, but it collapses your only recovery path into "re-run the extraction," which does not always give you the same data twice (source systems get pruned, APIs paginate differently over time, upstream tables get purged). The opposite failure is treating staging as query-able and letting analysts hit it directly: it has none of the cleaning, deduplication, or documentation the curated layer promises, so ad-hoc use of staging tends to produce numbers that quietly disagree with the official dashboard.