DAMA-DMBOK for Data Engineers: The Knowledge Areas That Actually Show Up in Interviews

DAMA-DMBOK — the Data Management Body of Knowledge published by DAMA International — is the reference framework whose vocabulary quietly shows up in half the senior data-engineering job specs you read, and it is the one most engineers have never actually opened. It is a thick book that carves the whole discipline of managing data into eleven knowledge areas, drawn as a wheel with governance at the centre, and it is easy to dismiss as a certification artefact written for architects and consultants rather than the person wiring up the pipeline. That dismissal is a mistake in an interview room, because the framework is exactly the shared language a panel reaches for when they ask "who owns this dataset," "how do you measure whether this table is trustworthy," or "walk me through the metadata you would capture" — and the candidate who can name the knowledge area, the operating model, and the metric scores far higher than the one who improvises.

This guide is the pragmatic filter every senior data engineer wishes existed the first time the data management body of knowledge came up on a scorecard: which of the eleven knowledge areas you actually touch daily, and which stay academic. It walks the DAMA wheel from the governance hub outward and lands on the five areas that get probed hardest — data governance (ownership, stewardship, decision rights, and enforced policy), data quality (the six measurable dimensions and how to score them), data architecture and data modeling (conceptual-to-physical, normalization versus dimensional, and slowly changing dimensions), and metadata management (technical, business, and operational metadata, plus the column-level lineage interviewers love to ask you to build). Each section pairs a teaching block with a Solution-Tail interview answer — code, 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 SQL practice library →, harden your checks on the data validation practice library →, and rehearse schema work on the database practice library →.

---

On this page

• Why the DAMA-DMBOK wheel maps to real interview questions
• Data governance — the hub of the wheel
• Data quality — dimensions, rules, and measurement
• Data architecture and data modeling
• Metadata management — the area that ties the wheel together
• Cheat sheet — DAMA-DMBOK knowledge-area recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the DAMA-DMBOK wheel maps to real interview questions

Eleven knowledge areas, one governance hub — interviewers only test the handful you touch daily

The one-sentence invariant: the DAMA-DMBOK is a map of the entire data-management discipline into eleven knowledge areas arranged around a governance hub, but a data-engineering interview only probes the five or six areas that touch the pipeline you build — so the winning move is to recognise the framework's vocabulary, know which area a question lives in, and answer with that area's operating model and metric rather than improvising. The framework was written to be comprehensive across roles — architects, stewards, compliance officers, BI leads — which is why most of it feels abstract to an engineer. The value for you is not memorising all eleven areas cover to cover; it is the shared vocabulary that lets you say "that is a data-quality question, and here are the six dimensions I would measure" instead of "yeah, we check the data looks okay."

The DAMA wheel — governance at the centre, ten areas around the rim.

• The hub. Data Governance sits at the centre of the wheel because every other area inherits its policies, standards, and decision rights. You cannot have data quality without someone empowered to set the quality thresholds; you cannot have metadata management without a policy that says who curates the glossary.
• The rim. The ten surrounding areas are Data Architecture, Data Modeling and Design, Data Storage and Operations, Data Security, Data Integration and Interoperability, Document and Content Management, Reference and Master Data, Data Warehousing and Business Intelligence, Metadata, and Data Quality.
• The framing. DAMA draws it as a wheel (not a stack) to make the point that no area is "first" — they are interdependent, and governance binds them together. Interviewers use the wheel as a checklist of vocabulary they can reach for.

Which knowledge areas actually show up for a data engineer.

• Probed hard. Data Governance, Data Quality, Metadata, Data Modeling and Design, Data Architecture. These are the areas whose vocabulary maps directly to daily engineering tasks — owning a dataset, validating a load, cataloguing a table, designing a schema.
• Probed sometimes. Data Integration and Interoperability (ETL/ELT, CDC), Reference and Master Data (the customer-360 / golden-record question), Data Security (column masking, PII), Data Storage and Operations (partitioning, retention).
• Rarely probed. Document and Content Management, and the reporting-tool specifics of Data Warehousing and BI — these lean toward BI-analyst and content-manager roles, not pipeline engineers.

The four axes an interviewer probes inside any area.

• Vocabulary. Do you name the area and its concepts precisely — "stewardship," "conformed dimension," "operational metadata" — or do you talk around them? Naming the concept is the fastest seniority signal.
• Ownership. Who is accountable, who is responsible, who executes? Every DMBOK area has a role model; the ability to separate owner from steward from custodian is a governance-literacy tell.
• Measurement. Can you turn the area into a number? Data quality has dimension scores; governance has policy-coverage metrics; metadata has catalog-completeness. "We measure it" beats "we care about it."
• Lineage. Can you trace a value end to end — where it came from, what transformed it, who consumes it? Lineage is the connective tissue the whole wheel depends on, and it is the most common hands-on ask.

What interviewers listen for.

• Do you name the framework and the governance hub without prompting? — senior signal.
• Do you separate ownership from stewardship from custodianship cleanly? — required answer.
• Do you turn a vague quality question into the six measurable dimensions? — senior signal.
• Do you describe metadata as technical, business, and operational rather than "column descriptions"? — senior signal.
• Do you treat the DMBOK as a vocabulary map, not a certification to recite? — required answer.

Worked example — the wheel-to-interview map

Detailed explanation. The single most useful artifact for a DMBOK-flavoured interview is a one-page map from knowledge area to the interview question it answers and the daily task it governs. Every senior data-management discussion converges on a handful of these rows; having them in your head lets you locate any question inside the wheel in seconds. Walk through building the map for the five areas that matter most to a data engineer.

• The area. The DMBOK knowledge area name, as the panel would say it.
• The interview question. The phrasing you will actually hear.
• The daily task. The pipeline work the area governs.

Question. Build the wheel-to-interview map for governance, quality, metadata, architecture, and modeling, and note the one-line answer each area expects.

Input.

Knowledge area	Typical interview question	Daily engineering task
Data Governance	"Who owns this dataset and who can change the schema?"	Assigning owners/stewards; enforcing access
Data Quality	"How do you know this table is trustworthy?"	Validation checks in the pipeline
Metadata	"What metadata would you capture for this feed?"	Cataloguing tables; wiring lineage
Data Architecture	"How do the layers of the platform fit together?"	Bronze/silver/gold, storage choices
Data Modeling	"Model this domain — normalize or star?"	DDL, keys, SCDs

Code.

The DAMA wheel — engineer's read (memorise this)
================================================

                    ┌───────────────────────┐
                    │   DATA GOVERNANCE     │   ← the hub: policy + decision rights
                    │  owners · stewards    │
                    │  policies · standards │
                    └───────────┬───────────┘
                                │ every area inherits from the hub
        ┌───────────────┬───────┴───────┬───────────────┐
        ▼               ▼               ▼               ▼
  ┌───────────┐  ┌───────────┐   ┌───────────┐   ┌───────────┐
  │  QUALITY  │  │ METADATA  │   │ ARCHITECT │   │ MODELING  │
  │ 6 dims +  │  │ tech/biz/ │   │ layers +  │   │ 3NF +     │
  │ scorecard │  │ ops + lineage │ storage   │   │ star/SCD  │
  └───────────┘  └───────────┘   └───────────┘   └───────────┘

  Answer format for any area:  name it → operating model → the metric.

Step-by-step explanation.

1. Each row of the map pins a knowledge area to the exact question phrasing a panel uses, so when you hear "who owns this dataset," you immediately place it in Data Governance and reach for the ownership/stewardship model rather than a vague answer about permissions.
2. The daily-task column is what makes the framework concrete: governance is not paperwork, it is the access grant you write; quality is not a slogan, it is the validation query in your DAG; metadata is not documentation, it is the catalog row and the lineage edge.
3. The hub-and-rim shape is the memory aid. If a question does not obviously fit a rim area, ask "is this really a governance question about who decides?" — surprisingly often it is, because governance is the layer that binds the rest.
4. The answer format — name the area, give its operating model, then give the metric — is the reusable template. It works for every area because DAMA structured every area around roles, activities, and deliverables.
5. The map deliberately omits the rarely-probed areas (document management, BI reporting) so you spend interview prep on the five that convert to a data-engineering scorecard.

Output.

Question you hear	Area you place it in	One-line answer shape
"Who can change this schema?"	Governance	owner is accountable, steward curates, custodian enforces
"Is this data trustworthy?"	Quality	score six dimensions, trend them, quarantine failures
"What metadata matters here?"	Metadata	technical + business + operational, plus lineage
"How is the platform layered?"	Architecture	conceptual blueprint → storage tiers → serving
"Model this domain"	Modeling	3NF for the source, star + SCD for the warehouse

Rule of thumb. Do not memorise the DMBOK cover to cover. Memorise the wheel-to-interview map: for any question, name the knowledge area, state its operating model, then give the metric. That three-beat answer is what a panel scores as data-management literacy.

Worked example — mapping knowledge areas to platform layers

Detailed explanation. A common senior question is "walk me through your platform and tell me which governance concerns live at each layer." The DMBOK areas are not evenly distributed across a modern lakehouse — governance and metadata span every layer, while modeling concentrates at the serving layer. Codifying the mapping makes the answer reproducible. Walk through a bronze/silver/gold lakehouse and place each area.

• Bronze (raw landing). Ingestion fidelity; technical metadata; source ownership.
• Silver (cleaned/conformed). Data quality dimensions; integration; reference/master data.
• Gold (serving/marts). Dimensional modeling; BI; business metadata / glossary.

Question. Place the five key knowledge areas across the bronze, silver, and gold layers and note where each area does its heaviest work.

Input.

Layer	Primary knowledge areas	What the engineer does
Bronze	Metadata, Governance	capture schema + source owner, no transforms
Silver	Data Quality, Integration, Master Data	validate, dedupe, conform keys
Gold	Data Modeling, BI, Metadata (business)	star schemas, SCDs, glossary terms

Code.

# A knowledge-area map keyed by platform layer (illustrative)
DMBOK_BY_LAYER = {
    "bronze": {
        "governance": "register source owner + retention policy",
        "metadata":   "harvest technical metadata (types, keys, load ts)",
        "quality":    "structural checks only (schema drift, row count)",
    },
    "silver": {
        "quality":     "score the six dimensions; quarantine failures",
        "integration": "conform keys, dedupe, standardise codes",
        "master_data": "resolve golden records for customer/product",
    },
    "gold": {
        "modeling":  "star schema, conformed dims, SCD Type 2",
        "metadata":  "business glossary terms + column lineage",
        "bi":        "certified marts consumers can trust",
    },
}

def areas_at(layer: str) -> list[str]:
    """Which DMBOK areas do their heaviest work at this layer?"""
    return sorted(DMBOK_BY_LAYER[layer].keys())

print(areas_at("bronze"))  # ['governance', 'metadata', 'quality']
print(areas_at("silver"))  # ['integration', 'master_data', 'quality']
print(areas_at("gold"))    # ['bi', 'metadata', 'modeling']

Step-by-step explanation.

1. Bronze is deliberately transform-free, so the DMBOK work there is metadata capture (what did we receive) and governance registration (who owns it, how long we keep it). Quality at bronze is structural only — did the schema drift, did the row count collapse — not semantic.
2. Silver is where data quality earns its keep: the six dimensions are scored here because this is the first layer where you have cleaned, conformed data worth measuring. Integration and master-data resolution also concentrate here, because conforming keys is the prerequisite for a golden record.
3. Gold is the modeling layer: star schemas, conformed dimensions, and slowly changing dimensions live here, along with the business metadata (glossary terms) that lets a BI consumer trust the mart.
4. Two areas — governance and metadata — span all three layers rather than concentrating in one. That spanning is exactly why DAMA draws governance as the hub and metadata as the connective area: they are not a layer, they are a cross-cutting concern.
5. The map turns a vague "walk me through your platform" into a structured tour: at each layer, name the areas doing the work and the concrete artefact they produce. That structure is the seniority signal.

Output.

Concern	Bronze	Silver	Gold
Governance	owner + retention	policy enforcement	certification sign-off
Metadata	technical	operational	business / glossary
Quality	structural	six dimensions scored	reconciliation vs source
Modeling	none (raw)	conform keys	star + SCD

Rule of thumb. When asked to walk your platform, tour it layer by layer and name the DMBOK area doing the heaviest work at each. Governance and metadata span every layer; quality peaks at silver; modeling peaks at gold. That layered answer reads as architecture literacy.

Worked example — scoring your own interview answer against DMBOK vocabulary

Detailed explanation. The fastest way to level up a data-management answer is to grade it against the DMBOK vocabulary before you say it. Weak answers use folk terms ("the data looks fine," "someone maintains it"); senior answers use the framework's precise nouns. Walk through a rubric that upgrades a folk answer to a framework answer, concept by concept.

• Folk answer. Vague, role-free, unmeasured.
• Framework answer. Names the area, the role, and the metric.

Question. Take three folk answers and rewrite each into a DMBOK-literate answer that a senior panel would score highly.

Input.

Folk phrasing	Missing concept	Framework phrasing
"Someone looks after that table"	ownership model	"the marketing domain owner is accountable; a steward curates it"
"We check the data is okay"	quality dimensions	"we score completeness, validity, and timeliness against thresholds"
"It's documented somewhere"	metadata types	"technical metadata is in the catalog; business terms in the glossary"

Code.

