Exercises: Data Governance and Cataloging

Solutions and grading notes are in the instructor companion. Exercises marked 🧪 are hands-on and use code/catalog_audit.py from this chapter.

Warm-Up

Exercise 30.1 — The decision test

Difficulty: ★☆☆ · Time: 15 minutes

§30.1 offers one test for whether a governance artifact is worth maintaining: what decision does it change?

Apply it to each of the following, and for each write either the decision it changes or the words "no decision — delete it."

  1. A field recording each table's row count as of the day the catalog was populated.
  2. A field recording each table's grain.
  3. A field recording which team owns each table.
  4. A field recording the date the owner was last verified.
  5. A "data dictionary" spreadsheet listing every column in the warehouse with its data type.
  6. A tag marking which tables contain data classified confidential.
  7. A field recording the original author of each model.

Deliverable: seven lines. For any you mark "no decision," say what would have to change for it to earn its place.

Exercise 30.2 — Four status tiers

Difficulty: ★☆☆ · Time: 15 minutes

§30.2 defines four status tiers: certified, standard, internal, deprecated.

Assign a tier to each, and give a one-sentence reason:

  1. gold.daily_revenue — feeds the 6am report, owned by #data-platform.
  2. silver.stg_orders — a staging model, referenced by nine downstream models, referenced by no dashboard.
  3. analytics.customer_ltv_v2 — built by a data scientist eight months ago, queried by two people.
  4. gold.dim_customer_deprecated — replaced by gold.dim_customer, still queried by one dashboard.
  5. A table created by the Chapter 21 Spark migration and last written to fourteen months ago.

Then: §30.2 reports that assigning internal by directory took search from ~4,000 results to ~600. What is the risk of assigning a tier by directory rather than by judgment, and what would you do about it?

Exercise 30.3 — Read the audit output

Difficulty: ★☆☆ · Time: 20 minutes · 🧪

Run the chapter's tool against its bundled fixture:

python code/catalog_audit.py --demo

It reports eleven findings at three severities.

  1. Which finding is blocking, and why is it the only one?
  2. Two findings concern ownership. Explain the difference between them.
  3. One finding reports a gap between grants and usage. What number would make that finding go away, and is making it go away the right goal?
  4. Pick the finding you would fix last, and justify the ordering.

Core

Exercise 30.4 — Find your second definition

Difficulty: ★★☆ · Time: 45 minutes

Case Study 1 found four definitions of active_customer with a grep.

Pick a metric that matters where you work — or, if you have no such corpus, use this book's repository — and run the equivalent search across every place SQL, Python, or notebook code is written.

Report:

  1. The command you ran, and how you chose the pattern.
  2. The number of implementations found.
  3. The number of distinct definitions among them.
  4. For each distinct definition: is it the same question answered differently (reconcile), or a different question wearing the wrong name (rename)?

If you find exactly one definition, say how confident you are that your pattern would have found a second, and what phrasing it would have missed.

Exercise 30.5 — The tie-breaker

Difficulty: ★★☆ · Time: 30 minutes

Case Study 1's three-week dispute ended with one question: "which of these has already been said to someone outside the company?"

  1. Explain in two or three sentences why the question works — what property makes it decisive where arguments about merit were not.
  2. Construct a situation where the question returns nothing. What do you do then?
  3. Construct a situation where the question returns an answer and following it would be wrong.
  4. §26.8 pre-decided "stale over wrong"; Case Study 1 found a decision already made. State the common technique in one sentence.

Exercise 30.6 — Rename versus reconcile

Difficulty: ★★☆ · Time: 30 minutes

Case Study 1 renamed three of eleven implementations rather than reconciling them, and its first draft would have migrated all eleven — which would have degraded a production churn model.

For each pair below, decide reconcile or rename, and give the test you applied:

  1. revenue = sum of order totals, vs revenue = sum of order totals net of refunds.
  2. revenue = booked at order time, vs revenue = recognized at shipment.
  3. session = 30-minute inactivity gap, vs session = 30-minute gap, capped at 4 hours.
  4. active_user in a churn model (30-day any event) vs on a board deck (12-month orders).
  5. latency = p50 of the API, vs latency = end-to-end freshness of the warehouse.

