Chapter

Warehouses, Lakes and Analytical Tables

Part G · From Records to Insight|3,873 words|about 17 min read|Volume G

42.0 What this chapter gives you#

The database that accepts one customer’s order and the system that studies five years of orders solve different workloads. They may share data, but they need not share every storage layout, query pattern or operating boundary.
You will distinguish an analytical model from the files holding it, then connect facts, dimensions, catalogues and snapshots. The aim is to understand responsibilities rather than memorize product labels.
The examples retain the shop’s four agreed lines and introduce separately labelled analytical fixtures. No warehouse service, object-storage account or Iceberg engine is deployed by the companion exercises.

42.1 Operational versus analytical work#

42.1.1 PLAIN — in simple words#

Operational work asks small questions while doing the business: Can this order be accepted? Which items are available? Has this request already succeeded?
Analytical work asks questions across many records: How did quantities vary by month? Which report definitions disagree? What changed after a process revision?
The same underlying facts can support both, but a large report should not accidentally prevent the shop from accepting orders. Sharing data creates a need to manage resource use and freshness expectations.

42.1.2 PLAIN — a picture in your head#

Mira’s counter register is arranged for finding and changing one order quickly. Her annual review uses copies spread across a large table so she can compare many months at once.
Doing the annual review directly on the busy counter would obstruct customers, even if every calculation were correct.
Where the comparison breaks: one database can support mixed workloads with suitable design. Separate operational and analytical systems are an architectural choice, not a law requiring every small shop to buy two products.

42.1.3 PLAIN — a worked example#

Suppose the counter issues 100 short requests per second, while a monthly report scans 50 million historical lines. These are invented workload assumptions, not measurements of KedByte’s systems.
The report’s acceptable completion time might be minutes, while a counter request has a much shorter service objective. Running both without resource controls can make their interests conflict.
A reporting copy could isolate some work, but it introduces a delay between accepted orders and report availability. A label such as “data applied through 10:30” becomes part of the report’s interpretation.
The canonical four-line exercise is much smaller. It does not justify a distributed warehouse. Its value is teaching how the responsibilities change as the workload and organization grow.

42.1.4 PLAIN — what is really happening inside#

Operational designs often emphasize constrained updates and targeted access. Analytical designs often emphasize scanning selected columns, joining descriptive data and aggregating large populations.
Copying data separates some resource and failure boundaries, but adds capture, reconciliation and access-management duties. A second system is not a free performance improvement.
Materialized results can reduce repeated computation. Their usefulness depends on whether the refresh policy is compatible with the question’s required freshness.

42.1.5 TECHNICAL — the engineer’s version#

OLTP and OLAP are workload categories, not universal product classes. Characterize transaction size, scan volume, concurrency, update patterns, latency targets and consistency requirements before choosing an architecture.
PostgreSQL materialized views store query results for later access and may be stale until refreshed. A materialized result is not automatically synchronized with every source change. [S169]
Isolation through replicas or separate stores must be evaluated against capture lag, query visibility and recovery procedures. A report’s source cut is part of its correctness claim, not merely an operations statistic.

42.1.6 WORDS — remember these#

Operational workload: queries and changes that run the business — work often focused on small, current, constrained transactions. Analytical workload: queries that study populations of records — work often involving scans, grouping and historical comparison. Materialized view: a stored query result — derived data whose refresh policy determines how current it is.

42.2 Warehouse modelling#

42.2.1 PLAIN — in simple words#

An analytical model organizes measurable facts and the descriptions used to group them. An order-line quantity is a fact; a product category is a description that might help group facts.
Start by saying what one fact row means. A row for an order line cannot be mixed casually with a row for an entire order or a daily stock snapshot.
Historical descriptions need a policy. A customer changing category today does not automatically mean that last year’s report should use the new category.

42.2.2 PLAIN — a picture in your head#

Mira places each order slip beside cards describing its product, branch and date. The slip holds the agreed quantity and price; the cards help arrange the slips for different questions.
If a descriptive card changes, she must decide whether old slips keep their earlier description or use today’s classification.
Where the comparison breaks: a dimension join can multiply rows when several description versions match one fact. Correct analytical modelling requires enforced or checked uniqueness of the matching interpretation.

