Gowtham Potureddi

Posted on Aug 3

Anchor Modeling & 6NF: Temporal, Agile, Immutable Warehouse Design

#python #sql #interview #dataengineering

anchor modeling is the warehouse design discipline that takes normalization to its logical extreme — one table per entity identity, one table per attribute, one table per relationship — so that a schema can absorb new facts, track every historical change, and reconstruct any past state without ever rewriting a row or running a destructive migration. Where a Kimball star schema packs twenty columns into a wide dimension and a Data Vault satellite groups a handful of descriptive fields, Anchor Modeling decomposes the entity all the way down to sixth normal form — the point at which each relation holds nothing but a key and, at most, a single non-key value. The payoff is a schema that treats change as the default rather than the exception: a new attribute is a new table, a corrected fact is a new row, and the history of the business is preserved by construction.

That payoff is why senior data engineers and architects keep circling back to 6NF when they inherit a warehouse whose columns change every quarter, whose auditors demand a defensible history, and whose analysts keep asking "what did this customer look like on the day they churned?" This guide is the senior walkthrough of Anchor Modeling as a temporal database technique: the four constructs (anchors, attributes, ties, and knots), the immutable data append-only rule that makes history free, point-in-time and bitemporal reconstruction, additive schema evolution that ships with zero downtime, and the join-heavy trade-off that every skeptic raises first. Each section pairs a teaching block with a Solution-Tail interview answer — real SQL, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the dimensional-modeling practice library →, rehearse the temporal SQL on the SQL practice library →, and sharpen the history-tracking axis with the slowly-changing-data practice library →.

On this page

Why 6NF and Anchor Modeling exist
Anchors and attributes: the 6NF core
Ties and knots: relationships and shared domains
Temporality: point-in-time, bitemporal, and immutable history
Schema evolution, performance, and the tooling verdict
Cheat sheet — Anchor Modeling and 6NF recipes
Frequently asked questions
Practice on PipeCode

1. Why 6NF and Anchor Modeling exist

The two problems every warehouse eventually hits — schema churn and lost history — and the "decompose to the irreducible" answer

The one-sentence invariant: Anchor Modeling exists because the two forces that break every long-lived warehouse are schema change and history loss, and the discipline answers both by decomposing every entity to sixth normal form — one table for identity, one table per attribute, one table per relationship — so that adding a fact never rewrites a table and recording a change never overwrites the past. A wide table couples every column to every other column: adding a nullable column locks the table, changing a type rewrites it, and an in-place UPDATE silently destroys the prior value. Anchor Modeling breaks that coupling at the schema level. Each attribute lives alone, so it can be added, historized, or retired without touching any other attribute; each fact is written once and never updated, so the full history is a property of the storage rather than a feature you have to bolt on.

The two problems that motivate extreme normalization.

Schema evolution. Business requirements change faster than DDL review cycles. In a wide dimension, "add a customer loyalty tier" means ALTER TABLE dim_customer ADD COLUMN, a lock, a backfill, and a downstream contract renegotiation. In Anchor Modeling it means CREATE TABLE for one new attribute — additive, isolated, and reversible.
Temporality. Analysts and auditors need to know not just the current state but every past state: what the price was on the invoice date, what the customer's address was when they placed the order. A destructive UPDATE throws that away. Sixth normal form plus append-only inserts keeps it by construction — the history is the table.

The "decompose to the irreducible" philosophy.

Sixth normal form defined. A relation is in 6NF when it cannot be decomposed further without loss — informally, each relation holds a key plus at most one non-key attribute. Where 3NF removes transitive dependencies and 5NF removes join dependencies, 6NF removes all non-trivial join dependencies, leaving atoms.
Why go this far. Because the atom is the unit of independent change. When an attribute is alone in its own relation, it evolves independently, historizes independently, and is queried independently. The cost of composing atoms back into a row is paid at read time by a join; the benefit is that write-time change is free.
Immutability follows naturally. Once each attribute is a thin key-plus-value relation with a validity timestamp, the natural write is an INSERT of a new version, never an UPDATE of the old one. Immutable, append-only storage is not a bolt-on; it is what 6NF makes obvious.

How it compares to Data Vault and Kimball.

Kimball (dimensional). Wide denormalized dimensions and fact tables optimized for BI query simplicity. History is handled with slowly-changing-dimension (SCD) types bolted onto the dimension. Fewest joins, easiest for analysts, worst for schema churn and fine-grained history.
Data Vault. Hubs (business keys), links (relationships), and satellites (grouped descriptive attributes with load timestamps). A middle ground: more normalized than Kimball, less atomic than Anchor Modeling. Satellites group several attributes, so adding one attribute may still touch a satellite.
Anchor Modeling. Anchors (identity), attributes (one per property), ties (relationships), knots (shared domains). The most normalized of the three: each attribute is its own table, so change is maximally isolated and history is maximally granular — at the cost of the most tables and the most joins.

What interviewers actually probe.

Can you name the four constructs — anchors, attributes, ties, knots — and say what each holds? — required answer.
Do you explain why one table per attribute in terms of independent evolution and historization, not "because normalization is good"? — senior signal.
Do you volunteer the join-cost trade-off and the mitigation (views, join/table elimination in columnar engines) before being asked? — senior signal.
Do you place Anchor Modeling relative to Data Vault and Kimball rather than as an isolated technique? — senior signal.
Do you describe history as "append a new version, never overwrite" rather than "we add an SCD2 flag"? — required answer.

Worked example — the Anchor vs Data Vault vs Kimball comparison table

Detailed explanation. The single most useful artifact for an extreme-normalization interview is a memorised comparison across the three paradigms. Every discussion of Anchor Modeling converges on "how is this different from Data Vault?" within minutes; having the axes in your head is what separates a fluent answer from a hand-wave. Walk through building the table for a Customer entity that needs volatile attributes, full history, and frequent schema change.

Entity. Customer with identity plus name, email, loyalty tier, and status.
Requirement. Track every change to every attribute; add new attributes quarterly; reconstruct state as-of any date.
Axes. Granularity of decomposition, unit of schema change, how history is stored, join count at read, and analyst friendliness.

Question. Build the three-paradigm comparison for the Customer entity and state which requirement each paradigm serves best.

Input.

Axis	Kimball (star)	Data Vault	Anchor Modeling
Unit of decomposition	wide dimension	hub + grouped satellites	anchor + one table per attribute
Add one attribute	ALTER wide table	ALTER or new satellite	new attribute table (CREATE only)
History mechanism	SCD1/2/3 flags	satellite load-date rows	append-only historized attribute
Joins to read full entity	0–1	few	many (one per attribute)
Analyst friendliness	highest	medium	lowest (needs views)

Code.

-- Kimball: one wide dimension, SCD2 for history
CREATE TABLE dim_customer (
    customer_sk   BIGSERIAL PRIMARY KEY,   -- surrogate
    customer_bk   BIGINT      NOT NULL,    -- business key
    name          TEXT,
    email         TEXT,
    loyalty_tier  TEXT,
    status        TEXT,
    valid_from    DATE NOT NULL,
    valid_to      DATE,                     -- NULL = current (SCD2)
    is_current    BOOLEAN NOT NULL DEFAULT TRUE
);

-- Data Vault: hub + one satellite grouping descriptive attributes
CREATE TABLE hub_customer (
    customer_hk   BYTEA PRIMARY KEY,        -- hashed business key
    customer_bk   BIGINT NOT NULL,
    load_ts       TIMESTAMPTZ NOT NULL,
    record_source TEXT NOT NULL
);
CREATE TABLE sat_customer_details (
    customer_hk   BYTEA  NOT NULL REFERENCES hub_customer,
    load_ts       TIMESTAMPTZ NOT NULL,
    name          TEXT,
    email         TEXT,
    loyalty_tier  TEXT,
    status        TEXT,
    PRIMARY KEY (customer_hk, load_ts)      -- history by load timestamp
);

-- Anchor Modeling: anchor + one table per attribute (6NF)
CREATE TABLE CU_Customer (
    CU_ID   BIGSERIAL PRIMARY KEY           -- identity only
);
CREATE TABLE CU_NAM_Customer_Name (
    CU_ID       BIGINT NOT NULL REFERENCES CU_Customer,
    CU_NAM_Name TEXT   NOT NULL,
    CU_NAM_ValidFrom DATE NOT NULL,
    PRIMARY KEY (CU_ID, CU_NAM_ValidFrom)   -- append-only history
);
-- ... CU_EMA_Customer_Email, CU_LOY_Customer_LoyaltyTier, CU_STA_Customer_Status,
--     each its own single-attribute historized table

Step-by-step explanation.

The Kimball dimension packs every attribute into one wide row. Reading a customer is a single lookup — the analyst's dream — but adding loyalty_tier was an ALTER TABLE with a lock and a backfill, and history is carried by the SCD2 valid_from/valid_to/is_current triple, which every query must filter on correctly.
The Data Vault splits identity (hub_customer) from description (sat_customer_details). History lives in the satellite's load_ts — each change inserts a new satellite row. But the satellite still groups four attributes, so changing only email writes a full satellite row, and adding a fifth attribute means altering the satellite or creating a second one.
Anchor Modeling gives identity its own table (CU_Customer, just a key) and every attribute its own historized table. Changing email inserts one row into CU_EMA_Customer_Email and touches nothing else. Adding loyalty_tier is a pure CREATE TABLE — no lock on anything existing.
The cost is visible in the join count: reconstructing the full customer row means joining the anchor to four attribute tables. Kimball needs zero joins; Data Vault needs one or two; Anchor Modeling needs one per attribute. This is the trade the interviewer is testing whether you understand.
The naming convention (CU_, CU_NAM_, CU_STA_) is not decoration — Anchor Modeling uses a strict mnemonic scheme so the four constructs are readable from the table name alone, which is what makes a hundred-table schema navigable.

Output.

Requirement	Best served by	Why
Simplest analyst queries	Kimball	one wide row, zero joins
Balanced history + auditability	Data Vault	satellites, load-date history, hashed keys
Maximal schema agility + granular history	Anchor Modeling	one table per attribute; additive change
Volatile attributes changing often	Anchor Modeling	isolated historized attribute tables

Rule of thumb. Never present Anchor Modeling as "better normalization." Present it as a trade: you buy maximal schema agility and granular immutable history, and you pay with many tables and read-time joins. State the trade in one breath — that is the senior framing.

Worked example — the "should we consider Anchor Modeling" rubric

Detailed explanation. Anchor Modeling is a specialist tool, not a default. A senior architect runs a short rubric before recommending it, because the join cost and table count only pay off under specific conditions. Codifying the rubric makes the recommendation defensible: you can point at the checklist rather than at taste.

Change frequency. How often does the schema change? Rarely → Kimball is fine. Quarterly or faster → Anchor Modeling's additive evolution earns its keep.
History granularity. Do you need per-attribute history, or is a coarse SCD2 snapshot enough? Per-attribute → Anchor Modeling; coarse → Data Vault or Kimball.
Temporality. Do you need point-in-time or bitemporal reconstruction? If auditors or regulators ask "what did this look like on date X," 6NF makes it natural.
Engine. Is the target a columnar engine with join/table elimination? If yes, the join cost is largely optimized away; if you are on a row-store with a naive planner, the join cost bites.

Question. Score three scenarios against the rubric and record the recommended paradigm for each.

Input.

Scenario	Change freq	History granularity	Temporality	Engine
Retail BI star	yearly	coarse	current + SCD2	row-store BI
Insurance policy warehouse	monthly	per-attribute	bitemporal (regulated)	columnar MPP
Startup event mart	weekly (early)	none needed	current only	columnar

Code.

# Rubric scorer — illustrative decision helper
def recommend_paradigm(change_freq_per_year: int,
                       needs_per_attribute_history: bool,
                       needs_bitemporal: bool,
                       columnar_engine: bool) -> str:
    """Return a warehouse-modeling paradigm recommendation."""
    agility = change_freq_per_year >= 4
    deep_history = needs_per_attribute_history or needs_bitemporal

    if deep_history and (columnar_engine or agility):
        return "Anchor Modeling (6NF)"
    if deep_history:
        return "Data Vault"
    if agility and not deep_history:
        return "Data Vault (lite) or Anchor Modeling"
    return "Kimball (star schema)"


print(recommend_paradigm(1,  False, False, False))   # → Kimball (star schema)
print(recommend_paradigm(12, True,  True,  True))    # → Anchor Modeling (6NF)
print(recommend_paradigm(52, False, False, True))    # → Data Vault (lite) or Anchor Modeling

Step-by-step explanation.

Scenario 1 (retail BI) changes yearly, needs only coarse SCD2 history, and runs on a row-store BI engine where joins are relatively expensive. The rubric returns Kimball — the extreme normalization would buy agility the business does not need and impose a join cost the engine does not want.
Scenario 2 (insurance) changes monthly, is legally required to answer "what was the policy state as-of the claim date and as-of what we knew at the time" (bitemporal), and runs on a columnar MPP engine. This is the sweet spot: deep_history is true and the columnar engine neutralizes the join cost, so the rubric returns Anchor Modeling.
Scenario 3 (startup mart) changes weekly during rapid iteration but needs no historical reconstruction. Agility without deep history points to Data Vault-lite or a pragmatic Anchor Modeling subset — the key driver is being able to add attributes without migrations, not history.
The engine axis is decisive and often forgotten. Anchor Modeling on a naive row-store planner is a join-cost trap; Anchor Modeling on a columnar engine that does join and table elimination is close to free at read time. Never recommend it without naming the engine.
The rubric refuses to recommend Anchor Modeling purely on "we like normalization." Deep history plus either agility or a columnar engine is the gate; miss both and the trade is not worth it.

