Support compiling/executing SQL in the context of a model

[drewbanin]

Describe the feature

Today, the rpc server supports compiling/executing SQL in the context of a dbt project. This is good! Unfortunately, dbt uses a fake/stub compilation context for these compile/execute tasks, so model-specific variables like this or macros like is_incremental() return incorrect values in compilation. Instead, the rpc server should provide a mechanism for specifying the node (eg. by unique_id or path) which should provide the compilation context for the rpc method.

Describe alternatives you've considered

Not sure how else something like this could work... possibly by providing a set of context overrides alongside the rpc call?

Additional context

• Applies on all databases
• Would be most useful for interactive development use-cases for the rpc server, like in the dbt Cloud IDE (or similar)

Who will this benefit?

Users who leverage the rpc server as a part of their dbt code development workflow; dbt Cloud users

Are you interested in contributing this feature?

I would be happy to help :)

[jtcohen6]

This is a good idea! Thanks for opening @drewbanin

What I'm wondering: Should compiling/running manifest nodes be a totally separate process from compiling/running arbitrary dbt code? If we know that a file maps to a node in the manifest, might we want to actually compile that node directly?

So, rather than grabbing the SQL from my_model and passing it to the compile_sql method, we could:

• Send compile request with parameter models: ['my_model']
• Poll and grab results['node']['compiled_sql']

One important difference is that the compile task, as a "project command" rather than a "SQL command," builds the adapter cache as its first step. That feels appropriate, in order for introspective variables such as is_incremental() to return correct results.

This gets slightly trickier for running SQL / previewing data, since we wouldn't want to actually execute the dbt model / materialization. In that case, we'd have to compile the node (as above) and then pass the compiled SQL into the run_sql method—or provide a streamlined task for doing just that.

A downside of this approach is that the RPC server will compile the node's raw_sql as saved in the manifest, not a supplied sql parameter. This could lead to confusion if there's a mismatch between raw_sql and the would-be-supplied sql, i.e. because of unsaved changes in a code editor / since the RPC server was last HUP'd.

I think that limitation applies to both approaches, though. If a model's SQL references {{ this }}, and we were to add {{ config(alias = 'something') }} to that model, without saving / HUP'ing, and submit its encoded SQL to the compile_sql method, along with a pointer to its node in the manifest—we'd compile incorrect SQL, right?

[saraleon1]

+1 dbt Cloud user

User is trying to access {{ model.name }} in a macro, and when they are testing their macro development in cloud via the preview and compilation functions in the IDE, they see this return as request rather than the actual model name.

Unfortunately, our analysts love the preview and compile function in dbt cloud. That’s the reason why we can not use the {{ model.name }} macro as long as this issue exists.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.

[jeremyyeo]

A customer reached out about this so I'd just record this loom to show that preview/compile indeed does not resolve correctly (but when the model is built - it does resolve correctly).

https://www.loom.com/share/14163a6a4f604d70bf4613114db11f8f

[jtcohen6]

We are not going to be able to solve this within the constraints of the dbt-rpc server. It is a very important consideration when building interactive compile/preview capabilities into dbt-core + dbt-server.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.