Tabby Stack dbt Style Guide

-- Good
select
    id
    , upper(trim(name)) as name_normalized
    , case when status = 'active' then true else false end as is_active
    , cast(created_at as date) as created_date
from users
where deleted_at is null

-- Bad
SELECT
    id,
    UPPER(TRIM(name)) AS name_normalized,
    CASE WHEN status = 'active' THEN TRUE ELSE FALSE END AS is_active,
    CAST(created_at AS DATE) AS created_date
FROM users
WHERE deleted_at IS NULL

select
  column_1
  , column_2
  , case
      when condition_1 then 'value_1'
      when condition_2 then 'value_2'
      else 'default'
    end as derived_column
from table_name
where condition

-- Good: Leading commas
select
    id
    , first_name
    , last_name
    , email
    , created_at
from users

-- Bad: Trailing commas
select
    id,
    first_name,
    last_name,
    email,
    created_at
from users

-- Good: Explicit aliasing
select
    u.id as user_id
    , u.email as user_email
    , o.id as order_id
from users as u
left join orders as o
    on u.id = o.user_id

-- Bad: Implicit aliasing
select
    u.id user_id
    , u.email user_email
    , o.id order_id
from users u
left join orders o
    on u.id = o.user_id

select
    s.created_at as subscription_created_at
    , s.id as subscription_id
    , s.account_id
    , coalesce(
        s.annual_revenue,
        s.monthly_revenue * 12,
        0
      ) as estimated_arr
from subscriptions as s

select
    s.subscription_id
    , a.account_id
    , p.plan_name
from subscriptions as s
left join accounts as a
    on s.account_id = a.id
    and s.workspace_id = a.workspace_id
left join plans as p
    on s.plan_id = p.id
    and s.billing_interval = p.billing_interval

where
    s.created_at::date >= '2023-01-01'
    and s.plan_type <> 'internal'
    and s.status = 'active'

with normalized_domains as (...)         -- Describes the transformation
, accounts_with_duplicates as (...)      -- Indicates intermediate state
, accounts_deduped as (...)              -- Final cleaned version
, subscription_events as (...)           -- Describes the data content

-- Pre-normalize domains for efficient joining
with normalized_domains as (
    select distinct
        domain
        , lower(trim(domain)) as domain_normalized
    from {{ ref('base_clearbit__company_domains') }}
)

, accounts_with_enrichment as (
    select
        a.id as account_id
        , a.company_name
        , a.domain
        , d.industry
    from {{ ref('base_app__accounts') }} as a
    left join normalized_domains as d
        on lower(trim(a.domain)) = d.domain_normalized
)

, accounts_deduped as (
    select *
    from accounts_with_enrichment
    qualify row_number() over (
        partition by account_id
        order by domain nulls last
    ) = 1
)

select * from accounts_deduped

-- Pre-normalize company domains for efficient joining
with normalized_domains as (
    ...
)

-- Deduplicate accounts to ensure unique account_id
select *
from accounts_base
qualify row_number() over (...)

base_stripe__subscriptions
base_stripe__invoices
base_app_public__users
base_salesforce__accounts
base_segmcore__tracks

from subscriptions as s
left join accounts as a
    on s.account_id = a.id
left join users as u
    on a.owner_id = u.id
left join plans as p
    on s.plan_id = p.id

dbt/
├── models/
│   ├── source_mirror/       # 1:1 copies from source systems
│   │   ├── stripe/
│   │   ├── salesforce/
│   │   └── ...
│   ├── data_prep/           # Cleaned, renamed, typed
│   │   ├── stripe/
│   │   └── ...
│   ├── enrich/           # Source-specific business logic
│   ├── integrate/               # Cross-source combinations
│   ├── core/     # Org-wide canonical tables
│   └── serve/   # Consumer-specific delivery
│       ├── retail_usa/
│       └── finance/
├── macros/
│   ├── communication/     # Domain-specific macros
│   │   ├── advance_notice/
│   │   └── reminders/
│   └── utilities/
├── tests/                 # Singular tests
├── seeds/
├── snapshots/
├── docs/                  # Documentation
├── dbt_project.yml
└── .sqlfluff

