Data Warehousing and Data Lakes Questions

Online Transaction Processing (OLTP) systems are built to handle many small, fast reads and writes for a single business transaction, while Online Analytical Processing (OLAP) systems are built to handle fewer, heavier queries that scan and aggregate across large volumes of historical data. For a startup at roughly 1,000 transactions per second needing both order processing and analytics, I'd recommend two separate systems rather than one combined one.

The distinction, dimension by dimension

Worked example: the recommendation

At 1,000 transactions per second, the order-processing system has to guarantee that a payment and an inventory decrement either both happen or neither does, with response times the customer will notice if they're slow. That's squarely an OLTP workload, and it needs a database built for exactly that: row-level locking, strong consistency, and indexes tuned for 'find this one order.'

The analytics side is a different shape of problem entirely: 'total revenue by category, by day, for the last quarter' touches millions of rows and doesn't care if it's a few minutes stale. Running that query directly against the transactional database competes for the same resources the checkout flow needs, and a single slow-running analytical query can visibly degrade checkout latency for customers, which is the actual failure mode that pushes teams toward separating the two.

So the practical setup is: keep a dedicated OLTP database for order processing, and periodically move data (via a change feed or scheduled batch job) into a separate warehouse or OLAP-oriented store for reporting. This isolates the two workloads so a reporting query never threatens checkout latency, and each side can be scaled independently: the OLTP side for transaction throughput, the analytics side for data volume and query concurrency.

For a data scientist doing exploratory analysis, the OLAP side is also the right place to work from: it's already structured for aggregation, and it won't compete with production traffic the way querying the live transactional database would.

Trade-offs and pitfalls

The main pitfall at small scale is assuming you need this separation from day one when a single well-indexed database would genuinely be fine for a while; premature separation adds operational complexity (a second system, and a pipeline keeping it in sync) before the workload actually demands it. The opposite pitfall, more relevant at 1,000 transactions per second, is running analytical queries directly against the production OLTP database 'just this once' and having that become a habit: it's the single most common way an analytics need turns into a production incident.

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.

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.

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.

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.