Then: state the general rule in one sentence, and name the check that reveals which case you are in.

Exercise 30.7 — Granted versus used

Difficulty: ★★☆ · Time: 45 minutes

Case Study 2's two queries — who has access, and who has used it — produced 62 and 23.

  1. Write the equivalent pair for a warehouse you have access to (Snowflake account_usage, BigQuery INFORMATION_SCHEMA.JOBS, Postgres pg_stat_statements + information_schema.role_table_grants).
  2. Run them. Report both numbers and the gap.
  3. Characterize the gap the way §"The Analysis" does — how many are role changes, decommissioned services, contractors, legitimately idle, unattributable?
  4. What window did you use, and what is the longest legitimate idle period among your consumers? If you do not know, say how you would find out.

Exercise 30.8 — The unattributable resource protocol

Difficulty: ★★☆ · Time: 30 minutes

Case Study 2's four-step protocol: revoke the capability, alert on use, wait a full business cycle, then delete.

  1. Apply it to a table nobody can attribute. What is the equivalent of "revoke but keep observability"?
  2. Apply it to a scheduled DAG.
  3. Apply it to a dashboard.
  4. For which of the three is the protocol weakest, and why?
  5. Two of Kestrel's four announced themselves. What would you have done differently if all four had been silent?

Exercise 30.9 — Instrument the asking

Difficulty: ★★☆ · Time: 40 minutes

§30.9's 📗 reaction produced 84 gotchas across 61 models in six months, and cut repeat questions from 31% to 12%.

  1. Read the last thirty questions in your organization's data help channel (or the last thirty issues, if you have no channel). How many would have produced a useful catalog entry?
  2. Of those, how many are about a gotcha rather than about where something is?
  3. Design the smallest possible mechanism for capturing them where you work. It must cost the answerer no more than one action.
  4. §30.9 claims the gotchas are the only catalog text not written by the model's author, "because an author cannot write down what they find obvious." Test this: pick a model you built and try to list its gotchas. Then ask someone who uses it.

Exercise 30.10 — The boundary field

Difficulty: ★★☆ · Time: 30 minutes

§30.10's ⚠️ callout records the boundary around data you do not control:

meta:
  external: true
  upstream_owner: "supplier-b, ops@supplier-b.example"
  upstream_controls: "none known; no agreement; no change notification"
  our_controls: "schema declared at read; format assertion; 90d retention"
  last_verified: "2026-08-19"
  1. Fill it in for a real external source you depend on. Leave upstream_controls honest — "none known" is a legitimate value and the most common correct one.
  2. Which of the three silent divergences (retention, classification, ownership) applies to your source?
  3. §30.10 says "ask" is skipped more often than it should be. Draft the email. Three sentences.
  4. What would you do if the answer is "we do not notify anyone of schema changes"?

Advanced

Exercise 30.11 — Extend the auditor

Difficulty: ★★★ · Time: 90 minutes · 🧪

