dbt Conventions

This page is built from the rule files Claude reads (.claude/rules/dbt-architecture.md, .claude/rules/dbt-marts.md, .claude/rules/dbt-yaml.md, and .claude/rules/dbt-sql.md), so people and Claude review against the same text. Cite rules by ID (A2, S10) in review.

A rule changes through a PR to its rule file. If you disagree with a rule in review, open an issue; the feature PR follows the current rule.

The standard applies to the lines a PR adds or changes. Old violations elsewhere in a model are left alone, and existing models are not swept.

Architecture

Layers

Data flows from source to consumer through these layers. Each layer has 1 job.

Layer	Does	Lives in (kipptaf)
`stg_`	1 source table: rename, cast, filter soft-deletes, dedup source duplicates, unpivot a source that arrives wide	`<source>/staging/`
Source `int_`	Logic within 1 source system	`<source>/intermediate/`
Domain `int_`	Cross-source business entities and identity resolution	Domain folders (A11)
Marts	`dim_`, `fct_`, `bridge_`: the core every consumer reads	`marts/`
`rpt_`	Thin shaping of marts for 1 tool, including that tool's pivots	`extracts/<tool>/`

Source packages hold stg_ and source int_ only. District projects hold package config, district-only source models, and rpt_ wrappers over kipptaf extracts. Cross-region business logic lives only in kipptaf.

Allowed edges

Model	May read
`stg_`	`source()`, except a district `int_` or `base_`
Source `int_`	`stg_`, source `int_`, and `snapshot_` of a model in the same source folder; `source()` for district union wrappers
Domain `int_`	`stg_`, any source `int_`, domain `int_`, `snapshot_`
Marts	Domain `int_`, other marts
`rpt_`	Marts, domain `int_`; in a district project, its kipptaf `rpt_` through `source("kipptaf_extracts", …)`; a wrapper that reconciles against its target system (kippmiami `rpt_focus__*`) may also read that system's `stg_`
Exposures	`rpt_`, marts; Cube reads marts only

base_ models count as int_ until #2541 renames them; classify each as source or domain by what it reads, as for any int_.

Rules

A1. Read only the layers your layer allows

A model reads only what the Allowed edges table lists for its layer. An rpt_ never reads another rpt_, except a district wrapper over its kipptaf counterpart.

Why: when every layer reads from its own place, logic has 1 home and a reviewer knows where to look for it.
Good: rpt_tableau__<dashboard> reads fct_student_attendance_enrollment_daily.
Bad: rpt_tableau__<dashboard> reads stg_powerschool__attendance.
Enforced by: dbt-layer-check, with a baseline of existing violations.

A2. An `rpt_` reads a mart when 1 covers the entity

Read a domain int_ only while no mart covers the entity, and link an issue for the missing mart in the PR.

Why: marts are the 1 shared definition; an rpt_ built on an int_ rebuilds logic the mart already owns.
Good: rpt_gsheets__<roster> reads dim_staff.
Bad: rpt_gsheets__<roster> reads int_people__staff_roster when dim_staff has the columns.
Enforced by: review.

A3. An intermediate either builds or reshapes

A build model keeps a grain and assembles columns (joins, decodes, cleanup); its name has no suffix. A reshape model changes the grain; its name ends in _pivot, _unpivot, _rollup, _scaffold, or _union. A crosswalk is a build model whose entity is a mapping. Existing names stay.

Why: the name tells a reader what happens to the grain before they open the file.
Good: int_people__staff_roster (build), int_kippadb__standardized_test_unpivot (reshape).
Bad: int_<concern>__scores_unpivoted.
Enforced by: dbt-layer-check (new models).

A4. 1 model per grain

Each grain has 1 model within a source folder, and 1 across the domain folders. A reshape model is the canonical model for its output grain. Before adding an intermediate, check whether a model already has that grain; extend it instead of adding a sibling.

Why: 2 models at 1 grain drift apart, and the next author cannot tell which is right.
Good: a new staff column goes into int_people__staff_roster, not a second model at 1 row per staff member.
Bad: int_<source>__scores and int_<source>__scores_clean in 1 source folder at the same grain.
Enforced by: review; dbt-layer-check warns on a shared uniqueness grain.

A5. Dedup only in `stg_` or source `int_`

