# SQL Readability: A Consideration

**URL:** <https://discourse.getdbt.com/t/sql-readability-a-consideration/1095>\
**Category:** Archive\
**Created:** [April 28, 2020, 6:50am UTC](https://discourse.getdbt.com/t/sql-readability-a-consideration/1095 "2020-04-28T06:50:05Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![RaulingAverage](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/raulingaverage/32/391_2.png) [@RaulingAverage](https://discourse.getdbt.com/u/RaulingAverage)\
**Post date:** [April 28, 2020, 6:50am UTC](https://discourse.getdbt.com/t/sql-readability-a-consideration/1095/1 "2020-04-28T06:50:05Z")

</div>

Hello dbt team,

Hope you are doing well. I wanted to reach out because when working with the dbt example codes, and seeing other people’s repos, I see a lack of what SQL readability should look like, in regards to indentation and formatting.

SQL Readability is an opinionated topic, but bare with me.

[In this article,](https://raulingaverage.dev/SQL-Readability/) I talk about SQL Readability from where I learned somewhere else, and moreover, provide resources on my research.

Ultimately, queries may look as such:

```auto
/*

* Querying the Myspace Events table with user information for the time
* after the first Musician profile-user was created.

*/

WITH earliest_musician_user_date AS ( 

    SELECT 
    
        MIN(created_date)
    
    FROM mySpace_users 
    
    WHERE profile = 'Musician'

)

SELECT 

    id, 
    created_date, 
    events.login_id, 
    geo_state, 
    first_name, 
    last_name, 
    SUM(login.logIn) AS total_logins -- Obtaining total logins

FROM mySpace_events AS events

LEFT OUTER JOIN AS myspace_users users

ON users.id = events.user_id

WHERE login.date >= CURRENT_DATE - earliest_musician_user_date

GROUP BY 1,2,3,4,5,6

```

---

<div class="post-metadata">

**Author:** ![emilie](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/emilie/32/23_2.png) [@emilie](https://discourse.getdbt.com/u/emilie)\
**Post date:** [April 28, 2020, 1:17pm UTC](https://discourse.getdbt.com/t/sql-readability-a-consideration/1095/2 "2020-04-28T13:17:56Z")

</div>

The best way to encourage readability is through standardization.  
When people know the structure of what they’re reading, they can spend less time figuring out what’s going on a more time focused on the content. That’s where SQL Style Guides come in.

We’ve talked about this before when @mhmazur shared his SQL Style Guide in this discourse post: [Sharing my SQL Style Guide](https://discourse.getdbt.com/t/sharing-my-sql-style-guide/410)

@claire recently explained some of the reasoning in Fishtown’s style guide around CTEs: [Why the Fishtown SQL style guide uses so many CTEs](https://discourse.getdbt.com/t/why-the-fishtown-sql-style-guide-uses-so-many-ctes/1091/2)

Some of the other’s frequently mentioned in dbt slack:

- Fishtown’s: [https://github.com/fishtown-analytics/corp/blob/master/dbt\_coding\_conventions.md#sql-style-guide](https://github.com/fishtown-analytics/corp/blob/master/dbt_coding_conventions.md#sql-style-guide)
- Kickstarter’s: [https://gist.github.com/fredbenenson/7bb92718e19138c20591](https://gist.github.com/fredbenenson/7bb92718e19138c20591)
- GitLab’s (where I work): [https://about.gitlab.com/handbook/business-ops/data-team/sql-style-guide/](https://about.gitlab.com/handbook/business-ops/data-team/sql-style-guide/)

For a _project’s_ readability, it’s less about any particular query and more about consistency across queries.

It’s hard to be definitive across organizations because every organization has a slightly different context. For example, your example above takes up a lot of space on a 13 inch Macbook. From the discourse view, I cannot see the whole thing on a single screen:

 ![Screen Shot 2020-04-28 at 8.56.12 AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/d6b599aad46599a5754649e6136642440c8cb792.png)

Even when I move it to vim, this pretty straight-forward query takes up most of the screen:

 ![Screen Shot 2020-04-28 at 8.56.44 AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/2e729609fa052816dfe6d86ea10343a9debda080.png)

A company that standard issues external monitors to team members can make more spacing demands without considering the negative effect to productivity. Your recommendations _should_ be considerate of who is working on the project and what their environment might look like.

My personal reaction (not GitLab’s) is that your outlined suggestion is wasting a lot of valuable screen space. I would rewrite it as follows:

```auto
WITH myspace_events AS (

    SELECT *
    FROM myspace_events

), myspace_users AS (

    SELECT min(created_date) as earliest_musician_date
    FROM myspace_users
    WHERE profile = 'Musician'

), final AS (

    SELECT event_id, -- I would have renamed this ID in a base model
        created_date,
        myspace_events.login_id, 
        geo_state,
        first_name,
        last_name,
        SUM(login) AS total_login -- you reference a login.login but don't alias a table called login
    FROM myspace_events
    LEFT OUTER JOIN myspace_users
    ON myspace_users.user_id = myspace_events.user_id -- I would have renamed user_id in the base model
    WHERE login_date >= (CURRENT_DATE - earliest_musician_date)
    GROUP BY 1, 2, 3, 4, 5, 6

)

SELECT * 
FROM final

```

This reduces the SQL from 38 to 29 lines.  
Notable differences include:

- removing comments, as there are better places to put them; the comments I include here are for reference and I would not include them in an actual query
- reducing blank lines
- helping visualize order of operations using parentheses where there are multiple actions in a single line
- avoiding ambiguous column names, like `id` by renaming them in base models
- not aliasing model names when adding 8 characters means you don’t have to
- using CTEs to call source data

You can see how much less screenspace this occupies in a text editor:

 ![Screen Shot 2020-04-28 at 9.16.13 AM](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/de88bd1cfed13d9dd19321aee729b7ddc636ea96.png)

But opinions on SQL style guides is a bit like opinions on everything else… everyone has one.

---

<div class="post-metadata">

**Author:** ![RaulingAverage](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/raulingaverage/32/391_2.png) [@RaulingAverage](https://discourse.getdbt.com/u/RaulingAverage)\
**Post date:** [April 29, 2020, 6:02am UTC](https://discourse.getdbt.com/t/sql-readability-a-consideration/1095/3 "2020-04-29T06:02:47Z")

</div>

Major +1 to your feedback @emilie. I particularly like your weigh in on the existing query, with supplemented sources.

> I am particularly going to update my Readability recommendation from your resources as well (E.g. [Gitlab SQL Style Guide](https://about.gitlab.com/handbook/business-ops/data-team/sql-style-guide/))

And this, I feel, would be a great reason to further have the Fishtown team consider an update to the SQL Readability standards that are both on examples & also mentioned in their best practices.

My hopes would be to at least help other team’s before they have their own technical debt before following what’s previously been created, as I would like to be “considerate of who is working on the project and what their environment might look like.”

I know that “SQL style guides is a bit like opinions”, but I was hoping this could be a ripe opportunity for the team to come to re-consideration on SQL Readability before, hopefully, more adoption would occur–especially after [Fishtown’s recent highlighted news](https://blog.getdbt.com/fishtown-analytics-announces-12-9m-fundraise/)

Anyways, I really appreciate the response!

---

<div class="post-metadata">

**Author:** ![emilie](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/emilie/32/23_2.png) [@emilie](https://discourse.getdbt.com/u/emilie)\
**Post date:** [April 29, 2020, 12:47pm UTC](https://discourse.getdbt.com/t/sql-readability-a-consideration/1095/4 "2020-04-29T12:47:25Z")

</div>

Hmm. I’m just now realizing that your comments about SQL readability are specifically towards Fishtown’s SQL Style Guide. I do find their SQL style very readable- and, it’s incredibly consistent. Maybe you can give _specific_ feedback on what you find difficult to parse, so that someone can help explain the reasoning behind that approach.

---

<div class="post-metadata">

**Author:** ![RaulingAverage](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/raulingaverage/32/391_2.png) [@RaulingAverage](https://discourse.getdbt.com/u/RaulingAverage)\
**Post date:** [April 30, 2020, 2:30am UTC](https://discourse.getdbt.com/t/sql-readability-a-consideration/1095/5 "2020-04-30T02:30:50Z")

</div>

Oh yeah, no worries @emilie!

Though the opinion is to the team, I feel a standardization in the [Fishtown SQL Style Guide](https://github.com/fishtown-analytics/corp/blob/master/dbt_coding_conventions.md#sql-style-guide) & [examples](https://github.com/fishtown-analytics/jaffle_shop/blob/master/models/marts/core/dim_customers.sql), can occur.

> I would create a PR, but rather get the opinions before taking such an abrupt action. 👀

Here are some concerns I had

- Spacing
- lowercase on Reserve Words
- Lines can be longer than 80 characters
- Reserve Words are in-line w.r.t query level (kinda like aligning brackets, in other languages)

> Note: These are personal thoughts on past experiences, and do not reflect the readability at my current work now.

Using the dbt coding conventions example

This

```auto
with

my_data as (

    select * from {{ ref('my_data') }}

),

some_cte as (

    select * from {{ ref('some_cte') }}

),

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 then my_data.start_date + 7
            else my_data.cancellation_date
        end as cancellation_date,

        -- use a line break before aggregations
        sum(some_cte.field_4),
        max(some_cte.field_5)

    from my_data

    left join some_cte  
        on my_data.id = some_cte.id

    where my_data.field_1 = 'abc'
      and (
          my_data.field_2 = 'def' or
          my_data.field_2 = 'ghi'
      )

    group by 1, 2, 3, 4
    having count(*) > 1

)

select * from final

```

Could look like this

```auto
WITH my_data AS (
    SELECT * FROM {{ ref('my_data') }}
),

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

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
        THEN
                 my_data.start_date + 7
        ELSE 
                 my_data.cancellation_date
        END AS cancellation_date,

        -- Use a line break before aggregations.
        SUM(some_cte.field_4),
        MAX(some_cte.field_5)

    FROM 
            my_data

    LEFT JOIN 
            some_cte  
    ON 
             my_data.id = some_cte.id

    WHERE 
            my_data.field_1 = 'abc'
               AND (
                my_data.field_2 = 'def' or
               my_data.field_2 = 'ghi'
                )

    GROUP BY 
         my_data.field_1,
        my_data.field_2,
        my_data.field_3,
        cancellation_date,
    HAVING 
        COUNT(*) > 1

)

SELECT * FROM FINAL

```