SQLFluff dbt Templater

Warnings

• gotcha The `dbt` templater is not the default for SQLFluff; the default is `jinja`. You must explicitly set `templater = dbt` in your `.sqlfluff` configuration file. This setting cannot be overridden in subdirectory configuration files. [1]
  Fix: Add `templater = dbt` under the `[sqlfluff]` section in your project's root `.sqlfluff` configuration file.
• breaking Version compatibility with `dbt-core` can be a breaking change. SQLFluff 4.x explicitly drops support for `dbt` versions 1.4 and older, and supports up to `dbt` 1.10. Newer `dbt-core` releases (e.g., 1.8) have historically introduced incompatibilities that required `sqlfluff` updates. [8, 17, 19] Additionally, `dbt` Fusion engine is not natively compatible with the `sqlfluff-templater-dbt`, which relies on `dbt-core`'s templater. [14, 15]
  Fix: Always check the SQLFluff release notes and documentation for the supported `dbt-core` versions when upgrading either library. For `dbt` Fusion, using the standalone `dbt-core` engine templater locally is a current workaround. [14]
• gotcha Using the `dbt` templater is generally more complex and slower than the default `jinja` templater because it needs to compile the full dbt project. If your dbt models access a database at compile time, the templater will also require database access. [1, 21]
  Fix: Consider the trade-off between speed and accuracy. For CI/CD contexts where compilation accuracy is critical, the `dbt` templater is preferred. For local IDE or git hook usage where speed is paramount, the `jinja` templater might be more suitable, potentially with mock macros. [1]
• gotcha SQLFluff 4.1.0 introduced a `max_parse_depth` configuration setting (default 255) to protect against resource exhaustion from deeply nested SQL. Extremely complex or deeply nested dbt models might hit this limit, leading to parsing errors. [5, 11]
  Fix: If encountering parsing issues with deep queries, consider increasing `max_parse_depth` in your `.sqlfluff` configuration file under the `[sqlfluff]` section: `max_parse_depth = 500` (or a suitable higher value).
• breaking SQLFluff 3.x dropped support for Python 3.7. The latest versions of `sqlfluff` and `sqlfluff-templater-dbt` require Python 3.9 or newer. [8, 6]
  Fix: Ensure your environment uses Python 3.9 or a later version. Upgrade your Python installation if necessary.
• gotcha By default, errors encountered during dbt compilation are ignored by the templater (`dbt_skip_compilation_error = True`). This can mask underlying issues in your dbt project that prevent full template rendering. [1]
  Fix: To surface these errors and ensure complete template rendering, set `dbt_skip_compilation_error = False` in the `[sqlfluff:templater:dbt]` section of your `.sqlfluff` configuration file.

Install

• pip install sqlfluff sqlfluff-templater-dbt dbt-core dbt-snowflake # Replace dbt-snowflake with your adapter Install with dbt-core and an adapter

Quickstart

To use sqlfluff-templater-dbt, you must first install the package along with `sqlfluff`, `dbt-core`, and your specific `dbt` adapter. You then configure SQLFluff in a `.sqlfluff` file at your dbt project root, explicitly setting `templater = dbt` and specifying your SQL `dialect`. [1, 4, 12] It's also recommended to configure `project_dir` and `profiles_dir` within the `[sqlfluff:templater:dbt]` section and enable dbt built-ins in the `[sqlfluff:templater:jinja]` section. [1, 4, 9] An `.sqlfluffignore` file is crucial to prevent linting dbt compilation artifacts. [1, 12] Once configured, you can run `sqlfluff lint` or `sqlfluff fix` from your project's root.

# 1. Create a dbt project, e.g., using `dbt init my_dbt_project`
# 2. Navigate into your dbt project directory
# 3. Create a .sqlfluff configuration file:
# .sqlfluff
# ---
# [sqlfluff]
# dialect = snowflake # Or your dbt adapter's dialect
# templater = dbt
#
# [sqlfluff:templater:dbt]
# project_dir = ./
# profiles_dir = ~/.dbt/ # Or path to your dbt profiles.yml
#
# [sqlfluff:templater:jinja]
# apply_dbt_builtins = True # Enable dbt macros like `ref`, `var`, `is_incremental()`
# ---

# 4. Create an optional .sqlfluffignore file to exclude dbt artifacts:
# .sqlfluffignore
# ---
# target/
# dbt_packages/
# macros/
# venv/
# .venv/
# node_modules/
# logs/
# ---

# 5. Run SQLFluff to lint your dbt project
# (Make sure your dbt profile is configured for compilation if models query database at compile time)
import subprocess
import os

# Example dbt model file
with open('models/my_model.sql', 'w') as f:
    f.write("-- my_model.sql\n\nSELECT {{ ref('another_model') }} FROM {{ source('my_schema', 'my_table') }}\n")

# Lint all SQL files in the project
print("\n--- Running sqlfluff lint ---")
result_lint = subprocess.run(['sqlfluff', 'lint', '.'], capture_output=True, text=True)
print(result_lint.stdout)
if result_lint.stderr: print("Error:", result_lint.stderr)

# Fix linting issues (if any are auto-fixable)
print("\n--- Running sqlfluff fix ---")
result_fix = subprocess.run(['sqlfluff', 'fix', '.'], capture_output=True, text=True)
print(result_fix.stdout)
if result_fix.stderr: print("Error:", result_fix.stderr)