Lint rules

Syntax Rules

case-keyword

Inconsistent capitalization of keywords

SDF Errors:

- `SDF107`

SQLFluff Alias: L010

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # capitalization_policy is one of [consistent, upper, lower, pascal, snake, camel, off] 
   case-keyword: consistent # default

Anti-pattern

Referencing keywords in inconsistent capitalization. All references should be either uppercase or lowercase, not both.

select a 
FROM my_table

Best-practice

SELECT a 
FROM my_table 
 
-- or 
 
select a 
from my_table

case-literal

Inconsistent capitalization of boolean/null literal

SDF Errors:

- `SDF108`

- `SDF109`

SQLFluff Alias: L040

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # capitalization_policy is one of [consistent, upper, lower, pascal, snake, camel, off] 
   case-literal: consistent # default

Anti-pattern

Referencing literals in inconsistent capitalization. All references should be either uppercase or lowercase, not both.

SELECT 
   a, 
   null, 
   true, 
   FALSE 
FROM my_table

Best-practice

SELECT 
   a, 
   NULL, 
   TRUE, 
   FALSE 
FROM my_table 
 
-- or 
 
SELECT 
   a, 
   null, 
   true, 
   false 
FROM my_table

case-type

Inconsistent capitalization of datatypes.

SDF Errors:

- `SDF110`

SQLFluff Alias: L063

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # capitalization_policy is one of [consistent, upper, lower, pascal, snake, camel, off] 
   case-type: consistent # default

Anti-pattern

Referencing datatypes in inconsistent capitalization. All references should be either uppercase or lowercase, not both.

CREATE TABLE t ( 
   a INT, 
   b varchar(100) 
);

Best-practice

CREATE TABLE t ( 
   a INT, 
   b VARCHAR(100) 
); 
 
-- or 
 
CREATE TABLE t ( 
   a int, 
   b varchar(100) 
);

case-function

Inconsistent capitalization of function names

SDF Errors:

-`SDF106`

SQLFluff Alias: L030

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 semantic-rules: 
   ... 
   # capitalization_policy is one of [consistent, upper, lower, pascal, snake, camel, off] 
   case-function: null # default (off)

Anti-pattern

Referencing functions in inconsistent capitalization. All references should be either uppercase or lowercase, not both.

SELECT 
   max(a) AS max_a, 
   MIN(a) AS min_a 
FROM my_table

Best-practice

SELECT 
   MAX(a) AS max_a, 
   MIN(a) AS min_a 
FROM my_table 
 
-- or 
 
SELECT 
   max(a) AS max_a, 
   min(a) AS min_a 
FROM my_table

disallow-these-identifiers

SDF Errors:

-`SDF124`

SQLFluff Alias: L029

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # identifiers_to_block is an array of names to block from being identifiers. Can still be used in strings (e.g., '{blocked_identifier}'). 
   disallow-these-identifiers: [] # default

flag-keywords-used-as-identifiers

Keywords should not be used as identifiers

SDF Errors:

-`SDF125`

SQLFluff Alias: L029

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # ignore_words is an array of names to ignore in this rule 
   flag-keywords-used-as-identifiers: [] # default

Anti-pattern

Using built-in keywords or functions as identifiers.

SELECT 
   'my_table_name' AS table 
FROM my_table

Best-practice

SELECT 
   'my_table_name' AS table_name 
FROM my_table

disallow-these-chars-in-quoted-identifiers

Do not use special characters in identifiers

SDF Errors:

-`SDF117`

SQLFluff Alias: L057

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # special_chars is a string of chars to block (e.g., "% 
<>"") 
   disallow-these-chars-in-quoted-identifiers: ".#%{}

<>*?/!'
":@+`|=" # default

Anti-pattern

Using special characters within identifiers when creating or aliasing objects.

SELECT 
    num AS "My Num", 
    num > 100 AS "num > 100", 
    num / 100.0 AS "num %" 
FROM my_table

Best-practice

SELECT 
    num AS my_num, 
    num > 100 AS num_greater_than_100, 
    num / 100.0 AS num_percentage 
FROM my_table

flag-unnecessary-quoted-identifiers

Unnecessary quoted identifier

SDF Errors:

-`SDF118`

SQLFluff Alias: L059

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-unnecessary-quoted-identifiers: on # default

Anti-pattern Using quotes when naming a valid identifier, that is not a reserved keyword.

SELECT 'a' AS "a"

Best-practice Use unquoted identifiers where possible.

SELECT 'a' AS a

flag-inconsistent-qualified-column-reference

Inconsistent column references in SELECT clause of both qualified and unqualified references

SDF Errors:

-`SDF112`

SQLFluff Alias: L028

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # column_qualifier is one of [qualified, unqualified, consistent] 
   flag-inconsistent-qualified-column-reference: consistent # default

