Skip to content

dbt Conventions

Model conventions

Models follow standard prefixes reflecting their layer in the data flow.

Staging (stg_)

Modular building blocks from source data.

  • File naming: stg_{source}__{entity}.sql
  • contract: enforced: true — required on all staging models
  • Uniqueness test — required: either a single-column unique: test or dbt_utils.unique_combination_of_columns for composite keys

Intermediate (int_)

Layers of logic with clear and specific purposes, preparing staging models to join into the entities we want.

  • Folder structure: subdirectories by area of business concern
  • File naming: int_{business_concern}__{entity}_{verb}.sql
  • Business concerns: assessments, surveys, people
  • Verbs: pivot, unpivot, rollup
  • Uniqueness test — required: either a single-column unique: test or dbt_utils.unique_combination_of_columns for composite keys

Marts / Extracts (rpt_)

Wide, rich views of the entities our organization cares about, or extracts consumed by reporting tools and applications.

  • contract: enforced: true — required on all marts and extract models. These are the last stop before data reaches an external reporting tool (Tableau, PowerSchool, Google Sheets, etc.). Schema changes break downstream exposures and must be made deliberately.
  • Uniqueness test — required: either a single-column unique: test or dbt_utils.unique_combination_of_columns for composite keys

SQL conventions

  • Join on _dbt_source_project in any query that joins two CTEs built from unioned regional datasets, to prevent cross-region row matching:
left_cte._dbt_source_project = right_cte._dbt_source_project

Each union view materializes the column once — inline regexp_extract on a bare select * view, or the extract_source_project() macro otherwise; see src/dbt/kipptaf/CLAUDE.md for which form applies. Downstream models select it through rather than re-deriving it. The old union_dataset_join_clause() macro, which re-ran regexp_extract on _dbt_source_relation at every call site, was removed in #3142.

To extract a human-readable region label, use the extract_region() macro:

{{ extract_region("s") }} as region
  • No GROUP BY without aggregation — use DISTINCT instead.
  • DISTINCT requires a comment explaining why it is necessary.
  • No ORDER BY in SELECT statements — ordering belongs in the reporting layer, not in dbt models.
  • No GROUP BY ALL — never use in production models. Always list grouping columns explicitly; GROUP BY ALL obscures intent and breaks silently when upstream columns are added or removed.
  • No SELECT * in the final SELECT of rpt_ or mart models — list columns explicitly. Wildcards obscure column sourcing, create ambiguity when both sides of a join share a column name, and leave the contract declaration as the only readable spec. Pass-through CTEs (select * from ref(...)) are fine.
  • Filter conditions: ON vs WHERE — row-filter conditions on the preserved table belong in WHERE, not ON. For INNER JOIN the result is identical but intent is hidden; for LEFT JOIN, a filter in ON keeps non-matching left rows whereas WHERE eliminates them — a silent semantic change. Exception: for FULL JOIN, conditions in ON that reference only one side are intentional and cannot be moved to WHERE.
  • Timezone-aware today — use {{ var("local_timezone") }} so current_date reflects Eastern time rather than UTC:
current_date('{{ var("local_timezone") }}')
  • Removing diacritical marks — to normalize names with accented characters:
regexp_replace(normalize(name_col, NFD), r'\pM', '')
  • Time travel — query a table as it existed at a point in time:
select *
from my_table
for system_time as of timestamp('2025-08-22 23:59:59')
  • New or modified external sources — stage before building. See Staging external sources for the full command and CI requirements.

ref() and source()

Use ref() to reference other dbt models; use source() for raw source tables declared in a sources: YAML file:

{{ ref("stg_amplify__benchmark_student_summary") }}
{{ source("amplify", "src_amplify__benchmark_student_summary") }}

BigQuery scalar functions

Shared UDFs in the functions dataset:

Function Returns
functions.current_academic_year() Current academic year integer
functions.date_to_sy(date_col) Academic year of a given date
functions.region_join(left_col, right_col) Deprecated — no call sites remain; join on _dbt_source_project 
select
    functions.current_academic_year() as academic_year,
    functions.date_to_sy(att_date) as att_academic_year,
from my_table

functions.region_join is the SQL-UDF twin of the retired union_dataset_join_clause macro — it compares two _dbt_source_relation values, the predicate this page now tells you not to write. It still exists in the functions dataset but has no call sites in this repo.

Model properties file

Every model must have a corresponding [model_name].yml properties file. Write it by hand — there is no scaffold generator in this repo. Column names and types come from INFORMATION_SCHEMA.COLUMNS on the built relation:

select column_name, data_type
from `teamster-332318`.<schema>.INFORMATION_SCHEMA.COLUMNS
where table_name = '<model_name>'
order by ordinal_position

The shape:

models:
  - name: model_name
    config:
      contract:
        enforced: false # keep false while building; remove this line before merging
    columns: # required for contracted models
      - name: column_name
        data_type: string | int64 | date | ... # required for contracted models
        data_tests: # optional column-level tests
          - not_null
          - accepted_values:
              values: [...]
    data_tests: # optional model-level tests
      - dbt_utils.unique_combination_of_columns: # use for composite keys
          arguments:
            combination_of_columns:
              - column_a
              - column_b

Exposures

Every external tool that consumes our data must have a dbt exposure defined in the consuming project (typically src/dbt/kipptaf/models/exposures/). Exposures make the dependency graph explicit and power Dagster asset lineage.

All exposures require a name, label, type, owner, depends_on (listing every model the tool uses), and a url linking to the external tool/workbook/sheet:

exposures:
  - name: exposure_name_snake_case
    label: Human Readable Title
    type: dashboard | notebook | analysis | ml | application
    owner:
      name: Data Team
    depends_on:
      - ref("rpt_tableau__some_model")
      - ref("another_model")
    url: https://... # optional
    config:
      meta:
        dagster:
          kinds:
            - tableau # or: googlesheets, powerschool, etc.
            - ... # additional kinds

Tableau dashboards that refresh on a schedule must additionally include the Tableau workbook LSID and a cron_schedule under asset.metadata. Workbooks without a scheduled refresh can omit the asset block:

config:
  meta:
    dagster:
      kinds:
        - tableau
      asset:
        metadata:
          id: <tableau-workbook-lsid-uuid>
          cron_schedule: "0 7 * * *" # omit entirely if no scheduled refresh