Data Warehousing and Data Lakes Questions

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.

At real enterprise scale, the general lakehouse-versus-managed-warehouse trade-off sharpens around three things specifically: how concurrency actually behaves under load, what Atomicity, Consistency, Isolation, Durability (ACID) guarantees really mean for complex, high-volume writes, and how well each side handles mixed streaming-plus-batch ingestion without extra engineering effort.

What actually changes at scale

Concurrency. At moderate scale, both a lakehouse and a managed warehouse can serve many concurrent business intelligence (BI) users acceptably. At enterprise scale, a managed warehouse's concurrency handling (workload isolation, automatic scaling of independent compute clusters) tends to be more turnkey: the vendor has already solved the noisy-neighbor problem. A lakehouse can match this, but it usually requires deliberate compute-pool separation and tuning that the team has to design and maintain, rather than getting it largely for free from the platform.

Atomicity, consistency, isolation, and durability guarantees. Open table formats provide table-level ACID guarantees, which is a real and important upgrade over plain files. At enterprise scale, though, the volume and concurrency of writers (many pipelines committing to the same tables simultaneously) stresses that guarantee harder: metadata operations that are effortless with a handful of writers can become a real bottleneck with hundreds, and this is a genuine engineering problem a lakehouse team has to actively manage. A managed warehouse's transactional model is generally simpler to reason about (its query engine and storage are one integrated system), which is part of why it's easier to operate at scale with less specialized tuning, even though it offers less flexibility for complex multi-writer merge patterns.

Mixed streaming-plus-batch ingestion. A lakehouse's storage model is a natural fit for combining continuous streaming writes with periodic large batch loads into the same tables, because both are just writers committing to the same underlying table format. Managed warehouses have added streaming ingestion paths as well, but historically their strength was batch-oriented loading, so mixing a high-volume continuous stream with large batch jobs against the same warehouse tables is more likely to need careful workload management to avoid one interfering with the other.

Worked example

Consider an enterprise ingesting both a continuous stream of transaction events and nightly batch corrections from a legacy system into the same customer-activity table, served to hundreds of concurrent BI analysts. On a lakehouse, this is architecturally natural (both are writers against an ACID table), but the team needs mature compaction and metadata management to keep query performance from degrading as writer volume grows. On a managed warehouse, the batch and streaming paths might need to be more explicitly separated and reconciled, but the query-serving side to hundreds of concurrent analysts is more likely to just work at that scale without as much bespoke tuning.

Trade-offs and pitfalls

The pitfall at this scale is picking a side based on the same general trade-offs that applied at moderate scale, and being surprised when metadata scaling, writer concurrency, or workload isolation becomes the actual bottleneck rather than storage cost or basic query speed. Enterprise-scale lakehouse deployments succeed when the team genuinely invests in the operational discipline (compaction schedules, metadata monitoring, workload-isolated compute) the pattern requires; they struggle when it's adopted purely for the cost story without that investment.

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.

Deciding whether to actually move a workload between managed cloud warehouses comes down to whether the new platform meaningfully improves something the current one is failing at (performance, cost, or a capability gap), because a migration is expensive enough that 'roughly comparable' isn't a good enough reason to do it.

What I'd actually look at

Performance for your real workload, not a generic benchmark: how the specific queries your dashboards and reports run today perform on the candidate platform, at your actual data volumes and concurrency levels.

Cost, modeled against your real usage pattern, not list pricing: whether your workload is steady (favoring predictable, reserved-style pricing) or bursty (favoring on-demand, pay-per-query pricing), since the two platforms may price these patterns very differently.

Feature and ecosystem fit: whether your existing Business Intelligence (BI) tools, orchestration, and data-loading tools connect natively, and whether any platform-specific feature your team actually relies on today (a particular semi-structured data type, a specific data-sharing capability) has an equivalent on the other side.

Operational overhead: how much day-to-day tuning, maintenance, and specialized expertise each platform demands from your team, since a technically superior platform that needs skills your team doesn't have yet has a real, ongoing cost.

Worked example: structuring the pilot

Rather than a broad, open-ended trial, I'd scope a pilot around a small number of representative, real workloads, not synthetic benchmarks: for instance, one high-concurrency dashboard, one heavy nightly aggregation job, and one workload involving your messiest semi-structured data.

Run both platforms side by side against the same real queries and data for a few weeks, and measure the things that actually matter for the decision: query latency at your typical concurrency, the modeled cost for your specific usage pattern (not vendor list pricing), and how much manual tuning or troubleshooting effort each platform required from your engineers to hit acceptable performance.

At the end, the honest output isn't just 'platform B was 20 percent faster'; it's whether that improvement, weighed against migration cost, cost difference, and any features you'd gain or lose, clears the bar to justify moving a live production workload.

Trade-offs and pitfalls

The most common mistake is running the pilot on a clean, synthetic dataset that doesn't reflect your real data's messiness (skew, semi-structured fields, unusual query patterns), which can make a migration look like a clear win in the pilot and then underperform once real production traffic and data quirks hit it. The second is underweighting operational overhead: a platform that benchmarks faster but requires ongoing specialized tuning your team doesn't have the bandwidth for can end up costing more in engineering time than it saves in compute cost.

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.