Answer-upgrade rubric (run silently before you speak)
=====================================================

  1. NAME THE AREA
     folk: "data stuff"        → framework: "this is a metadata question"

  2. NAME THE ROLE
     folk: "someone"           → framework: "owner (accountable) + steward (responsible)"

  3. NAME THE METRIC
     folk: "it's fine"         → framework: "DQ score 96%, SLA freshness < 2h"

  4. NAME THE ARTEFACT
     folk: "we track it"       → framework: "policy registry row + catalog entry + lineage edge"

  Score: 0 beats hit = folk (junior).  4 beats hit = framework (senior).

Step-by-step explanation.

1. Beat one is naming the area. The moment you say "this is a data-quality question" or "that lives in metadata management," the panel hears that you have a mental model, not just anecdotes. It costs one sentence and buys credibility.
2. Beat two is naming the role. Replacing "someone" with the owner/steward/custodian split shows you understand that data management is a set of accountabilities, not a pile of tasks — the single most common gap in junior answers.
3. Beat three is naming the metric. Every DMBOK area is designed to be measured; converting "it's fine" into a dimension score or an SLA number is the difference between an opinion and evidence.
4. Beat four is naming the artefact — the durable thing the work produces, such as a policy registry row, a catalog entry, or a lineage edge. Artefacts prove the practice is real and repeatable rather than heroic.
5. Running the rubric silently before you answer takes two seconds and converts almost any folk answer into a framework answer. Practise it until the four beats are automatic.

Output.

Beat	Junior answer	Senior answer
Area named	rarely	always
Role named	"someone"	owner + steward + custodian
Metric named	"it's fine"	dimension score / SLA
Artefact named	"somewhere"	registry / catalog / lineage

Rule of thumb. Before every data-management answer, run the four-beat rubric — name the area, the role, the metric, and the artefact. Hitting all four converts a folk answer into a framework answer, and framework answers are what a senior panel scores.

Senior interview question on the DAMA-DMBOK framework

A senior interviewer often opens with: "We keep hearing 'data governance' and 'data quality' thrown around. You clearly know the pipeline side — but do you have the data-management vocabulary to talk to our stewards and compliance team? Walk me through the DAMA-DMBOK framework, tell me which knowledge areas you actually work in, and show me how you would structure an answer about a dataset nobody trusts."

Solution Using a knowledge-area map to structure a governance-literate answer

# A reusable answer framework driven by the DMBOK wheel.
# Given a problem statement, route it to the right knowledge area,
# then emit a three-beat answer: area -> operating model -> metric.

WHEEL = {
    "governance": {
        "triggers": ["who owns", "who decides", "who can change", "policy", "access"],
        "operating_model": "domain owner (accountable) + steward (curates) + custodian (enforces)",
        "metric": "policy coverage %, access-review pass rate",
    },
    "quality": {
        "triggers": ["trust", "accurate", "clean", "nulls", "duplicates", "fresh"],
        "operating_model": "score six dimensions vs thresholds; quarantine + remediate",
        "metric": "DQ score per dimension, % rows passing",
    },
    "metadata": {
        "triggers": ["document", "catalog", "lineage", "glossary", "what does this column mean"],
        "operating_model": "harvest technical + business + operational metadata; wire lineage",
        "metric": "catalog completeness %, lineage coverage %",
    },
    "modeling": {
        "triggers": ["model", "schema", "normalize", "star", "dimension", "grain"],
        "operating_model": "conceptual -> logical -> physical; 3NF source, star + SCD warehouse",
        "metric": "grain documented, SCD strategy per dim",
    },
    "architecture": {
        "triggers": ["platform", "layers", "storage", "bronze", "silver", "gold"],
        "operating_model": "layered lakehouse; governance + metadata span all layers",
        "metric": "layer SLAs, certified-gold coverage",
    },
}

def route(problem: str) -> str:
    p = problem.lower()
    for area, spec in WHEEL.items():
        if any(t in p for t in spec["triggers"]):
            return (f"[{area}] {spec['operating_model']} "
                    f"| metric: {spec['metric']}")
    return "[governance] default: clarify decision rights first"

print(route("Nobody trusts the revenue table and there are duplicate rows"))
# -> [quality] score six dimensions ... | metric: DQ score per dimension ...

print(route("Who is allowed to change the customers schema?"))
# -> [governance] domain owner (accountable) + steward ... | metric: policy coverage % ...

# The spoken answer this framework produces, for "a dataset nobody trusts":

"That's primarily a data-quality question with a governance root cause.
 First, governance: who is the accountable domain owner, and is there a
 named steward? A dataset nobody trusts usually has no owner. Second,
 quality: I'd profile it across the six DMBOK dimensions — completeness,
 uniqueness, validity, consistency, accuracy, timeliness — score each
 against a threshold, and quarantine failing rows instead of blocking the
 whole load. Third, metadata: I'd publish the scores and the lineage so
 consumers can see why to trust it. The deliverables are a policy-registry
 owner, a DQ scorecard, and a catalog entry with lineage."

Step-by-step trace.

Step	Input	Framework output
Route the problem	"nobody trusts the table + duplicates"	area = quality (governance root cause)
Name the operating model	quality	six dimensions, threshold, quarantine
Name the role	governance	owner accountable, steward curates
Name the metric	quality + governance	DQ score per dimension, policy coverage
Name the artefacts	all	registry row, DQ scorecard, catalog + lineage

After routing, the answer is no longer improvised — it names the knowledge area, states that area's operating model, gives the metric, and lists the durable artefacts. The same framework handles a schema question (routes to modeling), a platform question (routes to architecture), or a lineage question (routes to metadata) with the identical three-beat shape.

Output:

Answer quality	Improvised	Framework-routed
Area named	sometimes	always
Root cause surfaced	rarely	governance root cause named
Metric offered	"looks fine"	six-dimension score
Artefacts listed	none	registry + scorecard + lineage
Panel read	"good engineer"	"engineer who can talk to stewards"

Why this works — concept by concept:

• The wheel as a router — treating the eleven knowledge areas as a routing table turns any vague question into a located one. The trigger words map the question to an area; the area supplies the operating model and metric. This is why the DMBOK is worth knowing even if you never take the certification.
• Governance as the default — when a problem does not obviously fit a rim area, the framework falls back to governance, because "a dataset nobody trusts" almost always has a decision-rights root cause: no owner, no steward, no policy. Naming that root cause is the senior move.
• Three-beat answer shape — area, operating model, metric. Every DMBOK area is structured around roles, activities, and deliverables, so this shape fits every area. It is the reusable template that keeps answers precise under pressure.
• Artefacts over intentions — closing every answer with the durable artefacts (registry row, scorecard, catalog entry, lineage edge) proves the practice is real. Panels distrust "we care about quality" and trust "here is the scorecard we publish."
• Cost — near zero. The framework is a lookup table you carry in your head; routing a question costs one sentence of naming. The payoff is O(1) per question versus the O(N) flailing of an improvised answer that never names the area, the role, or the metric.

Design
Topic — design
Design problems on data platforms and governance

Practice →

Database Topic — database Database problems on schemas and data management

Practice →

---

2. Data governance — the hub of the wheel

data governance is the policy layer — ownership, stewardship, and the decision rights every other knowledge area inherits

The mental model in one line: data governance is the operating model that assigns accountability for data — a domain owner who is accountable, a steward who curates and defines, and a custodian who physically enforces — and expresses that accountability as policies and standards in a registry that the platform actually enforces, so the answer to "who owns this dataset" is a name and a policy, not a shrug. Every other DMBOK area inherits from governance: quality thresholds are set by governance, glossary curation is a governance duty, retention and access are governance policies. This is why the DAMA wheel puts governance at the hub — it is the layer that gives every other area its authority.

The three roles interviewers make you separate.

• Owner — accountable. Usually a business domain leader (head of marketing for the marketing data domain). Accountable for the data's fitness for purpose, its policies, and its budget. Owners do not write SQL; they set direction and sign off.
• Steward — responsible. The subject-matter expert who curates definitions, sets quality rules, resolves ambiguity ("what counts as an active customer?"), and maintains the glossary. Stewardship is the role a senior data engineer most often plays or partners with.
• Custodian — executes. IT / the data platform team (often you) that physically stores, secures, and moves the data and enforces the owner's policies. The custodian implements; the owner decides; the steward defines.

The governance operating model.

• The council / governance body. A cross-domain group that ratifies enterprise-wide standards (naming conventions, classification levels, retention defaults) so domains do not each invent their own.
• Policies. Enforceable statements: "PII columns are masked for non-privileged roles," "financial tables retain seven years," "every certified table has a named owner."
• Standards. The concrete rules a policy compiles into: the list of PII column tags, the retention interval per classification, the naming pattern for certified marts.
• RACI. The matrix that pins each activity (define, approve, implement, monitor) to a role, so no activity is unowned and none is double-owned.

How governance becomes real for an engineer.

• A registry, not a wiki. Ownership and policy live in a queryable table, not a Confluence page nobody reads. If you can SELECT the owner of a table, governance is real; if you have to ask around, it is theatre.
• Enforcement in the platform. Access policies are GRANT/REVOKE and row/column masking, not a spreadsheet of who-should-see-what. Governance you cannot enforce is a suggestion.
• Metrics. Policy coverage (what fraction of certified tables have an owner and a classification), access-review pass rate, time-to-remediate a policy violation. Governance without metrics cannot prove it is working.

Common interview probes on data governance.

• "What is the difference between a data owner and a data steward?" — required answer: owner is accountable and sets direction; steward is responsible and curates definitions.
• "Where does governance live in your platform?" — a queryable policy/ownership registry plus enforced grants, not a wiki.
• "How do you enforce a PII policy?" — column-level masking / role-based grants driven by classification tags, not manual review.
• "How do you measure governance?" — policy coverage %, access-review pass rate, time-to-remediate.

Worked example — the ownership RACI and a policy registry

Detailed explanation. The canonical governance artefact is a pair of tables: a RACI that pins each activity to a role, and a policy registry that records, per dataset, its owner, steward, classification, and retention. Build both from scratch so an engineer can answer "who owns this and what are the rules" with a query.

• RACI. Rows are activities (define term, approve access, implement masking, monitor quality); columns are roles (owner, steward, custodian, council).
• Policy registry. One row per dataset: owner, steward, classification, retention, PII flag.

Question. Write the RACI as data and a policy_registry table, then a query that answers "who is accountable for the customers table and what is its retention?"

Input.

Activity	Owner	Steward	Custodian	Council
Define business term	A	R	I	C
Approve access request	A	C	R	I
Implement masking	I	C	R	I
Monitor quality	A	R	C	I

Code.