code/catalog_audit.py implements a set of catalog checks. Add three of the following, with tests:

  1. Grain missing on a model referenced by a dashboard (a stricter version of the existing check).
  2. A certified model whose upstream includes a deprecated model.
  3. A classification that decreases downstream — a model reading a confidential source and declaring itself internal (§30.6's directionality).
  4. A retention policy with no mechanism — a stated period and no job that enforces it (§30.7).
  5. An owner who is an individual rather than a team where the model is certified.
  6. A metric name defined in more than one model (Case Study 1's grep, as a check).

Requirements: each check must have a fixture that triggers it and one that does not; each must declare a severity and justify it; and --self-check must still pass.

Then answer: which of your three would produce the most false positives in a real catalog, and what would you do about it?

Exercise 30.12 — Design an access review that can say no

Difficulty: ★★★ · Time: 60 minutes

Case Study 2's test: could the reviewer's answer have been "no"?

Design a review for a system you know. Specify:

  1. The unit the reviewer decides on — not a role name.
  2. The evidence presented per unit.
  3. The default action, and the recovery path if the default is wrong.
  4. The window, justified against your slowest legitimate consumer.
  5. The metric that tells you the review is working.
  6. The failure mode you expect in the second year, once it is routine.

Then: estimate the reviewer's time per cycle. If it exceeds 90 minutes, cut something and say what.

Exercise 30.13 — The undetectable defect

Difficulty: ★★★ · Time: 45 minutes

Case Study 1's 🏭 callout claims that two internally-consistent definitions of a metric are undetectable by every control in this book: Chapter 17's contracts, Chapter 23's twenty-two assertions, Chapter 29's watermark check, Chapter 25's freshness monitors.

  1. Try to disprove it. Construct an assertion that would have caught gross_margin computed two ways, both internally consistent, one including shipping cost.
  2. If your assertion works, what does it require that a normal test does not?
  3. If it does not work, state precisely why — what property of the defect defeats it?
  4. gross_margin was wrong for seven months and used in a pricing decision. Write the two-sentence postmortem finding. Is it a governance failure, a review failure, or something else?

Exercise 30.14 — Governance as a by-product

Difficulty: ★★★ · Time: 60 minutes

§30.11 argues that a governance artifact survives in proportion to how much of it is a by-product of work someone already does.

  1. List five governance artifacts in your organization. For each: what work produces it, and would that work happen anyway?
  2. Rank them by predicted staleness in one year. Then check the ones that are more than a year old and see whether your ranking was right.
  3. Pick the one you predict will go most stale and redesign it as a by-product. What work does it attach to?
  4. §30.11 claims Chapter 23's register is a governance artifact that cannot go stale. Explain why, in terms of the property that distinguishes it from a description.
  5. Is there an artifact that cannot be made a by-product? Name one and defend it.

Exercise 30.15 — The imposed program

Difficulty: ★★★ · Time: 45 minutes

§30.12 covers governance imposed from outside — a requirement arrives with a deadline and a template.

You receive this, four weeks out: "Every data asset must have a documented owner, a classification, and a retention period. Evidence required by the 30th."

You have 290 tables, 47 roles, and 34 owners who no longer resolve.

  1. What do you do in week one? Be specific about the order.
  2. Which of the three fields do you populate honestly at scale, and which will be mostly guesses? Say so explicitly.
  3. §30.12 recommends satisfying the requirement in a way that leaves something useful behind. What is the useful residue here, and what would the cheapest compliant answer have left instead?
  4. The template asks for a single owner per table. Some tables genuinely have none. What do you write? (§30.4's custodial tier is one answer; defend it or propose better.)
  5. Write the two sentences you send back to the requester before starting, and say what you hope they change.

Project Milestone

Exercise 30.16 — Catalog the Kestrel platform

Difficulty: ★★★ · Time: 2–3 hours · 🧪

Your platform now has models, DAGs, quality assertions, SLOs, and infrastructure. Catalog it.

  1. Populate five fields for every model you have built: description, owner, grain, status, gotchas. Anything you cannot fill honestly, leave empty — and count the empties.
  2. Run catalog_audit.py against your real catalog, not the fixture. Fix what it finds, or record why not.
  3. Run the grep from Case Study 1 across your models for the three metric names you use most.
  4. Run the granted-versus-used pair from Exercise 30.7 against your own warehouse if you have one, or against the fixture if not.
  5. Write the boundary field (§30.10) for every external source your platform reads.
  6. Add one check to catalog_audit.py that encodes something specific to your platform.

Deliverable: the populated catalog, the audit output before and after, the count of honest empties, and a paragraph on which of the five fields was hardest to fill and why.

The honest-empties count is the point of the exercise. A catalog with no empty fields, populated in an afternoon, is a catalog of guesses.