`dbt show` not working with 1.8+

[jeremyyeo]

With dbt 1.8+, the macro name we have here get_limit_subquery_sql:

dbt-fabric/dbt/include/fabric/macros/adapters/show.sql

Lines 1 to 14 in d4f99e7

	{% macro fabric__get_limit_subquery_sql(sql, limit) %}
	{% if sql.strip().lower().startswith('with') %}
	{{ sql }} order by (select null)
	offset 0 rows fetch first {{ limit }} rows only
	{% else -%}
	select *
	from (
	{{ sql }}
	) as model_limit_subq order by (select null)
	offset 0 rows fetch first {{ limit }} rows only
	{%- endif -%}
	{% endmacro %}

No longer apply - that macro has been renamed to get_limit_sql:

https://github.com/dbt-labs/dbt-adapters/blob/main/dbt/include/global_project/macros/adapters/show.sql#L21-L26

Therefor, users doing dbt show with 1.8+ will run into the syntax error:

$ dbt --debug show -s foo
03:00:06  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.5", "profile_name": "all", "target_name": "fb", "node_id": "model.my_dbt_project.foo"} */

  
  

select 1 id
  
  limit 5

03:00:06  Opening a new connection, currently in state closed
03:00:06  fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=5xxoty5si6telax6vdkh6id534-fmmrkvhkp7pu7edhh7uldtp2my.datawarehouse.pbidedicated.windows.net;Database=fabric_test_instance;SQL_ATTR_TRACE=SQL_OPT_TRACE_OFF;Authentication=ActiveDirectoryServicePrincipal;UID={1e74e997-4c1f-41c5-8520-15b1f11a9b35};PWD=***;encrypt=Yes;TrustServerCertificate=No;APP=dbt-fabric/1.8.7;ConnectRetryCount=1
03:00:06  fabric adapter: Connected to db: fabric_test_instance
03:00:06  fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near 'limit'. (102) (SQLExecDirectW)")
03:00:06  On model.my_dbt_project.foo: Close
03:00:06  Database Error in model foo (models/foo.sql)
  ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near 'limit'. (102) (SQLExecDirectW)")
03:00:06  Finished running node model.my_dbt_project.foo
03:00:06  Connection 'master' was properly closed.
03:00:06  Connection 'model.my_dbt_project.foo' was properly closed.
03:00:06  Encountered an error:
Runtime Error
  Database Error in model foo (models/foo.sql)
    ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near 'limit'. (102) (SQLExecDirectW)")
03:00:06  Resource report: {"command_name": "show", "command_wall_clock_time": 12.242369, "process_user_time": 0.818058, "process_kernel_time": 0.161916, "process_mem_max_rss": "129351680", "command_success": false, "process_in_blocks": "0", "process_out_blocks": "0"}
03:00:06  Command `dbt show` failed at 15:00:06.778800 after 12.24 seconds
03:00:06  Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107960210>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1079a8550>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1083d3910>]}
03:00:06  Flushing usage events

^ You can see from the SQL statement - we basically used the SQL from dbt-adapters.

Workaround

The workaround is to simply add this macro to your project:

-- macros/show.sql
{% macro fabric__get_limit_sql(sql, limit) %}

    {% if sql.strip().lower().startswith('with') %}
        {{ sql }} order by (select null)
    offset 0 rows fetch first {{ limit }} rows only
    {% else -%}
        select *
        from (
            {{ sql }}
        ) as model_limit_subq order by (select null)
    offset 0 rows fetch first {{ limit }} rows only
    {%- endif -%}

{% endmacro %}

Same macro but renamed.

$ dbt --debug show -s foo
03:02:01  Began running node model.my_dbt_project.foo
03:02:01  Re-using an available connection from the pool (formerly list_fabric_test_instance_dbt_jyeo, now model.my_dbt_project.foo)
03:02:01  Began compiling node model.my_dbt_project.foo
03:02:01  Writing injected SQL for node "model.my_dbt_project.foo"
03:02:01  Began executing node model.my_dbt_project.foo
03:02:01  Using fabric connection "model.my_dbt_project.foo"
03:02:01  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.5", "profile_name": "all", "target_name": "fb", "node_id": "model.my_dbt_project.foo"} */

  

    select *
        from (
            

select 1 id
        ) as model_limit_subq order by (select null)
    offset 0 rows fetch first 5 rows only

03:02:01  Opening a new connection, currently in state closed
03:02:01  fabric adapter: Using connection string: DRIVER={ODBC Driver 18 for SQL Server};SERVER=5xxoty5si6telax6vdkh6id534-fmmrkvhkp7pu7edhh7uldtp2my.datawarehouse.pbidedicated.windows.net;Database=fabric_test_instance;SQL_ATTR_TRACE=SQL_OPT_TRACE_OFF;Authentication=ActiveDirectoryServicePrincipal;UID={1e74e997-4c1f-41c5-8520-15b1f11a9b35};PWD=***;encrypt=Yes;TrustServerCertificate=No;APP=dbt-fabric/1.8.7;ConnectRetryCount=1
03:02:01  fabric adapter: Connected to db: fabric_test_instance
03:02:02  SQL status: OK in 0.000 seconds
03:02:02  On model.my_dbt_project.foo: Close
03:02:02  Finished running node model.my_dbt_project.foo
03:02:02  Connection 'master' was properly closed.
03:02:02  Connection 'model.my_dbt_project.foo' was properly closed.
03:02:02  Command end result
03:02:02  Previewing node 'foo':
| id |
| -- |
|  1 |

03:02:02  Resource report: {"command_name": "show", "command_success": true, "command_wall_clock_time": 10.492844, "process_user_time": 0.849826, "process_kernel_time": 0.146971, "process_mem_max_rss": "131022848", "process_in_blocks": "0", "process_out_blocks": "0"}
03:02:02  Command `dbt show` succeeded at 15:02:02.130138 after 10.49 seconds
03:02:02  Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1086ad510>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1082ebad0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107ae92d0>]}
03:02:02  Flushing usage events

[prdpsvs]

Addressed in #201 PR

[stegus64]

Please release a version that includes this fix as soon as possible.

This issue causes the fantastic "Power user for dbt" VSCode extension to not work properly with fabric which is very unfortunate.

[tabit01]

@prdpsvs please estimate a release date for this fix. It is a really confounding error to come across.

[prdpsvs]

@tabit01 , I will release this change this week.