-- Policy registry: governance as queryable data, not a wiki page
CREATE TABLE governance.policy_registry (
    dataset          TEXT        PRIMARY KEY,   -- 'analytics.customers'
    domain           TEXT        NOT NULL,      -- 'marketing'
    owner_email      TEXT        NOT NULL,      -- accountable business owner
    steward_email    TEXT        NOT NULL,      -- responsible SME
    custodian_team   TEXT        NOT NULL,      -- 'data-platform'
    classification   TEXT        NOT NULL,      -- 'public'|'internal'|'confidential'|'restricted'
    contains_pii     BOOLEAN     NOT NULL DEFAULT FALSE,
    retention_months INT         NOT NULL,
    certified        BOOLEAN     NOT NULL DEFAULT FALSE,
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO governance.policy_registry
    (dataset, domain, owner_email, steward_email, custodian_team,
     classification, contains_pii, retention_months, certified)
VALUES
    ('analytics.customers', 'marketing', '[e-mail]',
     '[e-mail]', 'data-platform', 'confidential', TRUE, 84, TRUE),
    ('analytics.orders',    'commerce',  '[e-mail]',
     '[e-mail]', 'data-platform', 'internal', FALSE, 36, TRUE);

-- "Who is accountable for customers and what is its retention?"
SELECT owner_email        AS accountable_owner,
       steward_email      AS responsible_steward,
       classification,
       contains_pii,
       retention_months
FROM   governance.policy_registry
WHERE  dataset = 'analytics.customers';

Step-by-step explanation.

1. The policy_registry table turns governance from prose into data. The primary key is the fully-qualified dataset name, so every certified table has exactly one row that names its owner, steward, and custodian — the three roles the interview forces you to separate.
2. The owner_email is the accountable party (a business leader), while steward_email is the responsible SME. Keeping them in separate columns encodes the RACI distinction directly in the schema, which is the point interviewers probe.
3. classification and contains_pii are the inputs to enforcement: the next worked example drives column masking off exactly these fields. Governance data is only useful if enforcement reads it.
4. retention_months is a policy compiled into a number a retention job can act on. "Financial data kept seven years" becomes 84, which a scheduled purge queries — policy as executable data.
5. The final SELECT is the whole payoff: "who owns this and what are the rules" is now a query returning a name and a policy, not a shrug. That queryability is the difference between real governance and governance theatre.

Output.

accountable_owner	responsible_steward	classification	contains_pii	retention_months
[e-mail]	[e-mail]	confidential	true	84

Rule of thumb. Store ownership and policy in a queryable registry keyed by the fully-qualified dataset name, with separate columns for the accountable owner and the responsible steward. If you cannot SELECT the owner of a table, your governance is a wiki page, not a control.

Worked example — enforcing a classification policy with column-level access

Detailed explanation. A policy that is not enforced is a suggestion. The registry says analytics.customers is confidential and contains PII; the platform must translate that into who can see the raw ssn column versus a masked version. Walk through role-based column masking driven off the classification, so enforcement reads the registry rather than a manual spreadsheet.

• The rule. Non-privileged roles see a masked ssn; privileged roles (fraud, compliance) see the raw value.
• The mechanism. A masking policy attached to the column, plus role grants.

Question. Enforce the PII policy on analytics.customers.ssn so that only a pii_reader role sees the raw value, and everyone else sees a masked value.

Input.

Role	Should see ssn
pii_reader (fraud, compliance)	raw 123-45-6789
analyst (default)	masked XXX-XX-6789
marketing_bi	masked XXX-XX-6789

Code.

-- 1. A masking policy: return raw for privileged roles, masked otherwise.
--    (Snowflake-style masking policy; the same idea maps to Postgres
--     column-level RLS or a secure view elsewhere.)
CREATE MASKING POLICY governance.mask_ssn AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('PII_READER') THEN val
        ELSE 'XXX-XX-' || RIGHT(val, 4)
    END;

-- 2. Attach the policy to the column the registry flagged as PII
ALTER TABLE analytics.customers
    MODIFY COLUMN ssn SET MASKING POLICY governance.mask_ssn;

-- 3. Grants derive from classification, not ad-hoc requests
GRANT SELECT ON analytics.customers TO ROLE analyst;      -- sees masked
GRANT SELECT ON analytics.customers TO ROLE marketing_bi; -- sees masked
GRANT ROLE pii_reader TO ROLE fraud_investigations;       -- sees raw

-- 4. A guardrail query: any PII-flagged column WITHOUT a masking policy is a violation
SELECT r.dataset, c.column_name
FROM   governance.policy_registry r
JOIN   information_schema.columns c
       ON  c.table_schema || '.' || c.table_name = r.dataset
WHERE  r.contains_pii = TRUE
  AND  c.column_name IN ('ssn', 'email', 'phone')
  AND  NOT EXISTS (
        SELECT 1 FROM information_schema.masking_policy_references m
        WHERE  m.ref_column_name = c.column_name
          AND  m.ref_table_name  = c.table_name
      );

Step-by-step explanation.

1. The masking policy is a function of the caller's role: PII_READER gets the raw value; everyone else gets XXX-XX- plus the last four digits. This is enforcement in the engine, not a review step a human can forget.
2. Attaching the policy to the ssn column ties enforcement to the exact column the registry flagged as PII. The link from registry (contains_pii = TRUE) to enforcement (masking policy on the column) is what makes governance a closed loop.
3. Grants are derived from classification rather than granted ad hoc: analysts and BI get masked access by default, and only a named privileged role sees raw PII. "Access request" becomes "which classification-driven role do you belong to."
4. The guardrail query is the governance metric in action — it lists any PII-flagged column that lacks a masking policy, which is a policy violation. Running it on a schedule turns "we have a PII policy" into "we prove the PII policy is enforced on every flagged column."
5. Because enforcement reads the registry, adding a new confidential table with a PII column automatically shows up in the guardrail until someone attaches the policy — the system nags itself toward compliance instead of relying on memory.

Output.

Caller role	ssn returned
fraud_investigations (pii_reader)	123-45-6789
analyst	XXX-XX-6789
marketing_bi	XXX-XX-6789
guardrail query	0 rows = compliant; >0 rows = violation

Rule of thumb. Drive enforcement off the classification in the registry: masking policies on PII columns, role grants derived from classification, and a scheduled guardrail query that flags any PII column missing a policy. Enforcement that reads governance data is the difference between a control and a comment.

Worked example — a governance scorecard you can trend

Detailed explanation. Governance without metrics cannot prove it works. The scorecard answers "how governed is our platform" with numbers: what fraction of certified tables have an owner, a classification, and (if PII) enforcement. Build a single query that produces the coverage percentages a governance council reviews monthly.

• Ownership coverage. % of certified tables with a non-null owner and steward.
• Classification coverage. % with a classification set.
• PII enforcement coverage. % of PII tables whose PII columns have a masking policy.

Question. Write the scorecard query that produces the three coverage percentages across all certified datasets.

Input.

Metric	Definition	Target
Ownership coverage	owner + steward present	100%
Classification coverage	classification set	100%
PII enforcement coverage	PII columns masked	100%

Code.

-- Governance scorecard: three coverage metrics in one pass
WITH certified AS (
    SELECT * FROM governance.policy_registry WHERE certified = TRUE
),
ownership AS (
    SELECT
        COUNT(*)                                              AS total,
        COUNT(*) FILTER (WHERE owner_email   IS NOT NULL
                          AND steward_email  IS NOT NULL)     AS with_roles,
        COUNT(*) FILTER (WHERE classification IS NOT NULL)     AS with_class
    FROM certified
),
pii AS (
    SELECT
        COUNT(*) FILTER (WHERE contains_pii)                   AS pii_total,
        COUNT(*) FILTER (WHERE contains_pii AND has_masking)   AS pii_enforced
    FROM (
        SELECT r.dataset, r.contains_pii,
               EXISTS (
                   SELECT 1 FROM information_schema.masking_policy_references m
                   WHERE m.ref_table_name = split_part(r.dataset, '.', 2)
               ) AS has_masking
        FROM certified r
    ) x
)
SELECT
    ROUND(100.0 * o.with_roles / NULLIF(o.total, 0), 1)          AS ownership_pct,
    ROUND(100.0 * o.with_class / NULLIF(o.total, 0), 1)          AS classification_pct,
    ROUND(100.0 * p.pii_enforced / NULLIF(p.pii_total, 0), 1)    AS pii_enforcement_pct
FROM ownership o CROSS JOIN pii p;

Step-by-step explanation.

1. The certified CTE scopes the scorecard to certified datasets — the tables the business actually relies on — so uncertified scratch tables do not dilute the metric.
2. The ownership CTE computes two coverage counts in one pass using FILTER: how many certified tables have both an owner and a steward, and how many have a classification. Coverage is these counts over the total.
3. The pii CTE cross-checks the registry against actual masking policies in information_schema, so PII enforcement coverage reflects reality (is the column masked) rather than intent (is it flagged).
4. The final SELECT turns the counts into three rounded percentages the council reviews monthly. Because it is one query, it can run on a schedule and be trended — the point is the slope over time, not a single snapshot.
5. Any percentage below 100 is a work list: the missing owners, the unclassified tables, the unmasked PII columns. The scorecard is not just a report; it is a queue of governance gaps to close.

Output.

ownership_pct	classification_pct	pii_enforcement_pct
96.4	100.0	88.9

Rule of thumb. Express governance as coverage percentages — ownership, classification, PII enforcement — computed by one scheduled query and trended monthly. A number that moves toward 100 is proof the practice works; "we take governance seriously" is not.

Senior interview question on data governance

A senior interviewer might ask: "A regulator is coming and they want to know, for our confidential customer data, who owns it, who can see the raw PII, and how you prove the policy is enforced. Design the governance layer — the roles, the registry, the enforcement, and the metric you would put in front of the regulator. Then tell me how a new confidential table gets governed automatically."

Solution Using a policy registry + role model + enforced masking + a coverage gate

-- 1. The registry is the source of truth for accountability + policy
CREATE TABLE governance.policy_registry (
    dataset          TEXT PRIMARY KEY,
    domain           TEXT NOT NULL,
    owner_email      TEXT NOT NULL,          -- accountable
    steward_email    TEXT NOT NULL,          -- responsible
    custodian_team   TEXT NOT NULL,          -- executes
    classification   TEXT NOT NULL CHECK (classification IN
                        ('public','internal','confidential','restricted')),
    contains_pii     BOOLEAN NOT NULL DEFAULT FALSE,
    retention_months INT NOT NULL,
    certified        BOOLEAN NOT NULL DEFAULT FALSE
);

-- 2. Enforcement derives from classification: masking + role grants
CREATE MASKING POLICY governance.mask_pii AS (val STRING) RETURNS STRING ->
    CASE WHEN CURRENT_ROLE() IN ('PII_READER','COMPLIANCE') THEN val
         ELSE 'XXX-XX-' || RIGHT(val, 4) END;

ALTER TABLE analytics.customers MODIFY COLUMN ssn   SET MASKING POLICY governance.mask_pii;
ALTER TABLE analytics.customers MODIFY COLUMN email SET MASKING POLICY governance.mask_pii;

-- 3. The regulator gate: one query proving enforcement coverage
CREATE OR REPLACE VIEW governance.enforcement_gate AS
WITH pii_cols AS (
    SELECT r.dataset, c.column_name,
           EXISTS (SELECT 1 FROM information_schema.masking_policy_references m
                   WHERE m.ref_table_name  = split_part(r.dataset, '.', 2)
                     AND m.ref_column_name = c.column_name) AS masked
    FROM governance.policy_registry r
    JOIN information_schema.columns c
      ON c.table_schema || '.' || c.table_name = r.dataset
    WHERE r.contains_pii = TRUE
      AND c.column_name IN ('ssn','email','phone','dob')
)
SELECT dataset, column_name, masked
FROM   pii_cols
WHERE  masked = FALSE;   -- empty view == fully enforced

# 4. New confidential tables get governed automatically via a CI check
#    that fails the deploy if a new table lacks a registry row / enforcement.
import psycopg2

REQUIRED_PII_COLS = {"ssn", "email", "phone", "dob"}

def audit_new_tables(conn) -> list[str]:
    """Return governance violations; a non-empty list fails CI."""
    violations: list[str] = []
    with conn.cursor() as cur:
        # a) certified tables missing a registry row
        cur.execute("""
            SELECT c.table_schema || '.' || c.table_name AS ds
            FROM   information_schema.tables c
            WHERE  c.table_schema = 'analytics'
            EXCEPT
            SELECT dataset FROM governance.policy_registry
        """)
        violations += [f"no registry row: {r[0]}" for r in cur.fetchall()]

        # b) PII columns with no masking policy
        cur.execute("SELECT dataset, column_name FROM governance.enforcement_gate")
        violations += [f"unmasked PII: {r[0]}.{r[1]}" for r in cur.fetchall()]
    return violations

if __name__ == "__main__":
    conn = psycopg2.connect("host=warehouse dbname=prod user=governance_ci")
    issues = audit_new_tables(conn)
    if issues:
        raise SystemExit("Governance gate failed:\n  " + "\n  ".join(issues))
    print("Governance gate passed: all tables registered + PII enforced")

Step-by-step trace.

Layer	Component	Purpose
Accountability	policy_registry	one row per dataset: owner, steward, custodian
Policy	classification + contains_pii	drives what enforcement is required
Enforcement	masking policy + role grants	raw PII only for PII_READER / COMPLIANCE
Regulator gate	enforcement_gate view	empty view = every PII column masked
Automation	CI audit	deploy fails if a new table is unregistered or unmasked

After deployment, the regulator's three questions each have a query answer: ownership comes from policy_registry, raw-PII access comes from the masking policy plus role grants, and proof-of-enforcement comes from the enforcement_gate view being empty. A newly deployed confidential table cannot ship unless the CI audit finds its registry row and its masking policy, so governance is enforced at merge time rather than discovered at audit time.

Output:

Regulator question	Answer artefact	Evidence
Who owns confidential customer data?	policy_registry.owner_email	[e-mail]
Who can see raw PII?	role grant to PII_READER	fraud + compliance only
How is the policy enforced?	masking policy on ssn/email	non-privileged see XXX-XX-####
How do you prove it?	enforcement_gate view	0 rows = fully enforced
New table governance	CI audit	deploy blocked until registered + masked

Why this works — concept by concept:

• Owner / steward / custodian split — the registry encodes the three-role model in three columns, so "who is accountable" (owner), "who curates" (steward), and "who enforces" (custodian) each have a queryable answer. This is the exact distinction the interview forces you to make.
• Classification-driven enforcement — masking and grants are derived from the classification and contains_pii fields rather than from ad-hoc tickets. Policy compiles into engine-enforced controls, which is what turns governance from a wiki into a guarantee.
• The enforcement gate as evidence — an empty view is a proof, not a promise. Handing a regulator "this view returns zero rows, and here is the query" is dramatically stronger than "we have a policy document."
• Governance in CI — auditing new tables at merge time means a confidential table cannot exist without an owner and enforced PII. The system trends toward 100% coverage because non-compliance blocks the deploy, not because someone remembers to check.
• Cost — one registry table, a masking policy per PII column, a view, and a CI step. The registry lookup is O(1) per dataset; the gate is O(PII columns). The eliminated cost is the frantic pre-audit scramble to reconstruct ownership and prove enforcement across hundreds of tables by hand.

Design
Topic — design
Design problems on governance and access control

Practice →

Data Validation Topic — data-validation Data validation problems on policy and coverage checks

Practice →

---

3. Data quality — dimensions, rules, and measurement

data quality is measured, not asserted — six dimensions, a rule per dimension, and a score you can trend

The mental model in one line: data quality in the DMBOK is not a vibe but a set of measurable dimensions — completeness, uniqueness, validity, consistency, accuracy, and timeliness — where each dimension is checked by explicit rules against thresholds, the results roll up into a per-dataset score you trend over time, and rows that fail are quarantined and remediated rather than silently dropped or silently loaded. The interview tell is whether you convert "is this data good?" into "here are the six dimensions I score and the thresholds I hold them to." A candidate who lists the dimensions and shows the scoring query is operating a decibel above one who says "we do some validation."

The six dimensions — what each one measures.

• Completeness. Are required values present? Measured as the fraction of non-null values in mandatory columns. A customer_id that is 2% null fails completeness.
• Uniqueness. Are entities represented once? Measured as count(distinct key) / count(*) or as duplicate-row counts. Duplicate orders inflate revenue.
• Validity. Do values conform to their domain, format, or range? An email that fails a pattern, a status outside the allowed set, a negative age — all validity failures.
• Consistency. Do related values agree, within and across tables? order_total should equal the sum of line items; a state should match its zip. Consistency is cross-field and cross-table.
• Accuracy. Does the value match the real-world truth? Hardest to measure because it needs a trusted reference — a golden source, a known-good sample, or a reconciliation against the system of record.
• Timeliness. Is the data fresh enough for its purpose? Measured as the lag between the event and its availability, against an SLA. A "real-time" dashboard fed by a 6-hour-late table fails timeliness.

Rules, thresholds, and scoring.

• A rule per dimension per dataset. Rules are concrete: "customer_id completeness >= 99.9%," "0 duplicate order_id," "email matches the RFC pattern," "load lag < 2h." Store them as data so they are auditable and reusable.
• Thresholds, not booleans. Real data is never perfectly clean; a dimension passes if it clears its threshold. Thresholds make the score continuous and trendable rather than a brittle pass/fail.
• A rolled-up score. Weight the dimensions (completeness and validity often weigh most) and roll them into a single 0–100 dataset score, plus the per-dimension breakdown so you can see why a score dropped.

Quarantine and remediation.

• Quarantine, do not drop. Failing rows go to a quarantine table with the failed rule attached, so nothing is silently lost and the failures are inspectable. Dropping bad rows hides the problem; loading them corrupts downstream.
• Remediate by rule. Some failures auto-correct (trim whitespace, standardise a code); some route to a steward; some block the load if a critical threshold is breached. The rule metadata says which.
• Trend the score. A single score is a snapshot; the slope is the signal. A dataset drifting from 99% to 95% over a week is a pipeline regression you catch before consumers do.

Common interview probes on data quality.

• "What are the dimensions of data quality?" — required answer: completeness, uniqueness, validity, consistency, accuracy, timeliness.
• "Which dimension is hardest to measure?" — accuracy, because it needs a trusted real-world reference.
• "What do you do with rows that fail?" — quarantine with the failed rule attached; remediate or route; do not silently drop.
• "How do you catch quality regressions?" — trend the per-dimension score; alert on the slope, not just a threshold.

Worked example — profiling a table across the six dimensions

Detailed explanation. The canonical data-quality artefact is a single profiling pass that scores all six dimensions for a dataset. Build it for an orders table so one query returns completeness, uniqueness, validity, consistency, and timeliness numbers (accuracy is handled separately via reconciliation).

• Completeness. Non-null customer_id and order_total.
• Uniqueness. Distinct order_id vs row count.
• Validity. status in an allowed set; order_total >= 0.
• Consistency. order_total equals line_items_total.
• Timeliness. Max load lag under the SLA.

Question. Write one profiling query over orders that returns a pass fraction for each measurable dimension.

Input.

Dimension	Rule on orders
Completeness	customer_id, order_total not null
Uniqueness	distinct order_id = row count
Validity	status in set; order_total >= 0
Consistency	order_total = line_items_total
Timeliness	loaded_at within 2h of order_ts

Code.

-- Six-dimension profile of analytics.orders in one pass
SELECT
    COUNT(*) AS rows_scanned,

    -- Completeness: fraction with required fields present
    ROUND(AVG( (customer_id IS NOT NULL AND order_total IS NOT NULL)::int ), 4)
        AS completeness,

    -- Uniqueness: distinct keys over rows (1.0 = no duplicates)
    ROUND( COUNT(DISTINCT order_id)::numeric / COUNT(*), 4)
        AS uniqueness,

    -- Validity: allowed status AND non-negative total
    ROUND(AVG( (status IN ('pending','paid','shipped','cancelled')
                AND order_total >= 0)::int ), 4)
        AS validity,

    -- Consistency: header total equals sum of line items (within a cent)
    ROUND(AVG( (ABS(order_total - line_items_total) <= 0.01)::int ), 4)
        AS consistency,

    -- Timeliness: loaded within the 2h SLA of the order timestamp
    ROUND(AVG( (loaded_at <= order_ts + INTERVAL '2 hours')::int ), 4)
        AS timeliness
FROM analytics.orders
WHERE order_ts >= now() - INTERVAL '1 day';

Step-by-step explanation.

1. Each dimension becomes a boolean-per-row expression averaged into a pass fraction. AVG((condition)::int) is the idiom: it returns the fraction of rows for which the condition holds, which is exactly a completeness or validity rate.
2. Completeness averages "required fields present," so a column that is 2% null yields 0.98. Validity averages two conditions at once (allowed status and non-negative total), so any invalid row drags the fraction below 1.0.
3. Uniqueness is the one dimension that is not a per-row average — it is a set property, count(distinct order_id) / count(*). A value of 1.0 means no duplicates; 0.98 means roughly 2% of rows are duplicate order_ids.
4. Consistency is cross-field: it checks the header order_total against line_items_total within a cent to absorb floating-point noise. This is the dimension that catches a broken join or a partial line-item load.
5. Timeliness compares loaded_at against the order timestamp plus the SLA. Scoping the whole query to the last day keeps the profile about current pipeline health rather than historical averages that hide today's regression.

Output.

rows_scanned	completeness	uniqueness	validity	consistency	timeliness
48,210	0.9992	0.9998	0.9987	0.9961	0.9725

Rule of thumb. Profile every dimension as a pass fraction in one query using AVG((condition)::int) for per-row dimensions and count(distinct key)/count(*) for uniqueness. Scope it to a recent window so the profile reflects current pipeline health, not a historical average that masks today's drop.

Worked example — a dimensioned rule engine that scores a dataset

Detailed explanation. Hard-coding the profile query per table does not scale. A rule engine stores rules as data — dimension, expression, threshold, weight — and evaluates them against any table, producing a per-dimension score and a weighted overall score. Build a small engine in Python that reads rules and returns a scorecard.

• Rules as data. Each rule names its dimension, a SQL predicate, a threshold, and a weight.
• Evaluation. Run each rule's predicate as a pass fraction; compare to threshold; weight into an overall score.

Question. Write a rule engine that evaluates a list of rules against a table and returns a dimension breakdown plus a weighted 0–100 score.

Input.

Rule	Dimension	Threshold	Weight
customer_id not null	completeness	0.999	3
unique order_id	uniqueness	1.000	2
valid status + total	validity	0.995	3
total = line items	consistency	0.990	2
loaded within SLA	timeliness	0.950	1

Code.

# A tiny data-quality rule engine: rules are data, scores are trendable.
from dataclasses import dataclass

@dataclass
class Rule:
    name: str
    dimension: str
    predicate: str   # SQL boolean expression evaluated per row
    threshold: float
    weight: int

RULES = [
    Rule("cust_id_present", "completeness",
         "customer_id IS NOT NULL AND order_total IS NOT NULL", 0.999, 3),
    Rule("order_id_unique", "uniqueness",
         "TRUE", 1.000, 2),  # uniqueness handled specially below
    Rule("status_valid", "validity",
         "status IN ('pending','paid','shipped','cancelled') AND order_total >= 0",
         0.995, 3),
    Rule("total_consistent", "consistency",
         "ABS(order_total - line_items_total) <= 0.01", 0.990, 2),
    Rule("loaded_on_time", "timeliness",
         "loaded_at <= order_ts + INTERVAL '2 hours'", 0.950, 1),
]

def score_table(cur, table: str, rules: list[Rule]) -> dict:
    results, weighted_sum, weight_total = {}, 0.0, 0
    for r in rules:
        if r.dimension == "uniqueness":
            cur.execute(
                f"SELECT COUNT(DISTINCT order_id)::numeric / COUNT(*) FROM {table}")
        else:
            cur.execute(
                f"SELECT AVG(({r.predicate})::int) FROM {table} "
                f"WHERE order_ts >= now() - INTERVAL '1 day'")
        frac = float(cur.fetchone()[0] or 0.0)
        passed = frac >= r.threshold
        results[r.name] = {"dimension": r.dimension, "value": round(frac, 4),
                           "threshold": r.threshold, "passed": passed}
        weighted_sum += r.weight * frac
        weight_total += r.weight

    overall = round(100.0 * weighted_sum / weight_total, 1)
    return {"table": table, "overall_score": overall, "rules": results}

# score = score_table(cur, "analytics.orders", RULES)
# -> {'table': 'analytics.orders', 'overall_score': 99.4, 'rules': {...}}

Step-by-step explanation.

1. A Rule is pure data: a dimension label, a SQL predicate, a threshold, and a weight. Because rules are data, they are auditable, version-controllable, and reusable across tables — the opposite of a bespoke query per dataset.
2. The engine evaluates each rule's predicate as a pass fraction via the same AVG((predicate)::int) idiom, except uniqueness, which is a set property and gets its own distinct-over-count query. Special-casing uniqueness keeps the abstraction honest.
3. Each rule records its measured value, its threshold, and whether it passed, so the scorecard shows not just the number but the why — which dimension is dragging and by how much relative to its threshold.
4. The overall score is a weighted average of the pass fractions, so completeness and validity (weight 3) move the score more than timeliness (weight 1). Weighting encodes business priority: a null customer id hurts more than a slightly late load.
5. Because the output is structured and deterministic, it is trivial to persist each run and trend the overall score and per-dimension values over time — the slope is what catches regressions before consumers file tickets.

Output.

Rule	Dimension	Value	Threshold	Passed
cust_id_present	completeness	0.9992	0.999	true
order_id_unique	uniqueness	0.9998	1.000	false
status_valid	validity	0.9987	0.995	true
total_consistent	consistency	0.9961	0.990	true
loaded_on_time	timeliness	0.9725	0.950	true

Rule of thumb. Store data-quality rules as data — dimension, predicate, threshold, weight — and evaluate them with one generic engine, not a bespoke query per table. Persist every run so you can trend the weighted score and each dimension; the trend is what turns quality from a checkbox into a monitored SLA.

Worked example — quarantine and remediation instead of silent drop

Detailed explanation. When rows fail a rule, the wrong moves are to silently drop them (hides the problem) or load them anyway (corrupts downstream). The right move is to route them to a quarantine table with the failed rule attached, then remediate or escalate. Build the quarantine split for the orders load.

• The split. Rows that pass all critical rules load to the clean table; failing rows go to quarantine with a reason.
• The remediation. Auto-fixable failures loop back; steward-review failures raise a ticket; critical breaches block the load.

Question. Write the load step that splits staging.orders into a clean insert and a quarantine insert tagged with the failed rule.

Input.

Outcome	Destination	Follow-up
passes all critical rules	analytics.orders	none
fails validity	quarantine.orders	auto-fix or steward
fails consistency	quarantine.orders	steward review

Code.

-- Quarantine table carries the row plus the reason it failed
CREATE TABLE quarantine.orders (
    LIKE staging.orders INCLUDING ALL,
    failed_rule   TEXT        NOT NULL,
    failed_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 1. Quarantine the failures, tagging each with the rule it broke
INSERT INTO quarantine.orders
SELECT s.*, v.failed_rule, now()
FROM   staging.orders s
CROSS JOIN LATERAL (
    SELECT CASE
        WHEN s.customer_id IS NULL OR s.order_total IS NULL         THEN 'completeness'
        WHEN s.status NOT IN ('pending','paid','shipped','cancelled')
             OR s.order_total < 0                                    THEN 'validity'
        WHEN ABS(s.order_total - s.line_items_total) > 0.01          THEN 'consistency'
        ELSE NULL
    END AS failed_rule
) v
WHERE v.failed_rule IS NOT NULL;

-- 2. Load only the clean rows to the certified table
INSERT INTO analytics.orders
SELECT s.*
FROM   staging.orders s
WHERE  s.customer_id IS NOT NULL
  AND  s.order_total IS NOT NULL
  AND  s.status IN ('pending','paid','shipped','cancelled')
  AND  s.order_total >= 0
  AND  ABS(s.order_total - s.line_items_total) <= 0.01;

-- 3. A remediation view: what is stuck in quarantine, by rule, oldest first
CREATE OR REPLACE VIEW quarantine.orders_worklist AS
SELECT failed_rule, COUNT(*) AS rows_stuck,
       MIN(failed_at) AS oldest, MAX(failed_at) AS newest
FROM   quarantine.orders
GROUP  BY failed_rule
ORDER  BY rows_stuck DESC;

Step-by-step explanation.

1. The quarantine table mirrors the staging schema (LIKE ... INCLUDING ALL) and adds failed_rule and failed_at, so a quarantined row carries its full payload plus the reason and time it failed — everything a steward needs to fix it.
2. The LATERAL subquery classifies each failing row with the first rule it breaks in priority order (completeness before validity before consistency). Attaching the specific rule is what makes remediation targeted instead of "some rows are bad."
3. The clean insert repeats the same predicates with the passing polarity, so exactly the rows that fail nothing land in analytics.orders. Clean and quarantine are complementary, so no row is lost and none is silently corrupted.
4. The remediation view aggregates quarantine by rule with the oldest timestamp, turning the quarantine table into a prioritised worklist: the most common failure and the longest-stuck rows surface first.
5. The pattern generalises: auto-fixable rules (whitespace, casing) can be corrected and re-inserted; steward-review rules raise a ticket from the worklist; a critical-threshold breach can abort the whole load. The routing decision is driven by the failed_rule tag.

Output.

failed_rule	rows_stuck	oldest	newest
consistency	188	2026-08-18 01:12	2026-08-18 02:44
validity	63	2026-08-18 01:30	2026-08-18 02:40
completeness	39	2026-08-18 01:05	2026-08-18 02:41

Rule of thumb. Never silently drop or silently load failing rows. Split the load into a clean insert and a quarantine insert that tags each row with the rule it broke, then work the quarantine as a prioritised list. Quarantine keeps failures inspectable; silent drops make them invisible until an auditor finds them.

Senior interview question on data quality

A senior interviewer might ask: "Our exec dashboard showed wrong revenue for two days before anyone noticed. Design a data-quality system for the orders feed that would have caught it: the dimensions you measure, how you score them, what happens to bad rows, and how you would have alerted before the dashboard was wrong. Make it reusable across our other feeds."

Solution Using a dimensioned rule set + weighted scorecard + quarantine + trend alert

-- 1. Rules stored as data, reusable across every feed
CREATE TABLE dq.rules (
    rule_id     SERIAL PRIMARY KEY,
    dataset     TEXT    NOT NULL,
    dimension   TEXT    NOT NULL,   -- completeness|uniqueness|validity|consistency|accuracy|timeliness
    predicate   TEXT    NOT NULL,   -- SQL boolean expr, or 'UNIQUE:col' for set rules
    threshold   NUMERIC NOT NULL,
    weight      INT     NOT NULL DEFAULT 1,
    is_critical BOOLEAN NOT NULL DEFAULT FALSE  -- critical breach blocks the load
);

INSERT INTO dq.rules (dataset, dimension, predicate, threshold, weight, is_critical) VALUES
 ('analytics.orders','completeness','customer_id IS NOT NULL AND order_total IS NOT NULL',0.999,3,TRUE),
 ('analytics.orders','uniqueness','UNIQUE:order_id',1.000,2,TRUE),
 ('analytics.orders','validity','status IN (''pending'',''paid'',''shipped'',''cancelled'') AND order_total>=0',0.995,3,FALSE),
 ('analytics.orders','consistency','ABS(order_total-line_items_total)<=0.01',0.990,2,TRUE),
 ('analytics.orders','timeliness','loaded_at<=order_ts+INTERVAL ''2 hours''',0.950,1,FALSE);

-- 2. Score history: one row per dataset per run, per dimension + overall
CREATE TABLE dq.score_history (
    dataset    TEXT        NOT NULL,
    run_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    dimension  TEXT        NOT NULL,
    value      NUMERIC     NOT NULL,
    overall    NUMERIC     NOT NULL
);

# 3. The reusable scorer + trend alert. Runs after every load.
import psycopg2

def run_dq(conn, dataset: str) -> dict:
    with conn.cursor() as cur:
        cur.execute("SELECT dimension, predicate, threshold, weight, is_critical "
                    "FROM dq.rules WHERE dataset=%s", (dataset,))
        rules = cur.fetchall()

        wsum, wtot, dims, blocked = 0.0, 0, {}, []
        for dim, pred, thr, wt, crit in rules:
            if pred.startswith("UNIQUE:"):
                col = pred.split(":", 1)[1]
                cur.execute(f"SELECT COUNT(DISTINCT {col})::numeric/COUNT(*) FROM {dataset}")
            else:
                cur.execute(f"SELECT COALESCE(AVG(({pred})::int),0) FROM {dataset} "
                            f"WHERE order_ts >= now() - INTERVAL '1 day'")
            val = float(cur.fetchone()[0])
            dims[dim] = val
            wsum += wt * val; wtot += wt
            if crit and val < float(thr):
                blocked.append(f"{dim}={val:.4f} < {thr}")

        overall = round(100.0 * wsum / wtot, 2)
        for dim, val in dims.items():
            cur.execute("INSERT INTO dq.score_history(dataset,dimension,value,overall) "
                        "VALUES (%s,%s,%s,%s)", (dataset, dim, val, overall))

        # Trend alert: overall dropped > 1.5 points vs the 7-run trailing mean
        cur.execute("""
            SELECT AVG(overall) FROM (
              SELECT DISTINCT run_at, overall FROM dq.score_history
              WHERE dataset=%s ORDER BY run_at DESC LIMIT 8 OFFSET 1) t
        """, (dataset,))
        prev = cur.fetchone()[0]
        conn.commit()

    alert = prev is not None and (float(prev) - overall) > 1.5
    return {"overall": overall, "dims": dims, "blocked": blocked, "trend_alert": alert}

Step-by-step trace.

Stage	Mechanism	Effect on the revenue bug
Rules as data	dq.rules per dataset	consistency + uniqueness marked critical
Score each dimension	weighted pass fractions	duplicate order_id drops uniqueness below 1.0
Critical breach	is_critical rule under threshold	load blocked before dashboard reads it
Persist + trend	dq.score_history	overall score dips vs 7-run mean
Trend alert	drop > 1.5 points	pages on-call before two days pass

Had this system fed the orders pipeline, the duplicate rows that inflated revenue would have driven the uniqueness dimension below its 1.000 threshold; because uniqueness is marked critical, the load blocks and pages on-call the first cycle — not two days later. Even a sub-critical drift shows up as the overall score sliding below its trailing mean, tripping the trend alert. The same dq.rules + scorer works for any feed by inserting rows for a new dataset.

Output:

Metric	Without DQ system	With DQ system
Detection latency	~2 days (human noticed)	first load cycle
Duplicate revenue	shipped to dashboard	blocked at load
Signal	none	uniqueness=0.94 critical breach
Alert	none	trend alert + critical block
Reuse	per-feed heroics	insert rules for new dataset

Why this works — concept by concept:

• Dimensions make quality measurable — decomposing "trustworthy" into completeness, uniqueness, validity, consistency, accuracy, and timeliness gives each concern its own number and threshold. The revenue bug was a uniqueness failure; naming the dimension is what let a rule catch it.
• Rules as data — storing rules in dq.rules makes the system reusable across every feed and auditable in version control. Adding coverage for a new table is an INSERT, not a new script.
• Critical vs non-critical — marking uniqueness and consistency critical means their breach blocks the load, while a late-arrival timeliness dip only dents the score. This encodes which failures are allowed to reach consumers and which never are.
• Trend over threshold — persisting score_history and alerting on the drop versus a trailing mean catches slow regressions a static threshold misses. The slope is the early-warning signal.
• Quarantine, not drop — pairing the scorer with a quarantine split keeps failing rows inspectable and remediable, so blocking the load does not lose data — it parks it for a steward. Cost — a rules table, a history table, and a scorer that runs O(rules) queries per load. The eliminated cost is two days of wrong executive numbers and the trust rebuild that follows.

Data Validation
Topic — data-validation
Data validation problems on quality dimensions and rules

Practice →

SQL Topic — sql SQL problems on profiling, dedupe, and null handling

Practice →

---

4. Data architecture and data modeling

data architecture is the enterprise blueprint; data modeling is the schema contract — conceptual to physical, normalized to dimensional

The mental model in one line: data architecture is the enterprise-level blueprint — how the platform's layers, storage, and integration fit together — while data modeling and design is the schema-level contract that descends from a conceptual model of entities, to a logical model of attributes and keys, to a physical model of tables and types, choosing normalized (3NF) shapes for operational sources and dimensional (star/snowflake with slowly changing dimensions) shapes for the warehouse. The interview probes whether you can move fluently up and down that ladder and pick the right normal form for the job: normalize to protect a source of truth, dimensionalize to serve analytics.

The three model levels.

• Conceptual. Entities and their relationships, no attributes, no keys — "a customer places many orders; an order contains many products." The shared business picture a non-technical owner can validate.
• Logical. Attributes, primary and foreign keys, normalization, data types in the abstract — still technology-independent. "Order has order_id (PK), customer_id (FK), order_ts, status."
• Physical. The actual DDL for a specific engine: column types, indexes, partitions, constraints, storage. "order_ts TIMESTAMPTZ, partitioned by month, indexed on customer_id."

Normalized vs dimensional.

• 3NF for the operational source. Third normal form removes redundancy so every fact lives in one place and updates cannot create contradictions. It is the right shape for a system-of-record OLTP database where write correctness dominates.
• Star schema for the warehouse. A central fact table (the measurements — order amounts) surrounded by denormalized dimension tables (the descriptors — customer, product, date). Denormalized on purpose so analytical queries are simple joins, not deep normalization chains.
• Snowflake schema. A star whose dimensions are themselves partly normalized (product to category to department). More normalized, slightly more complex joins; a judgment call.
• Grain. The single most important modeling decision: what one row of the fact table represents ("one row per order line"). State the grain before you model anything else.

Slowly changing dimensions.

• Type 1 — overwrite. No history; the dimension row is updated in place. Cheap, but you lose the past ("what was this customer's segment last year?").
• Type 2 — new row per change. History preserved by adding a new dimension row with valid_from / valid_to and a current flag. The workhorse for analytics that must reconstruct the past.
• Type 3 — previous-value column. A limited history that keeps only the prior value in an extra column. Rare; used when only one step of history matters.

Common interview probes on architecture and modeling.

• "Walk me from a conceptual to a physical model." — entities, then attributes/keys, then engine-specific DDL.
• "Normalize or star-schema this?" — 3NF for the operational source; star for the analytical warehouse; state the grain first.
• "How do you keep dimension history?" — SCD Type 2 with valid_from/valid_to and a current flag.
• "What is a conformed dimension?" — a dimension shared consistently across multiple fact tables so metrics align.

Worked example — conceptual to logical to physical for orders

Detailed explanation. The clearest way to demonstrate modeling fluency is to take one domain down all three levels. Walk orders from a conceptual sketch, to a logical model with keys and normalization, to physical Postgres DDL with types, partitioning, and indexes.

• Conceptual. Customer, Order, Product, and their relationships.
• Logical. Attributes, PKs/FKs, 3NF (line items split out).
• Physical. Postgres DDL: types, partitions, indexes, constraints.

Question. Model the orders domain at all three levels, ending in runnable physical DDL.

Input.

Level	What it fixes
Conceptual	entities + relationships
Logical	attributes + keys + normal form
Physical	engine types + partitions + indexes

Code.

-- CONCEPTUAL (entities + relationships, no attributes)
   CUSTOMER --< places >-- ORDER --< contains >-- ORDER_LINE >-- PRODUCT
   (one customer, many orders; one order, many lines; each line one product)

-- LOGICAL (attributes + keys, 3NF: line items separated from the header)
   CUSTOMER(customer_id PK, name, email, segment)
   ORDER(order_id PK, customer_id FK, order_ts, status)
   ORDER_LINE(order_id FK, line_no, product_id FK, qty, unit_price)   PK(order_id,line_no)
   PRODUCT(product_id PK, name, category)

-- PHYSICAL (Postgres DDL: types, partitioning, indexes, constraints)
CREATE TABLE public.orders (
    order_id     BIGINT       GENERATED ALWAYS AS IDENTITY,
    customer_id  BIGINT       NOT NULL REFERENCES public.customers(customer_id),
    order_ts     TIMESTAMPTZ  NOT NULL,
    status       TEXT         NOT NULL CHECK (status IN
                     ('pending','paid','shipped','cancelled')),
    PRIMARY KEY (order_id, order_ts)
) PARTITION BY RANGE (order_ts);

CREATE TABLE public.orders_2026_08 PARTITION OF public.orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE INDEX idx_orders_customer ON public.orders (customer_id);

CREATE TABLE public.order_lines (
    order_id    BIGINT       NOT NULL,
    line_no     INT          NOT NULL,
    product_id  BIGINT       NOT NULL REFERENCES public.products(product_id),
    qty         INT          NOT NULL CHECK (qty > 0),
    unit_price  NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, line_no)
);

Step-by-step explanation.

1. The conceptual level fixes only entities and relationships — customer places orders, orders contain lines, lines reference products. A business owner can validate this without knowing SQL, which is exactly its purpose: agreement before detail.
2. The logical level adds attributes and keys and applies third normal form by splitting ORDER_LINE out of ORDER. Line items are a repeating group, so 3NF gives them their own table keyed by (order_id, line_no) — no repeated columns, no update anomalies.
3. The physical level commits to Postgres specifics: TIMESTAMPTZ for the timestamp, a CHECK constraint encoding the valid status set, range partitioning by month for retention and pruning, and an index on the foreign key used in joins.
4. The primary key at the physical level becomes (order_id, order_ts) because Postgres range-partitioned tables must include the partition key in the primary key — a real engine constraint that only surfaces at the physical level, illustrating why the three levels are distinct.
5. Descending the ladder deliberately means each decision is made at the right altitude: relationships before attributes, normal form before types, logical keys before physical partitioning. Skipping levels is how models end up either over-engineered or missing a key.

Output.

Level	Artefact	Audience
Conceptual	entity-relationship sketch	business owner
Logical	attributes + keys + 3NF	data architect
Physical	Postgres DDL	data engineer / DBA

Rule of thumb. Model top-down through all three levels: fix entities and relationships conceptually, attributes and normal form logically, then engine types, partitions, and indexes physically. Each decision belongs at one altitude; making a physical decision (partitioning) at the logical level, or a logical decision (normalization) at the physical level, is how models go wrong.

Worked example — normalizing to 3NF to kill update anomalies

Detailed explanation. The classic modeling question is "why normalize?" The answer is update anomalies: a denormalized table repeats facts, and repeated facts drift out of sync. Take a denormalized orders_flat table and normalize it to 3NF, showing the anomaly the normalization removes.

• The problem. orders_flat repeats customer_email and product_category on every row.
• The anomaly. Updating a customer's email must touch every one of their order rows; miss one and the data contradicts itself.
• The fix. Split customer and product into their own tables; reference by key.

Question. Normalize orders_flat to 3NF and show the update that becomes safe.

Input.

Denormalized column	3NF home	Reason
customer_email	customers	depends on customer_id, not order
product_category	products	depends on product_id, not order
order_ts, status	orders	depends on order_id

Code.

-- BEFORE: denormalized — customer_email repeated on every order row
-- orders_flat(order_id, customer_id, customer_email, product_id,
--             product_category, order_ts, status)
-- Anomaly: changing an email touches N rows; miss one -> contradiction.

-- AFTER: 3NF — each fact lives in exactly one place
CREATE TABLE customers (
    customer_id   BIGINT PRIMARY KEY,
    customer_email TEXT  NOT NULL          -- lives once, here
);

CREATE TABLE products (
    product_id       BIGINT PRIMARY KEY,
    product_category TEXT   NOT NULL        -- lives once, here
);

CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    order_ts    TIMESTAMPTZ NOT NULL,
    status      TEXT NOT NULL
);