Anti-pattern

Using both qualified and unqualified column references in the SELECT clause. Reference should be consistent.

SELECT 
   a, 
   my_table.b 
FROM my_table

Best-practice

Use unquoted identifiers where possible.

-- qualified 
SELECT 
   my_table.a, 
   my_table.b 
FROM my_table 
 
-- unqualified 
SELECT 
   a, 
   b 
FROM my_table

flag-unqualified-multi-source-column-reference

Columns reference should specify source table or view in queries with more than one source.

SDF Errors:

-`SDF116`

SQLFluff Alias: L027

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag_unqualified-multi-source-column-reference: on # default

Anti-pattern

Not specifying the source table or view when referencing columns in queries involving more than one source, thus creating ambiguity.

SELECT 
   col_a, 
   col_b 
FROM table_a LEFT OUTER JOIN table_b USING (col_a)

Best-practice

SELECT 
   table_a.col_a, 
   table_b.col_b 
FROM table_a LEFT OUTER JOIN table_b USING (col_a)

flag-inconsistent-ordinal-column-reference

Inconsistent column references in GROUP BY/ORDER BY clauses of both ordinal and non-ordinal

SDF Errors:

-`SDF105`

SQLFluff Alias: L054

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-inconsistent-ordinal-column-reference: on # default

Anti-pattern

Using both explicit and implicit column references in the GROUP BY or ORDER BY clause. Reference should be consistent.

SELECT 
   a, 
   b, 
   SUM(b) AS sum_b 
FROM my_table 
GROUP BY 
   a, 2 
ORDER BY 
   1, b

Best-practice

Use unquoted identifiers where possible.

-- explicit 
SELECT 
   a, 
   b, 
   SUM(c) AS sum_c 
FROM my_table 
GROUP BY 
   a, b 
ORDER BY 
   a, b 
 
-- implicit 
SELECT 
   a, 
   b, 
   SUM(c) AS sum_c 
FROM my_table 
GROUP BY 
   1, 2 
ORDER BY 
   1, 2

flag-unnecessary-else

Redundant ELSE NULL in a case when statement

SDF Errors:

-`SDF119`

SQLFluff Alias: L035

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-unnecessary-else: on # default

Anti-pattern

Stating ELSE NULL within a CASE WHEN statement.

SELECT 
   CASE 
       WHEN num = 1 THEN '1' 
       WHEN num = 2 THEN '2' 
       ELSE NULL -- this is always implicit 
   END AS a 
FROM my_table

Best-practice

SELECT 
   CASE 
       WHEN num = 1 THEN '1' 
       WHEN num = 2 THEN '2' 
   END AS a 
FROM my_table

flag-unnecessary-case

CASE statement can be simplified

SDF Errors:

-`SDF120`

SQLFluff Alias: L043

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-unnecessary-case: on # default

Anti-pattern

The following CASE statement are unnecessary and can be simplified:

-- boolean outcome 
SELECT 
   CASE 
       WHEN num > 1 THEN TRUE 
       ELSE FALSE 
   END AS is_num_greater_than_one 
FROM my_table 
 
-- handle NULLs 
SELECT 
   CASE 
       WHEN num IS NULL THEN 0 
       ELSE num 
   END AS num_not_null 
FROM my_table

Best-practice

Use simpler SQL syntax:

-- boolean outcome 
SELECT 
   COALESCE(num > 1, FALSE) AS is_num_greater_than_one 
FROM my_table 
 
-- handle NULLs 
SELECT 
   COALESCE(num, 0) AS num_not_null 
FROM my_table

flag-unused-cte

Query defines a CTE (common-table expression) but does not use it

SDF Errors:

-`SDF121`

SQLFluff Alias: L045

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-unused-cte: on # default

Anti-pattern

Defining CTEs that are not being used in the query (e.g., sub_2 in the example below).

WITH 
sub1 AS ( 
    SELECT 'a' AS a 
), 
 
sub2 AS ( 
    SELECT 'b' AS b 
) 
 
SELECT a 
FROM sub1

Best-practice

WITH 
sub1 AS ( 
    SELECT 'a' AS a 
), 
 
SELECT a 
FROM sub1

flag-unnecessary-nested-case

Nested CASE statement in ELSE clause could be flattened.

SDF Errors:

-`SDF122`

SQLFluff Alias: L058

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-unnecessary-nested-case: on # default

Anti-pattern

Unnecessarily nesting CASE statements.

SELECT 
   CASE 
       WHEN num = 1 THEN '1' 
       ELSE 
       CASE 
           WHEN num = 2 THEN '2' 
       END 
   END AS my_num_to_varchar 
FROM my_table

Best-practice