Output.

Scenario	Recommendation	Deciding factor
Retail BI star	Kimball	low change + coarse history + row-store
Insurance policy warehouse	Anchor Modeling (6NF)	bitemporal + columnar
Startup event mart	Data Vault-lite / Anchor subset	agility without deep history

Rule of thumb. Recommend Anchor Modeling only when deep history (per-attribute or bitemporal) meets either high schema-change frequency or a columnar engine that eliminates the join cost. Absent both, a lighter paradigm wins. The rubric, not taste, defends the call.

Worked example — the four constructs at a glance

Detailed explanation. Before any DDL, the interviewer wants to hear the four constructs named and defined crisply. The whole of Anchor Modeling is these four table kinds and the rule that each stays as thin as possible. Walk through each construct and what it is allowed to contain.

Anchor. The immutable identity of an entity — a surrogate key and nothing else (plus optional metadata like a load timestamp). It never changes and is never deleted.
Attribute. A single property of an anchor, in its own table: the anchor's key, one value, and (if historized) a validity timestamp. One attribute, one table.
Tie. A relationship between two or more anchors: only their keys, plus an optional validity timestamp. Ties carry no business data of their own.
Knot. A shared, immutable, small enumeration — a lookup of a few fixed values (status codes, types, genders). Referenced by attributes so the enumeration is stored once.

Question. Classify each fragment of a Customer model into anchor, attribute, tie, or knot, and justify.

Input.

Fragment	Holds	Construct?
`CU_Customer(CU_ID)`	identity only	?
`CU_NAM(CU_ID, name, valid_from)`	one value + time	?
`CU_at_ST(CU_ID, ST_ID, valid_from)`	two keys + time	?
`STA_Status(STA_ID, value)`	fixed enum values	?

Code.

-- Anchor: identity only
CREATE TABLE CU_Customer (
    CU_ID BIGSERIAL PRIMARY KEY
);

-- Attribute: one property, historized
CREATE TABLE CU_NAM_Customer_Name (
    CU_ID            BIGINT NOT NULL REFERENCES CU_Customer,
    CU_NAM_Name      TEXT   NOT NULL,
    CU_NAM_ValidFrom DATE   NOT NULL,
    PRIMARY KEY (CU_ID, CU_NAM_ValidFrom)
);

-- Tie: relationship between two anchors (customer located at store)
CREATE TABLE CU_at_ST_Customer_Store (
    CU_ID              BIGINT NOT NULL REFERENCES CU_Customer,
    ST_ID              BIGINT NOT NULL,   -- REFERENCES ST_Store
    CU_at_ST_ValidFrom DATE   NOT NULL,
    PRIMARY KEY (CU_ID, ST_ID, CU_at_ST_ValidFrom)
);

-- Knot: shared immutable enumeration
CREATE TABLE STA_Status (
    STA_ID    SMALLINT PRIMARY KEY,
    STA_Value TEXT NOT NULL UNIQUE       -- 'active','dormant','closed'
);

Step-by-step explanation.

CU_Customer holds only CU_ID — pure identity, no describing values. That is the anchor. It is the join hub every attribute and tie references, and it never changes once created.
CU_NAM_Customer_Name holds the anchor key, one value (name), and a ValidFrom timestamp. One property, one table, historized by the timestamp in the primary key. That is an attribute.
CU_at_ST_Customer_Store holds two anchor keys and a validity timestamp — nothing else. It records that a customer is related to a store from a given date. That is a tie; note it carries no attribute of its own.
STA_Status holds a small fixed set of values shared across many customers. Rather than storing the string 'active' in every status row, attributes reference the knot's STA_ID. That is a knot — a shared immutable domain.
The naming convention encodes the construct: CU_ = anchor, CU_NAM_ = attribute of CU, CU_at_ST_ = tie between CU and ST, three-letter caps like STA_ = knot. Reading the type off the name is what keeps a 100-table Anchor model comprehensible.

Output.

Fragment	Construct	Reason
`CU_Customer(CU_ID)`	Anchor	identity only
`CU_NAM(CU_ID, name, valid_from)`	Attribute	one value, historized
`CU_at_ST(CU_ID, ST_ID, valid_from)`	Tie	keys only, relationship
`STA_Status(STA_ID, value)`	Knot	shared immutable enumeration

Rule of thumb. Memorise the four constructs as "identity, property, relationship, shared domain" and the rule "each stays as thin as possible." If a table you drew carries two describing values, it is not yet in 6NF — split it.

Senior interview question on positioning Anchor Modeling

A senior interviewer often opens with: "You are designing a warehouse for a regulated insurance line where product attributes change several times a year, auditors demand full historical reconstruction, and the target is a columnar MPP engine. The incumbent team wants a Kimball star. Make the case for Anchor Modeling over Data Vault and Kimball, name the trade-offs honestly, and say what you would do to keep analysts productive."

Solution Using a 6NF anchor design with views for analyst ergonomics

-- 1. Anchor + a couple of historized attributes for a Policy entity
CREATE TABLE PO_Policy (
    PO_ID       BIGSERIAL PRIMARY KEY,
    PO_Metadata TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()  -- load metadata only
);

CREATE TABLE PO_PRM_Policy_Premium (          -- premium changes several times a year
    PO_ID            BIGINT      NOT NULL REFERENCES PO_Policy,
    PO_PRM_Premium   NUMERIC(12,2) NOT NULL,
    PO_PRM_ValidFrom DATE        NOT NULL,
    PRIMARY KEY (PO_ID, PO_PRM_ValidFrom)
);

CREATE TABLE PO_STA_Policy_Status (
    PO_ID            BIGINT   NOT NULL REFERENCES PO_Policy,
    STA_ID           SMALLINT NOT NULL REFERENCES STA_Status,   -- knot
    PO_STA_ValidFrom DATE     NOT NULL,
    PRIMARY KEY (PO_ID, PO_STA_ValidFrom)
);

-- 2. Analyst-facing "latest" view hides the joins entirely
CREATE OR REPLACE VIEW v_Policy_Latest AS
SELECT p.PO_ID,
       prm.PO_PRM_Premium         AS premium,
       s.STA_Value                AS status
FROM   PO_Policy p
LEFT JOIN LATERAL (
    SELECT PO_PRM_Premium FROM PO_PRM_Policy_Premium a
    WHERE  a.PO_ID = p.PO_ID
    ORDER  BY a.PO_PRM_ValidFrom DESC LIMIT 1
) prm ON TRUE
LEFT JOIN LATERAL (
    SELECT STA_ID FROM PO_STA_Policy_Status a
    WHERE  a.PO_ID = p.PO_ID
    ORDER  BY a.PO_STA_ValidFrom DESC LIMIT 1
) st ON TRUE
LEFT JOIN STA_Status s ON s.STA_ID = st.STA_ID;

-- 3. Adding a new attribute next quarter is CREATE-only, zero migration
-- CREATE TABLE PO_RSK_Policy_RiskScore (PO_ID, PO_RSK_RiskScore, PO_RSK_ValidFrom, PK...);

Step-by-step trace.

Concern	Kimball star	Data Vault	Anchor Modeling (chosen)
Add premium history granularity	SCD2 on wide dim	new satellite row	one attribute table, append-only
Add a new attribute next quarter	ALTER + lock + backfill	ALTER/new satellite	CREATE TABLE only
Bitemporal reconstruction	hard (SCD2 only)	possible (load-date)	natural (per-attribute time)
Analyst query	0 joins	1–2 joins	joins hidden behind view
Columnar join cost	n/a	low	eliminated by planner

After the design lands, a premium change is one INSERT into PO_PRM_Policy_Premium; a status change is one INSERT into PO_STA_Policy_Status referencing the shared STA_Status knot; and next quarter's new RiskScore attribute is a single CREATE TABLE that locks nothing. Analysts never see the decomposition — they query v_Policy_Latest, and the columnar planner's join elimination keeps the view cheap.

Output:

Requirement	Result under Anchor Modeling
Per-attribute history	native (each attribute historized)
Quarterly schema change	additive, zero downtime
Auditor reconstruction	point-in-time + bitemporal supported
Analyst ergonomics	preserved via latest/as-of views
Join cost	eliminated on columnar engine

Why this works — concept by concept:

Sixth normal form — decomposing each property into its own key-plus-value relation makes every attribute an independent unit of change and history. That independence is the entire value proposition; it is what lets schema evolution be additive and history be free.
Immutable append-only attributes — a change is a new row keyed by (anchor_id, valid_from), never an UPDATE. The full history is a property of the table, so auditor reconstruction needs no separate audit log.
Knots for shared domains — statuses live once in STA_Status; attributes reference the knot key. This keeps enumerations consistent and storage small, and it makes renaming an enum label a one-row change.
Views hide the joins — analysts query v_Policy_Latest, not thirty attribute tables. The complexity is real but encapsulated; the ergonomics objection is answered by the view layer, not by denormalizing.
Cost — many tables and one join per attribute at read time, versus zero migration and free history at write time. On a columnar engine with join/table elimination the read cost is largely optimized away, making the net trade strongly favorable for volatile, regulated, temporal warehouses. O(1) writes per change; O(attributes) joins per full-row read, hidden behind a view.

Design
Topic — dimensional-modeling
Dimensional-modeling and paradigm-choice problems

Practice →

Design Topic — design Design problems on warehouse modeling trade-offs

Practice →

2. Anchors and attributes: the 6NF core

The anchor is pure identity and each attribute lives alone — one table per property is what makes change and history free

The mental model in one line: an anchor is nothing but an immutable surrogate key that represents an entity's identity for all time, and an attribute is a single property of that entity in its own table — the anchor's key, one value, and (when historized) a validity timestamp — so that every property can be added, changed, and versioned independently of every other property. This is the 6NF core of Anchor Modeling: a customer's name, email, and status are three separate tables, not three columns of one dimension. The apparent extravagance of one table per attribute is the exact mechanism that turns schema change into pure addition and turns history into append-only inserts you never have to design for separately.

What the anchor holds — and what it must not.

A surrogate key. A meaningless, stable identifier (BIGSERIAL, hash, or generated key). It represents identity, not any business value.
Optional metadata. A load timestamp or record-source tag is acceptable; a business attribute is not. The moment you put name on the anchor, you have left 6NF.
Immutability. The anchor row is created once and never updated or deleted. Every fact about the entity attaches to this key over time.

Static vs historized attributes.

Static attribute. A property that, by business rule, never changes — a birth date, a first-seen timestamp. Stored as anchor key plus one value, no validity timestamp.
Historized attribute. A property that changes over time — status, salary, email. Stored as anchor key plus value plus ValidFrom, with the pair (anchor_id, valid_from) as the primary key so each version is a distinct row.
The choice is per-attribute. Because each attribute is its own table, you decide historization one property at a time. A wide dimension forces one history policy on all columns; Anchor Modeling does not.

The append-only rule — history for free.

Never UPDATE. A change is an INSERT of a new (anchor_id, new_value, new_valid_from) row. The prior row stays untouched, so the history is complete by construction.
Latest = the max ValidFrom. The current value of an attribute is the row with the greatest ValidFrom for that anchor. A window function or DISTINCT ON retrieves it.
No separate audit table. Because every version persists, the attribute table is the audit trail. This is a major operational simplification over SCD2 flag juggling.

Why one table per attribute — the real reasons.

Independent evolution. Adding an attribute is CREATE TABLE; it locks nothing and requires no backfill of existing rows.
Independent historization. You can historize email without historizing birth_date, and change that decision later by creating a new table.
Independent typing and constraints. Each attribute carries its own type, nullability, and knot reference; a type change is scoped to one table.
Sparse-friendly. Entities that lack a property simply have no row in that attribute table — no NULLs sprawling across a wide dimension.

Worked example — anchor plus static and historized attribute DDL

Detailed explanation. The canonical anchor-and-attribute setup for an Actor entity: an anchor holding only identity, a static attribute for a value that never changes, and a historized attribute for a value that does. Build all three and load a couple of rows to see the shape.

Anchor. AC_Actor(AC_ID) — identity only.
Static attribute. AC_DOB_Actor_DateOfBirth — birth date never changes; no validity timestamp.
Historized attribute. AC_STG_Actor_Stagename — stage name can change; keyed by (AC_ID, ValidFrom).

Question. Write the anchor and both attribute tables, then insert one actor whose stage name changes once.

Input.

Object	Kind	Historized?
`AC_Actor`	anchor	n/a
`AC_DOB_Actor_DateOfBirth`	attribute	no (static)
`AC_STG_Actor_Stagename`	attribute	yes

Code.