-- The formerly-dangerous update is now a single-row change:
UPDATE customers SET customer_email = '[e-mail]' WHERE customer_id = 42;
-- Every order for customer 42 now reflects the new email by reference —
-- no repeated column, no possibility of contradiction.

Step-by-step explanation.

1. In orders_flat, customer_email depends only on customer_id, not on order_id — a transitive dependency that violates third normal form. Because it is stored per order row, one customer's email is repeated across all their orders.
2. That repetition is the update anomaly: changing the email requires updating every order row for that customer. If the update misses a row (a filtered WHERE, a failed batch), the table now holds two different emails for one customer — a contradiction the schema permits.
3. Normalizing moves customer_email into a customers table keyed by customer_id, where it appears exactly once. product_category moves to products for the same reason. Each non-key attribute now depends on its table's whole key and nothing else — the definition of 3NF.
4. The orders table keeps only what depends on the order itself — timestamp and status — and references customer and product by foreign key. The email is obtained by join, never by duplication.
5. The formerly-dangerous update becomes a single-row change to customers. There is no repeated column to drift, so the contradiction is now structurally impossible rather than merely discouraged — which is the whole point of normalization for a source of truth.

Output.

Operation	Denormalized (orders_flat)	Normalized (3NF)
Change a customer email	UPDATE N order rows	UPDATE 1 customers row
Risk of contradiction	high (partial update)	none (single home)
Storage of email	repeated per order	stored once
Read customer email	already on row	one join

