eonxcom/dbt-sync repository overview

⁠Dbt Style Guide

⁠Model Configuration

• Model-specific attributes (like sort/dist keys) should be specified in the model.
• If a particular configuration applies to all models in a directory, it should be specified in the dbt_project.yml file.
• In-model configurations should be specified like this:

{{config
    (
        materialized = 'table',
        sort = 'id',
        dist = 'id'
    )
}}

• Marts should always be configured as tables

⁠Conventions

• Only stg_ models (or base_ models if your project requires them) should select from sources.
• All other models should only select from other models.

⁠Naming and field conventions

• Schema, table and column names should be in snake_case.
• Use names based on the business terminology, rather than the source terminology.
• Each model should have a primary key.
• The primary key of a model should be named <object>_id, e.g. account_id – this makes it easier to know what id is being referenced in downstream joined models.
• For base/staging models, fields should be ordered in categories, where identifiers are first and timestamps are at the end.
• Timestamp columns should be named <event>_at, e.g. created_at, and should be in UTC. If a different timezone is being used, this should be indicated with a suffix, e.g created_at_aest.
• Booleans should be prefixed with is_ or has_.
• Price/revenue fields should be in decimal currency (e.g. 19.99 for $19.99; many app databases store prices as integers in cents). If non-decimal currency is used, indicate this with suffix, e.g. price_in_cents.
• Avoid reserved words as column names
• Consistency is key! Use the same field names across models where possible, e.g. a key to the customers table should be named customer_id rather than user_id.

⁠CTEs

• All {{ ref('...') }} statements should be placed in CTEs at the top of the file
• Where performance permits, CTEs should perform a single, logical unit of work.
• CTE names should be as verbose as needed to convey what they do
• CTEs with confusing or noteable logic should be commented
• CTEs that are duplicated across models should be pulled out into their own models create a final or similar CTE that you select from as your last line of code. This makes it easier to debug code within a model (without having to comment out code!)
• CTEs should be formatted like this:

WITH events AS (

    ...

),

-- CTE comments go here
filtered_events AS (

    ...

)

SELECT 
    <column list> 
    
FROM filtered_events

⁠SQL style guide

• Keywords should be in upper case

• Use trailing commas

• Indents should be four spaces (except for predicates, which should line up with the where keyword)

• Lines of SQL should be no longer than 80 characters

• Field names and function names should all be lowercase

• The AS keyword should be used when aliasing a field or table

• Fields should be stated before aggregates / window functions

• Aggregations should be executed as early as possible before joining to another table.

• Specify join keys - do not use using. Certain warehouses have inconsistencies in using results (specifically Snowflake).

• Prefer union all to union *

• Avoid table aliases in join conditions (especially initialisms) – it's harder to understand what the table called "c" is compared to "customers".

• If joining two or more tables, always prefix your column names with the table alias. If only selecting from one table, prefixes are not needed.

• Be explicit about your join (i.e. write inner join instead of join). left joins are normally the most useful, right joins often indicate that you should change which table you select from and which one you join to.

• DO NOT OPTIMIZE FOR A SMALLER NUMBER OF LINES OF CODE. NEWLINES ARE CHEAP, BRAIN TIME IS EXPENSIVE

⁠Example SQL

WITH my_data AS (

    SELECT * 
    FROM {{ ref('my_data') }}

),

some_cte AS (

    SELECT * 
    FROM {{ ref('some_cte') }}

),

some_cte_agg AS (

    SELECT
        id,
        SUM(field_4) AS total_field_4,
        MAX(field_5) AS max_field_5

    FROM some_cte
    GROUP BY id

),

final AS (

    SELECT [DISTINCT]
        my_data.field_1,
        my_data.field_2,
        my_data.field_3,

        -- use line breaks to visually separate calculations into blocks
        CASE
            WHEN my_data.cancellation_date IS NULL
                AND my_data.expiration_date IS NOT NULL
                THEN expiration_date
            WHEN my_data.cancellation_date IS NULL
                TEHN my_data.start_date + 7
            ELSE my_data.cancellation_date
        END AS cancellation_date,

        some_cte_agg.total_field_4,
        some_cte_agg.max_field_5

    FROM my_data
    LEFT JOIN some_cte_agg  
        ON my_data.id = some_cte_agg.id
    WHERE my_data.field_1 = 'abc'
        AND (
            my_data.field_2 = 'def' or
            my_data.field_2 = 'ghi'
        )
    HAVING COUNT(*) > 1

)

SELECT
    <column list>
FROM final

• Your join should list the "left" table first (i.e. the table you are selecting from):

SELECT
    trips.*,
    drivers.rating as driver_rating,
    riders.rating as rider_rating

FROM trips
LEFT JOIN users AS drivers
    ON trips.driver_id = drivers.user_id
LEFT JOIN users AS riders
    ON trips.rider_id = riders.user_id

⁠YAML style guide

• Indents should be two spaces
• List items should be indented
• Use a new line to separate list items that are dictionaries where appropriate
• Lines of YAML should be no longer than 80 characters.

⁠Example YAML

version: 2

models:
  - name: events
    columns:
      - name: event_id
        description: This is a unique identifier for the event
        tests:
          - unique
          - not_null

      - name: event_time
        description: "When the event occurred in UTC (eg. 2018-01-01 12:00:00)"
        tests:
          - not_null

      - name: user_id
        description: The ID of the user who recorded the event
        tests:
          - not_null
          - relationships:
              to: ref('users')
              field: id

⁠Jinja style guide

• When using Jinja delimiters, use spaces on the inside of your delimiter, like {{ this }} instead of {{this}}
• Use newlines to visually indicate logical blocks of Jinja