dbt / Intermediate: history and controlled workflows / Lesson 10
Seeds, reference data and source freshness
Govern small static lookup data and interpret freshness signals without treating either as a complete ingestion system.
On this page
Lesson goals
- Choose seeds for bounded reviewed reference data.
- Distinguish freshness timestamps, file versions and business completeness.
A seed is versioned data that becomes a relation
A small list of approved loan-state codes can live in a reviewed CSV seed. It is data, not a Jinja template or a general replacement for raw event ingestion. A seed command loads that file into a target relation and therefore performs database work. Keep file size and change frequency appropriate for source control, and exclude personal borrower records or credentials. A changed lookup can alter historical reports if joins always use its current values, so review semantic changes as carefully as model SQL. Version control records edits but does not decide which business period a code meaning applies to.
Preserve identifier spelling and one-row meaning
The synthetic CSV has one row per state_code and a display label. A code containing leading zeros would need a string type instead of numeric inference. Duplicate codes would multiply a loan join just like duplicate rows in any other dimension. Empty cells, quoting and case rules should be documented rather than inferred from a spreadsheet display. The two-row fixture is parsed locally as CSV, which proves its text structure only. No seed table is created, loaded or compared with a warehouse, and no engine type-inference behavior is claimed.
state_code,state_label
open,Open loan
returned,Returned loan
A complete synthetic two-row CSV. Labels are controlled data values and remain identical across course translations.
Seed configuration still depends on the target
The project fragment supplies a varchar type for state_code, assuming the selected target recognizes that type and width. Configuration scope must match the project and seed name. A type override prevents a particular inference accident but does not validate code uniqueness, allowed labels or semantic history. Type changes can require a supported rebuild path rather than a casual append. Keep the seed’s tests and downstream consumers in the change review. Do not run seed or full-refresh commands on a configured environment merely to inspect this example; all configuration remains non-runnable.
seeds:
library_analytics:
loan_state_codes:
+column_types:
state_code: varchar(16)A target-dependent type override for a fictional seed. It does not load data or establish a key constraint.
Freshness measures a clock signal rather than all data quality
A source freshness check can compare a configured loaded-at signal or supported metadata timing with thresholds. A recent maximum timestamp does not prove all branches delivered, that row counts are complete or that source clocks are honest. One newly loaded row can mask a missing historical partition if the check only observes a global maximum. Future timestamps and time-zone interpretation can distort the signal. Define which clock represents ingestion and how empty sources are treated. Freshness configuration syntax and support vary across generations and adapters, so use the selected documentation rather than copying an unrelated platform example.
Combine reference governance with source delivery evidence
Test seed key uniqueness and approved values, then reconcile joins against the modeled loan population. For raw sources, track expected branch or partition coverage and accepted/rejected counts alongside freshness. Retain owners for both lookup changes and source incidents. A source that is fresh but incomplete should not silently publish a normal circulation report. This course invokes no source freshness or seed command and contacts no warehouse. Local checks cover only CSV parsing, controlled-code uniqueness and a synthetic clock comparison, without claiming a complete data-quality or operational readiness test.
Common errors and fixes
A seed CSV is used as an unbounded event ingestion channel
- Why it happens
- Small governed reference data was confused with high-volume loading.
- How to fix it
- Use the approved ingestion system and retain seeds for suitable bounded data.
- Prevent it next time
- Review seed size, sensitivity and update frequency.
A fresh source is declared complete
- Why it happens
- A timestamp threshold replaced coverage and row reconciliation.
- How to fix it
- Check expected partitions, branches and counts separately.
- Prevent it next time
- Publish freshness and completeness as different signals.
A duplicate seed code inflates report counts
- Why it happens
- Reference-key uniqueness was assumed from a small CSV.
- How to fix it
- Validate the code key before joining and reconcile model counts.
- Prevent it next time
- Attach key tests to governed lookup relations.
Check your understanding
1. Does a maximum loaded-at value from today prove yesterday’s branches all arrived?
Show worked solution
No. It only supports the configured clock comparison. Check branch or partition coverage and source-row accounting separately. A single recent row can coexist with large missing populations, so the report’s acceptance policy must include completeness evidence.
2. What does locally parsing the two-row seed prove?
Show worked solution
It proves the CSV structure and, with an explicit check, uniqueness of those two controlled codes. It does not prove the adapter’s column types, database permissions or a successful seed load. Keep those execution boundaries clear in validation reports.
Key takeaways
- Seeds are governed data changes with real downstream effects.
- Freshness, completeness and correctness are distinct evidence.