dbt / Basic: project and model foundations / Lesson 3

Model grain, types and business SQL

Define one-row meaning and explicit data interpretation before turning loan records into reusable analytics.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

State keys, units, null rules and temporal meaning for a model.
Keep SQL-dialect and database behavior separate from dbt templating.

Name the fact represented by one row

A fact-loan model can represent one loan lifecycle, one loan event or one loan-day. Those grains lead to different keys and measures. Counting rows without naming the grain can turn an event history into an inflated loan count. The library’s chosen lifecycle model uses loan_id as the intended business key and retains borrowed_at and returned_at. That intention needs actual duplicate and missing-key checks; a filename beginning fct_ enforces nothing. Keep the rule beside the SQL and documentation so later joins, incremental updates and tests all refer to the same fact.

Interpret fields deliberately at the source boundary

The staging fragment preserves identity and derives a controlled state from the presence of returned_at. It assumes that NULL means not yet returned under the source contract, not a failed parse. If invalid timestamps were converted to NULL earlier, this expression would misclassify them as open loans. Preserve parsing outcomes separately before applying business logic. SQL types, quoting, time zones and date arithmetic belong to the selected database dialect and adapter context. dbt rendering a cast does not make that cast portable or prove the warehouse accepted it.

jinja Illustrative technical example; logical results are reasoned, not engine-tested.

select
  loan_id, branch_id, borrowed_at, returned_at,
  case when returned_at is null then 'open' else 'returned' end as loan_state
from {{ ref('stg_loans') }}

This assumes a validated timestamp-presence contract and one intended row per loan. It is not an engine-tested universal SQL dialect example.

A reference join can multiply the modeled fact

Joining loans to several branch-history rows can repeat every loan even when all identifiers look legitimate. Select the applicable branch version or join on the correct effective-time rule before summing measures. A left join preserves unmatched loans only if later predicates do not reject their NULL reference fields. Decide whether a missing branch is rejected, retained as unknown or excluded intentionally. These are data-model decisions, not choices dbt makes automatically. A graph edge from ref explains build order but does not certify key uniqueness or safe cardinality.

Aggregate at a declared reporting grain

The report fragment groups lifecycle rows by branch and uses conditional counting for open loans. COUNT(CASE ... THEN 1 END) ignores nonmatching NULL expressions, while COUNT(*) defines the full input population. A branch with three intended loan rows and two open rows should produce total_loans=3 and open_loans=2. This expected fixture result depends on the lifecycle grain and prior validation. If source events were joined without reduction, both counts could be wrong while their ratio remained plausible. Reconcile identities and totals rather than accepting a convenient percentage alone.

jinja Illustrative technical example; logical results are reasoned, not engine-tested.

select branch_id, count(*) as total_loans,
 count(case when loan_state = 'open' then 1 end) as open_loans
from {{ ref('fct_loans') }}
group by branch_id

For a fixed branch fixture with three lifecycle rows and two open states, the logical counts are3 and2. Row display order is unspecified without ORDER BY.

Model correctness needs quantity and identity evidence

A useful acceptance record states source scope, key checks, rejected records, time convention and measure reconciliation. Test zero-row inputs, NULL branch identifiers, duplicate histories and a return correction arriving late. An empty model can pass some data tests vacuously, so expected-population checks remain separate. Avoid hiding errors with DISTINCT or COALESCE until the domain meaning is established. This course executes no compiled query or model. Its tiny local relation models verify only stated counts and cardinality reasoning, with no claim about optimizer behavior, data types or database constraints.

Common errors and fixes

A row count is labelled a loan count without a grain

Why it happens: Events or join duplicates were treated as one lifecycle each.
How to fix it: Declare the fact key and reconcile contributing identities.
Prevent it next time: Review grain before adding metrics or joins.

Invalid return times become open loans

Why it happens: A parse failure was collapsed into the same NULL as a legitimate missing return.
How to fix it: Preserve parse outcomes and quarantine invalid timestamps.
Prevent it next time: Test missing and malformed source fields separately.

DISTINCT hides a duplicate reference defect

Why it happens: Output deduplication replaced a justified relationship rule.
How to fix it: Repair source-version selection and validate the join key.
Prevent it next time: Keep adversarial many-match fixtures in model tests.

Check your understanding

1. Three lifecycle rows include two open loans. What should the branch report count?

Show worked solution

It should show3 total loans and2 open loans under the stated validated grain. Verify the contributing loan identifiers and branch mapping, not only the ratio. If those rows actually represent events, redesign the model or measure before accepting the result.

2. Does a successful Jinja render prove the database supports the SQL cast used in a model?