dbt CLI preview command

[TylerHillery]

Summary of the feature

Be able to run dbt preview my_dbt_model to show the results of the model in the terminal similar to the preview functionality in dbt cloud.

Who will this benefit?

dbters who live in the CLI. Being able to preview the results of a model before it materializes in the database can be helpful to see if the output matches the user's expected behavior.

Are you interested in contributing to this feature?

I would really like to work on developing this if it garners enough interest.

[joellabes]

This would be really cool! I'm not sure if we want more dependencies on agate in the long term (I have seen its name spoken in anger on many issues over time), but disregarding that then using print_table might be a pretty straightforward way to achieve this

[TylerHillery]

Thanks for the advice! I'm currently in the early stages of working on this, reading through the code base to better understand it. Appreciate you pointing me in the right direction.

[jtcohen6]

@joellabes Agree on both fronts: doing this with agate would make it fairly trivial today, and probably not the right dependency long-term — but not in a way that would throw the whole effort to ruin if we someday find we need to swap out that last-mile piece.

If the goal is just to support something akin to dbt run-operation preview --args 'sql: "select 1 as id", I think it would take <10 lines of code to get that working. (@lostmygithubaccount and I were just playing around with it yesterday.) The greater complexity for this comes from the need to actually render/compile Jinja-SQL, before running it against the database and returning results — and ideally doing that in one fell swoop.

That's what the run_sql method of dbt-rpc exists to support today. It compiles the node, then executes the compiled SQL and returns a (wait for it) agate table of the results.

I think the dbt-rpc codebase is a bit of a minefield, so I don't recommend plumbing its depths too deeply — but at a high level, a real dbt preview task/command would need to do those same two steps.

[quazirfangenus]

If anybody is wondering what the macro will look like here is an example,

{% macro runSQL(query) %}
    {% do run_query(query).print_table() %}
{% endmacro %}

Put this macro inside runSQL.sql file inside the macros folder of your dbt project directory. From the dbt project directory run dbt run-operation runSQL --args '"query" : "select * from table_name"'.

Here you can find few parameters print_table takes to customize the output.

[dbeatty10]

@TylerHillery thank you for proposing this idea and thanks to everyone who upvoted it!

This was implemented in #7208, and it is part of the dbt Core 1.5 release scheduled for the end of this month. The 1st release candidate is out now for anyone who wants to try it out before then. We'd love to get your feedback and find any bugs before the final release!

You can see the docs here:
https://docs.getdbt.com/reference/commands/show

[joellabes]

I just gave this a go, I love that it works for previewing failing tests as well!

[dbeatty10]

A caveat is that it doesn't work for seeds yet: #7273

There are some workarounds in the meantime.