Analytical Stores: Warehouses, Lakes, Lakehouses, and OLAP Serving

The Number That Changed After Close

Finance closes the month at 6:00 p.m. The revenue dashboard says $4.8 million. The query has scanned invoices, payments, refunds, subscriptions, and currency conversions in the production database, slowing checkout while it runs. The next morning the dashboard says $4.76 million. Nobody knows whether a late refund arrived, a pipeline reran, a conversion rule changed, or one of the dashboard joins duplicated an invoice.

Moving that query to another database protects checkout, but it does not make the number trustworthy. A replica can reproduce the same ambiguous grain and private dashboard logic at a safer distance from customers. The analytical system has a harder job: preserve the evidence behind the number, give that evidence shared meaning, publish it at an agreed freshness, and explain every correction.

That job has a different workload from checkout. A transactional path finds or changes a small amount of current state while protecting business invariants. A revenue close scans history, joins several kinds of facts, groups them by period and account, and serves many consumers. Those scans favor columnar layout, compression, partition pruning, clustering, and sometimes precomputed aggregates. Long retention, broad access, synchronized dashboard refreshes, and repeated reprocessing create operational pressures that the source database was not designed to absorb.

The design should begin with the decision, not the storage label. A monthly close can tolerate a bounded batch window and should favor complete, repeatable inputs. A customer-facing usage meter may need updates within minutes. An investigator running one costly query needs a different capacity boundary from hundreds of dashboard tiles refreshing at 9:00 a.m. Freshness, concurrency, historical depth, and serving latency determine the system; “warehouse” or “lakehouse” does not determine the workload.

Follow One Invoice Through the Path

Take invoice I-2048. It has two line items, one payment, a currency conversion, and a refund that arrives after the reporting period appears to be closed. A trustworthy analytical path lets an operator follow each contribution from source to report.

The raw layer lands source records with their stable identifiers, source timestamps, extraction time, and enough metadata to detect missing or repeated loads. Its first obligation is evidence. It should retain the source representation long enough to replay a damaged transform or apply a corrected model. Raw data is therefore useful before it is trustworthy for reporting.

The cleaned layer makes that evidence usable. It validates required fields, standardizes time zones and currency representation, deduplicates repeated extracts or events, quarantines malformed records, and preserves the reason for rejection. A row does not silently disappear because it failed a cast. The pipeline can say that invoice I-2048 arrived, which checks it passed, and which source record must be repaired if it did not.

The modeled layer gives the records a declared grain. An invoice table has one row per invoice. An invoice-line table has one row per line. Payments and refunds remain facts at their own grains. Joining an invoice with two lines to two payment attempts can otherwise create four rows and an impressive but false total. Grain is not documentation added after the model; it is the condition that makes aggregation meaningful.

This layer also gives terms owners. “Booked,” “collected,” “refunded,” and “recognized” revenue are distinct calculations with effective dates, currency rules, exclusions, and a review path. Finance owns those definitions. Engineering owns extraction and pipeline reliability. The data-platform owner supplies shared modeling and serving mechanics. A semantic layer or metrics repository can make definitions reusable, but it cannot settle policy without named decision rights.

Finally, a serving layer publishes known views: the official close, a product-revenue summary, an account-health view, or a customer-facing usage total. Each view states its grain, permitted filters, refresh behavior, and whether it is official or exploratory. An OLAP serving system may make these constrained questions predictably fast with rollups, cubes, materialized views, or other precomputed layouts. Its speed is purchased by deciding in advance which dimensions and aggregations it will support.

Lineage connects the whole path. From the $4.76 million dashboard total, an operator should reach the serving model, its definition version, the transformation run, the accepted and rejected inputs, and the authoritative source records. Without that path, a data incident becomes an argument among teams holding different screenshots.

Choose Roles, Not Camps

A warehouse is a strong default for governed SQL analysis, curated tables, dimensional models, shared reporting, access control, and workload management. It earns the central role when many consumers need reviewable definitions and predictable analytical operations. It can still decay into hidden transforms, undocumented dependencies, and unmanaged spend if table contracts and ownership remain implicit.

A data lake uses object storage as a flexible landing and processing zone. It suits raw source history, semi-structured inputs, large files, long retention, and processing by more than one engine. Object storage supplies durable bytes, not table meaning. Without catalog discipline, file-layout rules, permissions, quality checks, and owners, a lake is evidence that nobody can safely interpret.

A lakehouse adds table semantics over lake storage: schema and partition metadata, versioned snapshots, table-level commits, schema evolution, and coordination among query engines. That can combine flexible storage with governed analytical tables. It also creates work in catalogs, metadata scaling, compaction, file sizing, permissions, and compatibility among table formats and engines. The architecture has moved responsibility; it has not removed it.

An OLAP serving store belongs where repeated analytical questions need low and predictable latency. It is often downstream from the place that retains raw history or performs broad modeling. Loading every source directly into the serving store confuses fast answers with recoverable truth: when a definition changes, the team still needs retained evidence and a reproducible way to rebuild the result.

These roles can coexist without forming a mandatory stack. Invoice records might land in object storage, become governed tables in a warehouse or lakehouse, and feed a narrow serving aggregate for a customer dashboard. A smaller system might meet its needs with one governed warehouse. Add a component only when a named workload or recovery obligation earns its operational cost.