{{
    config(
        materialized='view',
        schema='source_mirror'
    )
}}

select
    *
    , _fivetran_synced as _loaded_at
    , _fivetran_deleted as _is_deleted
from {{ source('stripe', 'subscriptions') }}

{{
    config(
        materialized='view',
        schema='data_prep'
    )
}}

select
    id as subscription_id
    , cast(created_at as timestamp_ntz) as created_at
    , nullif(trim(plan_name), '') as plan_name
from {{ ref('base_stripe__subscriptions') }}

{{
    config(
        materialized='view',
        schema='enrich'
    )
}}

with subscription_events as (
    select
        subscription_id
        , plan_name
        , created_at
        , lead(created_at) over (
            partition by account_id order by created_at
          ) as next_evcore_at
    from {{ ref('prep_stripe__subscriptions') }}
)

select * from subscription_events

{{
    config(
        materialized='view',
        schema='integrate'
    )
}}

select
    a.account_id
    , a.company_name
    , s.subscription_id
    , s.plan_name
    , u.last_login_at
from {{ ref('enr_salesforce__accounts') }} as a
left join {{ ref('enr_stripe__subscriptions') }} as s
    on a.stripe_customer_id = s.customer_id
left join {{ ref('enr_app__user_activity') }} as u
    on a.account_id = u.account_id

{{
    config(
        materialized='table',
        schema='core',
        cluster_by=['account_id', 'created_at']
    )
}}

{{
    config(
        materialized='table',
        schema='retail_usa_data',
        cluster_by=['store_id', 'order_date']
    )
}}

{{
    config(
        materialized='table',
        cluster_by=['subscription_id', 'created_at']
    )
}}

-- Model logic starts here
with ...

{{
    config(
        materialized='table',
        cluster_by=['account_id', 'created_at']
    )
}}

{{
    config(
        materialized='table',
        schema='core',
        post_hook=[
            "grant select on {{ this }} to role analyst",
            "{{ log_model_refresh(this.identifier) }}"
        ]
    )
}}

models:
  your_project:
    +on_schema_change: "sync_all_columns"

version: 2

models:
  - name: prep_subscriptions
    columns:
      - name: subscription_id
        description: "Unique identifier for the subscription"
        tests:
          - not_null
          - unique

      - name: created_at
        description: "Timestamp when the subscription was created"
        tests:
          - not_null

-- tests/assert_core_mrr_data_quality.sql

with validation_checks as (
  select
    -- Check for duplicate records
    count(*) as total_records
    , count(distinct subscription_id) as unique_subscriptions

    -- Validate temporal consistency
    , sum(case when churned_at < created_at then 1 else 0 end) as invalid_dates

    -- Validate MRR calculation accuracy
    , sum(
        case
          when mrr_amount != (
            base_mrr + expansion_mrr - contraction_mrr
          )
          then 1
          else 0
        end
      ) as incorrect_mrr_totals

  from {{ ref('core_monthly_recurring_revenue') }}
)

-- Return rows only when failures are detected (dbt convention)
select
  'Data Quality Issues Found' as test_name
  , *
from validation_checks
where
  total_records != unique_subscriptions
  or invalid_dates > 0
  or incorrect_mrr_totals > 0

tests/
├── assert_{model_name}_data_quality.sql
├── assert_{model_name}_outliers.sql
├── no_{business_rule_violation}.sql

version: 2

models:
  - name: model_name
    description: |
      **Brief title or purpose**

      Detailed description of the model's business purpose.

      **Key Features:**
      - Feature 1
      - Feature 2

      **Business Questions Answered:**
      - Question 1?
      - Question 2?

    meta:
      owner: "Team or Person Name"
      tags: ["domain", "category", "tier-1"]
      business_domain: "Domain Name"

    columns:
      - name: column_name
        description: |
          Clear description of what this column represents.
          Include calculation logic for derived columns:
          Formula: column_a + column_b
        tests:
          - not_null
          - unique

