vscode-dbt-power-user

vscode-dbt-power-user copied to clipboard

Expected behavior

With a configuration set in the extension setting "dbt.queryTemplate", the query preview tool should use that template instead of the default "select top 500".

Actual behavior

The query template is being ignored on any & all configuration levels (user/remote/workpace) and using the default SQL template instead, which breaks previews for t-sql queries using CTEs.

Steps To Reproduce

Running dbt within a VS Code Dev Container. I have set the query template setting to "set rowcount {limit}; {query}" on my user, in the devcontainer.json, and the .vscode/settings.json. When I run a query preview and look at the SQL tab to see the generated SQL, it will still have "select top 500".

Log output/Screenshots

No response

Operating System

Debian 11

dbt version

1.7.10

dbt Adapter

synapse 1.7.1

dbt Power User version

0.37.2

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

The compiled SQL of the preview query actually doesn't match the default either. It does match the SQL generated by the dbt show command for the Synapse adapter. Perhaps this has changed, or dbt Power User has started using it for preview queries.

Upstream cause: https://github.com/microsoft/dbt-synapse/pull/235

A quick local workaround is to create a macros/get_limit_subquery_sql.sql macro with the following:

{# Synapse doesnt support ANSI LIMIT clause - instead set rowcount for the session (risky) #}
{% macro get_limit_subquery_sql(sql, limit) %}
set rowcount {{ limit }};
{{ sql }}
{% endmacro %}