Lint and format your code

Enhance your development workflow by integrating with popular linters and formatters like SQLFluff, sqlfmt, Black, and Prettier. Leverage these powerful tools directly in the dbt Cloud IDE without interrupting your development flow.

Details

What are linters and formatters?

Linters analyze code for errors, bugs, and style issues, while formatters fix style and formatting rules. Read more about when to use linters or formatters in the FAQs

In the dbt Cloud IDE, you can perform linting, auto-fix, and formatting on five different file types:

• SQL — Lint and fix with SQLFluff, and format with sqlfmt
• YAML, Markdown, and JSON — Format with Prettier
• Python — Format with Black

Each file type has its own unique linting and formatting rules. You can customize the linting process to add more flexibility and enhance problem and style detection.

By default, the IDE uses sqlfmt rules to format your code, making it convenient to use right away. However, if you have a file named .sqlfluff in the root directory of your dbt project, the IDE will default to SQLFluff rules instead.

Use SQLFluff to lint/format your SQL code, and view code errors in the Code Quality tab.

Use sqlfmt to format your SQL code.

Format YAML, Markdown, and JSON files using Prettier.

Use the Config button to select your tool.

Customize linting by configuring your own linting code rules, including dbtonic linting/styling.

Lint

With the dbt Cloud IDE, you can seamlessly use SQLFluff, a configurable SQL linter, to warn you of complex functions, syntax, formatting, and compilation errors. This integration allows you to run checks, fix, and display any code errors directly within the Cloud IDE:

• Works with Jinja and SQL,
• Comes with built-in linting rules. You can also customize your own linting rules.
• Empowers you to enable linting with options like Lint (displays linting errors and recommends actions) or Fix (auto-fixes errors in the IDE).
• Displays a Code Quality tab to view code errors, provides code quality visibility and management, and displays the SQLFluff version used.

Ephemeral models not supported

Linting doesn't support ephemeral models in dbt v1.5 and lower. Refer to the FAQs for more info.

Enable linting

Linting is available on all branches, including your protected primary git branch. Since the dbt Cloud IDE prevents commits to the protected branch, it prompts you to commit those changes to a new branch.

1. To enable linting, open a .sql file and click the Code Quality tab.
2. Click on the </> Config button on the bottom right side of the console section, below the File editor.
3. In the code quality tool config pop-up, you have the option to select sqlfluff or sqlfmt.
4. To lint your code, select the sqlfluff radio button. (Use sqlfmt to format your code)
5. Once you've selected the sqlfluff radio button, go back to the console section (below the File editor) to select the Lint or Fix dropdown button:
   • Lint button — Displays linting issues in the IDE as wavy underlines in the File editor. You can hover over an underlined issue to display the details and actions, including a Quick Fix option to fix all or specific issues. After linting, you'll see a message confirming the outcome. Linting doesn't rerun after saving. Click Lint again to rerun linting.
   • Fix button — Automatically fixes linting errors in the File editor. When fixing is complete, you'll see a message confirming the outcome.
   • Use the Code Quality tab to view and debug any code errors.

Use the Lint or Fix button in the console section to lint or auto-fix your code.

Customize linting

SQLFluff is a configurable SQL linter, which means you can configure your own linting rules instead of using the default linting settings in the IDE. You can exclude files and directories by using a standard .sqlfluffignore file. Learn more about the syntax in the .sqlfluffignore syntax docs.

To configure your own linting rules:

1. Create a new file in the root project directory (the parent or top-level directory for your files). Note: The root project directory is the directory where your dbt_project.yml file resides.
2. Name the file .sqlfluff (make sure you add the . before sqlfluff).
3. Create and add your custom config code.
4. Save and commit your changes.
5. Restart the IDE.
6. Test it out and happy linting!

Configure dbtonic linting rules

Refer to the Jaffle shop SQLFluff config file for dbt-specific (or dbtonic) linting rules we use for our own projects:

dbtonic config code example provided by dbt Labs

[sqlfluff]
templater = dbt
# This change (from jinja to dbt templater) will make linting slower
# because linting will first compile dbt code into data warehouse code.
runaway_limit = 10
max_line_length = 80
indent_unit = space

[sqlfluff:indentation]
tab_space_size = 4

[sqlfluff:layout:type:comma]
spacing_before = touch
line_position = trailing

[sqlfluff:rules:capitalisation.keywords] 
capitalisation_policy = lower

[sqlfluff:rules:aliasing.table]
aliasing = explicit

[sqlfluff:rules:aliasing.column]
aliasing = explicit

[sqlfluff:rules:aliasing.expression]
allow_scalar = False

[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:ambiguous.column_references]  # Number in group by
group_by_and_order_by_style = implicit

For more info on styling best practices, refer to How we style our SQL.

Customize linting by configuring your own linting code rules, including dbtonic linting/styling.

Format

In the dbt Cloud IDE, you can format your code to match style guides with a click of a button. The IDE integrates with formatters like sqlfmt, Prettier, and Black to automatically format code on five different file types — SQL, YAML, Markdown, Python, and JSON:

• SQL — Format with sqlfmt, which provides one way to format your dbt SQL and Jinja.
• YAML, Markdown, and JSON — Format with Prettier.
• Python — Format with Black.

The Cloud IDE formatting integrations take care of manual tasks like code formatting, enabling you to focus on creating quality data models, collaborating, and driving impactful results.

Format SQL

