R11 · Week 11 Recall & Drill

Assessment scope

Attempt the recall prompts unaided, then work through the implementations and answer keys. The local programs use explicit fixtures and independent checks; product behavior is tied to named primary sources, not inferred from the toy models. Service deployment and production readiness are separate from this conceptual review. Timing budgets are adjustable practice suggestions.

Sessions covered

• S051 — dbt — Models, Tests, Docs, Warehouse-Native ELT
• S052 — Lakehouse — Delta / Iceberg / Hudi, ACID on Files
• S053 — Data Quality — Freshness, Volume, Schema, Distribution
• S054 — Governance & Cost — Lineage, PII, Attribution
• S055 — HTTP Fundamentals — Verbs, Status Codes, Headers, Caching

By the end of this revision you can

• Explain the ELT shift and how ref calls declare model dependencies and resolve target relations.
• Pick a materialisation for a staging model, a small dimension, a huge fact table, and a reusable helper.
• Explain immutable data, snapshots and a format-specific atomic commit/conflict protocol; distinguish the local model from multiwriter ACID and diagnose small-file overhead.
• Name four useful data-quality categories with a concrete failure each, and explain why schema validation alone cannot establish correctness.
• Use query tagging plus lineage to answer who pays for a table and what breaks if it is deleted.
• Choose HTTP methods, statuses and cache controls, and distinguish a cacheable contract from an unmeasured CDN hit-rate promise.

90-min structure

The 90-minute budget is pacing guidance, not a claim that full execution and every extension fit every learner's session.

Blank-page reconstruction · 30 min

S051 · dbt & Warehouse-Native ELT

1. Explain the difference between transforming before loading and transforming in the warehouse, and what made the latter viable.
2. Name the four canonical test types and say what each one protects.
3. Pick a materialisation for each of: a staging model, a small dimension, a very large fact table, and a reusable helper used once.

S052 · Lakehouse Formats

1. Explain in a minute what makes a directory of Parquet files into a table, using manifest, snapshot, and atomic swap.
2. State the commit/conflict protocol needed to prevent unsafe concurrent publication; do not assume any file rename provides it.
3. Distinguish removing a file in the transaction log from physically deleting it, and connect that to time travel.

S053 · Data Quality

1. Name the four pillars and give a one-line failure for each.
2. Explain the difference between a row-level rule and a statistical monitor.
3. Say why a fixed row-count threshold is a poor volume check, and what to use instead.

S054 · Governance & Cost

1. Compare the precision and maintenance costs of column-level and table-level lineage.
2. Explain what a query tag is and how it supports attribution and which shared costs remain unallocated.
3. Contrast catalog-first with producer-first governance and identify complementary producer and catalog responsibilities.

S055 · HTTP Fundamentals

1. Distinguish safe from idempotent, and classify each of the common verbs.
2. Distinguish the three common failure statuses in the scenario of a user opening an admin page.
3. Explain what an entity tag is and how it saves bandwidth.

Hands-on drill · 30 min

Task: build a miniature version of each idea — a model DAG with tests, a versioned table log, distribution checks, and a worked HTTP cache exchange. These local models are not dbt, table-format or CDN runtime reproductions.

Step 1 — a model DAG that resolves itself (8 min)

# dag.py — literal ref calls declare dependencies that this toy compiler discovers.
import re
import sqlite3
 
MODELS = {
    "stg_orders": """
        SELECT id, customer_id, CAST(amount AS REAL) AS amount, placed_on
        FROM raw_orders
    """,                                    # staging: rename, cast, pin the contract
    "int_customer_totals": """
        SELECT customer_id, SUM(amount) AS total, COUNT(*) AS n_orders
        FROM {{ ref('stg_orders') }} GROUP BY customer_id
    """,
    "mart_top_customers": """
        SELECT * FROM {{ ref('int_customer_totals') }} WHERE total > 100
    """,
}
 
def deps(sql):
    return set(re.findall(r"ref\(\s*'([^']+)'\s*\)", sql))
 
