Data Warehousing and Data Lakes Questions

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.

Separating compute from storage means the query engines that process data and the system that persists it are two independent components that can each scale on their own, rather than being bundled into one tightly coupled machine. For a cloud warehouse, this buys cost flexibility and concurrency; the real trade-off it introduces is that not every situation benefits from that decoupling.

What the separation actually buys you

Cost flexibility. Storage is billed continuously and is comparatively cheap, since it's just persisted, compressed data sitting in object storage. Compute only needs to run while queries are actually executing, so a team can spin up compute for a big nightly job and shut it down afterward, rather than paying for a fixed, always-on machine sized for peak load.

Concurrency. Because compute is decoupled from storage, you can run multiple independent compute clusters (or virtual warehouses) against the same underlying storage at once. A heavy overnight batch job and a set of interactive business intelligence (BI) users can each get their own compute, reading the same data, without competing for the same CPU and memory the way they would on a single coupled system.

Independent scaling. Storage scales with how much data you keep; compute scales with how much querying you're doing right now. Decoupling them means you're not forced to over-provision one to satisfy the other, for example buying more compute than you need just because your data volume grew.

Worked example

Say a warehouse holds five years of order history (large, cheap-to-store data) but most days only a handful of business intelligence (BI) analysts run moderate queries against the last quarter. With compute and storage separated, storage cost stays roughly proportional to the five years of data (cheap, since it's just sitting there), while compute cost stays proportional to actual query activity, a small, right-sized cluster most days, scaled up temporarily if someone runs a big historical analysis. With a tightly coupled system, you'd effectively have to size one machine for both the full data volume and for the occasional heavy query burst, paying for capacity you rarely use.

The one trade-off worth naming honestly: separating compute and storage generally introduces a network hop between them, since compute isn't reading off locally attached disk anymore. For most analytical workloads that's a fine trade for the flexibility gained. It matters more for a workload with extremely tight, latency-sensitive access patterns, where avoiding that network round trip and keeping compute and storage physically co-located would actually perform better.

Trade-offs and pitfalls

The most common misunderstanding is treating this as a free win with no downside: for genuinely latency-critical, tightly-coupled access patterns, like an embedded system reading and writing to local storage with sub-millisecond expectations, the network overhead of a decoupled architecture is a real cost, not a rounding error. For the vast majority of analytical BI and reporting workloads, though, the concurrency and cost benefits outweigh that overhead by a wide margin, which is exactly why every major cloud warehouse has moved to this model.

Direct answer

When order-level revenue reporting, customer-lifecycle analysis, and A/B test measurement all want the same table, pick the FINEST grain that all three can be derived from correctly, usually order-line or even event-level, and build the coarser views (order-level revenue, per-customer rollups) on top of it rather than picking one stakeholder's preferred grain and forcing the others to work around it.

Structured elaboration

• Why you can't just average the requests: order-level revenue reporting wants "one row per order" (fast, simple sums). Customer lifecycle analysis wants to trace a customer's behavior over time, which needs finer detail (individual orders and their timing) than a pre-aggregated summary. A/B test measurement needs to attribute individual actions to experiment variants, which usually needs the finest available grain (the raw event or order-line level) so the analysis isn't fighting pre-aggregation that already collapsed the variant-relevant detail.
• What goes wrong picking the wrong grain: if you build at order-level to satisfy revenue reporting, the A/B test team either can't measure their metric at all, or has to reconstruct finer detail from a coarser table, which risks double counting (splitting an order-level revenue figure back down to per-line-item without the original line data) or losing detail entirely (event timing collapsed into a daily order date, breaking time-since-treatment analysis).
• The resolution pattern: model at the finest defensible grain (order-line or raw event), and provide each stakeholder group their own summary view or aggregate table derived from that base fact table. This means slightly more upfront modeling and storage cost, but avoids re-litigating the grain decision every time a new analytical need appears.

Worked example

An order_line_fact at line-item grain, with order_key, customer_key, experiment_variant_key, date_key, amount. Revenue reporting builds SELECT order_key, SUM(amount) FROM order_line_fact GROUP BY order_key as an order-level view. Customer-lifecycle analysis queries the base fact table directly, ordered by customer_key, date_key. A/B test measurement filters and groups by experiment_variant_key directly on the line-item fact, without needing to reconstruct anything.

Trade-offs and pitfalls

The trap is assuming "the business wants a dashboard" means "the fact table should be at dashboard grain." Dashboards are downstream views; the fact table's job is to be the finest reusable source of truth those views (and future, not-yet-requested ones) can be built from. The cost is real: finer grain means more storage and slightly more complex aggregation logic for the simplest use case (order-level revenue), and that trade-off is worth stating explicitly to stakeholders rather than silently absorbing it.

Start at the KPI's (key performance indicator's) definition and walk the lineage graph backward one hop at a time, checking at each step whether that step's output looks anomalous compared to its historical pattern, which narrows down where the change entered rather than re-deriving the whole pipeline from scratch. Doing this quickly depends on having captured, ahead of time, the transformation SQL for each step, a versioned history of both the schema and the transformation logic, and a responsible owner for each dataset in the chain. Present the result to a non-technical stakeholder as a short, plain-language narrative of the single step that changed, not the full graph.

