Data Warehousing and Data Lakes Questions

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 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.

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.

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 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.