Why: a dedup further down hides an upstream bug instead of fixing it.
Good: dbt_utils.deduplicate in a staging model over a source that resends rows.
Bad: dbt_utils.deduplicate on the PK at the end of a mart.
Enforced by: dbt-layer-check (lines the PR changes) errors on dbt_utils.deduplicate, qualify row_number(), or a row_number() alias the model filters to = 1. That includes an edit inside a pick an older model already has. A pick that is the business rule (latest test per student) declares standard_exempt: {A5: <reason>}.

A6. Long in the core, wide at the edge

Intermediates and marts stay long: 1 row per fact at its natural grain. A pivot to a wide layout for 1 tool lives inside that tool's rpt_. An int_ pivot is fine when its output grain is reused.

Why: long data serves every consumer; a wide layout serves 1.
Good: a fact with 1 row per student, test, and subject.
Bad: int_deanslist__students__custom_fields__pivot read only by rpt_tableau__community_service.
Enforced by: review.

A7. Identity in the domain `int_`; keys through their macro

Decide which source records are the same person or thing in the domain int_. When an entity has a key macro ({{ student_key(...) }}), hash its surrogate key in a mart only through that macro, never with a direct generate_surrogate_key call.

Why: 1 macro holds each key's inputs, so a key cannot drift between the marts that hash it.
Good: {{ student_key("s.student_number") }} as student_key.
Bad: {{ dbt_utils.generate_surrogate_key(["s.student_number"]) }} as student_key.
Enforced by: dbt-layer-check (lines the PR changes).

A8. Exposures read only `rpt_` and marts

A tool reads an rpt_ or a mart. Cube reads marts only.

Why: the consumer layer absorbs schema change; a tool on an int_ breaks when the int_ changes.
Good: a Tableau exposure depends_on: ref("rpt_tableau__<dashboard>").
Bad: a Tableau exposure depends_on: ref("int_topline__student_metrics").
Enforced by: dbt-layer-check, with a baseline.

A9. No `select *` in the final select of an `rpt_`, a mart, or a view

The view rule covers every layer. A dbt_utils.union_relations wrapper is exempt, because its star is the column superset and it rebuilds on its own.

Why: the column list is the consumer's contract; a star hides it. A view also fixes its columns when it is created, and a view whose own SQL never changes is never recreated, so a star view never picks up a column added upstream.
Good: select student_key, academic_year, … from final.
Bad: select * from final; select u.*, c.days_in_term from u left join c in a view.
Enforced by: dbt-layer-check (lines the PR changes).

A10. Join unioned regional models on `_dbt_source_project`

When 2 models both union regional datasets, add a._dbt_source_project = b._dbt_source_project to the join.

Why: ids repeat across regions; without it, rows match across regions.
Good: on a.student_number = b.student_number and a._dbt_source_project = b._dbt_source_project.
Bad: on a.student_number = b.student_number between 2 cross-region unions.
Enforced by: review.

A11. Domain folders are listed

A kipptaf folder holding domain int_ models is listed in DOMAIN_FOLDERS in scripts/check_dbt_standard.py. Every other int_ is a source int_. Adding a domain is normal: add the folder and its list entry in 1 PR.

Why: the list is how the check tells a domain int_ from a source int_. It lives in the script, not in dbt_project.yml, because a +meta tag marks every model in the folder modified and rebuilds them all in CI.
Good: "students" in DOMAIN_FOLDERS.
Bad: a new enrollments/ folder of cross-source models missing from the list.
Enforced by: dbt-layer-check.

Marts

Dimensional marts (star schema) are consumed by Cube, Tableau, and the rpt_ layer. Bridge models (bridge_*) are factless facts that link 2 or more dimensions many-to-many.

Column-naming rubric

Applied to every mart column a PR adds, renames, or changes.