Rule of thumb. Normalize an operational source to 3NF so every fact has exactly one home and updates cannot create contradictions. The cost is a join on read; the benefit is that a partial update can never leave two truths in the table. For a system of record, that trade favors normalization.

Worked example — star schema with an SCD Type 2 dimension

Detailed explanation. The warehouse inverts the trade: analytics wants simple joins and preserved history, so you dimensionalize. Build a star schema with a fact_sales table and dimensions, and make dim_customer a Type 2 slowly changing dimension so a customer's historical segment is preserved.

• The star. fact_sales (grain: one row per order line) plus dim_customer, dim_product, dim_date.
• The SCD2. dim_customer keeps history via valid_from / valid_to and is_current, with a surrogate key.

Question. Model the star schema and show the SCD2 update when a customer's segment changes.

Input.

Table	Grain / role
fact_sales	one row per order line (measurements)
dim_customer	SCD2: surrogate key, valid_from/to, is_current
dim_product	descriptors
dim_date	calendar

Code.

-- Dimension with SCD Type 2 history
CREATE TABLE dim_customer (
    customer_sk   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- surrogate key
    customer_id   BIGINT      NOT NULL,      -- natural/business key
    name          TEXT        NOT NULL,
    segment       TEXT        NOT NULL,
    valid_from    TIMESTAMPTZ NOT NULL,
    valid_to      TIMESTAMPTZ,               -- NULL = still current
    is_current    BOOLEAN     NOT NULL DEFAULT TRUE
);