To format your SQL code, dbt Cloud integrates with sqlfmt, which is an uncompromising SQL query formatter that provides one way to format the SQL query and Jinja.

By default, the IDE uses sqlfmt rules to format your code, making the Format button available and convenient to use immediately. However, if you have a file named .sqlfluff in the root directory of your dbt project, the IDE will default to SQLFluff rules instead.

Formatting is available on all branches, including your protected primary git branch. Since the dbt Cloud IDE prevents commits to the protected branch, it prompts you to commit those changes to a new branch.

1. Open a .sql file and click on the Code Quality tab.
2. Click on the </> Config button on the right side of the console.
3. In the code quality tool config pop-up, you have the option to select sqlfluff or sqlfmt.
4. To format your code, select the sqlfmt radio button. (Use sqlfluff to lint your code).
5. Once you've selected the sqlfmt radio button, go to the console section (located below the File editor) to select the Format button.
6. The Format button auto-formats your code in the File editor. Once you've auto-formatted, you'll see a message confirming the outcome.

Use sqlfmt to format your SQL code.

Format YAML, Markdown, JSON

To format your YAML, Markdown, or JSON code, dbt Cloud integrates with Prettier, which is an opinionated code formatter. Formatting is available on all branches, including your protected primary git branch. Since the dbt Cloud IDE prevents commits to the protected branch, it prompts you to commit those changes to a new branch.

1. Open a .yml, .md, or .json file.
2. In the console section (located below the File editor), select the Format button to auto-format your code in the File editor. Use the Code Quality tab to view code errors.
3. Once you've auto-formatted, you'll see a message confirming the outcome.

Format YAML, Markdown, and JSON files using Prettier.

You can add a configuration file to customize formatting rules for YAML, Markdown, or JSON files using Prettier. The IDE looks for the configuration file based on an order of precedence. For example, it first checks for a "prettier" key in your package.json file.

For more info on the order of precedence and how to configure files, refer to Prettier's documentation. Please note, .prettierrc.json5, .prettierrc.js, and .prettierrc.toml files aren't currently supported.

Format Python

To format your Python code, dbt Cloud integrates with Black, which is an uncompromising Python code formatter. Formatting is available on all branches, including your protected primary git branch. Since the dbt Cloud IDE prevents commits to the protected branch, it prompts you to commit those changes to a new branch.

1. Open a .py file.
2. In the console section (located below the File editor), select the Format button to auto-format your code in the File editor.
3. Once you've auto-formatted, you'll see a message confirming the outcome.

Format Python files using Black.

FAQs

 When should I use SQLFluff and when should I use sqlfmt?

SQLFluff and sqlfmt are both tools used for formatting SQL code, but some differences may make one preferable to the other depending on your use case.

SQLFluff is a SQL code linter and formatter. This means that it analyzes your code to identify potential issues and bugs, and follows coding standards. It also formats your code according to a set of rules, which are customizable, to ensure consistent coding practices. You can also use SQLFluff to keep your SQL code well-formatted and follow styling best practices.

sqlfmt is a SQL code formatter. This means it automatically formats your SQL code according to a set of formatting rules that aren't customizable. It focuses solely on the appearance and layout of the code, which helps ensure consistent indentation, line breaks, and spacing. sqlfmt doesn't analyze your code for errors or bugs and doesn't look at coding issues beyond code formatting.

You can use either SQLFluff or sqlfmt depending on your preference and what works best for you:

• Use SQLFluff to have your code linted and formatted (meaning analyze fix your code for errors/bugs, and format your styling). It allows you the flexibility to customize your own rules.

• Use sqlfmt to only have your code well-formatted without analyzing it for errors and bugs. You can use sqlfmt out of the box, making it convenient to use right away without having to configure it.

 Can I nest `.sqlfluff` files?

To ensure optimal code quality, consistent code, and styles — it's highly recommended you have one main .sqlfluff configuration file in the root folder of your project. Having multiple files can result in various different SQL styles in your project.

However, you can customize and include an additional child .sqlfluff configuration file within specific subfolders of your dbt project.

By nesting a .sqlfluff file in a subfolder, SQLFluff will apply the rules defined in that subfolder's configuration file to any files located within it. The rules specified in the parent .sqlfluff file will be used for all other files and folders outside of the subfolder. This hierarchical approach allows for tailored linting rules while maintaining consistency throughout your project. Refer to SQLFluff documentation for more info.

 Can I run SQLFluff commands from the terminal?

Currently, running SQLFluff commands from the terminal isn't supported.

 Why is there inconsistent SQLFluff behavior when running outside the dbt Cloud IDE?

• Double-check that your SQLFluff version matches the one in dbt Cloud IDE (found in the Code Quality tab after a lint operation).
  
  
• If your lint operation passes despite clear rule violations, confirm you're not linting models with ephemeral models. Linting doesn't support ephemeral models in dbt v1.5 and lower.

 What are some considerations when using dbt Cloud linting?

Currently, the dbt Cloud IDE can lint or fix files up to a certain size and complexity. If you attempt to lint or fix files that are too large, taking more than 60 seconds for the dbt Cloud backend to process, you will see an 'Unable to complete linting this file' error.

To avoid this, break up your model into smaller models (files) so that they are less complex to lint or fix. Note that linting is simpler than fixing so there may be cases where a file can be linted but not fixed.

Related docs

• User interface
• Keyboard shortcuts
• SQL linting in CI jobs