SELECT 
   CASE 
       WHEN num = 1 THEN '1' 
       WHEN num = 2 THEN '2' 
   END AS my_num_to_varchar 
FROM my_table

flag-distinct-parenthesis

DISTINCT used with parentheses

SDF Errors:

-`SDF123`

SQLFluff Alias: L015

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-distinct-parenthesis: on # default

Anti-pattern

Using DISTINCT with parentheses. In the example below, all columns are affected by the DISTINCT but can mistaken to think only column a is.

SELECT DISTINCT(a), b 
FROM my_table

Best-practice

SELECT DISTINCT a, b 
FROM my_table

disallow-subquery-in

Join/From clauses should not contain subqueries. Use CTEs instead

SDF Errors:

-`SDF113`

SQLFluff Alias: L042

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   # subquery_scope is one of ['join', 'from', 'both'] 
   disallow-subquery-in: [from, join] # default

Anti-pattern

Selecting from a subquery instead of defining the subquery in a CTE.

SELECT 
   sub.a 
FROM (SELECT 'a' AS a) AS sub

Best-practice

WITH 
sub AS ( 
   SELECT 'a' AS a 
) 
 
SELECT 
   sub.a 
FROM sub

flag-misordered-join-condition

Join conditions column references should follow tables reference order

SDF Errors:

-`SDF114`

SQLFluff Alias: ST09

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-misordered-join-condition: on # default

Anti-pattern

Referencing columns in join ON clause statement in a different order than the tables are referenced in the JOIN clause

SELECT 
   first_table.a, 
   second_table.a 
FROM first_table 
   LEFT OUTER JOIN second_table ON ( 
   second_table.a = first_table.a 
)

Best-practice

SELECT 
   first_table.a, 
   second_table.a 
FROM first_table 
   LEFT OUTER JOIN second_table ON ( 
   first_table.a = second_table.a 
)

flag-select-item-order

Select wildcards then simple targets before calculations and aggregates.

SDF Errors:

-`SDF115`

SQLFluff Alias: L034

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-select-item-order: on # default

Anti-pattern

Wildcard listed after single targets

SELECT 
   a, 
   *, 
   b 
FROM my_table

Best-practice

   *, 
   a, 
   b 
FROM my_table

flag-trailing-semicolon-after-statements

Statements should not end with a semi-colon. Multi-statements must be separated with a semi-colon but the final statement should NOT end with one.

SDF Errors:

-`SDF111`

SQLFluff Alias: L052

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 syntax-rules: 
   ... 
   flag-trailing-semicolon-after-statements: on # default

Anti-pattern

Ending the last statement with a semi-colon

-- single statement 
SELECT a FROM my_table; 
 
-- multi-statement 
SELECT a FROM table_a; 
SELECT b FROM table_b;

Best-practice

-- single statement 
SELECT a FROM my_table 
 
-- multi-statement 
SELECT a FROM table_a; 
SELECT b FROM table_b

Semantic Rules

case-column

Inconsistent capitalization of column names

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 semantic-rules: 
   ... 
   # capitalization_policy is one of [consistent, upper, lower, pascal, snake, camel, off] 
   case-column: null # default (off)

Anti-pattern

Referencing column names in inconsistent capitalization.

SELECT 
   col_name_a, 
   ColNameB, 
   col_nameC 
FROM my_table

Best-practice

SELECT 
   col_name_a, 
   col_name_b, 
   col_name_c 
FROM my_table 
 
-- or 
 
SELECT 
   ColNameA, 
   ColNameB, 
   ColNameC 
FROM my_table 
 
-- or other formats applied to all column names consistently

case-table

Inconsistent capitalization of table names

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 semantic-rules: 
   ... 
   # capitalization_policy is one of [consistent, upper, lower, pascal, snake, camel, off] 
   case-table: null # default (off)

Anti-pattern

Referencing table names in inconsistent capitalization.

SELECT 
   TableA.*, 
   table_b.* 
FROM TableA JOIN table_b USING(my_col)

Best-practice

SELECT 
   table_a.*, 
   table_b.* 
FROM table_a JOIN table_b USING(my_col) 
 
-- or 
 
SELECT 
   TableA.*, 
   TableB.* 
FROM TableA JOIN TableB USING(my_col) 
 
-- or other formats applied to all table names consistently

case-alias

Inconsistent capitalization of alias

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 semantic-rules: 
   ... 
   # capitalization_policy is one of [consistent, upper, lower, pascal, snake, camel, off] 
   case-alias: null # default (off)

Anti-pattern

Referencing alias in inconsistent capitalization.

SELECT 
   A.*, 
   b.* 
FROM table_a AS A JOIN table_b AS b USING(my_col)

Best-practice

SELECT 
   a.*, 
   b.* 