42.2.3 PLAIN — a worked example#

Define the fact table’s grain as one agreed order line. The four canonical lines retain quantities 2, 1, 1 and 2 and line amounts 15,100, 2,000, 7,550 and 4,000 paise.
Grouping by product gives notebook quantity 3 and amount 22,650, and pen quantity 3 and amount 6,000. These still reconcile to six items and 28,650 paise.
Introduce a separate customer C-DEMO whose category is Retail for times [0, 100) and Trade from 100 onward. A synthetic fact at time 90 matches Retail; a fact at 110 matches Trade under an event-time classification policy.
If the Retail interval accidentally ends at 120, the time-110 fact matches both versions. A join now duplicates it. The defect lies in overlapping validity intervals, not in the addition function.

42.2.4 PLAIN — what is really happening inside#

A star-shaped model places a fact table near descriptive dimension tables. Keys connect the fact to one intended dimension interpretation.
Some dimensions overwrite attributes and answer questions using current descriptions. Others retain versions with validity boundaries, often called slowly changing dimensions. The selected history policy changes the answer.
Measures also have aggregation rules. Line amounts can be added within compatible currency and scope. A daily closing-stock value cannot be summed across days and called current stock, because each day is another observation of a state.

42.2.5 TECHNICAL — the engineer’s version#

Declare fact grain, dimensional keys, measurement units and additive scope. A measure may be additive across products but not across time or incompatible currencies; a ratio usually requires preserving its numerator and denominator.
In the versioned-dimension example, enforce or validate one matching interval for each fact’s key and reference time. A surrogate dimension key identifies a version but does not prove that its business-time assignment is correct.
Relational joins preserve the multiplicities implied by their matching conditions. The model’s correctness therefore includes dimension coverage and non-overlap checks, not only a foreign key to an existing dimension row. [S58] [S74]

42.2.6 WORDS — remember these#

Fact table: measurable observations at a declared grain — analytical records whose keys and units define what may be aggregated. Dimension: descriptive context for grouping facts — attributes connected to facts under a current or historical interpretation. Slowly changing dimension: a policy for evolving descriptions — a modelling approach that may overwrite attributes or preserve dated versions.

42.3 Files and catalogues#

42.3.1 PLAIN — in simple words#

A collection of files does not automatically form a reliable table. A reader needs to know which files belong, what their fields mean and which versions are current.
A catalogue supplies organized metadata about tables and their locations or definitions. It is a map, not the data itself and not automatically an access-control system.
“Lake” commonly describes a storage approach built around collections of data files. “Warehouse” commonly emphasizes managed analytical tables and queries. Real architectures can combine these ideas, so labels alone do not settle capabilities.

42.3.2 PLAIN — a picture in your head#

A storeroom contains boxes of order copies. The boxes have value, but a researcher cannot know which ones form the approved September collection without a catalogue.
A catalogue entry says which boxes to read and which schema explains their contents. It can also distinguish an old collection from a new one.
Where the comparison breaks: files can be uploaded concurrently, replaced or left orphaned after failed work. A directory listing is not necessarily the same thing as an atomic table publication protocol.

42.3.3 PLAIN — a worked example#

Suppose a table version consists of immutable files F1 and F2. A writer prepares replacement files F3 and F4 but has not yet published the new table metadata.
A reader that scans every visible file can accidentally combine F1, F2, F3 and F4, duplicating facts. A reader using the committed manifest reads only the files named by its chosen table version.
An abandoned upload F5 should not become part of the table simply because it exists beside the other files. It needs a successful publication boundary or a cleanup decision.
These file labels are an original conceptual model. The exercise does not rely on claims about a particular cloud provider’s directory-listing consistency or object-storage API.

42.3.4 PLAIN — what is really happening inside#

Data files store encoded records. Table metadata identifies schema, partitioning and the files or manifests making up a table state. A catalogue helps locate and update that metadata.
Readers follow a selected committed metadata version rather than guessing from whatever objects happen to be visible. This separates preparation from publication.
Storage permissions, catalogue permissions and query-engine permissions may be different boundaries. A user denied access through a dashboard might still read raw files if the underlying storage is broadly exposed.

