`dbt show` not working with 1.8+

[jeremyyeo]

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

https://github.com/microsoft/dbt-fabric/blob/d4f99e79c3083d8584c228ce3136285c18da77de/dbt/include/fabric/macros/adapters/show.sql#L1-L14

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.