Tracing back through transformations to raw source rows

Starting from the KPI as rendered on the dashboard, identify its metric definition, the aggregation and filter that produce the number, and the fact table it reads. At each hop upstream, from the fact table to its source transformations, and those to their upstream tables, eventually to raw ingested events, compare the current output to its recent historical values or to a smaller trusted baseline. The hop where the numbers stop looking anomalous relative to a recent, stable baseline is the boundary where the actual cause sits, one step downstream of that boundary. This bisection-style walk, checking a handful of hops rather than every row at every layer, is what makes the trace fast on a deep chain, instead of manually re-running every transformation from raw data forward.

Metadata you need captured ahead of time

• Transformation SQL for each step: without the actual logic recorded, not just "table B comes from table A," you can see that a number changed but not why, since the why usually lives in a filter, join, or aggregation that changed.
• Versioning of both schema and transformation logic: knowing not just what the current SQL is but what it was previously lets you diff and directly see what changed, rather than staring at the current logic and guessing whether it differs from before.
• A responsible owner recorded per dataset: once the boundary hop is found, you need to know who to actually ask or hand the fix to immediately, not after searching for who owns that table.

Presenting the trace to a non-technical stakeholder

Do not hand a stakeholder the dependency graph; translate the finding into a short narrative: what the number is built from, in plain language, and specifically which single step changed and what changed about it, whether a filter got stricter, a source started excluding some rows, or a join key stopped matching for a subset of records, dated against when the KPI's behavior shifted. Pair it with a simple before-and-after comparison at that one step, not the whole chain, so the stakeholder can see the specific cause rather than trusting the summary on faith, and state clearly whether the fix means the new number is correct and the old one was wrong, or the reverse.

Worked example

The weekly active-users KPI drops sharply. The trace starts at the KPI's definition, distinct users with a qualifying event in the trailing 7 days, reading from fct_user_activity. Checking that table's recent values against its trailing average shows it is also lower than expected, so the trace steps one hop further back to the transformation that builds it, which joins dim_user and a raw events table. dim_user's row count looks normal; the events table's row count for the last three days is noticeably below its usual volume. Stepping one more hop back, the transformation SQL that loads events from the raw ingestion source shows a filter excluding test events that was present before but is now unexpectedly also excluding a legitimate new event type introduced by a recent mobile-app release, because a substring match in the filter logic, changed in a deploy three days ago and visible via the versioned transformation history, unintentionally matches the new event type's name. That is the boundary: events looked wrong, its own upstream source did not. The fix is correcting the filter to exclude test events exactly rather than any type containing similar characters, and backfilling the undercounted days.
Presented to the stakeholder: "Weekly active users looked low because a filter change three days ago accidentally excluded a new type of app-open event alongside the test events it was meant to exclude. The undercounted days have been backfilled and today's number is corrected; no real drop in usage occurred."

Trade-offs and pitfalls

The bisection approach only works if enough of the chain actually has captured transformation SQL and version history; any hop where that metadata is missing turns back into manual archaeology at exactly that step, so the design choice with the most payoff is making metadata capture mandatory for every step, not just the most important-looking ones. The presentation pitfall is over-explaining: handing a business stakeholder the full lineage graph or every hop's SQL diff buries the one sentence they actually need, which is what changed and whether they can trust the new number.

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.