Gitlab hero border pattern left svg Gitlab hero border pattern right svg

SQL Style Guide

On this page


This style guide is for SQL and dbt.

SQL Style Guide

Since we don't have a linter, it is our collective responsibility to enforce this Style Guide.

Some of the below comments apply specifically to dbt which is the bulk of the SQL we write.

Field Naming Conventions

Dates

CTEs (Common Table Expressions)
WITH events AS ( -- think of these select statements as your import statements.

  ...

), filtered_events AS ( -- CTE comments go here

  ...

) 

SELECT * -- you should always aim to "select * from final" for your last model
FROM filtered_events
Style Guide
Example Code
with my_data as (

    SELECT *  FROM {{ ref('my_data') }}
    WHERE filter = 'my_filter'

), some_cte as (

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

)

SELECT 
  data_by_row['id']::bigint  AS id_field,
  field_1                    AS detailed_field_1,
  field_2                    AS detailed_field_2,
  detailed_field_3,
  CASE 
    WHEN cancellation_date IS NULL AND expiration_date IS NOT NULL
      THEN expiration_date
    WHEN cancellation_date IS NULL
      THEN start_date+7
    ELSE cancellation_date
  END                        AS cancellation_date,
  SUM(field_4)               AS field_4_sum,
  MAX(field_5)               AS field_5_max
FROM my_data
LEFT JOIN some_cte 
  ON my_data.id = some_cte.id 
WHERE field_1 = abc
  AND (field_2 = def OR field_2 = ghi)
GROUP BY 1, 2, 3, 4
HAVING count(*) > 1
ORDER BY 4
DESC
Commenting
Query Naming Convention

Follow the naming convention of analysis type, data source (in alpha order, if multiple), thing, aggregation (e.g. retention_sfdc_zuora_customer_count.sql)

Sources

When working with source tables with names that don't meet our usual convention or have unclear meanings, use identifiers to override their table names. (Docs on using identifiers)

Base Models
Testing
Other SQL Style Guides