42.3.5 TECHNICAL — the engineer’s version#

Apache Iceberg specifies table metadata, snapshots and manifests above data-file formats. It distinguishes the logical table state from individual stored objects. [S168]
Apache Parquet specifies a columnar file representation with metadata and column chunks. A Parquet file alone does not provide a multi-file transaction manager or a universal business schema. [S170]
Evaluate catalogues for identity, atomic update support, concurrency handling, permissions and interoperability. An architectural label such as lakehouse is not itself evidence that each requirement has been implemented and tested.

42.3.6 WORDS — remember these#

Catalogue: organized metadata about data assets — a service or representation locating tables and describing their definitions and versions. Manifest: an explicit inventory for a data version — metadata naming the files or entries that belong to a committed state. Orphan file: a stored object not referenced by an accepted table state — prepared or abandoned data requiring a deliberate retention or cleanup policy.

42.4 Table snapshots#

42.4.1 PLAIN — in simple words#

A table snapshot identifies one coherent version of an analytical table. A reader can continue using that version while a writer prepares another.
Publishing a new snapshot should not accidentally discard another writer’s accepted work. The update needs a rule for detecting that its starting version has become stale.
Keeping older snapshots allows historical reading only while the required files and metadata still exist and access remains permitted. A snapshot name is not an eternal recovery guarantee.

42.4.2 PLAIN — a picture in your head#

Mira publishes a numbered catalogue page listing the boxes for edition 10. Dev reads that page while she prepares edition 11 elsewhere.
Another assistant also starts from edition 10. Before publishing, both must check whether edition 10 is still the current starting point; otherwise the second publication could erase the first assistant’s additions.
Where the comparison breaks: real table systems use storage and catalogue-specific atomic operations and conflict checks. A paper edition number explains the condition but does not implement distributed concurrency control.

42.4.3 PLAIN — a worked example#

Our metadata model starts at version 10 with files {F1, F2}. Writer A prepares {F1, F2, F3} based on version 10 and publishes version 11.
Writer B prepares {F1, F2, F4}, also based on 10. An unconditional replacement would lose F3 from the current table view.
A compare-and-set publication checks that the current version still equals the expected base. B’s expected 10 no longer matches 11, so the stale publication is rejected without changing the current manifest.
B can review the new state and prepare a compatible result. Whether appending F4 can be safely combined depends on the operation and conflict policy; not every failed transaction can be retried by blindly taking a set union.

42.4.4 PLAIN — what is really happening inside#

Immutable data and metadata versions permit readers to hold a stable view while writers produce new objects. The accepted metadata pointer selects which version becomes current.
Field identity matters during schema changes. A field renamed from unit_price to agreed_unit_price should not be confused with a newly introduced field that happens to reuse an old name or position.
Expiration and cleanup remove historical states according to policy. A file still required by a retained snapshot cannot simply be deleted because the latest snapshot no longer uses it.

42.4.5 TECHNICAL — the engineer’s version#

Iceberg uses field IDs to preserve column identity across schema naming and ordering changes. Its metadata-commit operation must atomically replace the version on which a change was based; the catalogue-specific atomic mechanism is outside the core format specification. [S168]
The local compare-and-set manifest example tests a stale-writer condition only. It does not implement Iceberg’s file-level conflict validation, delete files, catalogues, storage durability or full schema evolution.
A time-travel query is bounded by retained metadata and files, supported reader versions and permissions. Recovery, audit retention and deletion obligations require separate design decisions beyond retaining a snapshot list.

42.4.6 WORDS — remember these#

Table snapshot: a coherent table version — metadata selecting the files and interpretation visible to a reader. Atomic metadata publication: one accepted version replaces its expected predecessor — a concurrency boundary preventing incompatible partial or stale updates. Field ID: stable column identity independent of a display name — metadata used to preserve meaning through supported schema changes.

42.5 Governance boundaries#

42.5.1 PLAIN — in simple words#

