Missing env variable (specified in profiles.yaml) running sqlfluff on pre-commit with dbt

I am using dbt and we set up pre-commit to lint our models before opening a PR.

After the update of dbt (1.4.5 to 1.5.0) and sqlfluff (2.0.2 to 2.1.0), we are no longer able to run pre-commit because we specify environment variables in our profiles.yaml that are not available locally since we use a Docker container to run our models:

# profiles.yml
jaffle_shop:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: fake-snowflake-account
      user: nicolas
      password: '{{ env_var(''USER_PASSWORD'') }}'
      db: jaffle_shop
      schema: dbt_nicolas
      threads: 4

# .pre-commit-config.yml
- repo: https://github.com/sqlfluff/sqlfluff
  rev: 2.1.0
  hooks:
    - id: sqlfluff-fix
      additional_dependencies: ['dbt-snowflake==1.5.0', 'sqlfluff-templater-dbt==2.1.0']

The error message:

==== finding fixable violations ====
FORCE MODE: Attempting fixes...
=== [dbt templater] Sorting Nodes...
Traceback (most recent call last):
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/bin/sqlfluff", line 8, in <module>
    sys.exit(cli())
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/sqlfluff/cli/commands.py", line 989, in fix
    _paths_fix(
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/sqlfluff/cli/commands.py", line 787, in _paths_fix
    result: LintingResult = linter.lint_paths(
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/sqlfluff/core/linter/linter.py", line 1206, in lint_paths
    for i, linted_file in enumerate(runner.run(expanded_paths, fix), start=1):
  File "/Users/nicolas/.cache/pre-commit/repo6v3c6d72/py_env-python3.10/lib/python3.10/site-packages/sqlfluff/core/linter/runner.py", line 142, in run
    for lint_result in self._map(
  File "/usr/local/Cellar/python@3.10/3.10.8/Frameworks/Python.framework/Versions/3.10/lib/python3.10/multiprocessing/pool.py", line 873, in next
    raise value
dbt.exceptions.EnvVarMissingError: Parsing Error
  Env var required but not provided: 'USER_PASSWORD'

I tried to edit the entry of the sqlfluff hook: entry: bash sqlfluff-fix.sh with:

# sqlfluff-fix.sh
export $(grep -v '^#' my.env | xargs -0)
sqlfluff fix --force --show-lint-violations --processes 0

Like here and it's working but it runs sqlfluff on the entire project, do you have an idea how to fix this?

Thanks a lot,
Nicolas

it feels a little strange that a linter would need credentials. I would probably look into a way to use a configuration only for linting that doesn't require a particular environment to work

failing that, you can provide placeholder values (assuming it doesn't actually attempt to make a connection) by overriding entry

for example:

- repo: https://github.com/sqlfluff/sqlfluff
  rev: 2.1.0
  hooks:
    - id: sqlfluff-fix
      entry: env USER_PASSWORD=fakepassword sqlfluff fix --force --show-lint-violations --processes 0

note that env reduces portability though even windows should have an env.exe if you're running git (which ships with a minimal posixlike environment)

disclaimer: I wrote pre-commit

sqlfluff released a fix (2.1.1) that removes the need to specify a profiles_dir in the configuration file. Updating sqlfluff solved the problem.