SQL - check unused cte-CodePudding

Let's say I have the below SQL query:

with unused_cte as 
   (select 1 as one),
used_cte as
   (select 2 as two)
select * from used_cte

I want to know your opinion on how I can automate the catching of unused CTE (the CTE which is not used in the SQL execution, for Example 'unused_cte' in this case) in the form of SQL assertion or dbt checks or any other suggestions.

CodePudding user response：

It will not be run, as this simple test shows .

the first CTE is a division by zero, which should produce and error.

But as the unused cte isn't executed, the second valid will run.

Postgres

with cte_unused as (select 1/0 as val), cte_used as (select 'it works' as val)
select * from cte_used

| val      |
| :------- |
| it works |

db<>fiddle here

MySQL

with cte_unused as (select 1/0 as val), cte_used as (select 'it works' as val)
select * from cte_used

| val      |
| :------- |
| it works |

db<>fiddle here

CodePudding user response：

If using dbt-core, you can add SQLFluff as your linter locally. Then, it will catch whenever you are calling a CTE inside a data model that is not being used.

See example below -> I have a model called stg_testing.sql that looks like the following:

with used_cte as (
  select 1 as fake_id
),

unused_cte as (
  select 2 as fake_id
),

final as (
  select fake_id
  from used_cte
)

select * from final

Since SQLFluff detects that the second CTE is not used, it will raise the following problem:

Query defines CTE (unused_cte) but does not use it. sqlfluff(L045) [Ln 5, Col 2]