Copying data for analysis creates another place where it must be protected, explained and eventually retained or removed according to policy.
A clean dashboard does not prove that the raw files, temporary exports and query logs are equally controlled. Governance follows the information through all relevant representations.
Ownership should identify who defines a metric, who may change its meaning and who handles a disagreement. Otherwise two carefully built reports can become competing versions of the truth without an agreed resolution process.

42.5.2 PLAIN — a picture in your head#

Mira locks the main register but leaves its photocopies on an open table. Protecting the original does not protect the copies.
She also needs labels explaining whether a report is provisional, corrected or approved for a particular purpose. A polished cover is not a governance state.
Where the comparison breaks: digital data can be copied into caches, notebooks and exported files automatically. A complete inventory needs technical discovery and process ownership, not only visible office shelves.

42.5.3 PLAIN — a worked example#

An analyst needs monthly product quantities. The four canonical order lines are sufficient to derive quantities 3 and 3 for notebook and pen; customer names are unnecessary for this particular calculation.
Including every customer attribute in the export increases exposure without helping that stated question. A narrower approved dataset can reduce the information handled.
A report row should retain its definition and source version, so a later reviewer can trace 22,650 notebook paise to the three agreed notebook units rather than a current-price lookup.
If the underlying line data is later removed under policy, the system must state which aggregate evidence remains and whether detailed reconstruction is still possible. It must not claim reproducibility from inputs it no longer holds.

42.5.4 PLAIN — what is really happening inside#

Governance combines metadata, access rules, retention decisions, ownership and evidence. No single catalogue field enforces all of these automatically.
Derived tables may preserve sensitive information even when direct identifiers are removed. Small groups, unique combinations and retained links can make a supposedly anonymous aggregate revealing.
Testing should include access through storage paths, query engines, extracts and recovery environments. Restoring an old snapshot may reintroduce information that has since been restricted or deleted unless the restore process accounts for that history.

42.5.5 TECHNICAL — the engineer’s version#

Treat lineage as a dependency graph from source versions through transformations to outputs. Record both the useful audit relationships and the access/retention implications of keeping those relationships.
Iceberg snapshot expiration describes removal of historical table references and eventual eligibility of unused files for cleanup. It is not a universal guarantee of erasure from every backup, export or consumer. [S168]
The chapter provides engineering boundaries, not legal retention periods or a certification of anonymization. Actual retention, exceptions and access decisions need the organization’s applicable requirements and authorized review.

42.5.6 WORDS — remember these#

Data lineage: how outputs depend on inputs — a recorded graph of source versions, transformations and derived assets. Metric owner: the accountable interpreter of a reported measure — a role responsible for definition changes, exclusions and disputes. Data minimization: limiting information to a justified purpose — a design practice reducing unnecessary collection, copying and exposure.

42.6 Selecting a design by workload#

42.6.1 PLAIN — in simple words#

Select a design by the questions, scale, update rate and operational responsibilities it must handle. A fashionable label is not a requirement.
A small consistent database with clear reports may be better suited to a modest task than a complicated collection of distributed services. A much larger workload may justify additional machinery.
Compare complete operating systems of work: ingestion, queries, corrections, access, recovery and cost. Fast query demonstrations alone leave many important responsibilities untested.

42.6.2 PLAIN — a picture in your head#

Mira does not build a warehouse complex to store four slips. She starts with a system she can understand and maintain.
When she adds many branches and years of history, she measures which part becomes difficult before expanding the design.
Where the comparison breaks: software migrations can be costly even before physical scale becomes large. Compatibility, team skills and future requirements matter, but they should be recorded as assumptions rather than treated as certainty.

42.6.3 PLAIN — a worked example#

Define three hypothetical requirements: refresh within 15 minutes, answer the monthly quantity report within 20 seconds under ten concurrent readers, and reproduce a selected earlier report from retained inputs.
Candidate A might use one database with controlled reporting queries. Candidate B might use a reporting replica. Candidate C might use captured files and a separate analytical engine.
For each candidate, test the same input, report definition and freshness condition. Include the cost of maintaining captures, updates, deletions and recovery, not only query elapsed time.
A candidate that answers in two seconds but omits the latest hour fails the stated freshness requirement. Another that meets both timing goals but cannot explain its input version fails the stated reproduction requirement.

42.6.4 PLAIN — what is really happening inside#

