Test compatibility with "dbt Power User" VS Code Extension

[aaronsteers]

As @DouweM mentions, we want Meltano to be the best way to run dbt.

In order to reach that goal, we must be compatible with the current best-in class IDE experience from third parties.

@pnadolny13 - Can you please use squared to try out the dbt Power User extension in VS Code?

When I tried using this with a meltano project previously, the plugin was not able to detect my models and the basic features did not work because of Meltano's own changes to base directory, working directory, etc. However, it's possible that the extension has improved, and it's possible that there are workarounds.

What I had to do:

• Move dbt_project.yml to the base of the repo.
• Move some of the dbt internal run artifacts directories back to the default locations.

Again, these workarounds may no longer be needed but we should know if we are compatible and what workarounds if any, are needed to get the extension working on a Meltano project?

cc @tayloramurphy

[pnadolny13]

@aaronsteers so far I was able to install the extension and get it to detect my project and files. I had to point it to my profiles and set file associations of *.sql to jinja-sql.

So I get some features, based on there list on the github page https://github.com/innoverio/vscode-dbt-power-user but I'm not able to hit the run button:

• "See the model graph" ✅
• "Go to definition" ✅
• "Autocompletion" ✅
• "dbt logs force tailing" ✅
• Run dbt (not listed but implied) ❌

If I change my python interpreter to use my .meltano dbt-snowflake venv then it detects dbt and allows me to click the run button but I get a message like:

21:13:07  Encountered an error while reading the project:

21:13:07    ERROR: Runtime Error
  Could not find profile named 'meltano'

21:13:07  Encountered an error:
Runtime Error
  Could not run dbt

I don't see a way where we'd be able to prefix the commands that it wants to run when I click the run button. You can add something to the end of the command like I configured it to add error and it prints the dbt command its trying to execute:

> Executing task: dbt run --model +cli_executions_base error

21:27:51  Encountered an error while reading the project:

The vs code settings that ended up getting configured were:

    "dbt.profilesDirOverride": "${workspaceFolder}/data/transform/profiles/snowflake/profiles.yml",
    "files.associations": {
        "*.sql": "jinja-sql"
    }

Overall I'd say this is pretty good coverage and would recommend it to people using Meltano. They still need to execute via the CLI but the IDE experience is upgraded significantly.

[aaronsteers]

@pnadolny13 - This is super helpful. Thank you!

I had to point it to my profiles and set file associations of *.sql to jinja-sql.

Can you document here a bit more about what you needed to do? For instance, did the extension prompt you to do this or did you follow instructions from the extension docs?

Nm. I see you have it documented here 😄 :

The vs code settings that ended up getting configured were:

    "dbt.profilesDirOverride": "${workspaceFolder}/data/transform/profiles/snowflake/profiles.yml",
    "files.associations": {
        "*.sql": "jinja-sql"
    }

Re:

If I change my python interpreter to use my .meltano dbt-snowflake venv then it detects dbt and allows me to click the run button

So, sounds like we have two docs issues and one bug/usability fix:

1. Document how to update VS Code settings for compatibility with the extension (same json snippet as above).
2. Document how to change the Python interpreter to be the dbt-snowflake folder (or similar) one in the .meltano folder.
   • Alternatively, we could submit a PR to the extension which allows us to override the interpreter for dbt via json settings.
3. Find solution to make the 'run' button work (if possible).

Does that sound about right in terms of making this generally available for Meltano users? (Ignoring questions of low/hi priority for now.)

This is really great. I agree this would provide value for Meltano users as-is. That said, the experience as of now is admittedly not as good or streamlined as the native dbt experience. And (at least over time), I think we should try to smooth those rough edges as much as is feasible.

cc @tayloramurphy

[tayloramurphy]

@z3z1ma made https://github.com/z3z1ma/dbt-osmosis which seems really cool and could be another option @pnadolny13 @aaronsteers

[pnadolny13]

@aaronsteers yeah that seems right.

1. Document how to update VS Code settings for compatibility with the extension (same json snippet as above).

This would be easy for the first iteration where "run" doesnt work but the other features do.

2. Document how to change the Python interpreter to be the dbt-snowflake folder (or similar) one in the .meltano folder.
   • Alternatively, we could submit a PR to the extension which allows us to override the interpreter for dbt via json settings.
3. Find solution to make the 'run' button work (if possible).