- name: health_score
  description: |
    Composite customer health score (0-100 scale) combining:
    - Product usage frequency (30% weight)
    - Feature adoption breadth (25% weight)
    - Support ticket sentiment (20% weight)
    - NPS survey responses (25% weight)
    Scores < 50 indicate churn risk requiring intervention.

- name: days_to_convert
  description: |
    Calculated: Days between trial start and paid conversion.
    Formula: DATEDIFF(day, trial_started_at, converted_at)
    NULL if not yet converted.

- name: mart_revenue_legacy
  description: |
    **DEPRECATED - Use `core_monthly_recurring_revenue` instead**

    Legacy model maintained for backward compatibility.
    Scheduled for removal: 2025-Q2
  meta:
    tags: ["legacy", "scheduled-removal"]

macros/
├── communication/
│   ├── advance_notice/
│   │   ├── email_attempt_dates.sql
│   │   ├── sms_attempt_dates.sql
│   │   ├── attempt_date_cols.sql
│   │   └── shipped_diff_cols.sql
│   └── reminders/
│       └── ...
└── utilities/
    └── explore_data.sql

{action}_{subject}_{output_type}.sql

email_attempt_dates.sql
shipped_diff_cols.sql
create_feature_registry_table.sql

{% macro attempt_date_cols(prefix, channel_expr, channel_name) %}
  -- Emits attempt 1..3 dates like: {prefix}1_{channel_name}_date
  {% for n in [1, 2, 3] %}
  , to_char(
      max(case when {{ channel_expr }} and attempt_number = {{ n }} then contact_at end),
      'YYYY-MM-DD'
    ) as {{ prefix }}{{ n }}_{{ channel_name }}_date
  {% endfor %}
{% endmacro %}

-- email_attempt_dates.sql
{% macro email_attempt_dates(prefix) %}
  {{ attempt_date_cols(prefix, "activity_type = 'Email'", "email") }}
{% endmacro %}

-- sms_attempt_dates.sql
{% macro sms_attempt_dates(prefix) %}
  {{ attempt_date_cols(prefix, "activity_type = 'SMS'", "sms") }}
{% endmacro %}

[sqlfluff]
templater = dbt
dialect = snowflake
exclude_rules = L034, L044
max_line_length = 120

[sqlfluff:templater:dbt]
profiles_dir = ~/.dbt
profile = your_profile_name
target = dev

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

[sqlfluff:rules:aliasing.table]
aliasing = explicit

[sqlfluff:rules:aliasing.column]
aliasing = explicit

[sqlfluff:rules:aliasing.expression]
aliasing = explicit

[sqlfluff:rules:layout.long_lines]
ignore_commcore_lines = true
ignore_commcore_clauses = true

[sqlfluff:rules:structure.column_order]
select_clause_elemcore_order_preference = any

[sqlfluff:indentation]
indcore_unit = space
indents = 2

[sqlfluff:layout:type:comma]
line_position = leading

[sqlfluff:rules:convention.select_trailing_comma]
select_clause_trailing_comma = forbid

[sqlfluff:rules:convention.terminator]
require_final_newline = true

# Lint all models
sqlfluff lint models/

# Lint specific layer
sqlfluff lint models/source_mirror/
sqlfluff lint models/data_prep/
sqlfluff lint models/enrich/
sqlfluff lint models/integrate/
sqlfluff lint models/core/
sqlfluff lint models/serve/

# Auto-fix linting issues
sqlfluff fix models/

# Fix specific file
sqlfluff fix models/data_prep/prep_stripe__subscriptions.sql

# Format SQL files
sqlfluff format models/

# .github/workflows/dbt-lint.yml
name: dbt Lint

on: [pull_request]

jobs:
  sqlfluff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Install SQLFluff
        run: pip install sqlfluff sqlfluff-templater-dbt
      - name: Lint SQL
        run: sqlfluff lint models/ --dialect snowflake