R11 · Week 11 Recall & Drill

Weekly revision · Week 11 · Covers 5 sessions from Mon–Fri.

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

90-min structure

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.

Gotcha you probably forgot: selecting every column in a staging model is a trap. Staging is exactly where you pin the contract — renaming, casting, and explicitly listing columns — so that a new upstream column does not silently propagate into every downstream model, and a removed one fails loudly at the boundary instead of mysteriously downstream.

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. Say why two concurrent writers do not corrupt each other.
3. Distinguish removing a file in the transaction log from physically deleting it, and connect that to time travel.

Gotcha you probably forgot: you cannot time-travel past your cleanup retention window. Vacuuming physically deletes files that older snapshots still reference, so the log entry survives while the data does not, and the older version becomes unreadable. Retention policy and time-travel guarantee are the same setting viewed from two directions.

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.

Gotcha you probably forgot: adding many checks quickly causes alert fatigue, which is worse than having no checks, because a noisy channel trains people to ignore the real one. The discipline is severity routing — page only for the checks that genuinely warrant waking someone, ticket the rest, and silence the informational ones — plus owning and deleting checks that keep firing falsely.

S054 · Governance & Cost

1. Say why column-level lineage is strictly more useful than table-level.
2. Explain what a query tag is and why it is the highest-leverage cost primitive.
3. Contrast catalog-first with producer-first governance and say which scales better in a new platform.

Gotcha you probably forgot: manual column classification does not survive contact with a growing platform, because every new derived table reintroduces untagged copies of sensitive fields. Automatic tag propagation along lineage is what keeps classification true over time — tag once at the source and let derivation carry it forward.

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.

Gotcha you probably forgot: without the variance header, a shared cache can serve a response generated for one representation to a client that asked for another — a compressed body to a client that cannot decompress it, or one language to a speaker of another. Any response whose content depends on a request header must declare that dependency, or the cache will confidently serve the wrong thing.

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 real cache header behaviour.

mkdir -p ~/projects/w11-drill && cd ~/projects/w11-drill

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

# dag.py — the essential idea behind ref(): dependencies are discovered, not declared.
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):
    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 SystemExit("cycle detected: " + str(set(models) - done))
    return order
 
con = sqlite3.connect(":memory:")
con.executescript("""
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())
 
# The four canonical tests, as plain queries returning failing rows.
TESTS = {
    "unique(customer_id)":  "SELECT customer_id FROM int_customer_totals GROUP BY 1 HAVING count(*)>1",
    "not_null(total)":      "SELECT customer_id FROM int_customer_totals WHERE total IS NULL",
    "relationships":        "SELECT customer_id FROM int_customer_totals WHERE customer_id NOT IN (SELECT customer_id FROM stg_orders)",
    "accepted_values":      "SELECT id FROM stg_orders WHERE amount < 0",
}
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 build order is derived purely from the reference calls, never declared — that is the whole argument for referencing by function instead of hardcoding table names. All four tests pass on this data. 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)

# versions.py — the metadata layer, stripped to its essence.
import json
from pathlib import Path
 
root = Path("tbl"); (root / "_log").mkdir(parents=True, exist_ok=True)
 
def commit(version, add=(), remove=()):
    """Each commit is one atomically-written log entry. Data files are never mutated."""
    entry = {"version": version, "add": list(add), "remove": list(remove)}
    tmp = root / "_log" / f".{version}.tmp"
    tmp.write_text(json.dumps(entry))
    tmp.rename(root / "_log" / f"{version:020d}.json")   # atomic rename = the commit
 
def snapshot(at=None):
    files = set()
    for p in sorted((root / "_log").glob("*.json")):
        e = json.loads(p.read_text())
        if at is not None and e["version"] > at:
            break
        files |= set(e["add"]); files -= set(e["remove"])
    return sorted(files)
 
commit(0, add=["part-000.parquet"])
commit(1, add=["part-001.parquet"])
commit(2, add=["part-002.parquet"], remove=["part-000.parquet"])
 
for v in (0, 1, 2):
    print(f"version {v}: {snapshot(v)}")
print("current:", snapshot())

Expected outcome: version zero and version one remain readable even though version two logically removed a file, because removal is a log entry and not a deletion. That is time travel, and it is entirely a property of the metadata. Now actually delete part-000.parquet from disk — simulating a vacuum — and note that reading version zero would now fail while its log entry still claims the file exists. That gap is exactly why the retention window bounds how far back you can travel.

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

# dq.py
import statistics
 
history = [10_200, 9_800, 10_050, 10_400, 9_950, 10_100, 10_300]  # last 7 days row counts
 
def static_check(today, floor=1_000):
    return today >= floor
 
def relative_check(today, history, tolerance=0.30):
    baseline = statistics.median(history)
    return abs(today - baseline) / baseline <= tolerance
 
for label, today in [("normal", 10_150), ("half the data", 5_100), ("double", 20_400)]:
    print(f"{label:<15} today={today:<7} static={'pass' if static_check(today) else 'FAIL':<5} "
          f"relative={'pass' if relative_check(today, history) else 'FAIL'}")

Expected outcome: the static floor passes all three cases, including the day where half the data silently vanished — which is the entire indictment of fixed thresholds. The relative check flags both the drop and the spike. Note that the doubling is flagged too: a volume check should be two-sided, because a duplicated load is just as much an incident as a missing one, and it is the one people forget to check for.

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

# Any public URL will do. Look at the headers, not the body.
curl -sSI https://example.com | tr -d '\r' | grep -Ei '^(HTTP|cache-control|etag|last-modified|vary|age)'

Then prove conditional requests save bandwidth:

ETAG=$(curl -sSI https://example.com | tr -d '\r' | awk -F': ' 'tolower($1)=="etag"{print $2}')
echo "etag: $ETAG"
curl -sS -o /dev/null -w 'full request:        %{http_code}  %{size_download} bytes\n' https://example.com
curl -sS -o /dev/null -H "If-None-Match: $ETAG" \
     -w 'conditional request: %{http_code}  %{size_download} bytes\n' https://example.com

Expected outcome: the first request returns a success status with a body; the conditional request returns a not-modified status with essentially no body. The client already had the bytes and the server confirmed that cheaply — that is the entire mechanism by which a cache layer absorbs traffic. If the site you pick does not send an entity tag, try another; the absence is itself informative, since it means every revalidation must re-download the full body.

Gap analysis + next week preview · 10 min

• In Step 1, could you have written the dependency-discovery logic yourself? If the reference mechanism still feels like syntax rather than the core idea, reread that session.
• Did the static check's failure in Step 3 surprise you? That is the exact shape of a data incident that reaches a dashboard undetected.
• Did the conditional request return a near-empty body as you expected? If the mechanism felt abstract before, that measurement is the whole thing.

Next week (S056–S060) moves fully into web and API engineering: REST design in depth; GraphQL and when it beats REST; authentication and authorisation with sessions, tokens, and delegated access; API security including rate limiting and input validation; and API versioning with backwards compatibility. The HTTP semantics from this week — verbs, status codes, idempotency, caching — is the substrate all of it sits on.

Part of the 6-month evergreen learning plan.