#

# The Orchard SQL Style Guide

# General Guidelines

- We optimize for readibility, maintanability, & robustness rather than lines of code. 
    - New lines are cheap, but brain time is expensive.
- We aim for our code to be as [DRY](https://docs.getdbt.com/terms/dry) as possible.
- We prioritise consistency over following the letter of this guidance
    - Being consistent makes it easier to read, maintain, and make changes to code in the future.
- We aim to be as explicit as possible in our definitions. Being explicity ensures that things work the eay we think they will, and make code easier to maintain.
- We prioritize verbositiy over brevity. When naming things, be as descriptive as possible. This makes it easier to understand where data is coming from, and how it is being used.

# General Best Practice
- Use 4 spaces to indent rather than tabs
- Wrap long lines of code onto new lines
- Avoid very long `select` statements. If your select statement is very long then consider moving logic back into CTEs, or into intermediate tables.
- Use `DATEDIFF` for date calculations over addition e.g.  `date_column + interval_column`.     
    - The function is more explicit and will work for a wider variety of date parts.
- Use `<>` rather than `!=`
- Use `like` rather than `ilike` for simplicity.
    - `lower(column) like '%my_col%'`

# Joins
- Always use explicit joins, never implicit joins
- Explicitly alias `inner join`
- Avoid `right join`
- Keep join conditions as simple as possible
    - Avoid `case` statements where posible


# Naming & Aliasing
- Use the `AS` operator when aliasing a column or table.
- Aliases & CTE names should be in `snake_case` and as verbose as possible. 
- Do not use [reserved words](https://docs.snowflake.com/en/sql-reference/reserved-keywords) for alias names
- Ambiguous fields like `id` or `date` should be prefixed by what idenfies them:
- Columns that represent known price values should be suffixed with that result e.g. `_price_usd`

# References
- When joining tables, reference the table name if the name is realtively short, for examply 20-30 characters.
    - Rename CTEs to be shorter but still descriptive if neccesary
    - If the table name is too long to reference, alias to something shorter but still descriptive
- When selecting columns from tables in queries with joins, qualify every columns in the select statement with the table name or alias

```sql 
-- Preferred
select
    dim_label.label_id,
    dim_label.label_name,
    summary_streams.download_activity_date,
    summary_streams.units
from facts.prod.v2_summary_streams_for_all_dsps_inc_amazon as summary_streams
LEFT JOIN dim_label
    ON summary_streams.label_id = dim_label.label_id

-- vs 

-- Not Preferred
select
    dl.label_id,
    dl.label_name,
    ss.download_activity_date,
    ss.units
from facts.prod.v2_summary_streams_for_all_dsps_inc_amazon as summary_streams ss
LEFT JOIN dim_label dl
    ON ss.label_id = dl.label_i
```

- Add comments to columns to split them into logical groups, e.g.
    - `ids`
    - `dates`
    - `names`
    - `identifiers`
    - `attributes`

```sql
  -- Preferred
select
/* ids */
    id as label_id,
/* dates */
    date as download_activity_date
/* names */
    name as label_name,
from table

```
- Boolean (yesno) fields should start with `has_`, `_is` or `_does`
```sql
select
    monetized as is_monetized,
    deleted as is_deleted,
    carve_out as has_carve_out
from table
```
- Dates should be have a `_date` suffix and timestamps should have an `_at` suffix

# CTEs
- Use CTEs early and often
- CTEs make code easier to read, understand, & maintain.
- Avoid subqueries as much as possible
- Use CTEs to "import" data
    - the [dbt codegen package](https://hub.getdbt.com/dbt-labs/codegen/latest/) can help you automate this
- Prefer to limit data using `where` and `having` statements, as well as aggregating data with `sum`, `count` etc. in CTEs first.
- Prefer to only join CTEs together in other CTEs, rather than joining tables directly
- Where performance permits, CTEs should perform a single, logical unit of work.
- CTE names should be as concise as possible while still being clear.
- CTE names should be as concise as possible while still being clear.


``` sql
/* good */

with monthly_spotify_streams_by_track AS (
select
/* dates */
    streamed_date,
/* ids */
    isrc,
/* aggregates */
    SUM(streams) as streams
from
    monthly_agg_spotify_streams
where
    1=1
    and streamed_date between '2020-01-01' and '2020-01-02'
group by 1,2
),

track_names AS (
select
/* ids */
    isrc,
/* names */
    track_name,
from
    dim_track
)

joined as (
select
/* dates */
    monthly_spotify_streams.streamed_date,

/* descriptors */
    montly_spotify_streams.isrc,
    track_names.track_name

/* aggregates */
    streams
from monthly_spotify_streams_by_track
inner join track_names
    on monthly_spotify_streams_by_track.isrc = track_names.isrc
),

final as (
select
/* dates */
    streamed_date,

/* descriptors */
    isrc,
    track_name

/* aggregates */
    streams
from joined
)

select * from final

```

# Data Types
- Many traditional [data types in Snowflake](https://docs.snowflake.com/en/sql-reference/intro-summary-data-types) are simply aliases for standard data types. In general, use default data types and not aliases.:
    - `NUMBER`` instead of `DECIMAL`, `NUMERIC`, `INTEGER`, `BIGINT`, etc.
    - `FLOAT` instead of `DOUBLE`, `REAL`, etc.
    - `VARCHAR` instead of `STRING`, `TEXT`, etc.
    - `TIMESTAMP` instead of `DATETIME`