Freshness Is an Observable State

The revenue report is not simply current or stale. Its state includes the latest complete source interval, the age of each input, rejected records, pipeline progress, and the policy for facts still in motion.

A batch close can wait for declared source cutoffs, check input completeness, reconcile totals, and publish one stable snapshot. Its delay is acceptable only because the decision permits it. A streaming path reduces that delay but must reason about event time, processing time, duplicates, out-of-order arrival, partial outages, and replay. A number that updates every few seconds can look authoritative while quietly omitting late records.

For invoice I-2048, the late refund forces a policy decision. The system may revise the prior period, post an adjustment in the current period, preserve an as-reported close beside a restated view, or publish both operational and accounting interpretations. Silent mutation is the one indefensible choice. Consumers need to know whether a changed number represents new evidence, a definition change, or a defect repair.

Freshness must be inspectable where the decision is made. Record the last complete source interval and successful publication, alert when the target is missed, and expose partial or degraded states rather than rendering an old total as if nothing happened. The target should follow the consumer’s consequence: fraud review, fulfillment, and customer usage may need minutes; a board report may value a controlled close over immediacy.

Build for the Second Answer

Analytical logic will be rerun. A source repairs history, a currency rule changes, a transformation bug is found, or a model acquires a new definition. If outputs cannot be rebuilt, the first published answer becomes permanent because correction is too dangerous.

Reprocessing needs stable source identifiers, retained raw history, versioned transformation logic, replaceable or explicitly versioned outputs, and a record of which consumers received the earlier result. The team should be able to run the new logic in isolation, compare old and new totals, approve the difference, publish atomically at the serving boundary, and communicate the correction.

Reconciliation constrains that freedom. Invoice totals should tie back to billing at the chosen boundary; payments to processor or ledger evidence; refunds to their authoritative source. Completeness, uniqueness, referential integrity, valid ranges, and anomaly checks catch different failures. No one check establishes correctness, but together they prevent the analytical copy from drifting unnoticed away from operational facts.

The close should therefore preserve two kinds of evidence: the business facts that support the result and the execution record that shows which versions, inputs, checks, and exceptions produced it. That is what lets an operator distinguish a legitimate restatement from a broken rerun.

Make the Physical Design Answer the Questions

Analytical systems make expensive work easy to request. A query can scan years of history; a dashboard can repeat the scan all day; a streaming job can produce many tiny files; a team can retain raw events forever because choosing an expiry feels irreversible.

Layout should follow measured access paths. Time partitioning helps when most queries bound time, but overly fine partitions create metadata and file-management overhead. Clustering or sorting helps only on dimensions that prune real work. Columnar files should be large enough for efficient scans without making ingestion, compaction, and replacement unwieldy. Small-file accumulation needs a compaction policy and capacity that does not compete with current ingestion.

Precomputed serving tables, materialized views, cubes, and rollups trade flexibility and maintenance for latency and lower repeated scan cost. Record their source model, grain, refresh cadence, invalidation behavior, and rebuild path. Otherwise they become fast, stale copies of business logic that nobody owns.

Workload isolation protects both the platform and the close. Exploration, scheduled transformation, dashboard serving, backfill, and data-science jobs should not share unlimited capacity. Query limits, refresh policies, cost attribution, retention tiers, and budget alerts turn cost into an operating signal. Sudden spend usually reveals an unbounded scan, a duplicated transform, a hot dashboard, or retention that outlived its purpose.

Long retention also carries deletion, privacy, access, and meaning-drift obligations. Copies may exist in raw files, modeled tables, extracts, caches, and serving stores. A deletion policy that stops at the source system does not cover the analytical system.

Write the Product Record

Before a dataset becomes official, write one short record for the analytical product—not one generic platform diagram. The record should be concrete enough that another engineer can trace a disputed number and plan a rebuild.

Decision served:
Official status and owner:

Authoritative sources and stable identifiers:
Extraction method and expected source delay:

Fact tables and grain:
Dimensions and history policy:
Metric definitions and definition owner:

Freshness target and acceptable lateness:
Late-fact and closed-period policy:
Last-complete signal and missed-target alert:

Completeness, uniqueness, validity, and reconciliation checks:
Rejected-record handling:

Retained evidence and transformation versions:
Backfill, comparison, approval, and publication procedure:

Serving tables, supported questions, and consumers:
Retention, deletion, access, and audit policy:
Layout, refresh, query, and budget controls:

For the revenue close, fill the record with invoice, line, payment, refund, account, and currency sources. State whether I-2048 contributes booked, collected, recognized, or refunded revenue and on which effective date. Name the official serving table, the close cutoff, the late-refund policy, the source reconciliation, and every dashboard or export that must be notified after a restatement.

Then rehearse the correction. Publish the close, deliver the late refund, and discover a transform bug. Rebuild without reading from mutable serving output. Compare both versions, explain every material difference, preserve the as-reported result where policy requires it, and replace the official view without presenting a partial load.

The exercise is complete when the changed number no longer begins an archaeological dig. Its source evidence, grain, meaning, freshness, correction history, owner, and serving contract are all visible. That is the analytical system: not a destination for copied data, but a governed path from operational fact to a decision people can defend.