Skip to content

Library · Data Practice OS

Pipeline code: explicit vs metadata-driven

Reference guide · Published · Source reviewed

Every data team ends up asking whether to generate pipelines from YAML or write each one by hand. The useful answer is neither everywhere: split by layer. Generate the work that has the same shape for every source, and hand-write the logic.

Source and status. a platform team Options paper: pipeline transformation code - explicit models vs metadata-driven generation (draft, 26 Oct 2026; the team has not agreed the recommendation). The scores are the platform team's.

The options the platform team compared

Option Score (max 63)
0. No written standard: each repo keeps its own pattern 34
1. Explicit code for each table (hand-written loads and models) 51
2. Metadata-driven everywhere (model files are one-line macro calls) 31
3. Split by layer 54

The margin between 1 and 3 is small. If traceability and debugging carry more weight, option 1 wins. Option 2 scored lowest because a reviewer reads YAML and macro code instead of the SQL that runs, and errors are hard to trace.

Split by layer

Layer Approach
Raw table definition Once, in the source YAML: columns, types, load mode and file format
Raw load One load operation per pipeline (dbt run-operation) with a load mode per source. Never in model pre-hooks
Source models (SRC) Typed tables: generated from the YAML and committed, with a CI check that they still match. Documents (VARIANT/JSON/XML): hand-written parse
Logic (STG, INT, DW) Hand-written SQL. Business rules as data in seeds or vars
Macros Only for a repeated expression or an operation, never a whole model

The 13 proposed conventions

  1. Define each raw table once, in the source YAML.
  2. Load with one operation per pipeline. Log each file and its result. Load with RAW_LOADER_ROLE; create or alter tables with an owner role (Naming conventions).
  3. One SRC model per raw table, and only SRC reads raw. No SELECT *, joins or filters.
  4. Generate typed source models from YAML and commit them. Parse documents by hand.
  5. Business logic lives in hand-written STG, INT and DW models, not in macros.
  6. Keep rules as data, in seeds or vars.
  7. A macro is for a repeated expression or operation, not a model.
  8. One file per macro, named the same, documented in _macros.yml.
  9. Make each UDF from the project, in one place, with a test and no hard-coded database.
  10. When a pull request changes a macro or UDF, build and test every model that uses it.
  11. Write repeated data checks as generic data tests.
  12. Mark prototype and sandpit repos in the README.
  13. Folders source/, stage/, intermediate/, semantic/ map to schemas SRC, STG, INT, DW.

Review shared code with CODEOWNERS and branch protection. Allow third-party dbt packages only from an approved list with pinned versions, agreed with Security and DevOps.

Measure it, don't argue it

The platform team proposed checks the team can run in CI with dbt_project_evaluator:

Measure Check Target
Every model has a description fct_documentation_coverage 100%
Every model tests its grain (not_null + unique) fct_missing_primary_key_tests 0
Only source models read sources fct_direct_join_to_source 0
A reviewer finds one table's SQL in one file, and dbt compile shows what runs Sample review Yes
A change to a repeated rule touches one file Count files for a sample change 1
An engineer finds planted faults (misspelt column, wrong load mode, bad cast) Time to find each, per option Team sets

The planted-fault test is a cheap way to settle "is the generated version harder to debug?" with evidence. Check the package version against your dbt version: the rule names differ between the 1.x and 2.x branches.

Batch controls (from the GEN rebuild, proposed)

The checks that stop a batch: all expected tables present, and unique natural keys within the batch. Pending checks count as not passed, not as passed: row counts against the source, file hashes, and same columns as the previous batch. Freshness thresholds on LOADED_AT are set by Data product tier.

Not when

  • One small source with a few tables: hand-write it (option 1). Generation pays back across many similar sources.
  • Do not let the load operation grow into a framework with business logic in it. That is convention 7's job to prevent.

Related: dbt project standards, Data quality and observability, Data contracts.

Source links: Data Practice reading list.