This document describes the how SQL and dbt resources should be created in our repository.
- Use the pull request quality checklist for every PR
- Use correct SQL alignment
- Use one expression per line aka write tall
- Use leading commas for field names
- Use lower case keywords in SQL statements
- Prefer listing field names over "select *"
- Prefer listing group by fields over group by all
- Use field names in grouping/ordering clauses
- Don't use positional indictors like 1,2,3 in grouping/ordering clauses
- Prefer aliasing table names over the full table name
- Use "on" syntax in joins (SQL-99 joins)
- Prefer judicious use of left joins
- Avoid using right joins and outer joins
- Prefer using count when counting and sum when summing
- Prefer keeping subqueries simple
- Use a pass-through CTE declaration block at the beginning of the model.
- Use closed CTEs and implement a select * from the final CTE.
- Use staged CTEs for resuable queries
- Prefer "right size" final CTE files (about 3-4 CTEs total)
- Use upstream models in staging for more complex models
- Use DAGs when appropriate to stage models that can be run simultaneously
- Use a config block for all models
- Use dim_ prefix for dimension tables
- Use fact_ prefix for fact tables
- Don't prefix other tables
- Use unique table names
- Use schema prefixes for model names, e.g stg__ for staging and main__ for main
- Use plural table names, e.g, appointment_events rather than appointment_event
- Prefer renaming Rails id field to the singular table name _id e.g. the id field from the visits table becomes visit_id
- Avoid reserved words and function names as field names. If you must use a reserved word add an underscore to the beginning of the word, e.g. _rank instead of rank
- Use snake_case for table names and field names
- Use explicitly cast data types for fields that must be a specific data type
- Monetary values should be cast as decimal(38,2)
- Prefer deterministic functions over non-deterministic functions like current_time(), current_date(), etc.