-- 1. Anchor — identity only
CREATE TABLE AC_Actor (
    AC_ID       BIGSERIAL   PRIMARY KEY,
    AC_Metadata TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- 2. Static attribute — one value, no history (birth date never changes)
CREATE TABLE AC_DOB_Actor_DateOfBirth (
    AC_ID        BIGINT NOT NULL PRIMARY KEY REFERENCES AC_Actor,
    AC_DOB_Value DATE   NOT NULL
);

-- 3. Historized attribute — value + ValidFrom in the key (append-only)
CREATE TABLE AC_STG_Actor_Stagename (
    AC_ID            BIGINT NOT NULL REFERENCES AC_Actor,
    AC_STG_Value     TEXT   NOT NULL,
    AC_STG_ValidFrom DATE   NOT NULL,
    PRIMARY KEY (AC_ID, AC_STG_ValidFrom)
);

-- 4. Load one actor whose stage name changes once
INSERT INTO AC_Actor DEFAULT VALUES;                 -- AC_ID = 1
INSERT INTO AC_DOB_Actor_DateOfBirth (AC_ID, AC_DOB_Value)
VALUES (1, DATE '1975-06-01');

INSERT INTO AC_STG_Actor_Stagename (AC_ID, AC_STG_Value, AC_STG_ValidFrom)
VALUES (1, 'Reg Dwight', DATE '1975-01-01');         -- original
INSERT INTO AC_STG_Actor_Stagename (AC_ID, AC_STG_Value, AC_STG_ValidFrom)
VALUES (1, 'Elton John', DATE '1976-01-01');         -- change = new row, not UPDATE

Step-by-step explanation.

AC_Actor carries only AC_ID plus a load-metadata timestamp. No stage name, no birth date — those are separate concerns and therefore separate tables. This is the 6NF discipline made concrete.
AC_DOB_Actor_DateOfBirth is a static attribute: its primary key is just AC_ID because there is at most one birth date per actor and it never changes. No ValidFrom is needed — historizing an immutable value would be waste.
AC_STG_Actor_Stagename is historized: the key is (AC_ID, AC_STG_ValidFrom), so each stage name is a distinct row. The choice to historize this attribute but not DateOfBirth is possible precisely because they are separate tables.
The change of stage name is a second INSERT, not an UPDATE. After both inserts, the table holds two rows for actor 1 — the full history. Nothing was overwritten; nothing was lost.
Notice there is no NULL anywhere and no wide row. An actor with no recorded stage name simply has no row in AC_STG_Actor_Stagename — sparsity is represented by absence, not by NULL.

Output.

Table	AC_ID	Value	ValidFrom
AC_DOB_Actor_DateOfBirth	1	1975-06-01	(static)
AC_STG_Actor_Stagename	1	Reg Dwight	1975-01-01
AC_STG_Actor_Stagename	1	Elton John	1976-01-01

Rule of thumb. Put nothing but identity on the anchor, historize an attribute only when the business value actually changes over time, and record every change as an INSERT keyed by (anchor_id, valid_from). A static attribute keyed on the anchor alone is correct and cheaper than a needlessly historized one.

Worked example — the immutable append-only insert and latest-value read

Detailed explanation. The operational heart of Anchor Modeling is that writes are inserts and "current value" is a read-time computation. Walk through how a change is written and how the latest value is retrieved without ever mutating a row, using a historized Salary attribute on an Employee anchor.

Write. Every salary change inserts (EM_ID, new_salary, effective_date).
Read latest. DISTINCT ON (EM_ID) ... ORDER BY ValidFrom DESC (Postgres) or a windowed ROW_NUMBER.
No overwrite. The prior salary rows remain for history and audit.

Question. Insert three salary versions for one employee, then write the query that returns each employee's current salary.

Input.

Object	Purpose
`EM_Employee(EM_ID)`	anchor
`EM_SAL_Employee_Salary(EM_ID, value, valid_from)`	historized attribute
latest-value query	current salary per employee

Code.

-- Anchor + historized salary attribute
CREATE TABLE EM_Employee (EM_ID BIGSERIAL PRIMARY KEY);
CREATE TABLE EM_SAL_Employee_Salary (
    EM_ID            BIGINT        NOT NULL REFERENCES EM_Employee,
    EM_SAL_Value     NUMERIC(12,2) NOT NULL,
    EM_SAL_ValidFrom DATE          NOT NULL,
    PRIMARY KEY (EM_ID, EM_SAL_ValidFrom)
);

INSERT INTO EM_Employee DEFAULT VALUES;              -- EM_ID = 1

-- Three salary versions — each a new row, never an UPDATE
INSERT INTO EM_SAL_Employee_Salary VALUES (1,  60000, DATE '2023-01-01');
INSERT INTO EM_SAL_Employee_Salary VALUES (1,  66000, DATE '2024-01-01');
INSERT INTO EM_SAL_Employee_Salary VALUES (1,  72000, DATE '2025-01-01');

-- Latest value per employee (Postgres DISTINCT ON)
SELECT DISTINCT ON (EM_ID)
       EM_ID,
       EM_SAL_Value     AS current_salary,
       EM_SAL_ValidFrom AS since
FROM   EM_SAL_Employee_Salary
ORDER  BY EM_ID, EM_SAL_ValidFrom DESC;

-- Portable equivalent using a window function
SELECT EM_ID, EM_SAL_Value AS current_salary, EM_SAL_ValidFrom AS since
FROM (
    SELECT EM_ID, EM_SAL_Value, EM_SAL_ValidFrom,
           ROW_NUMBER() OVER (PARTITION BY EM_ID
                              ORDER BY EM_SAL_ValidFrom DESC) AS rn
    FROM   EM_SAL_Employee_Salary
) t
WHERE rn = 1;

Step-by-step explanation.

Each salary change is an INSERT, so after three raises the table holds three rows for employee 1. No prior salary was ever overwritten — the raise history is intact and queryable without any separate audit mechanism.
The current salary is not stored anywhere; it is derived as the row with the maximum ValidFrom per employee. This is the fundamental Anchor Modeling shift: "current" is a query, not a column.
DISTINCT ON (EM_ID) ... ORDER BY EM_ID, EM_SAL_ValidFrom DESC is the idiomatic Postgres way to grab the latest row per group — it keeps the first row per EM_ID after ordering by ValidFrom descending.
The window-function form (ROW_NUMBER() OVER (PARTITION BY EM_ID ORDER BY ValidFrom DESC), filter rn = 1) is the portable equivalent that runs on any engine, and is what you would wrap in a latest view.
Because the read computes latest at query time, the same table trivially answers "salary as-of 2024-06-01" by changing the filter to ValidFrom <= DATE '2024-06-01' — the point-in-time query we build in section 4. The immutable table is the single source for both current and historical reads.

Output.

EM_ID	current_salary	since
1	72000	2025-01-01

Rule of thumb. Treat "current value" as a derived read (max ValidFrom per anchor), never as a stored column you keep in sync. Writes are always inserts; the latest view does the "give me now" work, and the same table answers "give me then" for free.

Worked example — why one table per attribute beats a wide dimension

Detailed explanation. The most common objection is "why not just put the attributes in one table with SCD2?" The answer is concrete: a wide dimension couples change, history, and typing across all columns, and every one of those couplings costs something the isolated-attribute design avoids. Walk through the same three changes under both designs.

Change A. Add a new attribute (loyalty_tier).
Change B. Historize only email, leaving name static.
Change C. Change the type of phone from TEXT to a structured type.

Question. Compare the operational cost of changes A, B, and C under a wide SCD2 dimension versus isolated 6NF attributes.

Input.

Change	Wide SCD2 dimension	Isolated 6NF attributes
A: add attribute	ALTER TABLE + lock + backfill	CREATE TABLE (isolated)
B: historize one field	rewrites row on every change	insert into that attribute only
C: retype one field	ALTER on whole wide table	ALTER on one thin table

Code.

-- WIDE SCD2: one change to any column writes a whole new wide row
--            and adding a column locks the entire dimension
ALTER TABLE dim_customer ADD COLUMN loyalty_tier TEXT;   -- lock + backfill NULLs
-- Every email change duplicates name, phone, address, ... into a new SCD2 row:
INSERT INTO dim_customer (customer_bk, name, email, phone, loyalty_tier,
                          valid_from, is_current)
SELECT customer_bk, name, 'new@x.com', phone, loyalty_tier, CURRENT_DATE, TRUE
FROM   dim_customer WHERE customer_bk = 42 AND is_current;   -- write amplification

-- ISOLATED 6NF: each change touches exactly one thin table
CREATE TABLE CU_LOY_Customer_LoyaltyTier (     -- Change A: pure CREATE, no lock
    CU_ID BIGINT NOT NULL REFERENCES CU_Customer,
    CU_LOY_Value TEXT NOT NULL,
    CU_LOY_ValidFrom DATE NOT NULL,
    PRIMARY KEY (CU_ID, CU_LOY_ValidFrom)
);

-- Change B: historize email — one row into one table, no duplication of name/phone
INSERT INTO CU_EMA_Customer_Email (CU_ID, CU_EMA_Value, CU_EMA_ValidFrom)
VALUES (42, 'new@x.com', CURRENT_DATE);

-- Change C: retype phone — ALTER touches only the thin phone table
ALTER TABLE CU_PHO_Customer_Phone
    ALTER COLUMN CU_PHO_Value TYPE TEXT;   -- small table, fast rewrite

Step-by-step explanation.

Change A under the wide design is ALTER TABLE dim_customer ADD COLUMN, which takes a lock and, on many engines, rewrites or backfills the table. Under 6NF it is a pure CREATE TABLE that touches nothing existing — the isolation is the whole point.
Change B exposes the wide design's write amplification: because history is SCD2 on the whole row, changing only email copies name, phone, address, and every other column into a new row. Under 6NF, changing email inserts one narrow row into CU_EMA_Customer_Email and duplicates nothing.
Change C, a type change on phone, rewrites the entire wide dimension under SCD2. Under 6NF it rewrites only the thin CU_PHO_Customer_Phone table, which is orders of magnitude smaller and faster.
The wide design also forces one history policy on all columns: if the dimension is SCD2, static columns like name get versioned needlessly on every unrelated change. 6NF lets each attribute pick static or historized independently.
The trade the wide design buys — zero joins at read — is real, and it is why Kimball wins for stable BI. But for volatile, historized, frequently-evolving entities, the per-attribute isolation of 6NF removes the migration and write-amplification costs that dominate the wide design's total cost of ownership.

Output.

Change	Wide SCD2 cost	6NF cost
A: add attribute	lock + backfill whole table	CREATE TABLE, no lock
B: change one field	new wide row (all columns copied)	one narrow insert
C: retype one field	rewrite whole dimension	rewrite one thin table

Rule of thumb. Choose one-table-per-attribute when attributes evolve, historize, or retype independently and often. The wide dimension's zero-join read is worth it only when the schema is stable; once change is frequent, the isolation of 6NF wins on total cost of ownership.

Senior interview question on the one-table-per-attribute rule

A senior interviewer might ask: "Explain to a skeptical teammate why Anchor Modeling puts each attribute in its own table instead of a wide dimension. Use a concrete Customer with name, email, and status; show what happens when we add a new attribute, when only email changes, and when we need the value as-of a past date. Then show how you keep analysts productive despite the decomposition."

Solution Using isolated historized attributes plus a latest view

-- Anchor + three isolated attributes (name static, email + status historized)
CREATE TABLE CU_Customer (CU_ID BIGSERIAL PRIMARY KEY);

CREATE TABLE CU_NAM_Customer_Name (          -- static
    CU_ID BIGINT NOT NULL PRIMARY KEY REFERENCES CU_Customer,
    CU_NAM_Value TEXT NOT NULL
);

CREATE TABLE CU_EMA_Customer_Email (         -- historized
    CU_ID BIGINT NOT NULL REFERENCES CU_Customer,
    CU_EMA_Value TEXT NOT NULL,
    CU_EMA_ValidFrom DATE NOT NULL,
    PRIMARY KEY (CU_ID, CU_EMA_ValidFrom)
);

CREATE TABLE CU_STA_Customer_Status (        -- historized, references knot
    CU_ID BIGINT NOT NULL REFERENCES CU_Customer,
    STA_ID SMALLINT NOT NULL REFERENCES STA_Status,
    CU_STA_ValidFrom DATE NOT NULL,
    PRIMARY KEY (CU_ID, CU_STA_ValidFrom)
);

-- Latest view — one row per customer, joins hidden
CREATE OR REPLACE VIEW v_Customer_Latest AS
SELECT c.CU_ID,
       n.CU_NAM_Value AS name,
       e.CU_EMA_Value AS email,
       s.STA_Value    AS status
FROM CU_Customer c
LEFT JOIN CU_NAM_Customer_Name n ON n.CU_ID = c.CU_ID
LEFT JOIN LATERAL (
    SELECT CU_EMA_Value FROM CU_EMA_Customer_Email a
    WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_EMA_ValidFrom DESC LIMIT 1
) e ON TRUE
LEFT JOIN LATERAL (
    SELECT STA_ID FROM CU_STA_Customer_Status a
    WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_STA_ValidFrom DESC LIMIT 1
) st ON TRUE
LEFT JOIN STA_Status s ON s.STA_ID = st.STA_ID;

Step-by-step trace.

Scenario	Under a wide dimension	Under isolated 6NF attributes
Add `loyalty_tier`	ALTER + lock + backfill	CREATE TABLE `CU_LOY_...`
Only email changes	new full SCD2 row	one insert into `CU_EMA_...`
Email as-of a past date	parse `valid_from`/`valid_to` on wide row	`WHERE ValidFrom <= :d ORDER BY ValidFrom DESC LIMIT 1`
Analyst query	0 joins on wide row	query `v_Customer_Latest`

Walking the skeptic through it: adding an attribute is a CREATE TABLE that cannot lock the customer table because it does not touch it; an email change is a single narrow insert with no duplication of name or status; the as-of read is a one-line filter on the email attribute; and the analyst never sees any of the decomposition because v_Customer_Latest presents the classic one-row-per-customer shape.

Output:

CU_ID	name	email	status
42	Ada Lovelace	ada@x.com	active

Why this works — concept by concept:

Attribute isolation — each property in its own table means adding, historizing, or retyping one attribute never touches another. This is why schema change is additive and write amplification disappears.
Static vs historized per attribute — name is keyed on the anchor alone (static), while email and status carry ValidFrom (historized). The per-attribute choice is impossible in a single wide dimension with one history policy.
Knot reference for status — CU_STA stores the STA_ID, not the string, so the enumeration is centralized and consistent, and the latest view joins the knot to resolve the human-readable label.
Latest view — the LATERAL ... ORDER BY ValidFrom DESC LIMIT 1 pattern collapses the historized attributes to their current value and presents one clean row, answering the "keep analysts productive" requirement without denormalizing storage.
Cost — one join per attribute at read (hidden by the view) in exchange for zero-migration schema change, zero write amplification, and built-in history. On a columnar engine the joins are largely eliminated. O(1) per write; O(attributes) joins per full-row read.

SQL
Topic — sql
SQL latest-value and versioned-row problems

Practice →

SQL Topic — slowly-changing-data Slowly-changing-data and history-tracking problems

Practice →

3. Ties and knots: relationships and shared domains

Ties connect anchors with keys only and knots share small immutable enumerations — the two constructs that keep relationships and lookups in 6NF

The mental model in one line: a tie is a table that records a relationship between two or more anchors using nothing but their surrogate keys plus an optional validity timestamp, and a knot is a tiny immutable lookup table for a shared enumeration (statuses, types, roles) that many attributes reference by key — together they extend the 6NF discipline from an entity's own properties to the relationships between entities and the small fixed domains those entities draw on. A tie is the Anchor Modeling equivalent of a bridge or link table, but stripped to keys; a knot is the equivalent of a reference dimension, but immutable and shared. Both exist so that relationships and lookups get the same independent-evolution and append-only-history benefits that attributes do.

What a tie holds — and what it must not.

Anchor keys only. A tie between Employee and Department holds EM_ID and DE_ID — the identities being related, and nothing describing them.
Optional validity. If the relationship changes over time (an employee moves departments), add ValidFrom and put it in the key so each assignment is a versioned row.
No business attributes. The moment a tie carries a value like "role in this relationship," that value belongs in an attribute (often an attribute of the tie, or a knotted attribute) — the tie itself stays keys-only.
Cardinality by key shape. A 1:N tie keys on the "many" side; an M:N historized tie keys on all participants plus ValidFrom.

What a knot holds — and when to use one.

A small fixed set of values. Status(active, dormant, closed), Gender, Role. A handful to a few dozen rows, changing rarely or never.
Immutable and shared. The knot is written once; attributes across many anchors reference its key. Renaming a label is a one-row change that propagates everywhere by reference.
Not for large or volatile domains. A knot with ten thousand rows or one that changes daily is really an anchor with attributes — model it as such. Knots are for genuinely small, stable enumerations.
Knotted attributes and knotted ties. An attribute can reference a knot (status of a customer); a tie can reference a knot (the type of a relationship). This keeps the enumeration centralized in both cases.

Ties vs foreign keys vs Data Vault links.

Versus a plain FK. A foreign key embeds the relationship inside an entity's table, coupling it to that entity's lifecycle. A tie externalizes the relationship into its own table so it can historize and evolve independently.
Versus a Data Vault link. A tie is close kin to a link, but Anchor Modeling keeps ties keys-only and pushes any descriptive data into separate attributes, where a Data Vault link often pairs with a satellite that groups several relationship attributes.
Historization is the payoff. Because a tie is its own table, "who was in which department when" is answered by the same append-only pattern as any historized attribute — no relationship history is ever lost.

Common interview probes on ties and knots.

"What does a tie contain?" — required answer: anchor keys plus optional ValidFrom, no business data.
"When do you use a knot instead of an attribute?" — a small, immutable, shared enumeration referenced by many rows.
"How do you historize a relationship?" — add ValidFrom to the tie and key on it; each change is a new row.
"How do you model a role inside a relationship?" — a knotted attribute of the tie, keeping the tie itself keys-only.

Worked example — a tie between two anchors, plus load

Detailed explanation. The canonical tie: Employee and Department anchors joined by a static tie that records current department membership. Build both anchors, the tie, and load a couple of assignments to see that the tie carries only keys.

Anchors. EM_Employee(EM_ID), DE_Department(DE_ID).
Tie. EM_in_DE_Employee_Department(EM_ID, DE_ID) — keys only (static version first).
Load. Two employees assigned to departments.

Question. Write the two anchors and the keys-only tie, then assign two employees to departments.

Input.

Object	Kind	Holds
`EM_Employee`	anchor	EM_ID
`DE_Department`	anchor	DE_ID
`EM_in_DE_Employee_Department`	tie	EM_ID, DE_ID

Code.

-- Two anchors
CREATE TABLE EM_Employee   (EM_ID BIGSERIAL PRIMARY KEY);
CREATE TABLE DE_Department (DE_ID BIGSERIAL PRIMARY KEY);

-- A knot for department names could exist; here name is an attribute:
CREATE TABLE DE_NAM_Department_Name (
    DE_ID BIGINT NOT NULL PRIMARY KEY REFERENCES DE_Department,
    DE_NAM_Value TEXT NOT NULL
);

-- Tie — keys only (static membership)
CREATE TABLE EM_in_DE_Employee_Department (
    EM_ID BIGINT NOT NULL REFERENCES EM_Employee,
    DE_ID BIGINT NOT NULL REFERENCES DE_Department,
    PRIMARY KEY (EM_ID)            -- 1:N: each employee in one department
);

-- Load
INSERT INTO EM_Employee   DEFAULT VALUES;    -- EM_ID 1
INSERT INTO EM_Employee   DEFAULT VALUES;    -- EM_ID 2
INSERT INTO DE_Department DEFAULT VALUES;    -- DE_ID 10
INSERT INTO DE_Department DEFAULT VALUES;    -- DE_ID 20
INSERT INTO DE_NAM_Department_Name VALUES (10, 'Engineering');
INSERT INTO DE_NAM_Department_Name VALUES (20, 'Finance');

INSERT INTO EM_in_DE_Employee_Department (EM_ID, DE_ID) VALUES (1, 10);
INSERT INTO EM_in_DE_Employee_Department (EM_ID, DE_ID) VALUES (2, 20);

-- Read: who is in Engineering
SELECT t.EM_ID
FROM   EM_in_DE_Employee_Department t
JOIN   DE_NAM_Department_Name d ON d.DE_ID = t.DE_ID
WHERE  d.DE_NAM_Value = 'Engineering';

Step-by-step explanation.

EM_Employee and DE_Department are pure anchors — identity only. The department's name is not on the anchor; it is a separate attribute DE_NAM_Department_Name, exactly as with any entity property.
The tie EM_in_DE_Employee_Department holds only EM_ID and DE_ID. It records the relationship and nothing else — no start date yet (this is the static version), no role, no business value.
The primary key (EM_ID) encodes 1:N cardinality: each employee belongs to exactly one department. An M:N relationship would key on (EM_ID, DE_ID); we historize it in the next example by adding ValidFrom.
Loading is two inserts into the tie. Because the tie references both anchors, a foreign-key violation would catch an assignment to a non-existent employee or department.
Reading "who is in Engineering" joins the tie to the department-name attribute — the relationship and the descriptive value stay in separate tables, and the join composes them at read time.

Output.

EM_ID (in Engineering)
1

Rule of thumb. Keep ties keys-only and encode cardinality in the primary key: (many_side) for 1:N, (all_participants) for M:N, and add ValidFrom to the key when the relationship is historized. Any descriptive value about the relationship goes in a separate attribute, never in the tie.

Worked example — a knot for a status enumeration

Detailed explanation. A knot centralizes a small immutable domain so it is stored once and referenced by key everywhere. Build a Status knot and wire a customer's historized status attribute to reference it, so that the label lives in one place.

Knot. STA_Status(STA_ID, STA_Value) with a handful of fixed rows.
Knotted attribute. CU_STA_Customer_Status(CU_ID, STA_ID, ValidFrom) references the knot.
Benefit. The status label is stored once; changing "closed" to "terminated" is a one-row update to the knot.

Question. Create the status knot, historize a customer's status referencing it, and resolve the current status label.

Input.

Object	Kind	Holds
`STA_Status`	knot	STA_ID, STA_Value
`CU_STA_Customer_Status`	knotted attribute	CU_ID, STA_ID, ValidFrom

Code.

-- Knot — small immutable enumeration
CREATE TABLE STA_Status (
    STA_ID    SMALLINT PRIMARY KEY,
    STA_Value TEXT     NOT NULL UNIQUE
);
INSERT INTO STA_Status (STA_ID, STA_Value) VALUES
    (1, 'active'), (2, 'dormant'), (3, 'closed');

-- Knotted, historized attribute — references the knot by key
CREATE TABLE CU_STA_Customer_Status (
    CU_ID            BIGINT   NOT NULL REFERENCES CU_Customer,
    STA_ID           SMALLINT NOT NULL REFERENCES STA_Status,
    CU_STA_ValidFrom DATE     NOT NULL,
    PRIMARY KEY (CU_ID, CU_STA_ValidFrom)
);

-- Customer 42 goes active, then dormant — each a new row
INSERT INTO CU_STA_Customer_Status (CU_ID, STA_ID, CU_STA_ValidFrom)
VALUES (42, 1, DATE '2024-01-01');
INSERT INTO CU_STA_Customer_Status (CU_ID, STA_ID, CU_STA_ValidFrom)
VALUES (42, 2, DATE '2025-03-01');

-- Current status label for customer 42
SELECT s.STA_Value AS current_status
FROM   CU_STA_Customer_Status cs
JOIN   STA_Status s ON s.STA_ID = cs.STA_ID
WHERE  cs.CU_ID = 42
ORDER  BY cs.CU_STA_ValidFrom DESC
LIMIT  1;

Step-by-step explanation.

STA_Status holds three fixed rows. This is a knot: small, immutable, and shared. Every customer's status references one of these three keys rather than storing the string, so the enumeration is defined once.
CU_STA_Customer_Status is a knotted historized attribute: it stores STA_ID (the knot key), not the label, plus ValidFrom for history. It is an attribute like any other, except its value is a reference into the knot.
Two inserts record the customer going active then dormant. As with every historized attribute, the change is an append, so the full status timeline is preserved.
Resolving the current status joins the attribute to the knot and takes the latest ValidFrom. The join turns the stored key 2 into the human-readable label dormant.
The centralization pays off on a label change: renaming closed to terminated is UPDATE STA_Status SET STA_Value='terminated' WHERE STA_ID=3 — one row, and every customer that ever referenced status 3 now reads the new label, with no rewrite of the millions of customer-status rows.

Output.

current_status
dormant

Rule of thumb. Use a knot for any small, stable enumeration referenced by many rows: store the key in the attribute, keep the label in the knot, and you get consistent enumerations plus one-row label changes. If the domain is large or volatile, it is an anchor with attributes, not a knot.

Worked example — a historized employment tie

Detailed explanation. The static tie of the first example cannot answer "which department was this employee in last year." Historizing the tie fixes that: add ValidFrom to the key so each assignment is a versioned row, and the relationship gains the same append-only history as any attribute. Walk through the historized tie and an as-of read.

Historized tie. EM_in_DE(EM_ID, DE_ID, ValidFrom) keyed on (EM_ID, ValidFrom) for a 1:N assignment that changes over time.
Change. An employee moves from Engineering to Finance — a new tie row.
As-of read. Which department on a given date = latest row with ValidFrom <= date.

Question. Historize the employment tie, move an employee between departments, and read their department as-of a past date.

Input.

Object	Change
`EM_in_DE_Employee_Department`	add `ValidFrom` to key
assignment	Eng (2023) → Finance (2025)
as-of read	department on 2024-06-01

Code.

-- Historized tie — ValidFrom in the key makes each assignment a version
CREATE TABLE EM_in_DE_Employee_Department (
    EM_ID              BIGINT NOT NULL REFERENCES EM_Employee,
    DE_ID              BIGINT NOT NULL REFERENCES DE_Department,
    EM_in_DE_ValidFrom DATE   NOT NULL,
    PRIMARY KEY (EM_ID, EM_in_DE_ValidFrom)     -- 1:N over time
);

-- Employee 1: Engineering from 2023, moves to Finance in 2025
INSERT INTO EM_in_DE_Employee_Department VALUES (1, 10, DATE '2023-01-01');
INSERT INTO EM_in_DE_Employee_Department VALUES (1, 20, DATE '2025-01-01');

-- As-of read: which department was employee 1 in on 2024-06-01?
SELECT d.DE_NAM_Value AS department_as_of
FROM   EM_in_DE_Employee_Department t
JOIN   DE_NAM_Department_Name d ON d.DE_ID = t.DE_ID
WHERE  t.EM_ID = 1
  AND  t.EM_in_DE_ValidFrom <= DATE '2024-06-01'
ORDER  BY t.EM_in_DE_ValidFrom DESC
LIMIT  1;

-- Current department (no date filter, just latest)
SELECT d.DE_NAM_Value AS department_now
FROM   EM_in_DE_Employee_Department t
JOIN   DE_NAM_Department_Name d ON d.DE_ID = t.DE_ID
WHERE  t.EM_ID = 1
ORDER  BY t.EM_in_DE_ValidFrom DESC
LIMIT  1;

Step-by-step explanation.

Adding EM_in_DE_ValidFrom to the primary key turns the static tie into a historized one: each department assignment is now a distinct row keyed by (EM_ID, ValidFrom), so the relationship has a timeline.
The department move is a second INSERT, not an UPDATE. After both, the tie holds two rows for employee 1 — Engineering from 2023 and Finance from 2025 — preserving the full assignment history.
The as-of read filters ValidFrom <= '2024-06-01' and takes the latest such row. On that date only the 2023 Engineering row qualifies (the 2025 Finance row is in the future), so it correctly returns Engineering.
The current-department read is the same query without the date filter: just the latest ValidFrom, which returns Finance. The one historized tie answers both "then" and "now" with the identical pattern used for historized attributes.
This is the general lesson: historizing a relationship in Anchor Modeling is mechanically identical to historizing an attribute — append a row with a new ValidFrom, and read with ValidFrom <= :as_of ORDER BY ValidFrom DESC LIMIT 1.

Output.

department_as_of	department_now
Engineering	Finance

Rule of thumb. Historize a tie exactly like an attribute: put ValidFrom in the primary key and read with the ValidFrom <= :as_of ORDER BY ValidFrom DESC LIMIT 1 idiom. Relationship history and attribute history use the same append-only pattern, so learn it once.

Senior interview question on modeling a historized relationship

A senior interviewer might ask: "Model an employee-to-department assignment in Anchor Modeling where an employee can move departments over time and each assignment carries a role (manager, member) drawn from a small fixed set. Show the anchors, the historized tie, the knot for the role, and how you would answer both 'who is in Engineering now' and 'what was the org chart on 2024-06-01'. Keep everything in 6NF."

Solution Using a historized tie plus a role knot

-- Anchors
CREATE TABLE EM_Employee   (EM_ID BIGSERIAL PRIMARY KEY);
CREATE TABLE DE_Department (DE_ID BIGSERIAL PRIMARY KEY);

-- Knot for the role enumeration (small, immutable, shared)
CREATE TABLE ROL_Role (
    ROL_ID    SMALLINT PRIMARY KEY,
    ROL_Value TEXT NOT NULL UNIQUE
);
INSERT INTO ROL_Role VALUES (1, 'member'), (2, 'manager');

-- Historized, knotted tie: assignment carries a role from the knot
CREATE TABLE EM_in_DE_Employee_Department (
    EM_ID              BIGINT   NOT NULL REFERENCES EM_Employee,
    DE_ID              BIGINT   NOT NULL REFERENCES DE_Department,
    ROL_ID             SMALLINT NOT NULL REFERENCES ROL_Role,   -- knotted tie
    EM_in_DE_ValidFrom DATE     NOT NULL,
    PRIMARY KEY (EM_ID, EM_in_DE_ValidFrom)
);

-- Load: employee 1 member of Eng (2023), promoted to manager of Finance (2025)
INSERT INTO EM_in_DE_Employee_Department VALUES (1, 10, 1, DATE '2023-01-01');
INSERT INTO EM_in_DE_Employee_Department VALUES (1, 20, 2, DATE '2025-01-01');

-- "Who is in Engineering now" — latest assignment per employee, filtered to Eng
WITH latest AS (
    SELECT DISTINCT ON (EM_ID) EM_ID, DE_ID, ROL_ID
    FROM   EM_in_DE_Employee_Department
    ORDER  BY EM_ID, EM_in_DE_ValidFrom DESC
)
SELECT l.EM_ID, r.ROL_Value AS role
FROM   latest l
JOIN   ROL_Role r ON r.ROL_ID = l.ROL_ID
WHERE  l.DE_ID = 10;

-- "Org chart on 2024-06-01" — latest assignment as-of that date per employee
WITH as_of AS (
    SELECT DISTINCT ON (EM_ID) EM_ID, DE_ID, ROL_ID
    FROM   EM_in_DE_Employee_Department
    WHERE  EM_in_DE_ValidFrom <= DATE '2024-06-01'
    ORDER  BY EM_ID, EM_in_DE_ValidFrom DESC
)
SELECT a.EM_ID, d.DE_NAM_Value AS department, r.ROL_Value AS role
FROM   as_of a
JOIN   DE_NAM_Department_Name d ON d.DE_ID = a.DE_ID
JOIN   ROL_Role r ON r.ROL_ID = a.ROL_ID;

Step-by-step trace.

Input row	EM_ID	DE_ID	ROL_ID	ValidFrom
assignment 1	1	10 (Eng)	1 (member)	2023-01-01
assignment 2	1	20 (Fin)	2 (manager)	2025-01-01

Walkthrough: (1) the role knot centralizes member/manager so the tie stores ROL_ID, keeping it a knotted tie rather than embedding the label. (2) The historized tie keys on (EM_ID, ValidFrom), so the promotion-and-move is a second append. (3) "Who is in Engineering now" takes the latest assignment per employee (DISTINCT ON ... ORDER BY ValidFrom DESC) and filters to DE_ID = 10; employee 1's latest is Finance, so they do not appear. (4) "Org chart on 2024-06-01" applies ValidFrom <= '2024-06-01' first, so employee 1's latest as-of row is the 2023 Engineering-member assignment.

Output:

Query	EM_ID	department	role
Engineering now	(employee 1 excluded — now in Finance)	—	—
Org chart 2024-06-01	1	Engineering	member

Why this works — concept by concept:

Historized tie — putting ValidFrom in the tie's key gives the relationship a full timeline, so both "now" and "as-of" reads use the same append-only history without a separate assignment-history table.
Knotted tie — the role is stored as ROL_ID referencing the ROL_Role knot, keeping the enumeration centralized while still attaching a value to the relationship; the tie stays free of free-text business data.
DISTINCT ON for latest-per-group — selecting the newest assignment per employee is the same latest-row idiom used for attributes, applied to the tie; adding a WHERE ValidFrom <= :d turns "now" into "as-of".
6NF preserved — anchors hold identity, the tie holds keys plus the knot reference plus time, and the knot holds the enumeration; no table carries more than its irreducible content.
Cost — reconstructing the org chart is a latest-per-employee scan of one narrow tie plus two small knot/attribute joins — cheap and index-friendly on (EM_ID, ValidFrom). O(assignments) scanned; O(1) knot lookups; the tie is the single source for current and historical org structure.

SQL
Topic — joins
Join and relationship-modeling problems

Practice →

Design Topic — design Design problems on relationship and lookup modeling

Practice →

4. Temporality: point-in-time, bitemporal, and immutable history

6NF makes time a first-class citizen — every historized attribute carries its own timeline, so any past state reconstructs by joining each attribute as-of a date

The mental model in one line: because every historized attribute and tie in an Anchor model is append-only and keyed by ValidFrom, reconstructing an entity's complete state as-of any date is a matter of joining, for each attribute, the single latest row whose ValidFrom is on or before that date — and adding a second time axis (transaction time alongside valid time) turns the model bitemporal, able to answer not just "what was true then" but "what did we believe was true then". Temporality is not a feature you add to Anchor Modeling; it is the emergent property of 6NF plus immutability. The same table that serves the current value serves every historical value, and the same query pattern, parameterized by a date, walks the entity backward through time.

Valid time, transaction time, and bitemporal.

Valid time. When a fact was true in the real world — the salary was 72,000 effective 2025-01-01. This is the ValidFrom we have used throughout.
Transaction time. When the database learned the fact — we recorded the 72,000 salary on 2025-01-05. A separate timestamp, often RecordedAt or AssertedAt.
Bitemporal. Both axes together. A temporal database is bitemporal when it can answer "what was the salary effective 2025-01-01, as we knew it on 2025-01-03" — before the raise was recorded, the answer is the old salary; after, the new one.
Why it matters. Regulated domains (insurance, finance) must reconstruct not just history but knowledge history: what we reported to a regulator on a date, using only what we knew then. Bitemporality is the only correct model for that.

Point-in-time reconstruction.

The pattern. For each attribute, take the row with the greatest ValidFrom <= :as_of. Join all such rows on the anchor to compose the entity's state on that date.
Missing rows. If an attribute has no row with ValidFrom <= :as_of, the entity did not have that property yet on that date — the join yields NULL, which is correct.
Latest is a special case. "Now" is just point-in-time with :as_of = current_date (or no filter). One query pattern, two uses.

Latest-view vs as-of-view.

Latest view. Collapses each historized attribute to its most recent row; the everyday analyst surface.
As-of view. Parameterized by a date (or built as a function / table-valued query); reconstructs the entity as of that date.
Both hide joins. Analysts and applications call the view; the decomposition and the ORDER BY ValidFrom DESC LIMIT 1 machinery stay encapsulated.

Correctness rules for temporal queries.

Per-attribute as-of. Each attribute is filtered and ranked independently — you cannot take "the latest row across all attributes," because attributes change on different dates.
Half-open intervals. Treat ValidFrom as the start of a half-open interval; the row is valid until the next row's ValidFrom. This avoids gaps and overlaps.
Bitemporal filter order. Filter transaction time first ("as we knew it on X"), then valid time ("effective on Y"), then take the latest — order matters for correctness.

Worked example — point-in-time reconstruction across attributes

Detailed explanation. The signature Anchor Modeling query: reconstruct an entity's full state as-of a date by joining each attribute's as-of-latest row. Build it for a Customer with historized name, email, and status, and reconstruct the customer as they were on a past date.

Attributes. CU_NAM (name), CU_EMA (email), CU_STA (status, knotted) — all historized.
As-of. Reconstruct customer 42 on 2024-06-01.
Pattern. One correlated latest-as-of subquery per attribute.

Question. Write the point-in-time query that returns customer 42's name, email, and status as of 2024-06-01.

Input.

Attribute	Rows for CU_ID 42 (value @ ValidFrom)
CU_NAM	Ada @ 2023-01-01
CU_EMA	ada@old.com @ 2023-01-01; ada@new.com @ 2025-02-01
CU_STA	active(1) @ 2024-01-01; dormant(2) @ 2025-03-01

Code.

-- Point-in-time reconstruction of customer 42 as-of 2024-06-01
WITH params AS (SELECT 42::BIGINT AS cu_id, DATE '2024-06-01' AS as_of)
SELECT
    p.cu_id,
    (SELECT CU_NAM_Value FROM CU_NAM_Customer_Name a
       WHERE a.CU_ID = p.cu_id
       ORDER BY a.CU_NAM_ValidFrom DESC LIMIT 1)                     AS name,
    (SELECT CU_EMA_Value FROM CU_EMA_Customer_Email a
       WHERE a.CU_ID = p.cu_id AND a.CU_EMA_ValidFrom <= p.as_of
       ORDER BY a.CU_EMA_ValidFrom DESC LIMIT 1)                     AS email,
    (SELECT s.STA_Value
       FROM CU_STA_Customer_Status a
       JOIN STA_Status s ON s.STA_ID = a.STA_ID
       WHERE a.CU_ID = p.cu_id AND a.CU_STA_ValidFrom <= p.as_of
       ORDER BY a.CU_STA_ValidFrom DESC LIMIT 1)                     AS status
FROM params p;

Step-by-step explanation.

The params CTE holds the two parameters — the anchor id and the as-of date — so the same query becomes a template: change as_of and you walk the customer to any point in time.
name uses a historized attribute but the sample data has only one row, so the as-of filter is optional here; for consistency you would add CU_NAM_ValidFrom <= as_of. Each attribute is queried by its own correlated subquery.
email filters CU_EMA_ValidFrom <= '2024-06-01' and takes the latest such row. On that date only ada@old.com (ValidFrom 2023-01-01) qualifies — the 2025 change is in the future — so the reconstruction correctly returns the old email.
status joins the knot and applies the same as-of filter: only the active row (2024-01-01) is on or before 2024-06-01, so the status resolves to active, not the later dormant.
The key correctness property: each attribute is reconstructed independently as-of the same date. You cannot take a single "latest row" across attributes because they changed on different dates — email and status have different timelines, and the per-attribute as-of subquery respects that.

Output.

cu_id	name	email	status
42	Ada	ada@old.com	active

Rule of thumb. Reconstruct point-in-time state one attribute at a time, each with ValidFrom <= :as_of ORDER BY ValidFrom DESC LIMIT 1. Never collapse attributes to a single latest row — their timelines are independent, and mixing them silently corrupts the reconstruction.

Worked example — a bitemporal attribute with valid and transaction time

Detailed explanation. A bitemporal attribute adds a RecordedAt (transaction time) alongside ValidFrom (valid time), so the table records both when a fact became true and when the database learned it. This lets you answer "as we knew it on date X" queries — essential when a fact is recorded late or corrected. Build a bitemporal salary attribute and query it two ways.

Axes. ValidFrom (effective date) and RecordedAt (when inserted).
Late recording. The 2025 raise is entered on 2025-01-10, five days late.
Two questions. Salary effective 2025-01-01 as-known-now vs as-known on 2025-01-03.

Question. Model a bitemporal salary attribute, record a late-entered raise, and answer the salary effective 2025-01-01 as known on two different transaction dates.

Input.

EM_ID	value	ValidFrom (valid)	RecordedAt (transaction)
1	66000	2024-01-01	2024-01-01
1	72000	2025-01-01	2025-01-10 (late)

Code.

-- Bitemporal salary attribute: valid time + transaction time
CREATE TABLE EM_SAL_Employee_Salary_BT (
    EM_ID            BIGINT        NOT NULL REFERENCES EM_Employee,
    EM_SAL_Value     NUMERIC(12,2) NOT NULL,
    EM_SAL_ValidFrom DATE          NOT NULL,     -- valid time
    EM_SAL_RecordedAt TIMESTAMPTZ  NOT NULL,     -- transaction time
    PRIMARY KEY (EM_ID, EM_SAL_ValidFrom, EM_SAL_RecordedAt)
);

INSERT INTO EM_SAL_Employee_Salary_BT VALUES
    (1, 66000, DATE '2024-01-01', TIMESTAMPTZ '2024-01-01 00:00'),
    (1, 72000, DATE '2025-01-01', TIMESTAMPTZ '2025-01-10 00:00');  -- recorded late

-- Q1: salary effective 2025-01-01, as we know it NOW
SELECT EM_SAL_Value AS salary_as_known_now
FROM   EM_SAL_Employee_Salary_BT
WHERE  EM_ID = 1
  AND  EM_SAL_ValidFrom  <= DATE '2025-01-01'
  AND  EM_SAL_RecordedAt <= now()
ORDER  BY EM_SAL_ValidFrom DESC, EM_SAL_RecordedAt DESC
LIMIT  1;

-- Q2: salary effective 2025-01-01, as we knew it ON 2025-01-03
SELECT EM_SAL_Value AS salary_as_known_on_jan3
FROM   EM_SAL_Employee_Salary_BT
WHERE  EM_ID = 1
  AND  EM_SAL_ValidFrom  <= DATE '2025-01-01'
  AND  EM_SAL_RecordedAt <= TIMESTAMPTZ '2025-01-03 00:00'
ORDER  BY EM_SAL_ValidFrom DESC, EM_SAL_RecordedAt DESC
LIMIT  1;

Step-by-step explanation.

The table has two time columns: ValidFrom for when the salary is effective and RecordedAt for when the row was inserted. The 2025 raise is effective 2025-01-01 but was recorded on 2025-01-10 — a realistic late entry.
Q1 asks for the salary effective 2025-01-01 using everything we know now: both rows have RecordedAt <= now(), and among rows with ValidFrom <= 2025-01-01 the latest valid-from is the 72000 row, so the answer is 72000.
Q2 asks the same effective-date question but restricted to what we knew on 2025-01-03: the RecordedAt <= 2025-01-03 filter excludes the 72000 row (recorded 2025-01-10), leaving only the 66000 row, so the answer is 66000.
The two answers differ for the same valid date — that is the whole point of bitemporality. Q1 reflects corrected knowledge; Q2 reflects historical knowledge. A regulator asking "what did you report on 2025-01-03" needs Q2, not Q1.
The filter order encodes the semantics: transaction time constrains "what we knew," valid time constrains "what was effective," and the double ORDER BY ... DESC LIMIT 1 picks the most recent belief about the most recent effective value within those constraints.

Output.

Query	salary
Q1 as known now	72000
Q2 as known on 2025-01-03	66000

Rule of thumb. Add transaction time (RecordedAt) only when you must answer "as we knew it then" — regulated reporting, corrections, audits. Filter transaction time first, then valid time, then take the latest of each. Absent that requirement, plain valid-time historization is simpler and sufficient.

Worked example — latest view vs as-of view

Detailed explanation. Analysts should never write the reconstruction machinery by hand. Package it: a latest view for "now" and a parameterized as-of function for "then." Build both over the customer attributes so downstream queries stay one-liners.

Latest view. v_Customer_Latest — each attribute collapsed to its newest row.
As-of function. f_Customer_AsOf(cu_id, as_of) — reconstructs one customer on a date.
Contract. Consumers call the view/function; the joins stay hidden.

Question. Build a latest view and an as-of table function over the customer attributes, and show both being called.

Input.

Object	Purpose
`v_Customer_Latest`	current one-row-per-customer
`f_Customer_AsOf(cu_id, as_of)`	point-in-time reconstruction

Code.

-- Latest view — "now"
CREATE OR REPLACE VIEW v_Customer_Latest AS
SELECT c.CU_ID,
       (SELECT CU_NAM_Value FROM CU_NAM_Customer_Name a
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_NAM_ValidFrom DESC LIMIT 1) AS name,
       (SELECT CU_EMA_Value FROM CU_EMA_Customer_Email a
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_EMA_ValidFrom DESC LIMIT 1) AS email,
       (SELECT s.STA_Value FROM CU_STA_Customer_Status a
          JOIN STA_Status s ON s.STA_ID = a.STA_ID
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_STA_ValidFrom DESC LIMIT 1) AS status
FROM CU_Customer c;

-- As-of function — "then"
CREATE OR REPLACE FUNCTION f_Customer_AsOf(p_cu_id BIGINT, p_as_of DATE)
RETURNS TABLE (cu_id BIGINT, name TEXT, email TEXT, status TEXT)
LANGUAGE sql STABLE AS $$
    SELECT p_cu_id,
      (SELECT CU_NAM_Value FROM CU_NAM_Customer_Name a
         WHERE a.CU_ID = p_cu_id AND a.CU_NAM_ValidFrom <= p_as_of
         ORDER BY a.CU_NAM_ValidFrom DESC LIMIT 1),
      (SELECT CU_EMA_Value FROM CU_EMA_Customer_Email a
         WHERE a.CU_ID = p_cu_id AND a.CU_EMA_ValidFrom <= p_as_of
         ORDER BY a.CU_EMA_ValidFrom DESC LIMIT 1),
      (SELECT s.STA_Value FROM CU_STA_Customer_Status a
         JOIN STA_Status s ON s.STA_ID = a.STA_ID
         WHERE a.CU_ID = p_cu_id AND a.CU_STA_ValidFrom <= p_as_of
         ORDER BY a.CU_STA_ValidFrom DESC LIMIT 1);
$$;

-- Call both
SELECT * FROM v_Customer_Latest WHERE CU_ID = 42;                 -- now
SELECT * FROM f_Customer_AsOf(42, DATE '2024-06-01');            -- then

Step-by-step explanation.

v_Customer_Latest encapsulates the per-attribute latest-row subqueries so analysts see the familiar one-row-per-customer shape and never write ORDER BY ValidFrom DESC LIMIT 1 themselves. This is the ergonomic answer to the "too many joins" objection.
f_Customer_AsOf is the same machinery parameterized by a date: each subquery adds ValidFrom <= p_as_of. Marking it STABLE lets the planner cache it within a statement and inline it efficiently.
Calling v_Customer_Latest returns the current state; calling f_Customer_AsOf(42, '2024-06-01') returns the reconstructed 2024 state. Same customer, two timelines, two one-line calls.
The function returns a TABLE, so it composes into larger queries — you can join f_Customer_AsOf for a whole cohort by lateral-joining it against a set of ids and dates, reconstructing many customers as-of many dates in one query.
The view/function layer is where the "keep analysts productive" contract lives. Storage stays in strict 6NF; the consumption surface looks like ordinary tables. This separation is what makes Anchor Modeling usable in practice rather than only elegant on paper.

Output.

Call	cu_id	name	email	status
v_Customer_Latest	42	Ada	ada@new.com	dormant
f_Customer_AsOf(42,'2024-06-01')	42	Ada	ada@old.com	active

Rule of thumb. Ship two consumption surfaces over every Anchor model: a latest view for "now" and an as-of function for "then." Keep storage in 6NF and let the view/function layer absorb the joins, so consumers write one-liners and never touch ValidFrom directly.

Senior interview question on reconstructing full state as-of a date

A senior interviewer might ask: "Given an Anchor model of a Policy with historized premium, historized status (knotted), and a historized coverage tie to a Product, write the query that reconstructs a policy's complete state — premium, status, and product — as of an arbitrary historical date. Explain why you cannot just take the latest row overall, and how you would make this reusable for auditors who ask for many policies as-of many dates."

Solution Using per-attribute as-of subqueries wrapped in a table function

-- Reconstruct one policy's full state as-of a date
CREATE OR REPLACE FUNCTION f_Policy_AsOf(p_po_id BIGINT, p_as_of DATE)
RETURNS TABLE (po_id BIGINT, premium NUMERIC, status TEXT, product TEXT)
LANGUAGE sql STABLE AS $$
    SELECT p_po_id,
      -- premium as-of
      (SELECT PO_PRM_Premium FROM PO_PRM_Policy_Premium a
         WHERE a.PO_ID = p_po_id AND a.PO_PRM_ValidFrom <= p_as_of
         ORDER BY a.PO_PRM_ValidFrom DESC LIMIT 1),
      -- status as-of (knotted)
      (SELECT s.STA_Value FROM PO_STA_Policy_Status a
         JOIN STA_Status s ON s.STA_ID = a.STA_ID
         WHERE a.PO_ID = p_po_id AND a.PO_STA_ValidFrom <= p_as_of
         ORDER BY a.PO_STA_ValidFrom DESC LIMIT 1),
      -- product via historized coverage tie, as-of
      (SELECT pr.PR_NAM_Value
         FROM PO_cov_PR_Policy_Product t
         JOIN PR_NAM_Product_Name pr ON pr.PR_ID = t.PR_ID
         WHERE t.PO_ID = p_po_id AND t.PO_cov_PR_ValidFrom <= p_as_of
         ORDER BY t.PO_cov_PR_ValidFrom DESC LIMIT 1);
$$;

-- Reusable for many policies as-of many dates (auditor request)
SELECT r.*
FROM   audit_requests req                        -- (po_id, as_of) pairs
CROSS JOIN LATERAL f_Policy_AsOf(req.po_id, req.as_of) r;

-- Single call
SELECT * FROM f_Policy_AsOf(7, DATE '2024-06-01');

Step-by-step trace.

Fact source	Rows (value @ ValidFrom)	As-of 2024-06-01 picks
PO_PRM (premium)	1200 @ 2023-01-01; 1350 @ 2025-01-01	1200
PO_STA (status)	active @ 2023-06-01; lapsed @ 2025-02-01	active
PO_cov_PR (product tie)	Gold @ 2023-01-01; Platinum @ 2025-01-01	Gold

Walkthrough: each fact is reconstructed by its own subquery filtering ValidFrom <= '2024-06-01' and taking the latest. Premium resolves to 1200 (the 1350 change is later), status to active (lapsed is later), and the coverage tie to the Gold product (Platinum is later). The three independent timelines are respected; a single "latest row overall" would be meaningless because premium, status, and the product tie changed on different dates. Wrapping the logic in f_Policy_AsOf and lateral-joining it against an audit_requests table of (po_id, as_of) pairs reconstructs any number of policies as-of any number of dates in one statement.

Output:

po_id	premium	status	product
7	1200	active	Gold

Why this works — concept by concept:

Per-attribute as-of reconstruction — each historized fact (premium, status, coverage tie) is filtered to ValidFrom <= :as_of and ranked independently, because their timelines are independent. This is the core correctness rule of temporal Anchor Modeling.
Knot resolution inside the as-of — the status subquery joins STA_Status after selecting the as-of row, so the label reflects the enumeration value referenced on that date.
Historized tie as a temporal fact — the coverage relationship is reconstructed with the same as-of idiom as an attribute, proving relationships and attributes share one temporal pattern.
Table function for reuse — f_Policy_AsOf packages the reconstruction so auditors' bulk requests become a single CROSS JOIN LATERAL over (po_id, as_of) pairs, rather than hand-written SQL per request.
Cost — one index-backed (PK, ValidFrom) lookup per fact per policy-date pair; with a B-tree on (anchor_id, ValidFrom) each subquery is an O(log n) seek plus a LIMIT 1. The reconstruction scales linearly in the number of requested policy-date pairs, and on a columnar engine the per-attribute seeks parallelize.

SQL
Topic — sql
Temporal SQL and point-in-time query problems

Practice →

SQL Topic — slowly-changing-data Bitemporal and as-of reconstruction problems

Practice →

5. Schema evolution, performance, and the tooling verdict

Additive evolution ships with zero downtime, views tame the join cost, and columnar engines eliminate most of it — the honest verdict on where Anchor Modeling wins and loses

The mental model in one line: Anchor Modeling's headline operational win is additive schema evolution — a new attribute or relationship is a CREATE TABLE that locks nothing and needs no backfill — and its headline cost is table explosion and read-time joins, which you mitigate with latest/as-of views, with the table-elimination and join-elimination optimizations that modern columnar engines apply automatically, and with tooling like the anchormodeling.com generator that produces the DDL and views for you. The verdict is not "always" or "never"; it is a clear-eyed trade: you win agility, immutability, and temporality, and you pay in table count and join cost that the right engine and the right view layer largely neutralize.

Additive evolution — the zero-downtime win.

Add an attribute. CREATE TABLE for the new attribute; existing rows and queries are untouched; the new attribute simply has no rows until data arrives. No lock, no backfill, no downstream break.
Add a relationship. A new tie is another CREATE TABLE. Modeling a new connection between existing entities requires no change to those entities.
Retire an attribute. Stop writing to it and drop it when consumers have migrated. Because it was isolated, its removal touches nothing else.
Contrast. In a wide dimension every one of these is a locking ALTER; the isolation of 6NF is exactly what makes them additive.

The table-explosion and join trade-off.

The reality. A modest domain can produce dozens or hundreds of tables — one per attribute, tie, and knot. Reconstructing a wide entity view joins many of them.
Naive-engine cost. On a row-store with a simple planner, those joins are real work and can dominate query time; this is the legitimate core of the skeptic's objection.
The mitigations. Views hide the joins from consumers; the query optimizer, given the 6NF structure, can eliminate joins and even whole tables that a query does not reference.

Table elimination and join elimination.

What it is. When a view exposes twenty attributes but a query selects only two, a capable optimizer prunes the joins to the other eighteen tables — they contribute nothing to the result, so they are never scanned.
Why 6NF enables it. Because each attribute is its own table joined by key with guaranteed one-row-per-anchor semantics, the optimizer can prove a join is unnecessary. This is far harder in denormalized schemas.
Columnar synergy. Columnar engines already read only referenced columns; combined with join elimination over 6NF tables, a query over a hundred-table model touches only the handful of tables it actually needs.

Tooling and the verdict.

Generators. The anchormodeling.com tooling and its XML model let you design anchors/attributes/ties/knots visually and generate the DDL, latest views, and point-in-time functions automatically — you rarely hand-write the boilerplate.
SQL:2011 temporal. Native SYSTEM VERSIONING and application-time periods in modern databases complement Anchor Modeling for the temporal machinery.
Where it wins. Volatile schemas, per-attribute history, bitemporal/regulated reconstruction, columnar targets.
Where it loses. Stable schemas, analyst-owned ad-hoc SQL without views, naive row-store planners, and teams without the tooling to manage the table count. In those, Kimball or Data Vault is the better call.

Worked example — adding a new attribute with zero downtime

Detailed explanation. The clearest demonstration of Anchor Modeling's agility: add a brand-new attribute to a live entity while queries keep running. Add a LoyaltyTier attribute to Customer and show that existing reads are unaffected and the new attribute is immediately usable.

Add. CREATE TABLE CU_LOY_Customer_LoyaltyTier — pure creation.
Backfill. Optional and incremental — insert rows only as tiers are assigned.
Consume. Extend the latest view (or add a new one) to expose it.

Question. Add a historized LoyaltyTier attribute to the existing Customer model with zero downtime, and extend the latest view.

Input.

Step	Effect on running system
CREATE new attribute table	none (no lock on existing tables)
INSERT tiers as assigned	incremental, no backfill required
REPLACE latest view	additive column; old queries unaffected

Code.

-- 1. Add the new attribute — CREATE only, locks nothing existing
CREATE TABLE CU_LOY_Customer_LoyaltyTier (
    CU_ID            BIGINT NOT NULL REFERENCES CU_Customer,
    CU_LOY_Value     TEXT   NOT NULL,
    CU_LOY_ValidFrom DATE   NOT NULL,
    PRIMARY KEY (CU_ID, CU_LOY_ValidFrom)
);

-- 2. Start writing tiers as they are assigned (no backfill needed)
INSERT INTO CU_LOY_Customer_LoyaltyTier (CU_ID, CU_LOY_Value, CU_LOY_ValidFrom)
VALUES (42, 'gold', CURRENT_DATE);

-- 3. Extend the latest view additively — existing columns unchanged
CREATE OR REPLACE VIEW v_Customer_Latest AS
SELECT c.CU_ID,
       (SELECT CU_NAM_Value FROM CU_NAM_Customer_Name a
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_NAM_ValidFrom DESC LIMIT 1) AS name,
       (SELECT CU_EMA_Value FROM CU_EMA_Customer_Email a
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_EMA_ValidFrom DESC LIMIT 1) AS email,
       (SELECT s.STA_Value FROM CU_STA_Customer_Status a
          JOIN STA_Status s ON s.STA_ID = a.STA_ID
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_STA_ValidFrom DESC LIMIT 1) AS status,
       (SELECT CU_LOY_Value FROM CU_LOY_Customer_LoyaltyTier a           -- new
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_LOY_ValidFrom DESC LIMIT 1) AS loyalty_tier
FROM CU_Customer c;

Step-by-step explanation.

Step 1 is a bare CREATE TABLE. It cannot lock CU_Customer, CU_NAM, or any other table because it does not reference them for modification — customers with no tier simply have no row in the new table. Queries running against every existing table continue uninterrupted.
There is no backfill obligation. In a wide dimension, ADD COLUMN loyalty_tier leaves every existing row with a NULL that a backfill must populate; here, absence of a row is "no tier yet," so you write tiers incrementally as they are assigned.
Step 2 inserts a tier for customer 42 the moment it is known. The attribute is usable immediately, with full history from its first row, and it is historized independently of every other customer attribute.
Step 3 extends v_Customer_Latest with one more subquery. CREATE OR REPLACE VIEW adds the loyalty_tier column; queries that select the old columns are unaffected, and new queries can select the new column. The evolution is additive at the consumption layer too.
The entire change — new attribute, first data, exposed in the view — happened with no lock, no backfill, and no downtime. That is the agility claim made concrete, and it is the single strongest argument for Anchor Modeling in a fast-changing domain.

Output.

CU_ID	name	email	status	loyalty_tier
42	Ada	ada@new.com	dormant	gold

Rule of thumb. Treat every schema change as an addition: new attribute = new table, new relationship = new tie, and extend views with CREATE OR REPLACE. Never ALTER a wide table under load when you can CREATE an isolated one instead — additive evolution is the whole reason to pay the join cost.

Worked example — a latest view that hides the joins (and enables elimination)

Detailed explanation. The view layer is both the ergonomics fix and the performance fix: it hides the joins from analysts, and it gives the optimizer a 6NF structure over which it can eliminate joins for queries that touch only a few attributes. Build a wide latest view and show that selecting two columns prunes the rest.

View. A wide v_Customer_Latest exposing many attributes.
Narrow query. Select only CU_ID and email.
Elimination. The optimizer skips the name/status/tier joins.

Question. Show a wide latest view and explain how a query selecting only two of its columns avoids scanning the other attribute tables.

Input.

Query	Columns selected	Attribute tables actually needed
full	name, email, status, tier	all four
narrow	email only	one (CU_EMA)

Code.

-- Wide latest view (as built above, exposing name/email/status/loyalty_tier)
-- A narrow query over it:
SELECT CU_ID, email
FROM   v_Customer_Latest
WHERE  CU_ID = 42;

-- Conceptually the optimizer rewrites this to touch ONLY CU_EMA:
--   SELECT c.CU_ID,
--          (SELECT CU_EMA_Value FROM CU_EMA_Customer_Email a
--             WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_EMA_ValidFrom DESC LIMIT 1)
--   FROM CU_Customer c WHERE c.CU_ID = 42;
-- The name/status/loyalty subqueries are pruned: their outputs are unused,
-- each is a scalar subquery guaranteed to affect no row filtering, so a
-- capable planner eliminates them entirely (join / subquery elimination).

-- Verify on Postgres:
EXPLAIN (ANALYZE, BUFFERS)
SELECT CU_ID, email FROM v_Customer_Latest WHERE CU_ID = 42;

Step-by-step explanation.

The wide view exposes four attributes, each as a correlated scalar subquery. A consumer writing SELECT CU_ID, email sees a simple two-column result and never knows there are four underlying tables.
Because the name, status, and loyalty_tier subqueries appear only in the SELECT list and their results are not selected, they contribute nothing to the output or to row filtering. A capable optimizer proves they are unused and removes them.
What remains after elimination is a query touching only CU_Customer and CU_EMA_Customer_Email — exactly the tables the answer depends on. The hundred-table model costs, for this query, one anchor lookup plus one attribute seek.
EXPLAIN (ANALYZE, BUFFERS) on Postgres shows which relations were actually scanned; on engines with strong scalar-subquery/join elimination (many columnar MPP systems), the plan touches only the referenced attribute tables. This is the empirical rebuttal to "too many joins."
The combination is what makes Anchor Modeling perform: 6NF gives the optimizer provably-prunable joins, columnar storage reads only referenced columns, and the view gives consumers a wide surface without paying for columns they do not select.

Output.

Query	Tables scanned	Result
`SELECT CU_ID, email`	CU_Customer + CU_EMA	1 row (42, ada@new.com)
`SELECT *`	all attribute tables	1 full row

Rule of thumb. Expose wide latest/as-of views but rely on join and subquery elimination so that narrow queries pay only for the attributes they select. Verify with EXPLAIN on your engine — if it does not prune unused attribute joins, either add targeted narrow views or reconsider Anchor Modeling on that engine.

Worked example — defending the join cost with numbers

Detailed explanation. The skeptic's objection is quantitative — "N joins per entity read." The senior rebuttal is also quantitative: index the (anchor_id, ValidFrom) keys, let elimination prune unused joins, and measure. Walk through the cost model and where it does and does not hold.

Full-row read. O(attributes) index seeks, each O(log n) on (anchor_id, ValidFrom).
Narrow read. O(1) after join elimination — only the selected attributes.
Where it bites. Naive row-store planner that does not eliminate, or unindexed keys forcing scans.

Question. Give the cost model for reading an entity under Anchor Modeling and state the two conditions under which the join cost becomes a real problem.

Input.

Read kind	Joins performed	Per-join cost (indexed)
full entity (all attrs)	one per attribute	O(log n) seek + LIMIT 1
narrow (k of N attrs)	k (after elimination)	O(log n) seek + LIMIT 1
unindexed keys	one per attribute	O(n) scan (bad)

Code.

-- The index that makes latest/as-of reads cheap on every attribute
CREATE INDEX ix_cu_ema_id_from ON CU_EMA_Customer_Email (CU_ID, CU_EMA_ValidFrom DESC);
CREATE INDEX ix_cu_sta_id_from ON CU_STA_Customer_Status (CU_ID, CU_STA_ValidFrom DESC);
CREATE INDEX ix_cu_loy_id_from ON CU_LOY_Customer_LoyaltyTier (CU_ID, CU_LOY_ValidFrom DESC);

-- With these, each latest/as-of subquery is an index seek + LIMIT 1:
--   ORDER BY (CU_ID, ValidFrom DESC) LIMIT 1  ->  single index range, first row
-- Full-row read = O(attributes) such seeks; narrow read = O(k) after elimination.

-- Anti-pattern that makes the skeptic right: no index on (anchor, ValidFrom)
-- forces a full scan + sort per attribute per read. Always create the index.

Step-by-step explanation.

Each latest/as-of subquery is WHERE anchor_id = ? [AND ValidFrom <= ?] ORDER BY ValidFrom DESC LIMIT 1. With a composite index on (anchor_id, ValidFrom DESC), this is a single index range scan returning the first row — an O(log n) seek, not a table scan.
A full-entity read performs one such seek per attribute, so its cost is O(attributes) index seeks. For a 20-attribute entity that is 20 cheap seeks — bounded, predictable, and index-backed.
A narrow read that selects k of N attributes costs, after join elimination, only k seeks. This is why the view-plus-elimination pattern matters: most real queries select a few attributes, so they pay k, not N.
The first condition under which the objection is valid: a planner that does not eliminate unused joins forces all N seeks even for a narrow query. On such engines, add targeted narrow views so each common query touches only its attributes.
The second condition: missing the (anchor_id, ValidFrom) index turns each attribute lookup into an O(n) scan-plus-sort, which is genuinely slow. The index is non-negotiable; with it, the join cost is bounded and small, and the skeptic's worst case does not occur.

Output.

Condition	Join cost	Verdict
Indexed keys + elimination	O(k) seeks for k selected attrs	cheap; objection answered
Indexed keys, no elimination	O(N) seeks per read	add narrow views
Unindexed keys	O(N) scans per read	fix the index first

Rule of thumb. Answer the join-cost objection with the index and the optimizer: composite (anchor_id, ValidFrom DESC) indexes make every latest/as-of read an index seek, and join elimination makes narrow reads pay only for selected attributes. The objection is real only on unindexed keys or a planner that cannot prune — fix those before blaming the model.

Senior interview question on defending Anchor Modeling's join cost

A senior interviewer might ask: "An architect rejects your Anchor Modeling proposal because 'reading one customer means twenty joins — it will never perform.' You are on a columnar MPP warehouse. Rebut the objection concretely: explain the indexing, the view layer, join and table elimination, and when the objection is actually correct. Then state honestly the cases where you would not use Anchor Modeling."

Solution Using indexed attributes, elimination-aware views, and an honest boundary

-- 1. Index every attribute on (anchor, ValidFrom DESC) — makes latest/as-of a seek
CREATE INDEX ix_cu_nam ON CU_NAM_Customer_Name        (CU_ID, CU_NAM_ValidFrom DESC);
CREATE INDEX ix_cu_ema ON CU_EMA_Customer_Email        (CU_ID, CU_EMA_ValidFrom DESC);
CREATE INDEX ix_cu_sta ON CU_STA_Customer_Status       (CU_ID, CU_STA_ValidFrom DESC);
CREATE INDEX ix_cu_loy ON CU_LOY_Customer_LoyaltyTier  (CU_ID, CU_LOY_ValidFrom DESC);

-- 2. Wide view for convenience + narrow views for hot paths
CREATE OR REPLACE VIEW v_Customer_Latest AS  /* ... all attributes ... */
    SELECT c.CU_ID /* + per-attribute latest subqueries */ FROM CU_Customer c;

CREATE OR REPLACE VIEW v_Customer_Contact AS   -- hot path: only id + email
SELECT c.CU_ID,
       (SELECT CU_EMA_Value FROM CU_EMA_Customer_Email a
          WHERE a.CU_ID = c.CU_ID ORDER BY a.CU_EMA_ValidFrom DESC LIMIT 1) AS email
FROM CU_Customer c;

-- 3. Prove the cost on the real engine
EXPLAIN SELECT CU_ID, email FROM v_Customer_Latest WHERE CU_ID = 42;  -- prunes to CU_EMA
EXPLAIN SELECT * FROM v_Customer_Contact WHERE CU_ID = 42;            -- always 1 attr

Step-by-step trace.

Objection element	Rebuttal	Mechanism
"twenty joins per read"	only for `SELECT *`; narrow reads prune	join / subquery elimination
"joins are slow"	each is an index seek + LIMIT 1	`(anchor, ValidFrom DESC)` index
"columnar can't help"	it reads only referenced columns/tables	columnar + elimination synergy
"analysts will struggle"	they query views, not tables	latest / as-of / narrow views
"when is it actually slow?"	naive planner or missing index	honest boundary

Rebuttal in order: (1) a full twenty-attribute read is twenty index seeks, not scans, thanks to the composite (anchor, ValidFrom DESC) indexes; (2) the common case is a narrow read, where join elimination prunes the model down to the one or two attribute tables the query references; (3) on a columnar MPP engine, elimination plus column pruning means the physical work is proportional to what is selected, not to the model size; (4) analysts use v_Customer_Latest or hot-path narrow views like v_Customer_Contact, never the raw tables. The honest boundary: on a row-store with a planner that cannot eliminate joins, or with missing indexes, the objection holds — there, prefer Kimball or Data Vault.

Output:

Query	Physical work	Perf verdict
`SELECT CU_ID, email` (wide view)	1 anchor + 1 attr seek	fast (eliminated)
`v_Customer_Contact`	1 anchor + 1 attr seek	fast (narrow by design)
`SELECT *` (all attrs)	N indexed seeks	acceptable, bounded
unindexed / naive planner	N scans	slow — do not use here

Why this works — concept by concept:

Composite (anchor, ValidFrom DESC) index — turns every latest/as-of subquery into a single index range returning the first row, so a join is an O(log n) seek, not a scan. This is the quantitative core of the rebuttal.
Join and table elimination — because 6NF attributes join by key with one-row-per-anchor semantics, the optimizer proves unreferenced attribute joins are unnecessary and prunes them, so narrow reads pay for k attributes, not N.
Columnar synergy — columnar storage reads only referenced columns and, combined with elimination, only referenced tables; the physical cost tracks the query's real needs, not the model's table count.
Layered views — a wide view for convenience plus narrow views for hot paths gives ergonomics and guarantees minimal work on the paths that matter most.
Cost + honest boundary — with indexes and an elimination-capable planner the join cost is O(k) index seeks per read and the objection dissolves; without them (naive row-store, missing indexes) it is O(N) scans and Anchor Modeling is the wrong choice. Naming that boundary is what makes the defense credible.

Design
Topic — dimensional-modeling
Modeling trade-off and schema-evolution problems

Practice →

SQL
Topic — optimization
Query-optimization and join-elimination problems

Practice →

Cheat sheet — Anchor Modeling and 6NF recipes

The four constructs. Anchor = immutable identity (surrogate key + optional load metadata, nothing else). Attribute = one property in its own table (anchor_id, value, optional ValidFrom). Tie = relationship between anchors (keys only + optional ValidFrom, no business data). Knot = small immutable shared enumeration (id, value) referenced by key. Read the construct off the name convention: XX_ anchor, XX_YYY_ attribute, XX_rel_ZZ_ tie, YYY_ knot.
The 6NF rule. Each relation holds a key plus at most one non-key value. If a table you drew carries two describing values, it is not in sixth normal form — split it. The atom is the unit of independent change, history, and typing.
Immutability / append-only. Never UPDATE a fact; a change is an INSERT of (anchor_id, new_value, new_valid_from). The attribute table is the audit trail — no separate history table, no SCD2 flag juggling. "Current value" is a derived read (max ValidFrom), never a stored column.
Historized attribute template. CREATE TABLE XX_YYY_Entity_Prop (XX_ID BIGINT REFERENCES XX_Entity, XX_YYY_Value <type> NOT NULL, XX_YYY_ValidFrom DATE NOT NULL, PRIMARY KEY (XX_ID, XX_YYY_ValidFrom)); plus CREATE INDEX ix ON XX_YYY_... (XX_ID, XX_YYY_ValidFrom DESC);. Static attributes drop ValidFrom and key on XX_ID alone.
Point-in-time query. Per attribute: WHERE anchor_id = ? AND ValidFrom <= :as_of ORDER BY ValidFrom DESC LIMIT 1. Reconstruct the whole entity with one such subquery per attribute — never a single "latest row overall," because attribute timelines are independent.
Bitemporal. Add RecordedAt (transaction time) beside ValidFrom (valid time). Answer "as we knew it on X, effective Y" by filtering RecordedAt <= X then ValidFrom <= Y then taking the latest of each. Use it only for regulated/corrected-knowledge reporting; plain valid-time historization is simpler otherwise.
Additive schema evolution. New attribute = CREATE TABLE (locks nothing, no backfill; absence of a row = "not set yet"). New relationship = new tie. Retire = stop writing, drop when consumers migrate. Extend views with CREATE OR REPLACE. Never ALTER a wide table under load when you can CREATE an isolated one.
Latest and as-of views. Ship two consumption surfaces: a latest view for "now" and an as-of function f_Entity_AsOf(id, date) for "then." Keep storage in 6NF; let the view/function layer absorb the joins so consumers write one-liners and never touch ValidFrom.
Table / join elimination. Expose wide views but rely on the optimizer to prune joins to unreferenced attributes; narrow queries then pay only for selected attributes. Verify with EXPLAIN. If the planner cannot prune, add targeted narrow views for hot paths.
Performance recipe. Composite (anchor_id, ValidFrom DESC) index on every historized attribute and tie turns latest/as-of into an index seek + LIMIT 1. Full read = O(attributes) seeks; narrow read = O(k) after elimination. Missing this index is the one thing that makes the "too many joins" objection true.
Vs Data Vault / Kimball. Kimball = wide dimensions, fewest joins, worst for churn/history. Data Vault = hubs/links/satellites, satellites group several attributes. Anchor Modeling = one table per attribute, maximal isolation and granular history, most tables. Present Anchor Modeling as a trade (agility + temporality + immutability for tables + joins), never as "better normalization."
When to use. Deep history (per-attribute or bitemporal) meeting either high schema-change frequency or a columnar engine with join/table elimination. Absent both, choose Kimball or Data Vault. Name the engine and the tooling (anchormodeling.com generator, SQL:2011 temporal) before committing — Anchor Modeling on a naive row-store without views is a join-cost trap.

Frequently asked questions

What is anchor modeling?

Anchor modeling is a database-modeling technique that is built for change: it decomposes every entity to sixth normal form, giving identity its own table (the anchor), each property its own table (an attribute), each relationship its own table (a tie), and each small shared enumeration its own table (a knot). All facts are written append-only — a change is a new row, never an overwrite — so the full history of the business is preserved by construction and any past state can be reconstructed. It was created by Lars Rönnbäck and Olle Regardt for highly temporal, agile data warehouses, and it trades many tables and read-time joins for maximal schema agility, granular immutable history, and first-class temporality.

What is 6NF (sixth normal form)?

6NF, or sixth normal form, is the highest normal form: a relation is in 6NF when it cannot be decomposed further without loss, which informally means each relation holds a key plus at most one non-key attribute. Where 3NF removes transitive dependencies and 5NF removes join dependencies, 6NF removes all non-trivial join dependencies, leaving irreducible atoms. Anchor Modeling applies 6NF literally: a customer's name, email, and status become three separate tables. The benefit is that each attribute becomes an independent unit of change, historization, and typing; the cost is that composing a full entity row requires joining the atoms back together at read time.

What are anchors, attributes, ties, and knots?

The four constructs of Anchor Modeling are: an anchor, which is the immutable identity of an entity (a surrogate key and nothing else); an attribute, which is a single property of an anchor in its own table, optionally historized with a ValidFrom; a tie, which is a relationship between two or more anchors holding only their keys plus an optional ValidFrom and no business data; and a knot, which is a small, immutable, shared enumeration (like a status or role lookup) referenced by attributes and ties by key. A strict naming convention makes the construct readable from the table name, which is what keeps a schema of a hundred small tables navigable. Attributes and ties can reference knots, keeping enumerations centralized and consistent.

Anchor Modeling vs Data Vault — what is the difference?

Both anchor modeling and Data Vault separate identity from description and track history by insertion rather than overwrite, but they differ in granularity. Data Vault uses hubs (business keys), links (relationships), and satellites that group several descriptive attributes with a load timestamp — so changing one attribute writes a whole satellite row, and adding an attribute may alter a satellite. Anchor Modeling goes further to 6NF: every attribute is its own table, so changing or adding one attribute touches exactly one small table and nothing else. Anchor Modeling therefore offers finer-grained history and more isolated schema evolution at the cost of more tables and more joins, while Data Vault is a pragmatic middle ground between Anchor Modeling's extreme normalization and Kimball's wide dimensions.

What is bitemporal data?

Bitemporal data tracks two independent time axes: valid time (when a fact is true in the real world) and transaction time (when the database recorded the fact). A bitemporal temporal database can answer not only "what was the salary effective January 1" (valid time) but "what did we believe the January 1 salary was, as of January 3" (transaction time) — which differ whenever a fact is recorded late or later corrected. In Anchor Modeling you make an attribute bitemporal by adding a RecordedAt column beside ValidFrom and filtering transaction time first, then valid time. Bitemporality is essential for regulated reporting, audits, and corrections, where you must reconstruct not just history but the knowledge you had at the time.

Does the join cost of Anchor Modeling matter in practice?

It depends entirely on the engine and the indexing. With a composite (anchor_id, ValidFrom DESC) index on every historized attribute, each latest or as-of read is a single index seek plus LIMIT 1, so a full-entity read is a bounded set of cheap seeks, not table scans. On a columnar engine with join and table elimination, a query selecting a few attributes prunes the joins to the tables it actually references, so it pays for k attributes rather than all N — the "twenty joins per read" objection dissolves for the common narrow query. The join cost genuinely matters only in two cases: a naive row-store planner that cannot eliminate joins, or missing indexes that force scans. Fix the index and pick an elimination-capable engine, and the cost is small; miss both, and Anchor Modeling is the wrong choice.

Practice on PipeCode

Drill the dimensional-modeling practice library → for the anchor-vs-Data-Vault-vs-Kimball trade-offs, the four constructs, and the paradigm-choice rubric senior interviewers probe.
Rehearse the temporal SQL on the SQL practice library → for point-in-time reconstruction, latest-value queries, bitemporal filters, and the (anchor, ValidFrom DESC) index patterns.
Sharpen the history-tracking axis with the slowly-changing-data practice library → for append-only historization, as-of views, and SCD-versus-6NF comparisons.
Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the constructs, temporality, and join-cost defense against real graded inputs.

Lock in Anchor Modeling and 6NF muscle memory

Docs explain the constructs. PipeCode drills explain the decision — when 6NF's additive evolution beats a wide dimension, when a knot beats an attribute, when point-in-time reconstruction needs per-attribute as-of subqueries, and when the join cost actually bites. Pipecode.ai is Leetcode for Data Engineering — model-first practice tuned for the temporal, immutable, agile trade-offs senior data engineers and architects actually defend in interviews.

Practice SQL problems →
Practice design problems →