In this chapter
- Generate the repetitive layer. Code rendered from the catalog can’t drift from it.
- A template framework has five parts. Patterns, conventions, bindings, a declared context and proof, on any engine.
- Conventions are metadata the patterns read. Naming rules and system column roles live outside every pattern.
- Prove it on your worst table, then own it. A green sample isn’t a green run, and a sealed pattern ships someone else’s opinion.
The most repetitive code a data team ships carries source data into the lakehouse and builds the integration layer, the silver layer built against the business’s agreed model. It’s hundreds of structurally identical objects that differ only in metadata.
Every landing table is the source table plus the same two audit columns. Every staging load is the same change-detection pattern pointed at another table. In a Data Vault, every satellite of descriptive history hangs off its hub of business keys the same way. The integration shape silver takes changes only which pattern repeats.
Why a hand-written layer drifts
Hand-writing that code produces drift. Notebook twelve starts as a copy of notebook eleven with two edits, and one gets forgotten. A column rename lands in the table that raised the ticket and misses two downstream tables nobody remembered.
Worse, every hand-written exception is an unrecorded decision. So the load that handles deletes differently stays invisible until it disagrees with the other forty at three in the morning. Across five hundred objects, the real design lives in the diffs between files. No reviewer will find it there.
Generation inverts the failure mode. Render every object from the catalog and the catalog can’t drift from the code, because the code came from it. The mapping pairs each source table and column with what it means, with who decided and why. It stops describing the pipelines and becomes the source they’re compiled from.
What comes out is a derived artifact, like a compiled binary. Read it and deploy it, but edit the model, never the output.
What is in a template framework, whatever the engine?
Your engine might be a vendor product, a set of dbt macros, a template engine that is yours or the generator script your team wrote three years ago. A working framework still needs the same five parts. Whichever one is missing will find you.
- Each repeated design is written once. “How we do staging” becomes one pattern per object kind and load style, not forty slightly different files.
- The rules sit outside the patterns. Naming, system columns, hashes and data types are conventions the patterns read as inputs.
- Metadata picks the pattern. A table’s layer, entity type and load style choose it. That’s a binding you can query, where a folder is only a hope.
- A pattern reads only what it’s promised. One that reads anything is neither portable nor testable, so what it may know, conventions included, is written down as its declared context.
- Every render can be proved. Any fragment renders against real metadata, and the platform confirms it accepts the result.
Ask any tool those five questions. Plenty of products show you patterns. Far fewer show the conventions as editable metadata, the bindings as rules, the context as a declared list and proof you can run on your own catalog this afternoon.
Nor will the literature ask for you. dbt Labs’ project-structure guide is the nearest thing the modern stack has to a prescriptive process. It’s disciplined but entirely folder-and-SQL convention inside one tool. Writing on metadata-driven generation goes deeper, but almost all of it is vendor authored and describes one product’s way.
Figure 7.1: the five parts on the pipeline. The conventions live in metadata the patterns read, never in the patterns themselves.Conventions are metadata, not code
Keeping conventions out of the patterns is what separates a framework from a pile of scripts.
Take naming. Stored as metadata, a naming convention is a file a few lines long. A schema pattern resolves the source system, and a table pattern joins the schema name to the table name. From those lines, fifty source tables get one hundred and fifty generated names, and renaming the estate is an edit, not a campaign.
Store the convention as code and every template that names anything carries its own copy, so changing it is archaeology.
System columns follow the same rule, and there the failure is silent.
Each layer adds columns that answer questions the source can’t. A value you can’t date can’t be traced. So every landed row is stamped with when it arrived and which system sent it: the load timestamp and the record source. Comparing every column to spot a change is slow, so staging stores one hash of the descriptive columns, the hash difference, and compares that.
And what did a row say last March? A persistent staging copy keeps every version of every row. Each version carries the dates it was true from and to (the effective-date pair), and a current-row flag marks the version in force now.
A framework declares each system column once, in a catalog, with its role stated as a fact: this column is the load timestamp, this one the hash difference. Some real tools infer the role instead, by recognizing a column’s transformation code. Wrap the load timestamp in a time-zone conversion and the code no longer matches. The role evaporates, and nothing errors.
Roles are decisions, so store them that way, never guessed back out of code.
Figure 7.2: one pattern, three generated names. The source table Kunde, from Willibald, the garden webshop of Maluga, the outdoor-living retailer the book’s examples run on, arrives with 12 columns; each layer’s name and additions come from declared conventions, not from anyone’s typing.
| Generated name | Columns | What this layer adds, by declared role | |
|---|---|---|---|
| Source | dbo.Kunde | 12 | The source’s own columns, exactly as delivered |
| Landing | lnd_dbo_Kunde | 14 | Load timestamp, record source |
| Staging | stg_dbo_Kunde | 15 | Hash difference |
| Persistent staging | psa_dbo_Kunde | 18 | Effective from, effective to, current flag |
Landing, staging and persistent staging are the family of copies every source table grows.
The same patterns, bound by layer and entity type, also render the integrated objects from the mapping. One Customer object is fed by both the webshop’s customers and the buyers of the roadshow, Willibald’s second sales channel, whose sales arrive as a separate file. It comes out of the mapping the way stg_dbo_Kunde comes out of dbo.Kunde.
Proof or drift
Every generation setup demos beautifully on the vendor’s sample. What separates a framework from a demo is what you can prove against your own metadata.
The first level of proof checks that the same input gives the same output, usually with snapshot tests. That’s render stability. It’s worth having and badly oversold. A stable render proves only that the template didn’t change, not that the platform accepts the result.
Code the suite blesses can still fail on a reserved word, a dialect quirk or a type your mapping never covered. A green offline suite isn’t a green run. A framework that can’t close that gap leaves you to find the rejections in production.
The second level is the one to insist on: every fragment of a pattern testable in isolation against your real tables, columns and ugly legacy names. A fictional sample is built to render cleanly, so it can’t surface your gaps. A pattern that renders the vendor’s Customer table perfectly can still fall over on your forty-column source with a reserved word in position three. Only a render against your catalog says so before the platform does.
So the acceptance test for any framework, bought or homegrown, takes five minutes. Point one pattern at your worst real table and render it. If a framework can’t, its proof story is a sample. And a sample is marketing.
Own your patterns
So who owns the patterns once the layer is generated? In most teams nobody does, and they end up as whatever the last consultant left behind. The fix is a named person who writes them, keeps them current and answers for what they render. That’s the template author.
An edit that touches five hundred objects deserves the review a schema change gets. The people who own the meaning and the conventions approve it, not a merge nobody read. Retiring a pattern takes the same discipline, because your framework must be able to say what still depends on it. One stranded object is quietly hand-maintained code again.
Then ask the question most evaluations skip: whose opinion does the generated code express, and what happens when you disagree?
New load syntax, table features and orchestration primitives arrive on the platform’s release cycle, not the vendor’s. So a sealed template set is out of date the day you adopt it. You wait on someone else’s release notes to use capability you already pay for.
The question isn’t whether the shipped patterns are good. It’s whether you can change the one block you disagree with and still receive the vendor’s next improvement to the rest. Fork the whole file and you maintain it forever. Change nothing and you ship someone else’s opinion. Override the one section, keep tracking upstream, and generation produces your opinion rather than the vendor’s.
| The people part | Name a template author, and have the owners of the meaning and the conventions approve each pattern edit like a schema change. |
| Metadata to capture | Record the naming rules, each system column’s role, the hash definitions, the data-type mappings per platform, the bindings and each overridden block with its reason, so anyone can tell your opinion from the vendor’s. |
| Regardless of stack | A homegrown script that answers all five framework questions beats a product that answers two. |
In DeltaVault today
DeltaVault, our metadata catalog, generates a table’s code from its metadata, and a project’s Templates tab shows which template renders for which table. Pick a layer in the tree, or an entity kind inside it such as Hub or Satellite, and the grid lists the template bound for each output: DDL, Load and Test. Each bound template carries a badge naming its scope (Platform, Organization or This project). Change a row’s template and this project diverges without touching any other.
Any template pinned to one table from that table’s Preview tab appears below the grid in a Table overrides list. A template’s Where it applies tab lists every rule that routes to it. The three tabs describe the same rules: by project, by template or by table.
