Pre-commit hooks are a powerful mechanism for enhancing your development workflow by automating various checks and processes before code is committed to a repository. In the context of SQLFluff, a popular SQL linter and formatter, pre-commit hooks can be used to ensure your SQL code adheres to best practices and maintains a consistent style. In this article, we will address a common issue encountered when using SQLFluff pre-commit hooks, specifically related to dbt profiles.
The Error: "User Error: DbtProjectError: Runtime Error Could not find profile named 'XXX'"
When working with SQLFluff and dbt, you may encounter an error message like the one below:
User Error: DbtProjectError: Runtime Error Could not find profile named 'XXX'
This error occurs because SQLFluff is unable to locate a dbt profile named 'XXX' in the
profiles.yml file. The
profiles.yml file is essential for connecting to your database and providing necessary configuration details to dbt. In the context of pre-commit hooks, this issue can arise because there is no
profiles.yml available locally during the hook's execution.
Background on dbt Profiles
Before diving into solutions, let's briefly understand how dbt profiles work. When a MATE Orchestrator job runs in a DataOps pipeline, it generates a
profiles.yml file from a template. The process involves the following steps:
Template Check: MATE Orchestrator first checks for a template file in the directory
/dataops/profiles. If no file named
profiles.template.ymlis found in this directory, it will either use a default template or a customized
profiles.template.ymllocated in the DataOps Runner host directory, typically
Rendering the Template: The rendering of this template file does not occur locally. Instead, it takes place within the orchestration or CI/CD environment, where the
profiles.ymlis generated based on the template and environment-specific configuration.
Solution: Providing a Local
profiles.yml for Pre-commit Hooks
To resolve the "Could not find profile named 'XXX'" error and enable SQLFluff to work with dbt templater as a pre-commit hook, you need to ensure that a
profiles.yml file is available locally. Here's what you can do:
Create a Local
profiles.yml: Manually create a
profiles.ymlfile in your project directory. You can copy the necessary configuration details from an existing
profiles.ymlused in your DataOps pipeline. Make sure it includes the 'XXX' profile or any other profiles your dbt project relies on.
profiles_dirin SQLFluff Configuration: To let SQLFluff know where your
profiles.ymlfile is located, you should specify the
profiles_dirwithin your SQLFluff configuration. To do this, follow these steps:
.sqlfluffconfiguration file (usually named
.sqlfluff.toml) in the root of your project directory, or create one if it doesn't exist.
Add the following configuration under the
<relative or absolute path to the directory that contains the profiles.yml file>with the actual path to your
[sqlfluff:templater:dbt] profiles_dir = <relative or absolute path to the directory that contains the profiles.yml file>
Verify Your Configuration: Ensure that your SQLFluff configuration is correctly set up with the
profiles_dirpointing to the directory containing your
Testing: Run your SQLFluff pre-commit hook again. The error should no longer occur, as SQLFluff will be able to access the required
For more details on SQLFluff configuration and installation, you can refer to the official documentation:
Pre-commit hooks are a valuable tool for maintaining code quality and consistency in your SQL projects. When using SQLFluff with dbt templater, ensuring that a local
profiles.yml is available and correctly configured in your SQLFluff settings is essential to avoid runtime errors. By following the steps outlined in this article, you can resolve the "Could not find profile named 'XXX'" error and streamline your SQL development workflow.