Updates for dbt show

[dbeatty10]

Contributions

• I have read the contribution docs, and understand what's expected of me.

Link to the page on docs.getdbt.com requiring updates

https://docs.getdbt.com/reference/commands/show

What part(s) of the page would you like to see updated?

Original content from @dataders copy-pasted from here and then lightly edited.

---

New dbt show feature in v1.5

dbt show shipped in 1.5.0 with a --limit flag that, when provided, would limit the number of results that dbt would grab to display.

One little problem

As described in dbt-labs/dbt-core#8496, most adapters load the entire model into memory while running dbt show which can be prohibitively slow for large data sets.

It does not modify the original query, which means that even if you provide --limit 5, the command will not complete until the entire underying query is complete which can take a long time if the query's result set is large. This is especially evident becaues dbt show is now also used for dbt Cloud IDE's "preview" button.

A fix for the problem

The fix was dbt-labs/dbt-core#8641, which extends the implementation of the --limit flag to wrap the underlying query into a CTE and append a LIMIT {limit} clause.

This fix was backported to 1.5 and 1.6, so that it applies to all versions of dbt that support dbt show.

Changes for adapter maintainers

No change is needed if the adapter's data platform supports ANSI SQL LIMIT clauses. Any changes can be made in a dispatched version of get_limit_subquery_sql(), see default__get_limit_subquery(). This was merged into 1.7.0, but also backported to 1.5 and 1.6, so if a change is needed, it will likely also need to be backported and patched accordingly.

dbt.tests.adapter.dbt_show.test_dbt_show also contains new tests to ensure the new behavior functions properly:

• BaseShowSqlHeader
• BaseShowLimit

Additional information

No response

[dbeatty10]

@dataders can you update this issue as-needed?