def toposort(models):
    unknown = set().union(*(deps(sql) for sql in models.values())) - set(models)
    if unknown:
        raise ValueError(f"unknown model references: {sorted(unknown)}")
    done, order = set(), []
    while len(order) < len(models):
        progressed = False
        for name, sql in models.items():
            if name not in done and deps(sql) <= done:
                order.append(name); done.add(name); progressed = True
        if not progressed:
            raise ValueError("cycle detected: " + str(set(models) - done))
    return order
 
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers(id INTEGER PRIMARY KEY);
INSERT INTO customers VALUES(1),(2),(3);
CREATE TABLE raw_orders(id INTEGER, customer_id INTEGER, amount TEXT, placed_on TEXT);
INSERT INTO raw_orders VALUES
 (1,1,'100','2026-01-01'),(2,1,'60','2026-01-02'),
 (3,2,'40','2026-01-03'),(4,3,'900','2026-01-04');
""")
 
for name in toposort(MODELS):
    sql = re.sub(r"\{\{\s*ref\(\s*'([^']+)'\s*\)\s*\}\}", r"\1", MODELS[name])
    con.execute(f"CREATE VIEW {name} AS {sql}")
    print(f"built {name:<22} deps={sorted(deps(MODELS[name])) or '-'}")
 
print(con.execute("SELECT * FROM mart_top_customers ORDER BY customer_id").fetchall())
 
# Four canonical categories plus one custom test, returning failing rows.
TESTS = {
    "unique(order_id)":  "SELECT id FROM stg_orders GROUP BY id HAVING count(*)>1",
    "not_null(total)":      "SELECT customer_id FROM int_customer_totals WHERE total IS NULL",
    "relationships":        "SELECT o.id FROM stg_orders o WHERE o.customer_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id=o.customer_id)",
    "nonnegative_amount":   "SELECT id FROM stg_orders WHERE amount < 0",
    "accepted_values":      "SELECT id FROM stg_orders WHERE strftime('%m',placed_on) NOT IN ('01','02')",
}
for name, sql in TESTS.items():
    bad = con.execute(sql).fetchall()
    print(f"test {name:<24} {'PASS' if not bad else f'FAIL ({len(bad)} rows)'}")

Expected outcome: the literal ref calls declare dependencies, and the topological build order is computed rather than manually listed. Hardcoded table names alone do not supply this toy compiler with dependency metadata. The fixture passes these checks; the relationship test now references an independent customer table, and uniqueness checks source order IDs rather than a grouped key that is unique by construction. The accepted-values rule explicitly restricts this exercise to January/February; the nonnegative-amount rule is a separate custom check. Now introduce a duplicate row or a negative amount into the raw table and rerun: the relevant test reports failing rows rather than a bare boolean, which is precisely why tests should return rows — you get the failing keys to debug with, not just bad news.

Step 2 — a transaction log over files (7 min)

Separate three things: immutable data bytes, metadata identifying a snapshot, and a concurrency-safe publication operation. The original rename example wrote only filenames, never data files, and could overwrite a competing version on POSIX systems. It did not implement multiwriter ACID or demonstrate readable Parquet time travel. This deliberately single-process model uses a checked expected-version transition and actual immutable byte values. A cloud implementation needs the table format's supported catalog/log commit protocol and conflict validation, not a local rename analogy.

class Conflict(Exception): pass
class Snapshots:
    def __init__(self):
        self.data = {}; self.versions = [frozenset()]
    def put(self, name, payload):
        if name in self.data:
            raise ValueError("immutable object already exists")
        self.data[name] = bytes(payload)
    def commit(self, expected, add=(), remove=()):
        if expected != len(self.versions)-1:
            raise Conflict("stale snapshot")
        if not set(add) <= self.data.keys():
            raise ValueError("missing data object")
        self.versions.append((self.versions[-1] | set(add)) - set(remove))
        return len(self.versions)-1
    def read(self, version):
        return [self.data[name] for name in sorted(self.versions[version])]
 
store=Snapshots(); store.put("a",b"row1"); store.put("b",b"row2")
assert store.commit(0,add=["a"]) == 1
assert store.commit(1,add=["b"],remove=["a"]) == 2
assert store.read(1)==[b"row1"] and store.read(2)==[b"row2"]
try: store.commit(1,add=["a"])
except Conflict: pass
else: raise AssertionError("stale writer accepted")
del store.data["a"]  # model a mistaken cleanup, not a filesystem deletion
try: store.read(1)
except KeyError: pass
else: raise AssertionError("old snapshot should lack its deleted data")

The check and append are not a thread-safe or distributed CAS. They expose the required atomic transition so you can reason about a stale writer. Delta, Iceberg and Hudi have distinct metadata, concurrency and cleanup mechanisms; they are not all the same numbered-log protocol or limited to the same file layout. Readers need retained metadata and retained data. Cleanup policies must consider time travel, concurrent readers/writers and recovery, so they cannot be summarized as one universally equivalent retention setting. Many small files add metadata, listing and task overhead; compaction rewrites data and commits a replacement snapshot under the format's protocol.

Step 3 — distribution checks beat threshold checks (7 min)

A static minimum of 1,000 does not detect a drop from a usual 10,000 to 5,100. A two-sided relative band catches both that drop and 20,400 duplicated rows. Neither is inherently superior: a contract may require a fixed exact minimum, while a seasonal rolling baseline may learn an incident as normal. Keep the threshold, baseline population and incident response explicit.

import statistics
history=[10200,9800,10050,10400,9950,10100,10300]
def relative_check(today, history, tolerance=.30):
    if not history or any(x < 0 for x in history) or today < 0 or tolerance < 0:
        raise ValueError("nonnegative counts/tolerance and nonempty history required")
    baseline=statistics.median(history)
    if baseline == 0:
        return today == 0
    return abs(today-baseline)/baseline <= tolerance
assert relative_check(10150,history)
assert not relative_check(5100,history)
assert not relative_check(20400,history)
assert relative_check(0,[0,0]) and not relative_check(1,[0,0])

Freshness checks ask whether expected data has arrived; volume checks count records; schema checks types/columns; distribution checks values or relationships. These are useful categories, not an exhaustive taxonomy. A perfectly typed table may contain duplicated orders or an unannounced currency change. A row rule returns failing keys; a statistical monitor reports aggregate deviation and uncertainty. Alert severity, ownership and a recovery action matter more than simply adding checks.

Step 4 — watch a real cache header work (8 min)

Work through this illustrative saved exchange, not a claimed live curl result:

HTTP/1.1 200 OK
Cache-Control: public, max-age=60
ETag: "catalog-v7"
Vary: Accept-Encoding
Content-Type: application/json
 
{"version":7}

Within freshness rules, a cache can reuse the stored representation without contacting the origin. Once stale, a client can send If-None-Match: "catalog-v7". If the selected representation still matches and the request conditions permit it, the server returns 304 Not Modified with appropriate metadata and no representation body; the client uses its stored bytes. If changed, the response is 200 with new bytes and a new validator. A 304 saves transfer but still requires revalidation traffic. Missing ETag does not force every revalidation to download a body: Last-Modified/If-Modified-Since is another validator path, and a fresh response may need no request at all.

Vary declares which request headers participate in representation selection. It is not an authorization boundary. Private personalized responses need appropriate cache controls and access policy; no-cache allows storage but requires validation before reuse, while no-store tells caches not to store. A public cache contract cannot promise that a CDN absorbs a particular traffic fraction without actual workload/configuration measurements.

dbt, lineage and HTTP answer key

ELT loads data before warehouse transformation, unlike an ETL design transforming before load. dbt's references declare dependencies and resolve model relations for the configured target; this miniature regex handles only literal single-quoted refs and is not a Jinja/dbt parser. Validate unknown references separately from cycles. A staging view avoids storage but recomputes; a small dimension table can be cheap to rebuild; a large fact may use an incremental materialization with explicit unique keys, late-arrival and full-refresh logic; an ephemeral helper compiles inline and can duplicate work. No choice is forced by the model's name alone. Warehouse compute performs SQL data processing, while compilation, connection orchestration and model scheduling can still incur tool-side costs.

A query tag labels team/model/run for attribution when supported and recorded; shared compute, idle capacity and storage still need allocation rules. Lineage helps identify downstream consumers, but unobserved dynamic queries and external exports can make it incomplete. Column lineage can be more precise than table lineage but costs more to maintain; it is not always the best first investment. Automatic sensitivity propagation is useful but needs rules for transformations, aggregation/declassification, access control and untracked copies. Producers and a central catalog need complementary ownership, not a universal winner between two governance slogans.

GET/HEAD are safe by defined intent (incidental logging is allowed) and idempotent. PUT and DELETE are idempotent by intended effect but not safe; repeated responses may differ. POST is not inherently idempotent. A payment API can add an explicit key+request-fingerprint+stored-result protocol with concurrent claim protection and documented retention; bind the key to the authenticated principal. Reuse the same key after an ambiguous timeout and reject a changed payload. Whether to retry a particular error depends on the API contract and budget, not “all 5xx forever.” A 201 with Location can identify a created resource; 400 describes malformed input; 401 challenges missing/invalid authentication; 403 refuses an authenticated/otherwise understood request; 404 can also conceal resource existence under a deliberate policy.

Retest: inject duplicate order ID 1 and demand a uniqueness failure; insert customer_id 99 and demand a relationship failure; inject a negative amount and demand the custom check fail. Do not expect the grouped customer's ID to expose source duplicates. For the snapshot model, create data before commit and assert a losing stale writer cannot publish; then explain why unreferenced data may require later safe cleanup.

Model tests, materializations and lineage worked answers

The four built-in generic dbt test categories are unique, not_null, accepted_values and relationships; the negative-amount query is a custom rule, not an accepted-values test. NULL handling is separate: SQL NOT IN with a NULL expression is unknown, so the accepted-month rule alone does not reject missing/invalid dates. Add an explicit missing-date rule if dates are required. SQLite also permissively casts some malformed numeric strings; passing this staging fixture does not establish a strict currency parser. In real staging, choose validated source types, explicit columns and casts with known engine behavior.

assert toposort(MODELS) == ['stg_orders','int_customer_totals','mart_top_customers']
assert con.execute('SELECT * FROM mart_top_customers ORDER BY customer_id').fetchall() == [(1,160.0,2),(3,900.0,1)]
# Independent adversarial rows, rolled back after each case.
for values,test in [((1,1,'1','2026-01-05'),'unique(order_id)'),
                    ((10,99,'1','2026-01-05'),'relationships'),
                    ((11,1,'-1','2026-01-05'),'nonnegative_amount'),
                    ((12,1,'1','2026-03-05'),'accepted_values')]:
    con.execute('SAVEPOINT case_check')
    con.execute('INSERT INTO raw_orders VALUES(?,?,?,?)',values)
    assert con.execute(TESTS[test]).fetchall()
    con.execute('ROLLBACK TO case_check'); con.execute('RELEASE case_check')
con.execute("INSERT INTO raw_orders VALUES(13,1,'1',NULL)")
assert not con.execute(TESTS['accepted_values']).fetchall()  # NULL is not a false predicate
assert con.execute('SELECT id FROM raw_orders WHERE placed_on IS NULL').fetchall() == [(13,)]
con.execute('DELETE FROM raw_orders WHERE id=13')
# An all-NULL group fails the aggregate rule; a mixed group can hide a missing amount.
con.execute('SAVEPOINT null_amounts')
con.execute('INSERT INTO customers VALUES(4)')
con.execute("INSERT INTO raw_orders VALUES(14,4,NULL,'2026-01-05')")
assert con.execute(TESTS['not_null(total)']).fetchall() == [(4,)]
con.execute("INSERT INTO raw_orders VALUES(15,4,'5','2026-01-05')")
assert con.execute(TESTS['not_null(total)']).fetchall() == []
assert con.execute('SELECT id FROM stg_orders WHERE amount IS NULL').fetchall() == [(14,)]
con.execute('ROLLBACK TO null_amounts'); con.execute('RELEASE null_amounts')
for broken in [{'a':"select * from {{ ref('missing') }}"},
               {'a':"select * from {{ ref('b') }}",'b':"select * from {{ ref('a') }}"}]:
    try: toposort(broken)
    except ValueError: pass
    else: raise AssertionError('invalid graph accepted')

What each test protects: uniqueness rejects repeated source IDs; not-null rejects missing values at the tested grain; relationships rejects non-NULL foreign keys absent from the independent parent relation; accepted-values restricts a non-NULL value to the declared set. These checks are complementary, not a proof of all business correctness. In the added fixture, customer 4 first has only a missing amount, so SUM(amount) is NULL and the aggregate test fails. Adding an amount of 5 makes that sum non-NULL while order 14 remains incomplete: use a source-row not-null rule when every order must have an amount. Choosing the grain is part of the assertion, not just the query syntax.

Materialization choices: a staging view pins names/types and recomputes from its source; a small dimension table persists an inexpensive rebuild; an incremental fact processes selected new/changed rows and needs late-update/unique-key/full-refresh rules; an ephemeral single-use helper can compile as a CTE without a persisted relation. Incremental does not mean “only append” or “never revisits old rows.” ELT became practical with warehouse compute/storage and scalable SQL engines, not because data validation no longer matters. References both resolve relations and expose dependencies, while manually configured lineage remains possible; ref is not the only way any dependency graph could exist.

Cost/impact example: query tags assign 60 measured compute units to team A and 30 to B; 10 shared idle units are unassigned. Direct tagged cost is A=60/B=30, not a complete 100-unit bill. Under an explicitly chosen proportional allocation rule A pays 60+10×60/90≈66.67 and B pays 33.33. Another policy could allocate shared cost equally. A lineage edge raw_orders.amount → stg_orders.amount → customer_totals.total warns that deleting/changing that source affects the total and top-customer mart. An unrelated email field is not necessarily needed for that metric; column lineage can narrow impact compared with the table-level graph. Dynamic SQL and exports can remain unobserved. Producers should own contracts/classification/change notice; the catalog discovers, documents and enforces shared policy where supported. Neither can infer all privacy semantics automatically.

Format-specific commit and data-quality solutions

For Iceberg, readers choose a snapshot described through table metadata/manifests; writers create immutable files and commit by an atomic metadata transition supported by the catalog, retrying/validating compatible conflicts. Delta uses its transaction log and supported storage/commit mechanisms; Hudi has a timeline with distinct table/concurrency modes. Do not apply the numbered-file/rename toy protocol universally. In the model, snapshot 1 names a, snapshot 2 replaces it with b, and deleting a makes snapshot 1 unreadable. Retaining metadata without the referenced bytes is not time travel. Compaction creates replacement files and publishes metadata; physically reclaim old files only under that format's retention and concurrent-reader rules.

Four incidents: freshness—yesterday's partition never arrives by its agreed deadline; volume—expected 10,100 rows becomes 5,100 or 20,400; schema—a required integer column disappears; distribution—a money column remains numeric but changes from dollars to cents. A not-null row check cannot detect the last incident. Seasonality and changing traffic require a relevant baseline, but absolute contractual minima can remain valuable. Route a blocking revenue failure to an accountable responder; a weak exploratory anomaly may be a ticket. Tune noisy rules with evidence rather than silencing a real incident or claiming all checks are necessarily better than none.

HTTP conditional-request answer and safe protocol model

The original live-URL exercise assumed every URL supplied an ETag and every conditional request would be 304. Instead, this explicit server model selects an authorized public representation first and recognizes only the single strong validator used by this fixture. It is not a general RFC validator parser or an HTTP server.

representations = {'en':('"en-v7"',b'catalog'), 'fr':('"fr-v7"',b'catalogue')}
def conditional_get(language, if_none_match=None):
    etag, body = representations[language]
    headers = {'ETag':etag,'Vary':'Accept-Language','Cache-Control':'public, max-age=60'}
    return (304,headers,b'') if if_none_match == etag else (200,headers,body)
status,headers,body = conditional_get('en')
assert (status,body) == (200,b'catalog')
assert conditional_get('en',headers['ETag'])[::2] == (304,b'')
assert conditional_get('fr',headers['ETag'])[::2] == (200,b'catalogue')
assert conditional_get('en','"old"')[0] == 200

Answer: fresh reuse can avoid any origin request; stale validation can save body bytes, not the round trip. Select the right Vary variant and authorization context before testing a validator. GET/HEAD/OPTIONS are safe by defined semantics; safe methods plus PUT/DELETE are idempotent, while POST/PATCH do not inherently promise idempotence. An unauthenticated admin request commonly gets 401 with a challenge, authenticated-but-forbidden gets 403, and deliberately hiding existence may use 404. Status codes are not a replacement for authorization. A payment retry contract can choose 201+Location on creation, documented replay status, 409 on changed-body key reuse and 400 for malformed input; retry only permitted transient cases with bounded backoff and the same principal/key/body. Concurrent first-use claims must be serialized atomically with the business operation or a durable coordination protocol.

Recall quiz — five repairs

1. Why ref? Discover declared dependencies and resolve environment-specific relations; build order is not inferred from arbitrary SQL text by magic.
2. Why don't two writers corrupt the table? Only a supported format/catalog conflict-safe protocol provides that guarantee; a local rename does not.
3. Why relative volume checks? They catch proportional loss/duplication missed by a low fixed floor, but baselines can drift or become contaminated.
4. Why query tags? They label observed costs and ownership; shared/idle/storage cost and missing tags still require allocation policy.
5. Safe payment retry? Bind principal/key/fingerprint/result durably, keep the same key after timeout, reject conflicting reuse and define retention and external-effect boundaries.

Answer constraints and corrections

A toy file manifest is not a verified Delta/Iceberg/Hudi protocol. Local rename may overwrite an existing path and is not a compare-and-swap commit. A 304 response reuses a cached representation under request/validator rules, not a body supplied by the 304 itself. Keep provider and HTTP-cache claims version-specific.

Gap analysis + next week preview · 10 min

• Can you derive dependency order and make duplicate, missing-parent and bad-value tests fail for the intended reason?
• Can you distinguish snapshot metadata from retained bytes and explain the stale-writer conflict check without calling it distributed CAS?
• Can you show a 304 with no body and a different Vary representation returning 200, without claiming any arbitrary live URL must behave that way?

Mark each answer unaided, hinted or not yet; select a failed assumption and use a new counterexample for the retest rather than memorizing one fixture. Keep the input contract, boundary case and independent expected answer next to any execution result.

Next week: S056–S060 cover REST resources, GraphQL, gRPC/Protobuf, OAuth/OIDC and OS resources. HTTP caching/idempotency is the foundation, not a substitute for schema evolution, authorization or ownership.

Corrective source notes

• About ref function | dbt Developer Hub — dbt ref resolves a Relation and declares dependencies that support compilation order and selection. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• Add data tests to your DAG | dbt Developer Hub — dbt data tests return violating rows; uniqueness tests must target source IDs rather than a key unique by grouping. The four built-in generic dbt data-test categories are unique, not_null, accepted_values and relationships; a negative-amount rule is custom. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• Materializations | dbt Developer Hub — dbt ephemeral models are inlined as CTEs rather than built as independent database relations. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• dbt incremental models — Incremental processing needs an explicit row filter and unique-key contract; it does not automatically solve late updates. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• Apache Iceberg reliability — Iceberg writers publish through an atomic metadata transition and retry from the updated state, not arbitrary local rename. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• Delta Lake vacuum utility — Delta data and log cleanup differ, and physical removal of referenced bytes limits historical reads. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• Concurrency Control | Apache Hudi — Hudi uses its own timeline and concurrency modes; it is not universally the same numbered-log protocol as another format. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• RFC 9110 HTTP semantics, June 2022 — HTTP idempotence concerns intended effect, not identical responses or an arbitrary POST automatically being retry-safe. A 304 has no representation body or trailers; the client reuses stored representation bytes. A 401 signals absent/invalid authentication credentials and requires an applicable WWW-Authenticate challenge. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.
• RFC 9111 HTTP caching, June 2022 — no-cache permits storage but requires successful validation before reuse. Vary-selected request header fields must match for reuse without revalidation; Vary does not itself authorize access. Retrieved 2026-10-07; exact passage reviewed 2026-10-07.

These passages support the named propositions, not blanket product certification. The lesson-specific derivations and fixtures are separate evidence; runtime boundaries are stated beside each worked exercise.