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

SQL Style Guide

On this page


SQL Style Guide

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

General Guidelines

Field Naming Conventions

Dates

Use CTEs (Common Table Expressions), not subqueries
General
Functions
JOINs
Example Code
 ```sql
 WITH my_data AS (
 
     SELECT *  
     FROM analytics.my_data
     WHERE filter = 'my_filter'
 
 ), some_cte AS (
 
     SELECT *
     FROM analytics.my_other_data
 
 )
 
 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

dbt Guidelines

General
Sources
Base Models
Organizing columns

When writing a base model, colummns should have some logical ordering to them. We encourage these 4 basic groupings:

Primary data is the key information describing the table. The primary key should be in this group along with other relevant unique attributes such as name.

Foreign keys should be all the columns which point to another table.

Logical data is for additional data dimensions that describe the object in reference. For a Salesforce opportunity this would be the opportunity owner or contract value. Further logical groupings are encouraged if they make sense. For example, having a group of all the variations of contract value would make sense.

Within any group, the columns should be alphabetized on the alias name.

An exception to the grouping recommendation is when we control the extraction via a defined manifest file. A perfect example of this is our gitlab.com manifest which defines which columns we extract from our application database. The base models for these tables can be ordered identically to the manifest as it's easier to compare diffs and ensure accuracy between files.

Testing

Other SQL Style Guides