In this chapter
- Why the integration layer must be generated, never hand-written.
- The five things a template framework consists of, whatever engine renders it.
- Conventions as metadata: naming, system columns, hashes, and bindings declared once.
- What proving a pattern actually requires, and why a green sample is not a green run.
Chapter 6 closed on a promise: whichever shape you chose, the integration layer that expresses it should be generated from the model you have been building since chapter 3. This chapter makes that promise concrete. “Use templates” is advice everyone nods at and almost nobody can evaluate, so the work here is to define the standard behind the advice. By the end you will know what a template framework actually consists of, and you will be able to judge any generation tool against that standard, including the scripts your own team wrote.
The most repetitive code you will ever ship
An integration layer is hundreds of structurally identical objects that differ only in their 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 a different table. Every satellite hangs off its hub by the same key treatment. The shape you chose in chapter 6 does not change this; it only changes which pattern repeats.
Hand-writing that layer produces drift, and the mechanism is worth naming precisely. Notebook twelve starts as a copy of notebook eleven with two edits, one of which gets forgotten. A column rename lands in the table that raised the ticket and misses the two downstream tables nobody remembered. Worse, every hand-written exception is a decision nobody recorded, so the one load that handles deletes differently stays invisible until it disagrees with the other forty at three in the morning. Multiply that by five hundred objects and the estate’s real design lives in the diffs between files, where no reviewer will ever find it.
Generation inverts the failure mode. When every object is rendered from the catalog, the catalog cannot drift from the code, because the code came from the catalog. The mapping you built in chapter 5 stops being documentation that describes the pipelines and becomes the source the pipelines are compiled from. What comes out the other side is a derived artifact, like a compiled binary: you can read it and you can deploy it, but you do not edit it, because the thing you edit is the model.
What is in a template framework, whatever the engine?
The word “templates” undersells what a working framework contains. Whether the engine is a vendor product, a set of dbt macros, a template engine that is yours, or the generator script your team wrote three years ago, the same five parts have to be there, and whichever one is missing will find you.
- The patterns. One template per object kind and load style: a landing table, a staging load, a satellite merge. A pattern is the design decision “how we do staging” written down once, rather than expressed forty slightly different ways across forty files.
- The conventions the patterns read. Naming rules, system-column definitions, hash definitions, data-type mappings. These are inputs to the patterns, not text buried inside them, and the next section explains why that placement decides everything else.
- The bindings. Which pattern applies to which object, decided by metadata such as this table’s layer, its entity type, and its load style, never by folder discipline. A binding you can query is a rule, while a folder convention is only a hope.
- The declared context. Exactly what a pattern is allowed to know about the world when it renders. This is the contract that makes patterns portable and testable, and it carries enough weight to be the whole subject of chapter 8.
- The proof. A way to render any fragment against real metadata, and a way to confirm the platform accepts the result. A sample is not proof; a test is.
Ask those five questions of any tool and the demonstrations sort themselves quickly. Plenty of products will show you patterns. Far fewer can show you the conventions as editable metadata, the bindings as rules, the context as a contract, and the proof as something you can run against your own catalog this afternoon.
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
Here is the placement decision that separates a framework from a pile of scripts: the conventions live in metadata the patterns read, never in the patterns themselves.
Take naming. A naming convention stored as metadata is a pattern file a few lines long: a schema pattern built from tokens like the source system, a table pattern like the schema name joined to the table name, and from those few lines every generated name in the estate falls out. Change one line and every name moves together, consistently, in the next render. Store the same convention as code and you get the same string-concatenation logic pasted into every template that names anything, so changing it becomes an archaeology project.
The same rule governs system columns, and here the stakes are higher because the failure is silent. Every layer adds columns the source never had: a load timestamp, a record source, a hash for change comparison, effective dates. A framework declares each of these once, in a catalog, with its role stated as a fact: this column is the load timestamp, this one is the hash difference. The alternative, which real tools actually ship, is inferring a column’s role by recognizing its transformation code, and that inference breaks the day someone customizes a transformation. Wrap the load timestamp in a time-zone conversion and the string no longer matches, the column’s role evaporates, and nothing errors. Roles are decisions. Store them as decisions, not as patterns to be guessed back out of code.
Figure 7.2: one pattern, three generated names. The source table Kunde, from the garden webshop of chapters 3 to 5, 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 |
Behind the persistent-staging name sit two tokens in a profile file: the schema pattern resolves the source system, and the table pattern joins the schema to the table name. That is the entire convention, and it is smaller than this paragraph. Fifty source tables produce one hundred and fifty generated names from those lines, which makes renaming the estate an edit to the file rather than a campaign.
The people part. The template author is a role, and in most teams nobody holds it, so the patterns end up being whatever the last consultant left behind. Name the owner. Then notice what generation does to change management: a pattern edit that touches five hundred objects is a decision with five hundred objects’ worth of consequence, so it deserves the same review a schema change gets. Who approves it belongs in chapter 3’s decision rights, not in a merge nobody read. Retiring a pattern demands the same discipline, because a framework must be able to say what still depends on the old one. Retire a pattern that strands even one object and you have quietly converted that object back to hand-maintained code.
Proof or drift
Every generation setup demos beautifully, because a demo renders 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 is render stability: the pattern produces the same output for the same input, usually held in place by snapshot tests. That is worth having, and it is also radically oversold, because a stable render proves only that the template did not change. It says nothing about whether the platform accepts the result. Generated code that the render suite blesses can still fail the day it meets a reserved word, a dialect quirk, or a type your mapping never covered. A green offline suite is not a green run, and a framework that cannot close that gap is asking you to discover platform rejections in production.
The second level is the one to insist on: every fragment of a pattern must be testable in isolation against real metadata, meaning your tables, your columns, and your ugly legacy names. A fictional sample context is engineered to render cleanly, and that is precisely its flaw, because a deliberately tidy fixture can never surface your gaps. The pattern that renders the vendor’s fictional Customer table perfectly may still fall over on your forty-column source with the reserved word in position three, and only a render against your catalog will say so before the platform does.
So the acceptance test for any framework, vendor-bought or homegrown, is a five-minute exercise: point one pattern at your worst real table and render it. If the framework cannot do that, its proof story is a sample, and a sample is marketing.
Own your patterns
One more question remains, and it is the one most evaluations skip: whose opinion does the generated code express, and what happens when you disagree?
Platforms are moving faster than any vendor’s template library can follow. New load syntax, new table features, and new orchestration primitives arrive on the platform’s release cycle, not your vendor’s. A fixed, sealed template set is therefore out of date by the day you adopt it, and every month after that you are waiting on someone else’s release notes to use capability you already pay your platform for. So the framework question is not whether the shipped patterns are good. It is whether you can open them, customize the one block you disagree with, and still receive the vendor’s next improvement to everything you did not touch. Fork the whole file and you own its maintenance forever; change nothing and you ship someone else’s opinion. The workable middle is surgical: override the one section, and keep tracking upstream for the rest. Applied that way, generation becomes acceleration that produces your opinion rather than the vendor’s.
The wider tooling landscape makes the same point from the opposite direction. The nearest thing the modern stack has to a prescriptive process is dbt Labs’ project-structure guide, which is genuinely disciplined but is entirely folder-and-SQL convention inside one tool. Writing on metadata-driven generation does go deeper, yet almost all of it is vendor authored and describes one product’s way. That is exactly why you need the five questions in tool-independent form: patterns, conventions, bindings, context, proof. They apply unchanged to dbt macros, to a hand-rolled generator, and to any vendor engine, and they are the difference between evaluating a framework and admiring a demo.
Metadata to capture. The naming rules as pattern files. The system-column catalog with each column’s declared role. The hash definitions. The data-type mappings per platform. The pattern bindings, queryable, never inferred from folders. And the customization points: which blocks your organization overrode, and why, so the next engineer can tell your opinion from the vendor’s.
Regardless of stack. Nothing in this chapter named an engine, because none of it depends on one. The five framework questions interrogate a homegrown script exactly as hard as they interrogate a product, and a script that answers all five is a better framework than a product that answers two.
Templates close the gap chapter 6 left open: the shape you chose now compiles from the model instead of drifting away from it. In doing so, they raise the question they cannot answer alone. A pattern renders from what it is allowed to read, and everything in this chapter quietly assumed that what it reads is complete, correct, and agreed. So what exactly does the pattern get to know? That contract, the declared context, is chapter 8.