I think these 2 could end up being the same solution. Switching interpreters doenst really solve anything for me yet, it finds dbt but until I can run dbt with meltano its not much help because none of my configs flow through. Also I got all the other features without configuring my python interpreter with dbt so that seems to only be used for the "run" featyre. Ideally we'd find a way to be able run dbt through meltano, until then the other 2 settings I listed give you everything except "run".

I wonder if the maintainers would accept a PR to configure your dbt executable, wouldnt that allow us to replace dbt with meltano invoke dbt-<adapter>? They let you append extra dbt arguments at the end of the command already. Maybe we can test that out in a fork.

[pnadolny13]

@tayloramurphy dbt-osmosis does look awesome! I've been starting to play around with it today. I think it would be in addition to this VS code extension though, not as a replacement. I saw this snippet in the readme:

When combined with an IDE such as VS Code, developers can work with renewed efficiency, enjoyment, and effectiveness throughout their days.

So I dont think its intending to be the IDE itself but more of a developer utility along side the IDE. @z3z1ma definitely correct me if I'm wrong though!

[aaronsteers]

I wonder if the maintainers would accept a PR to configure your dbt executable, wouldnt that allow us to replace dbt with meltano invoke dbt-<adapter>?

Yeah - I like this idea a lot! Logged an issue:

• Possible to override executable? AltimateAI/vscode-dbt-power-user#120

[aaronsteers]

@pnadolny13 - Another path here is to modify the venv $VIRTUALENV/bin/activate script so that it sources the environment variables needed: https://stackoverflow.com/a/20918496/4298208

This is not ideal though for a number of reasons which I won't get into...

Possible venv-based workaround (not advised)

Ignoring for a second all the reasons it would be less-than-ideal...

.../dbt-snowflake/bin/activate

# ...
source $MELTANO_PROJECT_ROOT/.env

Or, imagining a new command meltano config dbt-snowflake --format=dotenv that essentially creates a .env formatted output text, some iteration of the below might get us closer to a precise loading of the plugin's expected env entries:

# ...
source $MELTANO_PROJECT_ROOT/.env
if [[ -e MELTANO_ENVIRONMENT ]]; then
    meltano --environment=$MELTANO_ENVIRONMENT config dbt-snowflake --format=dotenv | source -

[aaronsteers]

The extension author has replied to the above and it sounds like they are open to adding a CLI-based invocation pattern which could accept a different dbt executable entrypoint.

I'm happy to work with you guys to make this work. At the moment we execute the commands through Python and obviously it's important to execute within the correct Python environment (as dbt supports multiple adapters and projects might use different dbt versions), this way we can provide the same experience as using the terminal.

You may contact me on the dbt slack and we can setup a call if that would be of any interest.

Looks like this line of code is helpful to understand the invocation pattern. Looks like they basically already use CLI-esque invocation syntax but it is passed via a Python interface:

https://github.com/innoverio/vscode-dbt-power-user/blob/master/src/dbt_client/dbtCommandFactory.ts#L119-L121

  private dbtCommand(cmd: string | string[]): string {
    return `import dbt.main; dbt.main.main([${cmd}])`;
  }

[z3z1ma]

So I dont think its intending to be the IDE itself but more of a developer utility along side the IDE. @z3z1ma definitely correct me if I'm wrong though!

Yep that's right. We scope the utility in the purposefully narrow (but important) developer-workflow gap that VS Code fails at for a dbt developer. So I use it in addition- it will not handle saving files or navigating the file system. It will excel at taking any dbt model you are working on (or any SQL at all) at any level of complexity and allow you to dive into an instant-feedback loop of type, adjust, test, etc. whenever you want so long as its open/running in a tab.

[aaronsteers]

For future readers' reference, this shipped after we closed this issue:

• Possible to override executable? AltimateAI/vscode-dbt-power-user#120

Some of the above could likely be improved by using relative path option to invoke dbt via meltano (which theoretically would also get any needed secrets).

[aaronsteers]

@pnadolny13 - Did you find a workaround to dbt_project.yml not being in the root directory?

• [CT-1357] [Feature] DBT_PROJECT_DIR env var dbt-labs/dbt-core#6078 (comment)
• Bug: warning message uses same path twice: project located in (path) that is contained inside another project (same path) AltimateAI/vscode-dbt-power-user#211 (comment)