-- Fact references the SURROGATE key, freezing the customer version at sale time
CREATE TABLE fact_sales (
    sale_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_sk  BIGINT NOT NULL REFERENCES dim_customer(customer_sk),
    product_sk   BIGINT NOT NULL REFERENCES dim_product(product_sk),
    date_sk      INT    NOT NULL REFERENCES dim_date(date_sk),
    qty          INT    NOT NULL,
    amount       NUMERIC(12,2) NOT NULL
);

-- SCD2 update: customer 42 moves from 'smb' to 'enterprise'
-- 1. Close the current row
UPDATE dim_customer
SET    valid_to = now(), is_current = FALSE
WHERE  customer_id = 42 AND is_current;

-- 2. Insert the new version as a NEW surrogate-key row
INSERT INTO dim_customer (customer_id, name, segment, valid_from, valid_to, is_current)
VALUES (42, 'Acme Co', 'enterprise', now(), NULL, TRUE);

Step-by-step explanation.

1. dim_customer has a surrogate key (customer_sk) distinct from the natural key (customer_id). The surrogate key is what makes SCD2 possible: multiple rows can share one customer_id while each has a unique customer_sk for its time slice.
2. fact_sales references customer_sk, not customer_id. This freezes the customer's attributes as they were at the time of sale — a sale made while customer 42 was smb forever points to the smb version, even after they become enterprise.
3. When the segment changes, step one closes the current row by stamping valid_to and clearing is_current. The old row is preserved intact, so historical facts still resolve to the correct past segment.
4. Step two inserts a brand-new row with a fresh surrogate key, the new segment, valid_from = now(), and is_current = TRUE. New sales join to this row; old sales stay bound to the closed one.
5. The result is that "revenue by segment last quarter" and "revenue by segment this quarter" both compute correctly, because each fact is anchored to the customer version in force when it happened. That preserved history is precisely what SCD Type 1 (overwrite) would have destroyed.

Output.

customer_sk	customer_id	segment	valid_from	valid_to	is_current
900	42	smb	2025-01-01	2026-08-18	false
1201	42	enterprise	2026-08-18	(null)	true

Rule of thumb. Dimensionalize the warehouse: a fact table at a stated grain surrounded by dimensions, with facts referencing dimension surrogate keys. Use SCD Type 2 (valid_from/valid_to + is_current + surrogate key) for any dimension whose history you must reconstruct. The surrogate key is what lets a fact remember the version of the world in force when it happened.

Senior interview question on data architecture and modeling

A senior interviewer might ask: "We have an operational Postgres for orders and a Snowflake warehouse for analytics. Design the model for both: the normalized operational schema and the dimensional warehouse schema, explain why each side uses a different shape, and show how a change to a customer's segment flows through so last year's reports do not silently change."

Solution Using a normalized OLTP source feeding a dimensional star with SCD2 and a conformed dimension