R1. Strip source-system prefixes/names (powerschool_, adp_, deanslist_, focus_, finalsite_) unless disambiguating unified columns. Source-agnostic naming is load-bearing — the mart surface must not change when Focus replaces PowerSchool or Finalsite replaces PowerSchool enrollment.
R2. No KIPP-specific language (teammate, employee_number, microgoal, dcid, oid, lep).
R3. Boolean fields use is_ / has_ prefix. On fact tables, countable 0/1 flags may use INT64 rather than BOOLEAN so SUM(is_x) / AVG(is_x) read naturally without casting. Weighted non-binary measures drop is_ (e.g. present_weight).
R4. Dates end _date; timestamps end _timestamp.
R6. Ed-Fi Unified Data Model nomenclature is the default for IDs, entity names, standard attributes. Deviate toward plain English for awkward descriptors.
R7. Keep ubiquitous acronyms; spell out internal ones. Ubiquitous, user-facing acronyms (gpa, ada, fte, etc.) stay verbatim; niche source-system acronyms (dcid, oid, lep) get spelled out or removed.
R8. Plumbing removed from mart SELECTs — see definition below.
R9. Remove dimension attributes reachable via FK. Includes natural keys that duplicate a surrogate FK, and date columns that duplicate a date-key FK.
R10. Entity qualification. Qualify a descriptive column with the model's entity prefix only when removing it creates a real downstream-join ambiguity (e.g. full_name on every person dim — not student_name). Otherwise default to unqualified. Don't entity-qualify bare reserved-word columns to satisfy BI field-list readability — Cube title: aliases BI presentation. Evaluate R10 / reserved-word rename decisions against raw-SQL ergonomics only.

Degenerate-dim rule

Text columns (e.g. incident_type, consequence_type) drop _code / _name suffixes. Exception: when a code AND a human name coexist in the same table (e.g. term_code + term_name, status_code + status_name), both suffixes stay. Under R10, both halves also keep their entity prefix so the pair stays consistent.

Plumbing definition

Removed from all mart SELECTs:

_dbt_source_relation (dbt internal, union-model metadata)
Source-system internal row IDs used only for upstream joins (DeansList lid, PowerSchool dcid, Amplify record IDs, iReady submission IDs, etc.)
Any column whose only historical use was as a join key in intermediate layers

Plumbing remains in staging/ and intermediate/ — only stripped from the mart SELECT.

Strict-chain traversal

Facts, bridges, and child dims FK to their direct parent(s) only; deeper dimensional context is reached by traversing the FK chain, not by denormalizing it into the row.

No diamond paths. A fact should never have two FK routes to the same ultimate dim. If a fact needs attributes of a deep dim (e.g. dim_regions from a staff observation), traversal goes through the chain (fct_staff_observations → dim_locations → dim_regions), not via a direct region_key on the fact.
Parent-fact inheritance. A child fact that FKs to a parent fact inherits the parent's dimensional context and does not repeat it. Example: fct_behavioral_consequences carries behavioral_incident_key only; student / location / region come through the parent, not duplicated here.

Watch for common diamond triggers: a new region_key on a fact that already FKs to a location or staff-work-assignment; role-playing date FKs pointed at the same dim_dates row without a role qualifier (created_date_key vs solved_date_key, not both date_key). If you find yourself adding an FK to avoid a join, the chain is probably already there — use it instead.

PK / FK / date column shapes

Primary key: <entity>_key, hashed through the entity's key macro (A7), or generate_surrogate_key([...]) for an entity with no macro yet.
Foreign key: <target>_key when unambiguous; <role>_<target>_key when multiple FKs to the same target coexist (e.g. submitter_staff_key + assignee_staff_key on fct_support_tickets). Never expose the raw natural key alongside its surrogate (R9).
FK constraint form: declare foreign keys with the ref-aware to: ref(...) + to_columns: form (dbt 1.9+) at the column level for single-column FKs — not model-level expression: ref(...), which is free text that doesn't capture the ref dependency. View marts only: a table mart carries no outgoing FK constraints and records the edge under config.meta.foreign_key instead.
Date FK (_date_key): raw DATE value matching dim_dates.date_key, not a hash. Never also expose the same date as a degenerate _date column next to its _date_key (R9).

Tests on marts and `rpt_` models

Marts and rpt_ models carry only error tests and unit tests. A warning on a model a dashboard or feed reads is one nobody acts on, so each test either fails the build or moves somewhere it can be acted on.

Until a folder has its own +severity: error block in dbt_project.yml, set config: severity: error on each new test there. An error test lands only after its query returns 0 rows in prod.

Sort each test by what it guards:

