Ability to lint DBT code

[mdesmet]

[pwildenhain]

👋 Hi there!

I'm one of the maintainers for sqlfluff - a python library for linting/formatting sql. We also have a VSCode extension for sqlfluff created by another one of our maintainers (cc: @dorzey).

🤔 Not sure if there is any opportunity for collaboration here, but at the very least, wanted to reach out and let you know there is a decent sql linter with a VSCode extension already out there 👍

cc: @alanmcruickshank if you have other questions, we'd be happy to discuss 😁 🙇

[dorzey]

Does it make sense to include it as one of the extensionDependencies?

[mdesmet]

When I tested sqlfluff it required a lot of additional configuration to make it work for me. I would be open to add it to the extensiondependencies if it provides a fully functional dbt linter out of the box, similar to a python or typescript. Maybe i didn't fully get it working so definitely open to discuss.

The main filosophy behind this extension is to let things work WITHOUT any configuration (auto discovering your dbt projects and fully leveraging vscode best practises like the remote and python plugin. If we can work this out, I'm definitely open to add it to the extensionDependencies.

[dorzey]

@mdesmet can you point me to the code that does this auto-discovery? I'd be interested to see if I can replicate it for the sqlfluff extension

[dorzey]

@mdesmet found it! Running via the terminal is a much neater idea; definitely changing vscode-sqlfluff to follow that approach.

[mdesmet]

Hey @dorzey,

I more meant that the extension automatically finds all the dbt projects/setup by scanning the file system and configuration. I found that sqfluff can't yet handle macros very well, also the --fix had some unexpected results. I think a lot of people are using a lot of macros in their dbt code so sqlflulff needs some smart way to handle it.

Regarding running through the terminal, we are actually just in the process of refactoring all this. It's not fully reliable when using virtual environments. We will now detect DBT in your environment through the python extension, I think this is the most reliable in the context of virtual environments and shells. We will also detect if dbt is not installed and install/upgrade it for you (choice).

https://github.com/innoverio/vscode-dbt-power-user/blob/b98b141c88aae25f7f4e14b0e4ac3c1c5af06aa9/src/dbt_client/dbtClient.ts#L43-L45

We then plan to use the found dbt to run 'dbt ls' when you are modifying your files to have an updated node graph.

[dorzey]

Thanks for the pointers. The next version of sqlfluff has improved dbt support.

I'm happy to align approaches so it could be included at some point; even if that is not right now.

[aaronsteers]

@mdesmet - I want to also voice my support for using sqlfluff as the linting standard for dbt projects. The built-in rule-level configurability is much-needed, dbt support is there by default (although possibly could still be improved?), and it has a lot of momentum in the community. Also, sqlfluff comes with auto-formatting / auto-correcting of many (not all) common linting failures. Having the same system lint and also provide the format fixes is a big win, since any project (like in python) can choose to exclude certain linting rules and those exclusions can be specified in one place for both linting and auto-fixing.

Currently our team uses both DBT Power User and the SQLFluff VS Code extensions, and I really like the idea of the dependsOn relationship once the default (config-less) experience is where it should be to be included automatically.

Just my two cents. However we get there, SQL linting support and autoformatting (a. la. "Black" in Python) are both hugely beneficial to the DBT community.