Architecture selection is a constraint problem. Some designs reduce scan cost but complicate updates; others simplify transactions but make very large historical scans expensive.
Separate reversible decisions from difficult commitments. File format, catalogue semantics, key design and source-history retention can constrain later migration.
Record failure cases as part of evaluation. A system should be observed while a load is late, a writer conflicts, a source changes schema and a restore is required, not only while every component is healthy.

42.6.5 TECHNICAL — the engineer’s version#

A workload specification should include data volume and distribution, query mix, concurrency, update/delete rates, service objectives, source lag, retention and recovery requirements. Avoid treating row count as a complete capacity model.
File layout, table format, catalogue and query engine are separate components with separate compatibility and failure properties. Measure their combined behaviour rather than assuming a capability from a format’s name. [S168] [S170]
The book’s analytical examples validate selected arithmetic, model boundaries and snapshot predicates. They are not a benchmark ranking or a procurement recommendation for a particular managed service.

42.6.6 WORDS — remember these#

Workload specification: a description of the actual work — volumes, distributions, query patterns and operating requirements used to evaluate a design. Freshness objective: how current a result must be — an explicit bound connecting publication to its required source state. Architecture trade-off: a benefit obtained with another cost or constraint — a choice evaluated against the complete task rather than one isolated metric.

42.97 Practice and worked answers#

Question: Why separate operational and analytical workloads? Answer: They can compete for resources and have different response, history and freshness needs. Separation is a choice with additional copy-management costs.
Question: What is the canonical fact-table grain? Answer: One agreed order line, not one order, payment or daily stock snapshot.
Question: Why can a historical dimension join double a fact? Answer: Overlapping validity intervals can make several dimension versions match the same fact.
Question: Does a Parquet file provide table transactions by itself? Answer: No. Multi-file table state and publication require additional metadata and coordination.
Question: What should writer B do after its expected base version 10 no longer matches current version 11? Answer: Reject the stale publication, inspect the new state and retry only under a compatible conflict policy.
Question: Can an expired snapshot always be queried later? Answer: No. Its metadata or required files may no longer be retained or accessible.
Question: Does the monthly product-quantity report need customer names? Answer: Not for the stated calculation. Additional fields need a separate justified purpose.
Question: A query is fast but one hour stale against a 15-minute requirement. Does it pass? Answer: No. Speed does not substitute for freshness.

42.98 Common wrong ideas#

Wrong: OLTP and OLAP must always use different products. Right: They are workload categories; architecture follows measured requirements.
Wrong: A warehouse fact table can mix grains freely. Right: Mixed grains produce ambiguous aggregation.
Wrong: Today’s category must describe every historical fact. Right: Historical interpretation requires an explicit policy.
Wrong: Every file in a folder belongs to the current table. Right: Committed metadata defines accepted membership.
Wrong: A catalogue automatically protects raw storage. Right: Access boundaries must be checked separately.
Wrong: A snapshot identifier guarantees permanent recovery. Right: Recovery depends on retained files, metadata, compatibility and permission.
Wrong: An aggregate contains no sensitive information. Right: Small or linkable groups can still reveal information.
Wrong: The most elaborate architecture is the most complete solution. Right: Unnecessary components add responsibilities and failure modes.

42.99 Chapter summary in 20 lines#

Operational and analytical work ask different kinds of questions.
Shared resources can make those workloads interfere.
Reporting copies introduce freshness and reconciliation duties.
Materialized results must have a refresh policy.
State one fact row’s grain precisely.
Dimensions provide descriptive grouping context.
Historical dimension changes need an interpretation rule.
Overlapping dimension versions can multiply facts.
Measures have explicit additive scopes and units.
A collection of files is not automatically a coherent table.
Catalogues describe and locate data assets.
Manifests identify accepted file membership.
Snapshots give readers a selected table version.
Atomic metadata updates detect stale writers.
Stable field IDs separate identity from naming.
Snapshot retention bounds time travel and cleanup.
Governance follows raw and derived copies alike.
Minimize data to the justified analytical purpose.
Evaluate query speed together with freshness and recovery.
Choose machinery by workload evidence, not architectural fashion.

Return to contents