Skip to content

dbt Development Conventions

Based on the dbt style guide. Most SQL rules are enforced by sqlfluff.

Naming

  • Schema, table and column names in PascalCase, e.g. DimBucket, FactUser, StgLegacyCountryState
  • CTEs in snake_case, e.g. dim_bucket
  • Marts are named after business terminology and stay source agnostic
  • Prefix facts with Fact, dimensions with Dim, stage models with Stg
  • Stage models are based on source terminology and are the first step of any modelling
  • Stage column names stay close to raw data unless logic is applied

Data types

  • Timestamps in UTC. Other time zones get a suffix, e.g. CreatedAtPT
  • Date columns carry a precision suffix, e.g. CreatedAt, EventTime
  • Booleans prefixed Is or Has, typed TRUE/FALSE rather than 0/1 or Yes/No

Keys

  • Primary keys of facts and dimensions use the surrogate_key macro from dbt_utils - Dimensions: built from the source primary key - Facts: built from the keys of the associated dimensions
  • Dimension primary key: <ObjectName>Key
  • Fact primary key: Fact<ObjectName>Key

SQL style

  • Trailing commas
  • Four-space indents, eight inside parentheses (sqlfluff 4.x)
  • Lines up to 200 characters
  • Field names, tables, Jinja keywords, functions and syntactic keywords in lowercase
  • Always use as when aliasing
  • Spaces inside Jinja delimiters: {{ this }}
  • select * from () is written select *, newline, from ()
  • Do not optimise for fewer lines: newlines are cheap, brain time is expensive

Best practices

  • Aggregate as early as possible, before joining
  • Prefer union all to union
  • Be explicit about joins (inner join, not join); left join is usually the most useful, right join suggests swapping the from table
  • Use on, never using (inconsistent results in Snowflake)
  • With two or more tables, prefix every column with the table alias; recommended for one table too
  • Avoid initialisms for aliases: user, not u
  • State grouping fields before aggregates and window functions