Grain: the backstop on every mart and rpt_ model. Add not_null on natural-key grain columns (unique skips nulls) unless they are non-null by construction; a generate_surrogate_key primary key needs only unique. If a grain test fails in prod on a source defect that waits on a fix elsewhere, set severity: warn on that one test with a TODO(#N) comment, and still flip the folder. A kipptaf rpt_ model listed on #5990 has no grain test yet: add one in any PR that modifies it, and check it off there.
A relationships test from a mart or rpt_ model to another model is not a regression test. Key drift is the key macros' job (A7). When two models must cover the same population, have both read one shared upstream model. A test comparing two separately built models also catches the gap between their rebuild times. Real orphans become an upstream warn. A column relationships test on the model that feeds a join, before the join drops unmatched rows, is still the right FK check. Exception: an outbound feed whose rows reference another feed's file (ParentSquare, Clever) keeps its file-to-file check, because the target system rejects a dangling id.
The model's own logic: a unit test.
A promise an upstream model makes: an error test on that model.
Source data: a test on the earliest model that sees it. Either a warn with a downstream defense and a description naming the source system, what is wrong, and what to change there; or, when no business rule can handle it, an error gate. A new gate lands as warn and flips after it passes in prod. A failing test skips only downstream tables; a view between a gate and its consumers still reads the bad rows.
Build timing: delete it.

SQL style

Our base is dbt Labs' How we style our SQL: lowercase, trailing commas, explicit inner join / left join, and CTEs over subqueries. sqlfmt and sqlfluff (.trunk/config/.sqlfluff) enforce the formatting. The rules below are where we add to or differ from that guide.

Where we differ from dbt Labs

Rule	dbt Labs	Us	Why
Import CTEs	Recommended	Banned (S4)	A CTE with no logic; `ref()` in `from` reads the same
`qualify`	Allowed	Banned (S1)	Not ANSI SQL; a ranked column plus `where` does the same
`group by 1, 2` / `group by all`	Prefers `group by 1, 2`	Banned (S1, S2)	Both break silently when the select list changes
Table aliases	Full names, no initials	Short initials (S16)	The repo norm; unique in the query

Rules

Common remedy for S3, S8, S9, and S10: derive the expression as a named column in an upstream CTE, then reference the plain column.

S1. ANSI SQL or a dbt macro first

Use BigQuery-only syntax only when it does something standard SQL cannot. qualify, group by all, and union all corresponding never pass that test.

Why: standard SQL reads the same to everyone and ports between engines.
Good: row_number() over (...) as rn in 1 CTE, where rn = 1 in the next.
Bad: qualify row_number() over (...) = 1.
Enforced by: sql-banned-syntax.

S2. Name every `group by` column

Why: positional grouping breaks silently when the select list is reordered.
Good: group by student_number, academic_year.
Bad: group by 1, 2.
Enforced by: sqlfluff AM06.

S3. No subqueries against tables or CTEs

Write a CTE and join it. The 1 exception is a scalar aggregate over unnest of an array, which is row-local.

Why: a CTE has a name and can be read and tested on its own.
Good: (select min(x) from unnest([d1, d2, d3]) as x) as earliest_date.
Bad: where student_number in (select student_number from enrolled).
Enforced by: sqlfluff ST05 in from and join; review in where.

S4. No pass-through import CTEs

Reference ref() and source() directly in from and join. Every CTE does real work.

Why: a CTE that only renames a ref adds a hop and no logic.
Good: from {{ ref("stg_powerschool__students") }} as s.
Bad: students as (select * from {{ ref("stg_powerschool__students") }}).
Enforced by: review.

S5. No `order by` in models

Why: ordering belongs to the tool that displays the data.
Good: no order by in the final select.
Bad: order by student_number at the end of a model.
Enforced by: review.

S6. Order the select list by complexity

Our house order, with no interleaving:

Plain columns, grouped by source table in join order, with a blank line between tables
Constants and literals
Simple functions (coalesce(...), simple if(...))
Nested functions
Logicals (if(condition, true, false))
Case statements
Window functions (row_number() over (...))

When a select reads from 1 table or CTE, do not prefix columns with an alias.

Why: a reader finds the plain columns first and the logic last.
Good: plain columns, then coalesce(...), then case ... end.
Bad: a case between 2 plain columns.
Enforced by: review.

S7. sqlfluff ST06 select order

sqlfluff's own rule, separate from S6: wildcards, then plain columns, then calculations. It treats cast() as a plain column.

Why: lint consistency.
Good: cast(x as int64) as x before date(y) as y_date.
Bad: date(y) as y_date before cast(x as int64) as x.
Enforced by: sqlfluff ST06.

S8. At most 1 level of function nesting

Aggregates passed as direct arguments do not count.

Why: nested calls hide intermediate values a reader needs to check.
Good: if(coalesce(x, y) > 0, 'a', 'b'); round(safe_divide(sum(a), sum(b)), 2).
Bad: if(coalesce(cast(x as int64), 0) > 0, 'a', 'b').
Enforced by: review.

S9. Cast early, once, with an alias

Cast in staging or where the raw value first appears, as a named column. Never nest cast() inside another function.

Why: 1 typed column downstream; an unaliased cast() is named f0_ by BigQuery.
Good: cast(student_id as string) as student_id in staging.
Bad: date(cast(entry_ts as timestamp)) in a mart.
Enforced by: review.

S10. `where` and `on` hold only plain comparisons

A filter in where, or 1-sided in on, compares a column to a literal, a variable, or another column. Two kinds of logic become a named column upstream instead: a calculation on a column, and a rule built from several conditions (any or, or 3 or more literal comparisons on one table). Name a rule column with is_ (S14). Literals, {{ var(...) }}, and current_date(...) on the other side are fine; so are expressions that combine both join sides.

Why: the join or filter reads as keys plus a named condition, and the rule has 1 home where it can be tested.
Good: where is_enrolled; and py.is_annual_state_test.
Bad: where date_diff(exit_date, entry_date, day) > 0; and ((py.score_source = 'state_nj' and ...) or (...)).
Enforced by: review.

S11. Row filters on the preserved table go in `where`

For a left join, a filter in on keeps non-matching rows. Exception: a full join condition on 1 side stays in on.

Why: on decides matches; where decides rows.
Good: left join t on a.id = t.id where a.is_active.
Bad: left join t on a.id = t.id and a.is_active.
Enforced by: review.

S12. `distinct` only for grain projection

Use distinct for a grouping with no aggregate, or when every column is fixed by the partition key; annotate it grain projection, not dup-masking. Never use it to hide duplicates.

Why: a masking distinct hides which row was meant.
Good: select distinct student_number, academic_year with the annotation.
Bad: select distinct * after a join that fans out.
Enforced by: review.

S13. Half-open intervals for date ranges that touch

When ranges can share a boundary date, use >= start and < end, not between.

Why: between matches both ranges on the shared date and fans out.
Good: enr.entrydate <= cc.dateenrolled and enr.exitdate > cc.dateenrolled.
Bad: cc.dateenrolled between enr.entrydate and enr.exitdate across enrollment stints.
Enforced by: review.

S14. Booleans are `is_` / `has_` columns

Convert to Y/N text only inside an rpt_ whose tool needs it. A fact's countable flags may be int64 0/1 instead (mart rubric R3).

Why: a boolean filters and aggregates directly.
Good: is_enrolled (bool).
Bad: enrolled_flag with values 'Y' / 'N' in an intermediate.
Enforced by: review.

S15. `if()` for 1 condition, `case` for 2 or more

Why: each form reads best at its own size.
Good: if(score >= 70, 'pass', 'fail').
Bad: nested if(a, 'x', if(b, 'y', 'z')).
Enforced by: review.

S16. Short table aliases, unique in the query

Derive them from the model name.

Why: short aliases keep joins readable; the repo already uses them.
Good: int_extracts__student_enrollments as e.
Bad: as t1, or 2 tables both aliased s.
Enforced by: review.

S17. `union all` branches list the same columns in the same order

Pad a missing column with cast(null as <type>) as <col>.

Why: BigQuery matches branches by position; 2 same-typed columns can swap silently.
Good: each branch selects student_number, academic_year, score.
Bad: select * from a union all select * from b.
Enforced by: review.

S18. Comments say only what the line cannot show

Rationale and background go in the model's YAML description:.

Why: SQL comments drift; YAML descriptions are published with the model.
Good: -- source resends rows on retry.
Bad: a paragraph explaining what the model is for.
Enforced by: review.

S19. Avoid `full join`

Why: every column can be null from either side, so each key and attribute needs a coalesce, and a key that differs between the sides silently splits 1 entity into 2 rows.
Good: the rows come from 1 named set, such as a driving model or a union all.
Bad: from a full join b on ..., then coalesce(a.x, b.x) for every column.
Enforced by: review.

S20. A literal that encodes a business rule gets a comment

A cutover year, threshold, or id in logic gets a one-line comment naming what it is. A value that changes every year belongs in a var() instead.

Why: a bare 2026 cannot tell a reader whether it is a cutover, a start year, or a value someone forgot to roll over.
Good: -- Miami's Focus cutover year. above where syear >= 2026.
Bad: a bare where syear >= 2026.
Enforced by: review.

Review rubric

Reviewers, human or Claude, check these and cite the rule ID:

Grain has a uniqueness test, and 1 model owns it (A4).
Dedup sits in the right layer; distinct is not masking duplicates (A5, S12).
The model sits in the right layer and is the right kind (A1, A3).
An rpt_ reads a mart when 1 exists (A2).
Join types are right, with no full join; filters sit in on or where correctly (S10, S11, S19).
Date-range joins are half-open where ranges touch (S13).
Nested logic is split into named columns past 1 level (S8, S9).
Comments say only what the line cannot show, and every business-rule literal has one (S18, S20).

Reference

Required config per layer

stg_, marts, and rpt_ models set contract: enforced: true. Marts and rpt_ models are the last stop before data reaches an external tool (Tableau, PowerSchool, Google Sheets), so a schema change breaks downstream exposures and must be deliberate.
Every model has a uniqueness test: a single-column unique: test or dbt_utils.unique_combination_of_columns for a composite key.
Exceptions: a kipptaf stg_ union view over district staging, and a thin district rpt_ wrapper over a kipptaf extract, carry no uniqueness test; it lives on the model they wrap. The district wrapper still enforces its contract, and a kipptaf union view enforces one only where its own properties file sets it.

Region labels

A10 covers joining unioned regional models on _dbt_source_project. Each union view materializes that column once; see src/dbt/kipptaf/CLAUDE.md for which form applies. For a human-readable region label, use the extract_region() macro:

{{ extract_region("s") }} as region

Handy SQL

Timezone-aware today, so current_date reflects local 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, to 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 must be staged 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

select
    functions.current_academic_year() as academic_year,
    functions.date_to_sy(att_date) as att_academic_year,
from my_table

functions.region_join also exists in the dataset. Don't use it; join on _dbt_source_project (A10).

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 also 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

dbt Conventions

Architecture

Layers

Allowed edges

Rules

A1. Read only the layers your layer allows

A2. An rpt_ reads a mart when 1 covers the entity

A3. An intermediate either builds or reshapes

A4. 1 model per grain

A5. Dedup only in stg_ or source int_

A6. Long in the core, wide at the edge

A7. Identity in the domain int_; keys through their macro

A8. Exposures read only rpt_ and marts

A9. No select * in the final select of an rpt_, a mart, or a view

A10. Join unioned regional models on _dbt_source_project

A11. Domain folders are listed

Marts

Column-naming rubric

Degenerate-dim rule

Plumbing definition

Strict-chain traversal

PK / FK / date column shapes

Tests on marts and rpt_ models

SQL style

Where we differ from dbt Labs

Rules

S1. ANSI SQL or a dbt macro first

S2. Name every group by column

S3. No subqueries against tables or CTEs

S4. No pass-through import CTEs

S5. No order by in models

S6. Order the select list by complexity

S7. sqlfluff ST06 select order

S8. At most 1 level of function nesting

S9. Cast early, once, with an alias

S10. where and on hold only plain comparisons

S11. Row filters on the preserved table go in where

S12. distinct only for grain projection

S13. Half-open intervals for date ranges that touch

S14. Booleans are is_ / has_ columns

S15. if() for 1 condition, case for 2 or more

S16. Short table aliases, unique in the query

S17. union all branches list the same columns in the same order

S18. Comments say only what the line cannot show

S19. Avoid full join

S20. A literal that encodes a business rule gets a comment

Review rubric

Reference

Required config per layer

Region labels

Handy SQL

ref() and source()

BigQuery scalar functions

Model properties file

Exposures

A2. An `rpt_` reads a mart when 1 covers the entity

A5. Dedup only in `stg_` or source `int_`

A7. Identity in the domain `int_`; keys through their macro

A8. Exposures read only `rpt_` and marts

A9. No `select *` in the final select of an `rpt_`, a mart, or a view

A10. Join unioned regional models on `_dbt_source_project`

Tests on marts and `rpt_` models

S2. Name every `group by` column

S5. No `order by` in models

S10. `where` and `on` hold only plain comparisons

S11. Row filters on the preserved table go in `where`

S12. `distinct` only for grain projection

S14. Booleans are `is_` / `has_` columns

S15. `if()` for 1 condition, `case` for 2 or more

S17. `union all` branches list the same columns in the same order

S19. Avoid `full join`

`ref()` and `source()`