FROM table_a AS a JOIN table_b AS b USING(my_col) 
 
-- or 
 
SELECT 
   A.*, 
   B.* 
FROM table_a AS A JOIN table_b AS B USING(my_col) 
 
-- or other formats applied to all aliases consistently

disallow-implicit-conversions-in

Prevent type implicit casting or coercion

DSF126

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 semantic-rules: 
   ... 
   # implicit_conversion_operators is a list of ['eq', 'in'] 
   disallow-implicit-conversions-in: ['eq', 'in'] # default

Anti-pattern Comparing two different types which causes implicit casting and confusing behavior.

SELECT 
    TRUE = 'TRUE', --> result: TRUE 
    TRUE = 'true', --> result: TRUE 
    'TRUE' = TRUE, --> result: FALSE 
    'true' = TRUE, --> result: TRUE 
    TRUE = '1', --> result: TRUE 
    '1' = TRUE, --> result: FALSE

Best-practice Comparison should be made between two values of the same type

Performance Rules

flag-function-application-in-where-on-indexed-columns

Avoid functions in where clauses over indexed, partitioned or clustered columns, to prevent filter push down and improve performance

Configuration:

workspace.sdf.yml

workspace: 
  ... 
--- 
linter: 
 ... 
 performance-rules: 
   ... 
   flag-function-application-in-where-on-indexed-columns: null # default (off)

Anti-pattern Use a function on an indexed column, which can cause a filter push down and hinder performance

SELECT a, b 
FROM my_table 
WHERE DATE(datetime_partition) >= DATE('2024-01-01')

Best-practice

SELECT a, b 
FROM my_table 
WHERE datetime_partition >= '2024-01-01'

Check Rules

Include SDF Checks as custom lint rules.

Configuration:

To enable an SDF check (e.g., check name: view_name_must_start_with_v_) as part of SDF lint, add the following configuration to your linter:

workspace.sdf.yml

workspace: 
  name: example_workspace_name
  ... 
--- 
linter: 
 ... 
 check-rules: 
   ... 
   # {workspace_name}.checks.{check_name}: on
   example_workspace_name.checks.view_name_must_start_with_v_: on

Learn how to create custom checks in our SDF Checks guide.

Reference

SQL Functions

​Syntax Rules

​case-keyword

​- SDF107

​case-literal

​- SDF108

​- SDF109

​case-type

​- SDF110

​case-function

​-SDF106

​disallow-these-identifiers

​-SDF124

​flag-keywords-used-as-identifiers

​-SDF125

​disallow-these-chars-in-quoted-identifiers

​-SDF117

​flag-unnecessary-quoted-identifiers

​-SDF118

​flag-inconsistent-qualified-column-reference

​-SDF112

​flag-unqualified-multi-source-column-reference

​-SDF116

​flag-inconsistent-ordinal-column-reference

​-SDF105

​flag-unnecessary-else

​-SDF119

​flag-unnecessary-case

​-SDF120

​flag-unused-cte

​-SDF121

​flag-unnecessary-nested-case

​-SDF122

​flag-distinct-parenthesis

​-SDF123

​disallow-subquery-in

​-SDF113

​flag-misordered-join-condition

​-SDF114

​flag-select-item-order

​-SDF115

​flag-trailing-semicolon-after-statements

​-SDF111

​Semantic Rules

​case-column

​case-table

​case-alias

​disallow-implicit-conversions-in

​DSF126

​Performance Rules

​flag-function-application-in-where-on-indexed-columns

​Check Rules

Syntax Rules

case-keyword

- `SDF107`

case-literal

- `SDF108`

- `SDF109`

case-type

- `SDF110`

case-function

-`SDF106`

disallow-these-identifiers

-`SDF124`

flag-keywords-used-as-identifiers

-`SDF125`

disallow-these-chars-in-quoted-identifiers

-`SDF117`

flag-unnecessary-quoted-identifiers

-`SDF118`

flag-inconsistent-qualified-column-reference

-`SDF112`

flag-unqualified-multi-source-column-reference

-`SDF116`

flag-inconsistent-ordinal-column-reference

-`SDF105`

flag-unnecessary-else

-`SDF119`

flag-unnecessary-case

-`SDF120`

flag-unused-cte

-`SDF121`

flag-unnecessary-nested-case

-`SDF122`

flag-distinct-parenthesis

-`SDF123`

disallow-subquery-in

-`SDF113`

flag-misordered-join-condition

-`SDF114`

flag-select-item-order

-`SDF115`

flag-trailing-semicolon-after-statements

-`SDF111`

Semantic Rules

case-column

case-table

case-alias

disallow-implicit-conversions-in

DSF126

Performance Rules

flag-function-application-in-where-on-indexed-columns

Check Rules