背景・目的
以前、下記で SQLFluff を試してみました。今回は設定ファイル（.sqlfluff）によるカスタマイズを試します。
まとめ
下記に特徴を整理します。
| 特徴 | 説明 |
|---|---|
| 設定ファイルの柔軟性 | .sqlfluff や pyproject.toml など複数の形式に対応。ネスト機能で DDL と DML で設定を分けられる |
| コーディング規約の反映 | 大文字小文字、インデント、AS 必須、エイリアス長など、チームの規約を設定に落とし込める |
| 禁止ワード | blocked_words で TEXT 型など特定のワードを検出可能。ただし自動修正はできない |
| サブクエリ制限 | FROM 句・JOIN 句のサブクエリを検出し、CTE への書き換えを促せる。WHERE 句は対象外 |
| fix の限界 | スタイル系（大文字小文字、インデント、AS 追加）は自動修正可能。構造的な問題（カラム修飾、禁止ワード）は手動対応が必要 |
概要
設定ファイルの種類
下記を基に整理します。
SQLFluff は以下のファイルから設定を読み込みます（後のものが優先）。
setup.cfgtox.inipep8.ini.sqlfluffpyproject.toml
いずれも INI 形式で、[sqlfluff] セクションに設定を記述します。pyproject.toml の場合は [tool.sqlfluff.core] セクションになります。
設定ファイルの配置とネスト
SQLFluff は以下の順序で設定ファイルを探し、後から見つかったものが前の設定を上書きします。
- OS のアプリ設定ディレクトリ（macOS:
~/.config/sqlfluff） - ホームディレクトリ（
~） - ホームディレクトリからカレントディレクトリまでの各ディレクトリ
- カレントディレクトリ
- lint 対象ファイルのディレクトリまでの各サブディレクトリ
これにより、プロジェクト全体の設定を親ディレクトリに、特定ディレクトリだけの設定をサブディレクトリに置く、という運用が可能です。
ファイル内コメントによる設定
SQL ファイル内にコメントで設定を記述することもできます。
-- sqlfluff:indentation:tab_space_size:2
-- sqlfluff:rules:capitalisation.keywords:capitalisation_policy:upper
SELECT *
FROM my_table
ファイル単位で設定を変えたい場合に使えます。
公式推奨のスターター設定
下記を基に整理します。
公式ドキュメントでは、新規プロジェクト向けに以下の方針を推奨しています。
- 設定ファイルは最小限にする（デフォルトと異なる部分だけ記述）
- 設定ファイルはチームの意思決定の記録として機能させる
- 新規プロジェクトではデフォルトより少し厳格にする
推奨設定の例:
[sqlfluff]
dialect = snowflake
templater = jinja
exclude_rules = ambiguous.column_count, structure.column_order
max_line_length = 120
processes = -1
[sqlfluff:indentation]
implicit_indents = allow
[sqlfluff:rules:aliasing.length]
min_alias_length = 3
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower
[sqlfluff:rules:convention.not_equal]
preferred_not_equal_style = c_style
デフォルト設定の主要項目
下記を基に整理します。
基本設定 [sqlfluff]
| 設定 | デフォルト | 説明 |
|---|---|---|
| dialect | None | SQL 方言（必須） |
| templater | jinja | テンプレートエンジン（raw/jinja/python/placeholder） |
| rules | all | 適用ルール |
| exclude_rules | None | 除外ルール |
| max_line_length | 80 | 最大行長 |
| processes | 1 | 並列プロセス数（-1 で全 CPU - 1） |
| fix_even_unparsable | False | パースエラーがあっても fix するか（非推奨） |
インデント [sqlfluff:indentation]
| 設定 | デフォルト | 説明 |
|---|---|---|
| indent_unit | space | space or tab |
| tab_space_size | 4 | スペース数 |
| indented_joins | False | JOIN をインデントするか |
| indented_ctes | False | CTE をインデントするか |
| indented_using_on | True | ON/USING をインデントするか |
| implicit_indents | forbid | 暗黙インデントの許可（forbid/allow/require） |
| trailing_comments | before | 長い行のコメント移動先（before/after） |
大文字小文字 [sqlfluff:rules:capitalisation.*]
| 対象 | セクション | デフォルト | 選択肢 |
|---|---|---|---|
| キーワード | capitalisation.keywords | consistent | consistent/upper/lower/capitalise |
| 関数名 | capitalisation.functions | consistent | 同上 |
| 識別子 | capitalisation.identifiers | consistent | 同上 |
| リテラル（NULL, TRUE等） | capitalisation.literals | consistent | 同上 |
| データ型 | capitalisation.types | consistent | 同上 |
エイリアス [sqlfluff:rules:aliasing.*]
| 設定 | デフォルト | 説明 |
|---|---|---|
| aliasing.table | explicit | テーブルエイリアスに AS を強制 |
| aliasing.column | explicit | カラムエイリアスに AS を強制 |
| aliasing.length | None | エイリアスの最小/最大長 |
その他のルール
| セクション | デフォルト | 説明 |
|---|---|---|
| convention.terminator | require_final_semicolon = False | セミコロンの扱い |
| convention.select_trailing_comma | forbid | SELECT 末尾カンマを禁止 |
| convention.blocked_words | None | 使用禁止ワード |
| convention.not_equal | consistent | 不等号スタイル（!= or <>） |
| structure.subquery | forbid_subquery_in = join | サブクエリの使用制限 |
実践
設定ファイルの作成
1.カレントディレクトリに、.sqlfluffを用意します
dialect の設定
1.下記のように書きます
[sqlfluff]
dialect = redshift
2.以前は、パラメータで指定しましたが、.sqlfluffから読み込んでlintされるか確認します
sqlfluff lint sql/redshift_bad.sql
== [sql/redshift_bad.sql] FAIL
L: 10 | P: 29 | LT01 | Expected single whitespace between 'IDENTITY' keyword
| and start bracket '('. [layout.spacing]
L: 17 | P: 8 | LT01 | Expected single whitespace between 'DISTKEY' keyword and
| start bracket '('. [layout.spacing]
L: 18 | P: 8 | LT01 | Expected single whitespace between 'SORTKEY' keyword and
| start bracket '('. [layout.spacing]
L: 27 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 30 | P: 6 | LT02 | Expected line break and indent of 4 spaces before 's'.
| [layout.indent]
L: 41 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 44 | P: 5 | LT09 | Select targets should be on a new line unless there is
| only one select target. [layout.select_targets]
All Finished!
下記と同じになりました
コーディング規約を設定する
一般的に有り得そうなルールを設定します。
| 規約 | .sqlfluff 設定 |
|---|---|
| キーワード大文字 | capitalisation.keywords = upper |
| 関数名大文字 | capitalisation.functions = upper |
| テーブル名・列名小文字 | capitalisation.identifiers = lower |
| NULL/TRUE 等大文字 | capitalisation.literals = upper |
| データ型大文字 | capitalisation.types = upper |
| 4スペースインデント | tab_space_size = 4 |
| AS 必須 | aliasing.table = explicit, aliasing.column = explicit |
| 行長 | max_line_length = 120（80 だと DDL で厳しいため緩めに） |
設定
1.下記のように設定します
[sqlfluff]
dialect = redshift
max_line_length = 120
[sqlfluff:indentation]
tab_space_size = 4
indent_unit = space
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = upper
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = upper
[sqlfluff:rules:aliasing.table]
aliasing = explicit
[sqlfluff:rules:aliasing.column]
aliasing = explicit
2.違反するようなクエリ（sql/coding_rule_bad.sql）を書いてみます
-- コーディング規約違反のサンプルSQL（.sqlfluff設定検証用）
-- 違反ポイント:
-- キーワード小文字、関数名小文字、エイリアスAS省略、
-- インデント2スペース、識別子大文字混在、Redshift固有構文
drop table if exists tmp_base_orders;
create table tmp_base_orders as
select
Order_Id,
order_date,
coalesce(Status, 'unknown') as status_name,
count(*) cnt,
sum(Amount) total_amount,
listagg(distinct Status, ', ') within group(order by Status) status_list,
getdate() as current_ts
from dwh.fact_orders orders
left join dwh.dim_status status
on orders.status = status.status_code
where order_date = '2025-01-01'
and status in ('ACTIVE', 'PENDING')
group by Order_Id, order_date, Status
having count(*) > 1
order by total_amount desc;
select
a.Order_Id,
a.total_amount,
approximate percentile_disc(0.5) within group (order by a.total_amount) over() median_amount
from tmp_base_orders a
where a.total_amount > 0;
unload ('select * from tmp_base_orders')
to 's3://my-bucket/export/orders_'
iam_role 'arn:aws:iam::XXXXXXXXXXXX:role/RedshiftRole'
delimiter ','
header
gzip;
lintの実行
1.lintを実行します
sqlfluff lint sql/coding_rule_bad.sql
== [sql/coding_rule_bad.sql] FAIL
L: 6 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 6 | P: 6 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 6 | P: 12 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 6 | P: 15 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 8 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 8 | P: 8 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 8 | P: 30 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 9 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 10 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 10 | P: 3 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 10 | P: 3 | RF02 | Unqualified reference 'Order_Id' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 11 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 11 | P: 3 | RF02 | Unqualified reference 'order_date' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 12 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 12 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 12 | P: 12 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 12 | P: 12 | RF02 | Unqualified reference 'Status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 12 | P: 31 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 13 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 13 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 13 | P: 12 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 14 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 14 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 14 | P: 7 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 14 | P: 7 | RF02 | Unqualified reference 'Amount' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 14 | P: 15 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 15 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 15 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 15 | P: 11 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 20 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 15 | P: 20 | RF02 | Unqualified reference 'Status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 15 | P: 34 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 41 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 46 | LT01 | Expected single whitespace between 'group' keyword and
| start bracket '('. [layout.spacing]
L: 15 | P: 47 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 53 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 56 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 15 | P: 56 | RF02 | Unqualified reference 'Status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 15 | P: 64 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 16 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 16 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 16 | P: 13 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 17 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 17 | P: 22 | AL01 | Implicit/explicit aliasing of table. [aliasing.table]
L: 18 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 18 | P: 6 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 18 | P: 26 | AL01 | Implicit/explicit aliasing of table. [aliasing.table]
L: 19 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 19 | P: 3 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 20 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 20 | P: 6 | LT02 | Expected line break and indent of 4 spaces before
| 'order_date'. [layout.indent]
L: 20 | P: 7 | RF02 | Unqualified reference 'order_date' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 21 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 21 | P: 3 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 21 | P: 7 | RF02 | Unqualified reference 'status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 21 | P: 14 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 22 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 22 | P: 7 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 22 | P: 10 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 22 | P: 10 | RF02 | Unqualified reference 'Order_Id' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 22 | P: 20 | RF02 | Unqualified reference 'order_date' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 22 | P: 32 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 22 | P: 32 | RF02 | Unqualified reference 'Status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 23 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 23 | P: 8 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 24 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 24 | P: 7 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 24 | P: 23 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 26 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 27 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 27 | P: 5 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 28 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 29 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 29 | P: 3 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 15 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 29 | P: 36 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 43 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 50 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 56 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 75 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 79 | LT01 | Expected single whitespace between 'over' keyword and
| start bracket '('. [layout.spacing]
L: 29 | P: 82 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 30 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 30 | P: 22 | AL01 | Implicit/explicit aliasing of table. [aliasing.table]
L: 31 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 33 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 34 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 35 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 36 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 37 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 38 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
All Finished!
大量に検出されました。まとめると下記のようなエラーでした
| ルール | 件数 | 内容 |
|---|---|---|
| CP01 | 37 | キーワードが大文字ではない |
| CP02 | 7 | 識別子が小文字ではない（Order_Id, Status, Amount） |
| CP03 | 7 | 関数名が大文字ではない（coalesce, count, sum 等） |
| LT02 | 14 | インデントが4スペースではない |
| AL01 | 3 | テーブルエイリアスに AS がない |
| AL02 | 4 | カラムエイリアスに AS がない |
| RF02 | 12 | 修飾なしカラム参照 |
| LT01 | 2 | スペーシング |
fixの実行
- fix用のコードをコピーします
cp ./sql/coding_rule_bad.sql ./sql/coding_rule_fixed.sql
2.fixを実行します
sqlfluff fix sql/coding_rule_fixed.sql
==== finding fixable violations ====
== [sql/coding_rule_fixed.sql] FAIL
L: 6 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 6 | P: 6 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 6 | P: 12 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 6 | P: 15 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 8 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 8 | P: 8 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 8 | P: 30 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 9 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 10 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 10 | P: 3 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 11 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 12 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 12 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 12 | P: 12 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 12 | P: 31 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 13 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 13 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 13 | P: 12 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 14 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 14 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 14 | P: 7 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 14 | P: 15 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 15 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 15 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 15 | P: 11 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 20 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 15 | P: 34 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 41 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 46 | LT01 | Expected single whitespace between 'GROUP' keyword and
| start bracket '('. [layout.spacing]
L: 15 | P: 47 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 53 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 15 | P: 56 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 15 | P: 64 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 16 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 16 | P: 3 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 16 | P: 13 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 17 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 17 | P: 22 | AL01 | Implicit/explicit aliasing of table. [aliasing.table]
L: 18 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 18 | P: 6 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 18 | P: 26 | AL01 | Implicit/explicit aliasing of table. [aliasing.table]
L: 19 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 19 | P: 3 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 20 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 20 | P: 6 | LT02 | Expected line break and indent of 4 spaces before
| 'order_date'. [layout.indent]
L: 21 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 21 | P: 3 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 21 | P: 14 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 22 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 22 | P: 7 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 22 | P: 10 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 22 | P: 32 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 23 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 23 | P: 8 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 24 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 24 | P: 7 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 24 | P: 23 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 26 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 27 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 27 | P: 5 | CP02 | Unquoted identifiers must be lower case.
| [capitalisation.identifiers]
L: 28 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 29 | P: 1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L: 29 | P: 3 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 15 | CP03 | Function names must be upper case.
| [capitalisation.functions]
L: 29 | P: 36 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 43 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 50 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 56 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 75 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 29 | P: 79 | LT01 | Expected single whitespace between 'OVER' keyword and
| start bracket '('. [layout.spacing]
L: 29 | P: 82 | AL02 | Implicit/explicit aliasing of columns. [aliasing.column]
L: 30 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 30 | P: 22 | AL01 | Implicit/explicit aliasing of table. [aliasing.table]
L: 31 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 33 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 34 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 35 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 36 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 37 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
L: 38 | P: 1 | CP01 | Keywords must be upper case. [capitalisation.keywords]
== [sql/coding_rule_fixed.sql] FIXED
80 fixable linting violations found
[11 unfixable linting violations found]
80件修正、11件は自動修正不可でした。
lintの実行（fix後）
1.修正後のコードにlintを実行します
sqlfluff lint sql/coding_rule_fixed.sql
== [sql/coding_rule_fixed.sql] FAIL
L: 10 | P: 5 | RF02 | Unqualified reference 'order_id' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 11 | P: 5 | RF02 | Unqualified reference 'order_date' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 12 | P: 14 | RF02 | Unqualified reference 'status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 14 | P: 9 | RF02 | Unqualified reference 'amount' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 15 | P: 22 | RF02 | Unqualified reference 'status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 15 | P: 59 | RF02 | Unqualified reference 'status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 21 | P: 5 | RF02 | Unqualified reference 'order_date' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 22 | P: 9 | RF02 | Unqualified reference 'status' found in select with more
| than one referenced table/view.
| [references.qualification]
L: 23 | P: 10 | RF02 | Unqualified reference 'order_id' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 23 | P: 20 | RF02 | Unqualified reference 'order_date' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 23 | P: 32 | RF02 | Unqualified reference 'status' found in select with more
| than one referenced table/view.
| [references.qualification]
All Finished!
残りは RF02（修飾なしカラム参照）の11件だけでした。
自動修正できない問題で、人間がどのテーブルのカラムか明示する必要があるようです。
コードを修正（RF02に対応するため直接修正）
- 上記の指摘通り、修飾名をつけ加えます
lintの実行（手動修正後）
- lintを実行します。エラーはありません。全件PASSです
sqlfluff lint sql/coding_rule_fixed.sql
All Finished!
修正前後の確認
1.diffをとります
diff sql/coding_rule_bad.sql sql/coding_rule_fixed.sql
6c6
< drop table if exists tmp_base_orders;
---
> DROP TABLE IF EXISTS tmp_base_orders;
8,24c8,25
< create table tmp_base_orders as
< select
< Order_Id,
< order_date,
< coalesce(Status, 'unknown') as status_name,
< count(*) cnt,
< sum(Amount) total_amount,
< listagg(distinct Status, ', ') within group(order by Status) status_list,
< getdate() as current_ts
< from dwh.fact_orders orders
< left join dwh.dim_status status
< on orders.status = status.status_code
< where order_date = '2025-01-01'
< and status in ('ACTIVE', 'PENDING')
< group by Order_Id, order_date, Status
< having count(*) > 1
< order by total_amount desc;
---
> CREATE TABLE tmp_base_orders AS
> SELECT
> orders.order_id,
> orders.order_date,
> COALESCE(orders.status, 'unknown') AS status_name,
> COUNT(*) AS cnt,
> SUM(orders.amount) AS total_amount,
> LISTAGG(DISTINCT orders.status, ', ') WITHIN GROUP (ORDER BY orders.status) AS status_list,
> GETDATE() AS current_ts
> FROM dwh.fact_orders AS orders
> LEFT JOIN dwh.dim_status AS status
> ON orders.status = status.status_code
> WHERE
> orders.order_date = '2025-01-01'
> AND orders.status IN ('ACTIVE', 'PENDING')
> GROUP BY orders.order_id, orders.order_date, orders.status
> HAVING COUNT(*) > 1
> ORDER BY total_amount DESC;
26,31c27,32
< select
< a.Order_Id,
< a.total_amount,
< approximate percentile_disc(0.5) within group (order by a.total_amount) over() median_amount
< from tmp_base_orders a
< where a.total_amount > 0;
---
> SELECT
> a.order_id,
> a.total_amount,
> APPROXIMATE PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a.total_amount) OVER () AS median_amount
> FROM tmp_base_orders AS a
> WHERE a.total_amount > 0;
33,38c34,39
< unload ('select * from tmp_base_orders')
< to 's3://my-bucket/export/orders_'
< iam_role 'arn:aws:iam::XXXXXXXXXXXX:role/RedshiftRole'
< delimiter ','
< header
< gzip;
---
> UNLOAD ('select * from tmp_base_orders')
> TO 's3://my-bucket/export/orders_'
> IAM_ROLE 'arn:aws:iam::XXXXXXXXXXXX:role/RedshiftRole'
> DELIMITER ','
> HEADER
> GZIP;
主な変更点は、下記のとおりです
| 修正内容 | 修正前 | 修正後 | 修正方法 |
|---|---|---|---|
| キーワード大文字化 | select, from, where 等 | SELECT, FROM, WHERE 等 | fix |
| 関数名大文字化 | coalesce, count, sum 等 | COALESCE, COUNT, SUM 等 | fix |
| 識別子小文字化 | Order_Id, Status, Amount | order_id, status, amount | fix |
| インデント | 2スペース | 4スペース | fix |
| AS 追加 | orders, cnt, total_amount | AS orders, AS cnt, AS total_amount | fix |
| カラム修飾追加 | order_id | orders.order_id | 手動 |
DDL の桁揃えスタイルとの共存
DDL でカラム定義を桁揃えしているプロジェクトでは、LT01（スペーシング）が大量に検出されます。
例えば、下記のようなものです。
-- 桁揃えスタイル（人間には読みやすいが LT01 違反）
CREATE TABLE sales_summary (
sale_id INTEGER NOT NULL,
customer_name VARCHAR(100) NOT NULL,
amount DECIMAL(12,2) NOT NULL
);
SQLFluff はこの余分なスペースを「不要」と判断しますが、DDL の桁揃えはチームの可読性を高める一般的なスタイルであり、NG ではありません。
対処法としては、SQLFluff のネスト機能を活用し、DDL ディレクトリにだけ LT01 を除外した .sqlfluff を配置する方法があります。
project/
├── .sqlfluff # DML用（LT01 有効）
└── ddl/
├── .sqlfluff # DDL用（exclude_rules = LT01）
└── tables/
これにより DML のスペーシングチェックは維持しつつ、DDL の桁揃えスタイルも許容できます。
コードの準備
- 下記のようなクエリを書きます
CREATE TABLE dwh.sales_summary (
sale_id INTEGER NOT NULL,
customer_name VARCHAR(100) NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(12,2) NOT NULL,
region VARCHAR(50) NOT NULL,
status VARCHAR(20) DEFAULT 'ACTIVE',
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
)
DISTSTYLE KEY
DISTKEY (region)
SORTKEY (sale_date, region);
lintの実行（LT01有効）
- まず、親の
.sqlfluff（LT01）でlintします
sqlfluff lint sql/ddl/cre_sales_summary.sql
== [sql/ddl/cre_sales_summary.sql] FAIL
L: 2 | P: 12 | LT01 | Expected only single space before 'INTEGER' keyword.
| Found ' '. [layout.spacing]
L: 2 | P: 30 | LT01 | Expected only single space before 'NOT' keyword. Found '
| '. [layout.spacing]
L: 3 | P: 18 | LT01 | Expected only single space before 'VARCHAR' keyword.
| Found ' '. [layout.spacing]
L: 3 | P: 35 | LT01 | Expected only single space before 'NOT' keyword. Found '
| '. [layout.spacing]
L: 4 | P: 14 | LT01 | Expected only single space before 'DATE' keyword. Found
| ' '. [layout.spacing]
L: 4 | P: 27 | LT01 | Expected only single space before 'NOT' keyword. Found '
| '. [layout.spacing]
L: 5 | P: 11 | LT01 | Expected only single space before 'DECIMAL' keyword.
| Found ' '. [layout.spacing]
L: 5 | P: 34 | LT01 | Expected single whitespace between comma ',' and numeric
| literal. [layout.spacing]
L: 6 | P: 11 | LT01 | Expected only single space before 'VARCHAR' keyword.
| Found ' '. [layout.spacing]
L: 6 | P: 34 | LT01 | Expected only single space before 'NOT' keyword. Found '
| '. [layout.spacing]
L: 7 | P: 11 | LT01 | Expected only single space before 'VARCHAR' keyword.
| Found ' '. [layout.spacing]
L: 7 | P: 34 | LT01 | Expected only single space before 'DEFAULT' keyword.
| Found ' '. [layout.spacing]
L: 8 | P: 15 | LT01 | Expected only single space before 'TIMESTAMPTZ' keyword.
| Found ' '. [layout.spacing]
L: 8 | P: 34 | LT01 | Expected only single space before 'NOT' keyword. Found '
| '. [layout.spacing]
L: 9 | P: 15 | LT01 | Expected only single space before 'TIMESTAMPTZ' keyword.
| Found ' '. [layout.spacing]
L: 9 | P: 34 | LT01 | Expected only single space before 'NOT' keyword. Found '
| '. [layout.spacing]
All Finished!
LT01 が16件検出されました。
.sqlfluffを配置
親の設定が引き継がれるので、変更点のみ記載します
https://docs.sqlfluff.com/en/stable/configuration/setting_configuration.html#nesting
- ddlディレクトリ直下に
.sqlfluffを用意します
[sqlfluff]
exclude_rules = LT01
lintの実行（LT01を無効）
1.lintを実行します
sqlfluff lint sql/ddl/cre_sales_summary.sql
All Finished!
PASSされました。LT01が除外され、桁揃えDDLが違反ゼロになりました
親の.sqlfluff（LT01有効）では、16件検出され、DDL用の .sqlfluffで0件になりました。ネストが効いています
禁止ワードの設定（convention.blocked_words）
プロジェクトによっては、特定のデータ型や構文を禁止したいケースがあります。例えば Redshift では TEXT 型は内部的に VARCHAR(256) として扱われるため、サイズを明示した VARCHAR(n) の使用を推奨するプロジェクトがあります。
SQLFluff の convention.blocked_words ルールを使えば、禁止ワードを設定して lint で検出できます。
[sqlfluff:rules:convention.blocked_words]
blocked_words = TEXT
コードの作成
1.sql/ddl/cre_test_blocked.sqlを作成します
CREATE TABLE dwh.test_blocked (
id INTEGER NOT NULL,
name TEXT NOT NULL,
description TEXT,
memo VARCHAR(500)
);
.sqlfluffを修正
- DDLだけではなく、DMLでもCASTなどで指定される可能性があるため、親の
.sqlfluffを修正します
[sqlfluff]
dialect = redshift
max_line_length = 120
[sqlfluff:indentation]
tab_space_size = 4
indent_unit = space
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = upper
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = upper
[sqlfluff:rules:aliasing.table]
aliasing = explicit
[sqlfluff:rules:aliasing.column]
aliasing = explicit
# 新規追加
[sqlfluff:rules:convention.blocked_words]
blocked_words = TEXT
lintの実行
- lintを実行します
sqlfluff lint sql/ddl/cre_test_blocked.sql
== [sql/ddl/cre_test_blocked.sql] FAIL
L: 3 | P: 17 | CV09 | Use of blocked word 'TEXT'. [convention.blocked_words]
L: 4 | P: 17 | CV09 | Use of blocked word 'TEXT'. [convention.blocked_words]
All Finished!
TEXT が2箇所で検出されました。（CV09）
blocked_words が効いています。VARCHAR(500) は問題なし、TEXT だけが検出されました。
コードの修正
fix機能では修正できないので、手動で修正します
1.はじめにコピーします
cp ./sql/ddl/cre_test_blocked.sql ./sql/ddl/cre_test_blocked_fixed.sql
2.修正します
CREATE TABLE dwh.test_blocked (
id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(500),
memo VARCHAR(500)
);
3.差分は以下のとおりです。TEXT型からVARCHAR(n)に変えました
diff sql/ddl/cre_test_blocked.sql sql/ddl/cre_test_blocked_fixed.sql
3,4c3,4
< name TEXT NOT NULL,
< description TEXT,
---
> name VARCHAR(100) NOT NULL,
> description VARCHAR(500),
lintの実行（修正後）
- lintを実行します。PASSしました
sqlfluff lint sql/ddl/cre_test_blocked_fixed.sql
All Finished!
サブクエリの禁止（structure.subquery）
よくある内容として、複雑なクエリを抑止するため、WITH を使用し、サブクエリは最小限にすることを推奨している場合があります。
SQLFluff の structure.subquery ルールを使えば、FROM 句・JOIN 句のサブクエリを検出できます。
[sqlfluff:rules:structure.subquery]
forbid_subquery_in = both
下記のような、設定が可能です。
| 設定値 | 意味 |
|---|---|
| join | JOIN 句内のサブクエリを禁止（デフォルト） |
| from | FROM 句内のサブクエリを禁止 |
| both | FROM 句・JOIN 句の両方で禁止 |
コードの準備
1.下記のようなクエリを用意します
-- サブクエリ禁止ルール検証用SQL
-- FROM句、JOIN句、WHERE句にサブクエリを使用
SELECT
orders.order_id,
orders.amount,
summary.total_amount
FROM (
SELECT
order_id,
amount,
region
FROM dwh.fact_orders
WHERE order_date = '2025-01-01'
) AS orders
INNER JOIN (
SELECT
region,
SUM(amount) AS total_amount
FROM dwh.fact_orders
GROUP BY region
) AS summary
ON orders.region = summary.region
WHERE orders.order_id IN (
SELECT order_id
FROM dwh.fact_returns
WHERE return_date = '2025-01-01'
);
.sqlfluffを修正
1.sqlfluffを修正します
[sqlfluff]
dialect = redshift
max_line_length = 120
[sqlfluff:indentation]
tab_space_size = 4
indent_unit = space
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = upper
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = upper
[sqlfluff:rules:aliasing.table]
aliasing = explicit
[sqlfluff:rules:aliasing.column]
aliasing = explicit
[sqlfluff:rules:convention.blocked_words]
blocked_words = TEXT
# 新規追加
[sqlfluff:rules:structure.subquery]
forbid_subquery_in = both
lintの実行
- lintを実行します
sqlfluff lint sql/subquery_bad.sql
== [sql/subquery_bad.sql] FAIL
L: 8 | P: 6 | ST05 | select_statement clauses should not contain subqueries.
| Use CTEs instead [structure.subquery]
L: 16 | P: 12 | ST05 | select_statement clauses should not contain subqueries.
| Use CTEs instead [structure.subquery]
L: 25 | P: 12 | RF02 | Unqualified reference 'order_id' found in select with
| more than one referenced table/view.
| [references.qualification]
L: 27 | P: 11 | RF02 | Unqualified reference 'return_date' found in select with
| more than one referenced table/view.
| [references.qualification]
All Finished!
下記のような結果になりました
| 行 | ルール | 内容 |
|---|---|---|
| L:8 | ST05 | FROM 句のサブクエリ → CTE を使え |
| L:16 | ST05 | JOIN 句のサブクエリ → CTE を使え |
| L:25 | RF02 | WHERE 句のサブクエリ内 → 検出されない |
FROM 句と JOIN 句のサブクエリは ST05 で検出され、WHERE 句のサブクエリは検出されていません。想定通りです。
コードの修正
1.コピーします
cp ./sql/subquery_bad.sql ./sql/subquery_fixed.sql
2.修正します
-- サブクエリをCTEに置き換えたSQL
WITH orders AS (
SELECT
order_id,
amount,
region
FROM dwh.fact_orders
WHERE order_date = '2025-01-01'
),
summary AS (
SELECT
region,
SUM(amount) AS total_amount
FROM dwh.fact_orders
GROUP BY region
)
SELECT
orders.order_id,
orders.amount,
summary.total_amount
FROM orders
INNER JOIN summary
ON orders.region = summary.region
WHERE orders.order_id IN (
SELECT fact_returns.order_id
FROM dwh.fact_returns
WHERE fact_returns.return_date = '2025-01-01'
);
3.差分を確認します
1,2c1
< -- サブクエリ禁止ルール検証用SQL
< -- FROM句、JOIN句、WHERE句にサブクエリを使用
---
> -- サブクエリをCTEに置き換えたSQL
4,8c3
< SELECT
< orders.order_id,
< orders.amount,
< summary.total_amount
< FROM (
---
> WITH orders AS (
15,16c10,12
< ) AS orders
< INNER JOIN (
---
> ),
>
> summary AS (
22c18,25
< ) AS summary
---
> )
>
> SELECT
> orders.order_id,
> orders.amount,
> summary.total_amount
> FROM orders
> INNER JOIN summary
25c28
< SELECT order_id
---
> SELECT fact_returns.order_id
27c30
< WHERE return_date = '2025-01-01'
---
> WHERE fact_returns.return_date = '2025-01-01'
lintの実行（修正後）
- lintを実行します
sqlfluff lint sql/subquery_fixed.sql
All Finished!
PASS です。違反ゼロになりました。
考察
今回の検証を通じて、SQLFluff の設定ファイルでコーディング規約をどこまで自動化できるかが見えてきました。
-
コーディング規約の自動化
- .sqlfluff の設定で想定のおおよそがカバーできた
- 特に大文字小文字、インデント、AS 必須などのスタイル系は fix で自動修正まで可能
- コメント規約などは SQLFluff では対応できず、生成AIなどで補完する必要がある
-
ネスト機能の実用性
- DDL の桁揃えスタイルとDMLスタイルの共存が課題だったが、サブディレクトリに .sqlfluff を配置するネスト機能で解決できた
- DML と DDL で異なるルールを適用できるのは実運用で大きなメリット
-
禁止ワードの活用
- blocked_words は TEXT 型の禁止だけでなく、プロジェクト固有の非推奨構文の検出にも応用できる
- ただし自動修正はできないため、検出→手動修正の運用になる
-
設定ファイルの運用
- 公式ドキュメントでは、設定ファイルはデフォルトと異なる部分だけ記述することを推奨している
- こうすることで「チームとしてどのような選択をしたか」が明確になり、設定ファイル自体がコーディング規約のドキュメントとして機能する
参考