Reference

Exit Codes

By default, SDF exits with the following status codes

0 if no linter violations were found
0 if linter completes successfully with any number of linter warnings
64 if the linter fails for syntax reasons in any file

In CI/CD scenarios you may want to have the linter exit with a non-zero code if the linter finds any violations. For this, SDF supports the -w error flag which turns any lint warning into an error. In sdf yml, you configure this as follows::

---
sdf-args:
  # turns lint warnings into errors with non-zero exit code
  lint: >
    -w error 

Capitalization Keywords

SDF107

Inconsistent capitalization of keywords

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`capitalization-keywords`	SDF107	L010	✅

Example: sdf lint -w capitalization-keywords=upper Configuration:

workspace.sdf.yml

sdf-args
   lint: >
      -w capitalization-keywords=consistent 
      ...
   
# capitalization_keywords is one of [consistent, upper, lower, pascal, snake, camel, off]
# Default: capitalization-keywords=consistent

Anti-pattern Referencing keywords in inconsistent capitalization. All keywords should be either uppercase or lowercase, not both. Keywords are dialect-specific.

select a 
FROM my_table 

Best-practice

SELECT a 
FROM my_table 
 
-- or 
 
select a 
from my_table 

Capitalization Literals

SDF108

SDF109

Inconsistent capitalization of boolean/null literal

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`capitalization-literals`	SDF108, SDF109	L040	✅

Example: sdf lint -w capitalization-literals=upper Configuration:

workspace.sdf.yml

sdf-args
   lint: >
      -w capitalization-keywords=consistent 
      ...
   # capitalization-literals is one of [consistent, upper, lower, pascal, snake, camel, off] 
   # default: capitalization-literals: consistent

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 

Capitalization Types

SDF110

Inconsistent capitalization of datatypes.

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`capitalization-types`	SDF110	L063	✅

Example: sdf lint -w capitalization-types=upper Configuration:

workspace.sdf.yml

sdf-args
   lint: >
      -w capitalization-types=consistent 
      ...
   # capitalization-types is one of [consistent, upper, lower, pascal, snake, camel, off] 
   # default: capitalization-types: consistent

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) 
); 

Capitalization Functions

SDF106

Inconsistent capitalization of function names

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`capitalization-functions`	SDF106	L030	✅

Example: sdf lint -w capitalization-functions=upper Configuration:

workspace.sdf.yml

sdf-args
   lint: >
      -w capitalization-functions=consistent 
      ...
   # capitalization-functions is one of [consistent, upper, lower, pascal, snake, camel, off] 
   # default: capitalization-functions: consistent

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 

Convention Blocked Words

SDF124

Specify a list of names to block from being identifiers.

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`convention-blocked-words`	SDF124	L062	❌

Examples:

Blocking a single word: sdf lint -w convention-blocked-words=department
Blocking multiple words: sdf lint -w convention-blocked-words=word1,word2,...,wordn

Configuration:

workspace.sdf.yml

sdf-args
   lint: >
      -w convention-blocked-words=foooo
      ...
   # default: []

References Keywords

SDF125

Keywords should not be used as identifiers

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`references-keywords`	SDF125	L029	❌

Example: sdf lint -w references-keywords Configuration:

workspace.sdf.yml

sdf-args
   lint: >
      -w references-keywords
      ...

Anti-pattern Using built-in keywords or functions as identifiers. sss

SELECT
    sum.a
FROM foo AS sum

Best-practice

SELECT
    vee.a
FROM foo AS vee

References Special Chars

SDF117

Do not use special characters in identifiers

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`references-special-chars`	SDF117	L057	❌

Example:

Disallows $ character: sdf lint -w references-special-chars=$
Disallows $ and . characters: sdf lint -w references-special-chars=$.

Configuration:

workspace.sdf.yml

sdf-args:
   # special_chars is a string of chars to block (e.g., "%<>"")
   lint: >
      -w references-special-chars=$.%<>
      ...

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 

References Quoting

SDF118

Unnecessary quoted identifier

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`references-quoting`	SDF118	L059	❌

Example: sdf lint -w references-quoting Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w references-quoting
      ...

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

References Qualification

SDF116

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

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`references-qualification`	SDF116	L027	❌

Example: sdf lint -w references-qualification Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w references-qualification
      ...

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) 

Ambiguous Column References

