For the ETL engineer

Audience — the engineer who owns the projection from upstream source systems into the two shared base tables at Sasquatch National Bank.

What you do today

You run a load — nightly batch, hourly micro-batch or streaming near-real-time, depending on the upstream system — and the data lands somewhere downstream that operators / executives / compliance look at. When something goes wrong, the symptom is rarely "ETL failed" (that's the easy case, which alerts catch); it's usually "the dashboard showed wrong numbers" or "we have drift on this account" or "this transfer is missing its counterpart leg."

The hard case is silent: the load ran, the row counts look right, but a metadata key got left blank, an external counterparty's leg didn't make it across, or a force-posted transfer wasn't tagged as such. The downstream sheets render as exceptions; the operators escalate; you reverse-engineer which load step was responsible.

What this tool does differently

Every dashboard reads off two tables: transactions (one row per posting leg) and daily_balances (one row per account-day). Every sheet — every L1 invariant, every L2 hygiene check, every Investigation question — projects off those two. Your ETL contract is a stable, narrow column shape (Schema v6); your debug surface is the same L1 Exceptions / Daily Statement / Transactions chain the operators use.

When a load goes silently wrong, the L1 dashboard surfaces it as a specific row on a specific sheet. The check_type names the class of failure — and tells you which load step to audit. The main classes land on their own sheets:

• Drift
• Overdraft
• Limit Breach
• Pending Aging
• Unbundled Aging
• Supersession Audit

Headline set, not the whole of it — chain / XOR / fan-in disagreements plus the balance-cadence checks also roll into L1 Exceptions. The L1 Invariants reference names every check with the columns it carries and the load step it points at.

Ship a load fix and the matching row drops off L1 Exceptions on the next run. The ETL is OBSERVABLE — you watch the fix land instead of hoping the load ran clean. (See it on the live spec_example dashboard.)

What we are NOT asking you to learn

• Not a new schema. The two base tables are the contract; if you can write to them in the right shape, every dashboard works.
• Not the dashboard internals. You don't author visuals or filters or drills. The dashboards are generated from the L2 YAML by the integrator role; you're upstream of that.
• Not every L2 primitive. You need to know which metadata keys your loads are responsible for setting. The L2 YAML names them; the integrator can tell you which ones are load-time vs. set-by-downstream-process.

How to start

1. Read the Data Integration handbook. It covers the two-table contract, the metadata keys, the matview refresh sequence and the idempotency / supersession rules that let you safely re-run a load.
2. Read Schema v6 — Data Feed Contract. It's the column-by-column reference for the two base tables. Treat it as the authoritative source for any "what type does this field need to be?" question.
3. Walk the recipes in Walkthroughs → ETL in this order:
   • How do I populate transactions?
   • How do I populate daily_balances?
   • How do I validate a single account-day?
   • How do I prove my ETL is working?
   • How do I tag a force-posted transfer?
   • How do I add a metadata key?
4. Bookmark What to do when demo passes but prod fails?. It's the canonical first-stop for "the loader works locally, but doesn't on real data" debug arcs.

Your daily routine

1. Verify the previous business day's load landed: open the Info sheet on any deployed dashboard. It carries a deploy stamp + matview row counts. Counts at zero or unchanged from yesterday means your load didn't run (or ran but landed in the wrong schema).
2. Spot-check a known busy account on Daily Statement. The per-(account, day) walk should show your loaded postings — opening + flow + closing should all reconcile.
3. Watch L1 Exceptions for new violations that look ETL-shaped (drift on a stable account, missing counterparty leg, force-posted-without- internal-catchup, supersession trail).
4. After any schema or metadata change, refresh all l2_* matviews. The L1 invariant views don't auto- refresh; stale matviews are a frequent cause of "the dashboard shows yesterday's state."

The matview-refresh contract

Per the Data Integration handbook, the refresh sequence is dependency-ordered: base tables first, then the L1 invariant matviews (l2_drift / l2_overdraft / l2_limit_breach / l2_stuck_pending / l2_stuck_unbundled, plus the chain / XOR / fan-in checks), then the daily-statement rollups, then the Investigation matviews (l2_inv_pair_rolling_anomalies, l2_inv_money_trail_edges) last — those read straight off the base tables so they don't depend on the L1 views, but the helper refreshes them at the end anyway. The CLI's refresh_matviews_sql(l2_instance) helper emits the right ordering for any L2 instance — call it from your load orchestrator after every transactions / daily_balances write.

The concepts you'll want grounded

• Double-entry posting — the conservation invariant your loads must preserve. Any leg you drop, double-load or sign-flip surfaces as drift.
• Sweep / net / settle — the daily cycle behind aggregating rails. Impacts how you batch your loads (you can't "load Monday's sweep on Wednesday" without confusing the matviews).
• Open vs. closed loop — the system-boundary question. Closed-loop legs you load both sides; open-loop you load the internal side and wait for the external counterparty's confirmation.
• Eventual consistency — why a stuck-pending row on a fresh load is not necessarily a bug; aging-bucket bands tell you when it becomes one.

What "good" looks like

After a few weeks of running the load against the dashboard:

• You're catching load anomalies from the L1 surface within a day of them happening, not from end-of-month reconciliation.
• New metadata keys you add show up immediately on the relevant L2 Flow Tracing sheets.
• When operators ask "is the load OK?" you answer from the Info sheet's matview row counts in 10 seconds.
• Load reruns + supersession traces produce zero net change on the L1 dashboard (the supersession audit catches what changed, the conservation check confirms net delta = 0).

That's the acceptance bar. The ETL works when SNB's downstream surfaces stay in-sync with the upstream feed without manual data-team intervention.