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.

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.

Analytical warehouses prefer columnar formats like Parquet or ORC because most analytical queries touch a small number of columns across a huge number of rows, and columnar storage is built specifically to exploit that pattern. Row-oriented storage, by contrast, is built for the opposite pattern: reading or writing one whole record at a time.

Why columnar wins for analytics

In row-oriented storage, all the columns for a single row sit next to each other on disk, so reading even one column means reading the whole row. In columnar storage, all the values for a single column sit together instead. That single layout change explains most of the benefit:

Less data read off disk. If a query only needs three columns out of fifty, columnar storage lets the engine read only those three columns' worth of bytes; row storage would have to read all fifty columns for every row, then discard the ones it didn't need.

Compression. Values within a single column tend to be far more similar to each other than a mixed row of different columns is (a status column might have only a handful of distinct values, for instance). That similarity compresses much better, and formats like Parquet and ORC take advantage of it with column-specific encodings, which further reduces the bytes actually read from disk.

Skipping data that can't match a filter. Columnar file formats keep summary statistics (like the minimum and maximum value) for chunks of each column. If a query filters on a date range, the engine can check those statistics and skip entire chunks that fall outside the range without reading them at all, on top of the column-level savings already described.

Worked example

Consider a query that computes total revenue for one product category last month, against a wide events table with fifty columns. In row-oriented storage, computing that aggregate still means reading every column of every row in the relevant time range, most of which the query never uses. In columnar storage, the engine reads only the category and revenue columns (plus whatever it needs to apply the date filter), and if the data is organized so most chunks clearly fall outside last month's date range, it can skip those chunks entirely. The net effect is that a query touching two or three columns out of fifty can end up reading a small fraction of the bytes a row-oriented equivalent would.

Trade-offs and pitfalls

Columnar formats aren't universally better: for a workload that reads or writes entire records at a time, like an online transaction touching every field of a single order, row-oriented storage is the better fit, because columnar formats pay a real cost when you need to reconstruct a whole row from scattered column files. This is exactly why transactional (OLTP) systems still use row-oriented storage even though analytical (OLAP) systems have moved to columnar: the two are optimized for genuinely different access patterns, not one being an outdated version of the other.

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.

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.