-- OPERATIONAL SIDE (Postgres): 3NF — write-correct, one home per fact
CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL,
    segment     TEXT NOT NULL          -- changes over time; source keeps only current
);
CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    order_ts    TIMESTAMPTZ NOT NULL,
    status      TEXT NOT NULL
);
CREATE TABLE order_lines (
    order_id BIGINT NOT NULL REFERENCES orders(order_id),
    line_no  INT NOT NULL,
    product_id BIGINT NOT NULL,
    qty INT NOT NULL, unit_price NUMERIC(12,2) NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

-- ANALYTICAL SIDE (Snowflake): dimensional star, conformed dim_customer as SCD2
CREATE TABLE dim_customer (
    customer_sk BIGINT AUTOINCREMENT PRIMARY KEY,   -- surrogate
    customer_id BIGINT NOT NULL,                     -- natural
    name STRING, segment STRING,
    valid_from TIMESTAMP_NTZ NOT NULL,
    valid_to   TIMESTAMP_NTZ,
    is_current BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE dim_date    (date_sk INT PRIMARY KEY, d DATE, month INT, year INT);
CREATE TABLE dim_product (product_sk BIGINT AUTOINCREMENT PRIMARY KEY, product_id BIGINT, name STRING, category STRING);

-- fact at grain: one row per order line, referencing surrogate keys
CREATE TABLE fact_sales (
    sale_sk     BIGINT AUTOINCREMENT PRIMARY KEY,
    customer_sk BIGINT NOT NULL REFERENCES dim_customer(customer_sk),
    product_sk  BIGINT NOT NULL REFERENCES dim_product(product_sk),
    date_sk     INT    NOT NULL REFERENCES dim_date(date_sk),
    qty INT NOT NULL, amount NUMBER(12,2) NOT NULL
);

-- SCD2 MERGE: when the source segment changes, close old + open new
MERGE INTO dim_customer t
USING staging_customers s
   ON t.customer_id = s.customer_id AND t.is_current
WHEN MATCHED AND t.segment <> s.segment THEN
    UPDATE SET t.valid_to = CURRENT_TIMESTAMP(), t.is_current = FALSE;
-- (a second INSERT inserts the new current version for changed + new customers)
INSERT INTO dim_customer (customer_id, name, segment, valid_from, is_current)
SELECT s.customer_id, s.name, s.segment, CURRENT_TIMESTAMP(), TRUE
FROM   staging_customers s
LEFT JOIN dim_customer t ON t.customer_id = s.customer_id AND t.is_current
WHERE  t.customer_sk IS NULL OR t.segment <> s.segment;

Step-by-step trace.

Concern	Operational (3NF)	Analytical (star)
Optimised for	write correctness	read simplicity + history
Customer segment	one current value	full history via SCD2
Join depth	normalized chains	one hop fact → dim
Fact grain	(rows are transactions)	one row per order line
Segment change	UPDATE in place	close old row, open new SK

After the change, the operational customers.segment is overwritten to enterprise (the source only needs the current truth). On the analytical side, the SCD2 MERGE closes the old smb row and opens a new enterprise row with a fresh surrogate key. Every fact_sales row booked while the customer was smb still points to the closed surrogate key, so last year's "revenue by segment" report is unchanged, while new sales roll into enterprise. The dim_customer is conformed — the same dimension serves fact_sales and any other fact — so segment metrics align across the warehouse.

Output:

Report	Before change	After change
Last year revenue: smb	$1.2M	$1.2M (unchanged)
This quarter revenue: smb	$0 (moved)	reflects only still-smb
This quarter revenue: enterprise	—	includes customer 42
Operational customers.segment	smb	enterprise (overwritten)

Why this works — concept by concept:

• 3NF for the source — the operational schema normalizes so each fact has one home and writes stay correct; the source only needs the current segment, so overwriting it is fine there.
• Dimensional star for analytics — the warehouse denormalizes into a fact plus dimensions so analytical queries are shallow joins at a stated grain, not deep normalization walks.
• SCD Type 2 preserves the past — closing the old dimension row and opening a new surrogate-key row means historical facts stay anchored to the world as it was, so last year's report cannot silently change. This is the exact fear the question raises.
• Conformed dimension — because dim_customer is shared consistently across facts, segment-based metrics agree everywhere, which is what makes a warehouse's numbers reconcilable across marts.
• Cost — two schemas and an SCD2 MERGE per load. Storage grows with dimension history, and reads pay one extra join. The eliminated cost is the far worse one: reports that mutate retroactively every time a customer attribute changes, destroying trust in every historical number.

Database
Topic — database
Database problems on modeling, normalization, and star schemas

Practice →

Design Topic — design Design problems on warehouse architecture and SCDs

Practice →

---

5. Metadata management — the area that ties the wheel together

metadata management is data about data — technical, business, and operational — and lineage is the query interviewers make you build

The mental model in one line: metadata management is the practice of capturing and curating data about data in three flavours — technical (types, keys, schema), business (glossary terms, ownership, definitions), and operational (load times, row counts, freshness) — publishing it in a catalog, and wiring lineage so any column can be traced to its sources, which is why metadata is the connective tissue that lets governance, quality, and modeling actually function. The interview tell is whether you name the three metadata types and can build column-level lineage on demand, because lineage is the single most-requested hands-on metadata exercise.

The three types of metadata.

• Technical metadata. The mechanical facts: column names, data types, primary/foreign keys, nullability, table sizes. Harvested automatically from the engine's information_schema or catalog. This is the cheapest metadata to collect and the foundation for everything else.
• Business metadata. The human meaning: what "active customer" means, which column is the certified revenue figure, who owns the term, what the acceptable values are. Curated by stewards; lives in a business glossary.
• Operational metadata. The runtime facts: when the table last loaded, how many rows arrived, how long the job ran, the current freshness. Emitted by the pipeline itself and the signal behind timeliness and reliability SLAs.

Active vs passive metadata.

• Passive metadata. A static snapshot — a catalog page you read. Useful but inert; it does not act.
• Active metadata. Metadata that drives behaviour — freshness metadata triggering an alert, classification metadata driving a masking policy, lineage metadata powering impact analysis. The 2020s shift is from cataloguing metadata to acting on it.

The catalog and lineage.

• The catalog. The searchable index of datasets that joins technical, business, and operational metadata into one place a consumer can browse. "What is this table, what does it mean, is it fresh, who owns it."
• Lineage. The graph of where data comes from and goes. Table-level lineage says "fact_sales derives from orders and customers"; column-level lineage says "revenue_usd derives from orders.total, orders.tax, and fx.rate." Column-level is what powers precise impact analysis.
• Impact analysis. The killer app of lineage: before you change or drop a column, query the lineage graph to find every downstream consumer. Without it, every schema change is a gamble.

Common interview probes on metadata management.

• "What are the types of metadata?" — required answer: technical, business, operational.
• "What is the difference between active and passive metadata?" — passive is a snapshot you read; active drives behaviour (alerts, masking, impact analysis).
• "Build column-level lineage for this transformation." — parse sources → target column edges; store as a graph.
• "How do you assess the blast radius of dropping a column?" — traverse the lineage graph downstream from that column.

Worked example — harvesting technical metadata from information_schema

Detailed explanation. Technical metadata is free — every SQL engine exposes it through information_schema. Build a harvester that snapshots columns, types, nullability, and keys into a metadata store, so the catalog has a queryable technical layer without manual entry.

• The source. information_schema.columns and table_constraints.
• The sink. A catalog.columns table snapshotting the technical facts with a harvest timestamp.

Question. Write the harvest query that captures technical metadata for the analytics schema into a catalog table.

Input.

Captured field	From
table, column, ordinal	information_schema.columns
data_type, is_nullable	information_schema.columns
is_primary_key	join to key_column_usage

Code.

CREATE TABLE catalog.columns (
    table_name    TEXT NOT NULL,
    column_name   TEXT NOT NULL,
    ordinal       INT  NOT NULL,
    data_type     TEXT NOT NULL,
    is_nullable   BOOLEAN NOT NULL,
    is_primary_key BOOLEAN NOT NULL DEFAULT FALSE,
    harvested_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (table_name, column_name, harvested_at)
);

-- Harvest technical metadata for the analytics schema
INSERT INTO catalog.columns
    (table_name, column_name, ordinal, data_type, is_nullable, is_primary_key)
SELECT
    c.table_name,
    c.column_name,
    c.ordinal_position,
    c.data_type,
    (c.is_nullable = 'YES')                                   AS is_nullable,
    (pk.column_name IS NOT NULL)                             AS is_primary_key
FROM   information_schema.columns c
LEFT JOIN (
    SELECT kcu.table_name, kcu.column_name
    FROM   information_schema.table_constraints tc
    JOIN   information_schema.key_column_usage kcu
           ON kcu.constraint_name = tc.constraint_name
    WHERE  tc.constraint_type = 'PRIMARY KEY'
) pk ON pk.table_name = c.table_name AND pk.column_name = c.column_name
WHERE  c.table_schema = 'analytics';

Step-by-step explanation.

1. The catalog.columns sink includes harvested_at in the primary key, so each harvest is a snapshot rather than an overwrite. Comparing snapshots over time is how you detect schema drift — a new column, a type change, a dropped key.
2. The core SELECT reads information_schema.columns for the mechanical facts: name, ordinal position, data type, and nullability. These are engine-maintained and always current, so the harvest never goes stale relative to the real schema.
3. The LEFT JOIN to a primary-key subquery flags which columns participate in the primary key by joining table_constraints to key_column_usage on the constraint name. A column absent from that set gets is_primary_key = FALSE.
4. Nullability is normalised from the 'YES'/'NO' string that information_schema returns into a real boolean, so downstream consumers get a clean type rather than a string comparison.
5. Scoping to table_schema = 'analytics' keeps the harvest to the certified layer; running the same statement per schema (or removing the filter) generalises it. The whole thing is zero-manual-entry technical metadata, refreshed by re-running on a schedule.

Output.

table_name	column_name	data_type	is_nullable	is_primary_key
customers	customer_id	bigint	false	true
customers	email	text	false	false
orders	order_id	bigint	false	true
orders	customer_id	bigint	false	false

Rule of thumb. Harvest technical metadata from information_schema on a schedule and snapshot it with a timestamp, never overwrite. The snapshot is free (the engine already maintains it), and diffing snapshots is how you detect schema drift before a downstream job breaks on it.

Worked example — building column-level lineage

Detailed explanation. The most-requested metadata exercise is column-level lineage: given a transformation, record which source columns feed which target column, then answer "what feeds revenue_usd?" and "what breaks if I drop fx.rate?" Build a lineage store and a small resolver.

• The edges. (source_table, source_column) -> (target_table, target_column) per transformation.
• The queries. Upstream (what feeds a column) and downstream (blast radius of a column).

Question. Model a lineage graph as edges and write the upstream and downstream traversals for a derived revenue_usd column.

Input.

Target column	Source columns
marts.sales.revenue_usd	orders.total, orders.tax, fx.rate
marts.sales.customer	orders.customer_id

Code.

# Column-level lineage as a directed graph of edges.
from collections import defaultdict, deque

# edge = (src_table, src_col) -> (tgt_table, tgt_col)
EDGES = [
    (("orders", "total"),      ("marts.sales", "revenue_usd")),
    (("orders", "tax"),        ("marts.sales", "revenue_usd")),
    (("fx", "rate"),           ("marts.sales", "revenue_usd")),
    (("orders", "customer_id"),("marts.sales", "customer")),
    (("marts.sales", "revenue_usd"), ("dash.kpi", "gross_revenue")),
]

upstream   = defaultdict(list)   # node -> its direct sources
downstream = defaultdict(list)   # node -> its direct consumers
for src, tgt in EDGES:
    upstream[tgt].append(src)
    downstream[src].append(tgt)

def sources_of(node) -> set:
    """All upstream columns that feed `node` (transitive)."""
    seen, q = set(), deque(upstream[node])
    while q:
        n = q.popleft()
        if n not in seen:
            seen.add(n)
            q.extend(upstream[n])
    return seen

def blast_radius(node) -> set:
    """All downstream columns affected if `node` changes (transitive)."""
    seen, q = set(), deque(downstream[node])
    while q:
        n = q.popleft()
        if n not in seen:
            seen.add(n)
            q.extend(downstream[n])
    return seen

print(sources_of(("marts.sales", "revenue_usd")))
# {('orders','total'), ('orders','tax'), ('fx','rate')}

print(blast_radius(("fx", "rate")))
# {('marts.sales','revenue_usd'), ('dash.kpi','gross_revenue')}

Step-by-step explanation.

1. Lineage is modelled as directed edges from a source column to a target column, one edge per contributing column of a transformation. Three columns feeding revenue_usd produce three edges, which is what makes the lineage column-level rather than table-level.
2. The edges are indexed twice — upstream maps a node to its direct sources, downstream maps a node to its direct consumers — so both traversal directions are O(1) per hop. Building both indexes once amortises every later query.
3. sources_of answers "what feeds this column?" with a breadth-first walk up the upstream index, following edges transitively. For revenue_usd it returns the three raw columns, even across multiple transformation hops, which is exactly what an auditor asking "where does this number come from" needs.
4. blast_radius answers the impact-analysis question with a breadth-first walk down the downstream index. Starting from fx.rate, it finds revenue_usd and, transitively, the dash.kpi.gross_revenue that depends on it — the full set of things that break if you drop or change the rate column.
5. The seen set guards against cycles and repeated visits, so the traversal terminates even if lineage has diamonds (a column feeding two paths that reconverge). This is the same graph traversal that powers a commercial catalog's impact view.

Output.

Query	Column	Result
sources_of	marts.sales.revenue_usd	orders.total, orders.tax, fx.rate
blast_radius	fx.rate	marts.sales.revenue_usd, dash.kpi.gross_revenue
blast_radius	orders.customer_id	marts.sales.customer

Rule of thumb. Store lineage as column-level directed edges and index both directions. Upstream traversal answers "where did this number come from" for audits; downstream traversal answers "what breaks if I change this" for impact analysis. Column-level edges are the difference between "orders feeds sales" and the precise blast radius of one column.

Worked example — joining the business glossary to the catalog

Detailed explanation. Technical metadata alone does not tell a consumer what a column means. The business glossary supplies that — curated definitions, ownership, and certification — and joining it to the technical catalog produces the unified view a data consumer actually browses. Build the glossary and the join.

• The glossary. Business terms with definitions, owners, and the physical column each maps to.
• The join. Glossary terms joined to technical metadata into one catalog view.

Question. Create a business glossary, map terms to columns, and produce a catalog view that unifies business and technical metadata.

Input.

Term	Definition	Maps to column
Gross Revenue	order total incl. tax, in USD	marts.sales.revenue_usd
Active Customer	ordered in last 90 days	marts.sales.customer

Code.

-- Business metadata: the curated glossary
CREATE TABLE catalog.glossary (
    term         TEXT PRIMARY KEY,
    definition   TEXT NOT NULL,
    owner_email  TEXT NOT NULL,
    table_name   TEXT NOT NULL,
    column_name  TEXT NOT NULL,
    certified    BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO catalog.glossary VALUES
 ('Gross Revenue', 'Order total including tax, converted to USD',
  '[e-mail]', 'marts.sales', 'revenue_usd', TRUE),
 ('Active Customer', 'Customer with >= 1 order in the last 90 days',
  '[e-mail]', 'marts.sales', 'customer', TRUE);

-- Unified catalog view: technical + business + operational in one place
CREATE OR REPLACE VIEW catalog.unified AS
SELECT
    c.table_name,
    c.column_name,
    c.data_type,                         -- technical
    c.is_nullable,                       -- technical
    g.term,                              -- business
    g.definition,                        -- business
    g.owner_email,                       -- business
    g.certified,                         -- business
    o.last_loaded_at,                    -- operational
    o.row_count                          -- operational
FROM   catalog.columns  c
LEFT JOIN catalog.glossary g
       ON g.table_name = c.table_name AND g.column_name = c.column_name
LEFT JOIN catalog.load_stats o
       ON o.table_name = c.table_name
WHERE  c.harvested_at = (SELECT MAX(harvested_at) FROM catalog.columns);

Step-by-step explanation.

1. The glossary table is business metadata: each row pairs a human term and definition with the physical column it maps to, plus the steward who owns the term and whether it is certified. This is the layer stewards curate by hand — the meaning technical metadata cannot supply.
2. The unified view left-joins the three metadata types on the column key: technical from catalog.columns, business from catalog.glossary, operational from catalog.load_stats. One row per column now carries type, meaning, owner, and freshness together.
3. The LEFT JOIN (not inner) means columns without a glossary term still appear — with null business fields — so the catalog shows the full schema and highlights which columns still lack a definition. That gap is a stewardship worklist.
4. Filtering catalog.columns to the latest harvested_at uses only the most recent technical snapshot, so the unified view reflects the current schema while the underlying snapshots preserve history for drift detection.
5. The result is the view a consumer browses to answer "what is this column, what does it mean, who owns it, and is it fresh?" in one place — which is exactly the catalog experience metadata management exists to deliver, and the operational fields make it active rather than a static page.

Output.

column_name	data_type	term	definition	owner_email	last_loaded_at
revenue_usd	numeric	Gross Revenue	Order total incl. tax, USD	[e-mail]	2026-08-18 02:00
customer	bigint	Active Customer	>= 1 order in last 90 days	[e-mail]	2026-08-18 02:00

Rule of thumb. Join the curated business glossary to harvested technical metadata and pipeline-emitted operational metadata into one catalog view keyed by column. Use a left join so undefined columns surface as a stewardship gap. The unified view — meaning plus type plus freshness — is what turns a schema dump into a catalog a consumer trusts.

Senior interview question on metadata management

A senior interviewer might ask: "A team wants to drop a column from the orders table. How does your metadata system tell them what will break, what the column means, who owns it, and whether anything downstream is certified? Design the metadata layer — the three types, the catalog, and the lineage — and show the exact impact-analysis query you would run before approving the change."

Solution Using a harvested catalog + business glossary + column lineage for impact analysis

-- Technical (harvested), business (glossary), operational (load stats) — unified
-- (catalog.columns, catalog.glossary, catalog.load_stats as built above)

-- Lineage edges, stored as data so impact analysis is a query
CREATE TABLE catalog.lineage (
    src_table  TEXT NOT NULL,
    src_column TEXT NOT NULL,
    tgt_table  TEXT NOT NULL,
    tgt_column TEXT NOT NULL,
    PRIMARY KEY (src_table, src_column, tgt_table, tgt_column)
);

INSERT INTO catalog.lineage VALUES
 ('orders','total',      'marts.sales','revenue_usd'),
 ('orders','tax',        'marts.sales','revenue_usd'),
 ('orders','customer_id','marts.sales','customer'),
 ('marts.sales','revenue_usd','dash.kpi','gross_revenue');

-- Impact analysis: everything downstream of orders.total (recursive)
WITH RECURSIVE impact AS (
    SELECT tgt_table, tgt_column, 1 AS depth
    FROM   catalog.lineage
    WHERE  src_table = 'orders' AND src_column = 'total'
  UNION ALL
    SELECT l.tgt_table, l.tgt_column, i.depth + 1
    FROM   catalog.lineage l
    JOIN   impact i
           ON l.src_table = i.tgt_table AND l.src_column = i.tgt_column
)
SELECT DISTINCT i.tgt_table, i.tgt_column, i.depth,
       g.term, g.owner_email, g.certified
FROM   impact i
LEFT JOIN catalog.glossary g
       ON g.table_name = i.tgt_table AND g.column_name = i.tgt_column
ORDER  BY i.depth;

# The approval gate: block the drop if anything CERTIFIED is downstream.
def can_drop(cur, table: str, column: str) -> tuple[bool, list]:
    cur.execute("""
        WITH RECURSIVE impact AS (
            SELECT tgt_table, tgt_column FROM catalog.lineage
            WHERE src_table=%s AND src_column=%s
          UNION ALL
            SELECT l.tgt_table, l.tgt_column FROM catalog.lineage l
            JOIN impact i ON l.src_table=i.tgt_table AND l.src_column=i.tgt_column)
        SELECT DISTINCT i.tgt_table, i.tgt_column, g.certified, g.owner_email
        FROM impact i
        LEFT JOIN catalog.glossary g
          ON g.table_name=i.tgt_table AND g.column_name=i.tgt_column
    """, (table, column))
    downstream = cur.fetchall()
    certified = [d for d in downstream if d[2]]          # certified consumers
    return (len(certified) == 0, downstream)

# ok, affected = can_drop(cur, "orders", "total")
# ok == False  -> revenue_usd + gross_revenue are certified; block + notify owners

Step-by-step trace.

Question the team asks	Metadata answering it	Query / artefact
What breaks if we drop orders.total?	lineage (downstream)	recursive impact CTE
What does the affected column mean?	business (glossary)	join to catalog.glossary
Who owns what breaks?	business (owner_email)	glossary owner column
Is anything downstream certified?	business (certified)	approval gate check
Is the affected mart fresh / used?	operational (load stats)	catalog.load_stats

Running the impact CTE from orders.total returns marts.sales.revenue_usd at depth 1 and dash.kpi.gross_revenue at depth 2, each joined to its glossary term, owner, and certification flag. Because both are certified, the approval gate returns False — the drop is blocked and the two stewards are notified with the exact columns and definitions at risk. The team learns the blast radius, the meaning, the owners, and the certification status before the change, not after a dashboard breaks.

Output:

Downstream column	Depth	Term	Owner	Certified
marts.sales.revenue_usd	1	Gross Revenue	[e-mail]	true
dash.kpi.gross_revenue	2	(KPI)	[e-mail]	true
Approval gate	—	—	—	BLOCKED

Why this works — concept by concept:

• Three metadata types together — technical answers "what type is it," business answers "what does it mean and who owns it," operational answers "is it fresh and used." Only all three together let the team make an informed decision about the drop.
• Lineage as queryable data — storing edges in a table turns impact analysis into a recursive SQL walk instead of tribal knowledge. The recursive CTE finds the full downstream set at any depth, which is the precise blast radius.
• Column-level, not table-level — because edges are per column, the analysis pinpoints revenue_usd and gross_revenue rather than vaguely warning that "something in sales uses orders." Precision is what makes the warning actionable.
• Active metadata gate — joining the certification flag turns passive lineage into an active control: a drop that would break a certified consumer is blocked and routed to owners. Metadata that acts, not metadata you read.
• Cost — a lineage table, a recursive query, and a join to the glossary. The traversal is O(edges) downstream of the target. The eliminated cost is the outage-and-apology cycle of dropping a column that silently fed a certified executive KPI.

SQL
Topic — sql
SQL problems on recursive queries and information_schema

Practice →

Data Processing
Topic — data-processing
Data processing problems on lineage and graph traversal

Practice →

---

Cheat sheet — DAMA-DMBOK knowledge-area recipes

• The wheel, one line each. Governance is the hub (policy + decision rights); the rim is Architecture, Modeling, Storage & Operations, Security, Integration, Document & Content, Reference & Master Data, Warehousing & BI, Metadata, Quality. For a data engineer, the five that convert to interview points are Governance, Quality, Metadata, Architecture, and Modeling — the rest are context.
• Answer shape for any area. Name the area → state its operating model → give the metric → name the artefact. "That's a data-quality question; I score six dimensions against thresholds; overall is 99.4%; the artefact is a scorecard plus a quarantine table." Four beats convert a folk answer into a framework answer.
• Governance role model. Owner = accountable (business domain leader, sets policy, signs off). Steward = responsible (SME, curates definitions and quality rules, maintains the glossary). Custodian = executes (data platform / IT, stores, secures, enforces). Council ratifies enterprise standards. Encode it as a policy_registry(dataset PK, owner, steward, custodian, classification, contains_pii, retention_months, certified).
• Governance enforcement + metric. Derive masking policies and role grants from classification / contains_pii; prove it with a guardrail view that returns PII columns lacking a masking policy (empty = compliant). Trend three coverage percentages: ownership, classification, PII enforcement. Audit new tables in CI so nothing ships ungoverned.
• The six data-quality dimensions. Completeness (required values present), Uniqueness (entities once), Validity (format/range/domain), Consistency (related values agree, cross-field and cross-table), Accuracy (matches real-world truth — needs a trusted reference), Timeliness (fresh enough vs SLA). Score each as a pass fraction: AVG((predicate)::int); uniqueness is count(distinct key)/count(*).
• Data-quality operating recipe. Store rules as data (dimension, predicate, threshold, weight, is_critical); roll up a weighted 0–100 score; persist score_history and alert on the drop vs a trailing mean, not just a static threshold; quarantine failing rows tagged with the failed rule (never silently drop or load); mark uniqueness/consistency critical so their breach blocks the load.
• Model-level ladder. Conceptual (entities + relationships, business-validatable) → Logical (attributes + keys + normal form, technology-independent) → Physical (engine types, partitions, indexes, constraints). Make each decision at its altitude; do not partition at the logical level or normalize at the physical level.
• Normalized vs dimensional. 3NF for the operational source (one home per fact, no update anomalies, write-correct). Star schema for the warehouse (fact at a stated grain + denormalized dimensions, shallow joins). State the grain first. Snowflake = partly normalized dimensions when a dimension hierarchy justifies it.
• SCD Type 2 template. Surrogate key + natural key + valid_from / valid_to + is_current. Facts reference the surrogate key so they freeze the dimension version at event time. On change: close the current row (valid_to = now(), is_current = false), insert a new current row. This is what stops last year's report from silently changing. Conformed dimension = shared consistently across facts.
• Three metadata types. Technical (types, keys, schema — harvest from information_schema, snapshot with a timestamp, diff for drift). Business (glossary term, definition, owner, certified — curated by stewards). Operational (last load, row count, freshness — emitted by the pipeline). Unify them in one catalog view keyed by column with a left join so undefined columns surface as a stewardship gap.
• Lineage + impact analysis. Store column-level edges (src_table, src_column) -> (tgt_table, tgt_column). Upstream traversal answers "where did this number come from" (audit); downstream traversal answers "what breaks if I change this" (impact). Use a recursive CTE (or a BFS over both indexes) and gate schema changes on whether any downstream consumer is certified.
• Active vs passive metadata. Passive = a snapshot you read. Active = metadata that drives behaviour: freshness triggering an alert, classification driving a masking policy, lineage powering an approval gate. The senior framing is "we act on metadata," not "we catalog it."
• Interview vocabulary matrix. Governance → owner/steward/custodian, RACI, policy, classification. Quality → the six dimensions, threshold, quarantine, remediation. Metadata → technical/business/operational, catalog, lineage, glossary, active. Architecture → conceptual/logical/physical, layers, storage. Modeling → 3NF, star/snowflake, grain, SCD, conformed dimension. Say the noun; the noun is the score.

Frequently asked questions

What is DAMA-DMBOK in one sentence?

DAMA-DMBOK is the Data Management Body of Knowledge, a reference framework published by DAMA International that organises the entire discipline of managing data into eleven interdependent knowledge areas — Data Governance, Data Architecture, Data Modeling and Design, Data Storage and Operations, Data Security, Data Integration and Interoperability, Document and Content Management, Reference and Master Data, Data Warehousing and Business Intelligence, Metadata, and Data Quality — drawn as a wheel with governance at the centre because every other area inherits its policies and decision rights. For a data engineer it is best treated not as a certification to recite but as a shared vocabulary map: it lets you locate any interview question inside an area and answer with that area's operating model and metric. The areas that convert most directly to data-engineering interviews are governance, data quality, metadata, data architecture, and data modeling.

What is the DAMA wheel?

The DAMA wheel is the diagram DMBOK uses to depict its knowledge areas: Data Governance sits at the hub, and the other ten areas ring the rim. It is drawn as a wheel rather than a stack to make two points — no area is "first," and governance binds all the others together because quality thresholds, glossary curation, retention, and access are all governance decisions the rim areas inherit. In practice an interviewer uses the wheel as a checklist of vocabulary they can reach for, so knowing which area a question lives in ("that's a metadata question," "that's a data-quality question") is the fastest way to signal data-management literacy. You do not need to memorise all eleven areas in depth; you need to recognise the wheel's shape and know the five areas a data engineer actually works in.

Which DMBOK knowledge areas matter most for data engineers?

Five areas convert directly to daily engineering work and get probed hardest: Data Governance (who owns a dataset, decision rights, enforced policy), Data Quality (the six measurable dimensions and how you score them), Metadata (technical/business/operational metadata, the catalog, and lineage), Data Architecture (how platform layers and storage fit together), and Data Modeling and Design (conceptual-to-physical, normalization versus dimensional, slowly changing dimensions). A second tier shows up sometimes — Data Integration and Interoperability (ETL/ELT, CDC), Reference and Master Data (the golden-record question), Data Security (masking, PII), and Data Storage and Operations (partitioning, retention). Two areas — Document and Content Management, and the reporting specifics of Data Warehousing and BI — lean toward content-manager and BI-analyst roles and rarely appear in a pipeline-engineer interview. Spend your prep on the first five.

Data governance versus data management — what is the difference?

Data management is the whole discipline — everything the DMBOK covers, all eleven knowledge areas — the full set of activities that keep data valuable, usable, and safe across its lifecycle. Data governance is one area within that discipline, but a special one: it is the layer that assigns accountability and decision rights and sets the policies and standards the other ten areas operate under. Put simply, management is the doing; governance is the deciding — who owns what, who is allowed to change it, what the rules are, and how compliance is proven. The interview trap is using the two words interchangeably; the senior move is to say "governance is the accountability-and-policy hub, and data management is the broader practice the hub governs," then name the governance role model (owner, steward, custodian) to show you understand where accountability sits.

What are the dimensions of data quality?

The DMBOK's core data quality dimensions are completeness (are required values present?), uniqueness (is each entity represented once, no duplicates?), validity (do values conform to their format, range, or allowed domain?), consistency (do related values agree, both across fields in a row and across tables?), accuracy (does the value match the real-world truth?), and timeliness (is the data fresh enough for its purpose versus an SLA?). The engineering discipline is to make each dimension a measured number rather than an assertion: score completeness and validity as pass fractions with AVG((predicate)::int), uniqueness as count(distinct key)/count(*), and timeliness as the fraction loaded within the SLA. Accuracy is the hardest because it requires a trusted reference to compare against — a golden source or a reconciliation against the system of record. Roll the dimensions into a weighted score, trend it, and quarantine rows that fail rather than silently dropping or loading them.

What is metadata management and why do interviewers ask about it?

metadata management is the practice of capturing and curating data about data and publishing it so people and systems can find, understand, and trust datasets. It spans three types: technical metadata (column types, keys, schema — harvested automatically from information_schema), business metadata (glossary terms, definitions, ownership — curated by stewards), and operational metadata (load times, row counts, freshness — emitted by the pipeline). Interviewers ask about it because metadata is the connective tissue that makes the rest of the wheel function — governance needs ownership metadata, quality needs freshness metadata, modeling needs schema metadata — and because the single most common hands-on ask is to build column-level lineage: the graph of which source columns feed which target column. Lineage powers impact analysis (what breaks if I drop this column?) and audit (where did this number come from?), and the modern framing is active metadata — metadata that drives alerts, masking, and approval gates rather than sitting in a catalog page nobody reads.

Practice on PipeCode

• Drill the SQL practice library → for the profiling, dedupe, recursive-lineage, and information_schema problems the metadata and quality areas lean on.
• Harden your checks on the data validation practice library → for the six-dimension scoring, threshold, and quarantine patterns that turn data quality into a gate.
• Rehearse schema work on the database practice library → for normalization, star schemas, and slowly changing dimensions.
• Stress-test the platform-level judgment on the design practice library → for governance, access control, and warehouse-architecture scenarios.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the DAMA-DMBOK knowledge areas against real graded inputs.

Turn DMBOK vocabulary into interview muscle memory

Docs define the knowledge areas. PipeCode drills make you fluent in them — when a question is really about governance decision rights, which data-quality dimension a bug lives in, how to model a slowly changing dimension, and how to trace column-level lineage before you drop a column. Pipecode.ai is Leetcode for Data Engineering — area-by-area practice tuned to the trade-offs senior data engineers actually defend.

Practice SQL problems →
Practice data validation problems →