SQLFluff运行pre-commit报Cannot instantiate a templated file unsliced错

近期为自有dbt项目添加SQLFluff支持，参照SQLFluff官方生产环境文档，通过pre-commit方式完成集成，执行校验时抛出模板渲染相关错误，无法定位根因。

现有配置 #

.sqlfluff基础配置 #

[sqlfluff]
dialect = postgres
templater = jinja
output_line_length = 80
ignore_templated_areas = True
runaway_limit = 100


[sqlfluff:rules]
tab_space_size = 2
max_line_length = 120
indent_unit = space
comma_style = trailing

[sqlfluff:rules:L014]
extended_capitalisation_policy = lower

[sqlfluff:templater:jinja]
apply_dbt_builtins = true

.pre-commit-config.yaml配置 #

repos:
      - repo: https://github.com/sqlfluff/sqlfluff
        rev: 1.0.0
        hooks:
          - id: sqlfluff-lint
            name: sqlfluff-lint
            entry: sqlfluff lint
            language: python
            description: 'Lints sql files with `SQLFluff`'
            types: [sql]
            require_serial: true
            additional_dependencies: []
    
          - id: sqlfluff-fix
            name: sqlfluff-fix
            # Needs to use "--force" to disable confirmation
            # By default all the rules are applied
            entry: sqlfluff fix --force
            language: python
            description: 'Fixes sql lint errors with `SQLFluff`'
            types: [sql]
            require_serial: true
            additional_dependencies: []

执行pre-commit run --all-files命令时，程序抛出如下错误：

File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/bin/sqlfluff", line 8, in <module>
    sys.exit(cli())
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/cli/commands.py", line 769, in fix
    result = lnt.lint_paths(
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 1143, in lint_paths
    self.lint_path(
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 1095, in lint_path
    for i, linted_file in enumerate(runner.run(fnames, fix), start=1):
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/linter/runner.py", line 101, in run
    for fname, partial in self.iter_partials(fnames, fix=fix):
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/linter/runner.py", line 54, in iter_partials
    for fname, rendered in self.iter_rendered(fnames):
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/linter/runner.py", line 43, in iter_rendered
    yield fname, self.linter.render_file(fname, self.config)
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 816, in render_file
    return self.render_string(raw_file, fname, config, encoding)
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/linter/linter.py", line 787, in render_string
    templated_file, templater_violations = self.templater.process(
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/templaters/jinja.py", line 413, in process
    TemplatedFile(
  File "/root/.cache/pre-commit/repo20y2aa42/py_env-python3.8/lib/python3.8/site-packages/sqlfluff/core/templaters/base.py", line 102, in __init__
    raise ValueError("Cannot instantiate a templated file unsliced!")
ValueError: Cannot instantiate a templated file unsliced!

根因定位 #

报错核心是Jinja模板渲染完成后，SQLFluff无法生成原始SQL和渲染后SQL的位置切片映射，三个配置问题共同导致该错误：

• 模板器选型错误：配置使用通用jinja模板器，该模板器无法识别dbt专属的ref()、source()、宏调用等语法，无法正确解析dbt项目上下文，渲染过程中位置映射断裂。
• 版本兼容性问题：使用的SQLFluff 1.0.0为早期正式版本，对dbt内置宏的适配存在已知bug，即便开启apply_dbt_builtins参数也无法完成正确的切片计算。
• 运行依赖缺失：pre-commit创建的独立运行环境中未安装dbt相关依赖，模板器加载dbt内置逻辑时失败，导致渲染结果不完整。

修复步骤 #

1. 替换为dbt专属模板器
   修改.sqlfluff配置，将模板器从通用jinja改为dbt专属模板器，指定dbt项目目录，确保模板器可以正确加载dbt项目上下文。修改后的核心配置如下：

   [sqlfluff]
   dialect = postgres
   templater = dbt
   output_line_length = 80
   ignore_templated_areas = True
   runaway_limit = 100
   
   [sqlfluff:templater:dbt]
   apply_dbt_builtins = true
   project_dir = ./
   

2. 升级SQLFluff到稳定兼容版本
   将.pre-commit-config.yaml中SQLFluff的版本号从1.0.0升级到2.3.x及以上的稳定版本，新版本已修复绝大多数dbt模板渲染的切片bug，兼容性更好。
3. 补全pre-commit运行依赖
   在两个SQLFluff hook的additional_dependencies字段中添加对应数据库的dbt适配器包（PostgreSQL环境对应dbt-postgres），确保pre-commit的独立运行环境具备完整的dbt渲染能力。修改后的pre-commit配置参考：

   repos:
     - repo: https://github.com/sqlfluff/sqlfluff
       rev: 2.3.5
       hooks:
         - id: sqlfluff-lint
           name: sqlfluff-lint
           entry: sqlfluff lint
           language: python
           types: [sql]
           require_serial: true
           additional_dependencies: ['dbt-postgres']
   
         - id: sqlfluff-fix
           name: sqlfluff-fix
           entry: sqlfluff fix --force
           language: python
           types: [sql]
           require_serial: true
           additional_dependencies: ['dbt-postgres']
   

4. 清理缓存重试运行
   配置修改完成后执行pre-commit clean清空旧版本的虚拟环境缓存，再重新执行pre-commit run --all-files即可正常运行。

单文件排查技巧：如果调整配置后仍有个别文件报错，可直接执行sqlfluff parse <报错文件路径.sql>，命令会直接输出模板渲染阶段的具体错误点（如未定义的变量、找不到的宏），无需从全量错误栈中定位问题。

内容的提问来源于stack exchange，提问作者d_mandy_1202