SDF105

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

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`ambiguous-column-references`	SDF105	L054	❌

Example: sdf lint -w ambiguous-column-references Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w ambiguous-column-references
      ...

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 

Structure Else Null

SDF119

Redundant ELSE NULL in a case when statement

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure-else-null`	SDF119	L035	❌

Example: sdf lint -w structure-else-null Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w structure-else-null
      ...

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 

Structure Simple Case

SDF120

CASE statement can be simplified

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure-simple-case`	SDF120	L043	❌

Example: sdf lint -w structure-simple-case Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w structure-simple-case
      ...

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 

Structure Unused Cte

SDF121

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

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure-unused-cte`	SDF121	L045	❌

Example: sdf lint -w structure-unused-cte Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w structure-unused-cte
      ...

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 

Structure Nested Case

SDF122

Nested CASE statement in ELSE clause could be flattened.

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure-nested-case`	SDF122	L058	❌

Example: sdf lint -w structure-nested-case Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w structure-nested-case
      ...

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 

Structure Distinct

SDF123

DISTINCT used with parentheses

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure-distinct`	SDF123	L015	❌

Example: sdf lint -w structure-distinct Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w structure-distinct
      ...

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 

Structure Subquery

SDF113

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

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure-subquery`	SDF113	L042	❌

Example: sdf lint -w structure-subquery=join Configuration:

workspace.sdf.yml

# subquery_scope is one of ['join', 'from', 'both'] 
sdf-args:
   lint: >
      -w structure-subquery
      ...

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 

Structure Join Condition Order

SDF114

Join conditions column references should follow tables reference order

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure_join_condition_order`	SDF114	ST09	❌

Example: sdf lint -w structure_join_condition_order Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w structure_join_condition_order
      ...

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 
) 

Structure Column Order

SDF115

Select wildcards then simple targets before calculations and aggregates.

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`structure-column-order`	SDF115	L034	❌

Example: sdf lint -w structure-column-order Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w structure-column-order
      ...

Anti-pattern Wildcard listed after single targets

SELECT 
   a, 
   *, 
   b 
FROM my_table 

Best-practice

   *, 
   a, 
   b 
FROM my_table 

Convention Terminator

SDF111

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.

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`convention-terminator`	SDF111	L052	✅

Example: sdf lint -w convention-terminator Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w convention-terminator
      ...

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 

Convention Comma

SDF104

Avoid trailing commas in lists.

Configuration	SDF Error	SQLFluff Alias	Auto-fix
`convention-comma`	SDF104	N/A	✅

Configuration:

workspace.sdf.yml

sdf-args:
   lint: >
      -w convention-comma
      ...

Anti-pattern

SELECT
    ['Very', 'Long', 'List',] AS mylist

Best-practice

SELECT
    ['Very', 'Long', 'List'] AS mylist

Linter

​Exit Codes

​Capitalization Keywords

​SDF107

​Capitalization Literals

​SDF108

​SDF109

​Capitalization Types

​SDF110

​Capitalization Functions

​SDF106

​Convention Blocked Words

​SDF124

​References Keywords

​SDF125

​References Special Chars

​SDF117

​References Quoting

​SDF118

​References Qualification

​SDF116

​Ambiguous Column References

​SDF105

​Structure Else Null

​SDF119

​Structure Simple Case

​SDF120

​Structure Unused Cte

​SDF121

​Structure Nested Case

​SDF122

​Structure Distinct

​SDF123

​Structure Subquery

​SDF113

​Structure Join Condition Order

​SDF114

​Structure Column Order

​SDF115

​Convention Terminator

​SDF111

​Convention Comma

​SDF104

Exit Codes

Capitalization Keywords

SDF107

Capitalization Literals

SDF108

SDF109

Capitalization Types

SDF110

Capitalization Functions

SDF106

Convention Blocked Words

SDF124

References Keywords

SDF125

References Special Chars

SDF117

References Quoting

SDF118

References Qualification

SDF116

Ambiguous Column References

SDF105

Structure Else Null

SDF119

Structure Simple Case

SDF120

Structure Unused Cte

SDF121

Structure Nested Case

SDF122

Structure Distinct

SDF123

Structure Subquery

SDF113

Structure Join Condition Order

SDF114

Structure Column Order

SDF115

Convention Terminator

SDF111

Convention Comma

SDF104