#056: How to Run Queries from the Terminal (dbt Show Command)

The dbt show command, added in dbt Core 1.5, compiles a model, runs it against your warehouse, and prints the first few rows directly in the terminal. Nothing is materialized and nothing is written to a file, so you can preview a model, inspect a column or chase a failing test without leaving your editor. You point it at a model with a selector, change the row count with a limit flag, or pass an inline query that uses ref to pick exactly the columns you want. It replaces the loop of copying compiled SQL into the database console just to see what a model returns. For small teams working in dbt Core it's one of the simplest workflow upgrades available.

What the dbt show command is

If you build models in dbt Core, you know the loop. You write SQL in your editor, run the model, then open the database console, paste in the compiled code, and look at the results to validate the data.

As of dbt 1.5 there's a command that shortcuts that. dbt show sits alongside dbt run, dbt build and dbt test.

It compiles the SQL definition of an object, which can be a model, a test or an analysis, runs it against the database, and previews the results in the terminal. By default it returns five rows.

Nothing gets materialized

The results of the preview query are not stored anywhere. They aren't materialized in the warehouse and they aren't written to a file. They're logged and displayed in the terminal, and that's it.

That matters for the test failure example later. You can preview a model you know is broken without creating or overwriting a table.

Two ways to use it

• A selector. Point the command at an existing model, the same way you'd select one for a run or a test.
• An inline query. Write your own select statement, use ref to point at a model, and pass it to the command as a string.

Both are below, in the order I'd learn them.

Using the command

Preview a model with a selector

Say I have a customers model I'm making changes to and I want to see what it returns. The command takes the same selector operator as run and test:

dbt show -s customers

-s is the short form of --select. dbt connects to the database using your default target, runs the compiled model, and prints the first five rows inline.

One thing you'll notice right away: it doesn't show every column. The terminal only has so much width, so a wide model gets cut off after the first six or so columns. The inline option further down is the answer to that.

What it runs against

The preview uses whatever target is the default in your profiles.yml. If you develop against a dev schema, you're looking at dev data, which is what you want: the state of your working copy, not production.

To preview against a different target, add --target the same way you would for a run.

Change the row count

Five rows is the default. The --limit flag adjusts it:

dbt show -s customers --limit 10

Now you get ten rows printed in the terminal. I wouldn't push this very high, since the output is inline and a few hundred rows in a terminal isn't useful to anyone. For a quick look at what a model produces, ten is plenty.

Query specific columns

Not seeing all the columns is a real limitation. Sometimes the columns you care about are the ones that got cut off, and sometimes you only want two or three of them anyway.

The inline flag

Instead of a selector, you can write a query and pass it with --inline. The ref function works inside it just like it does in a model file:

dbt show --inline "select * from {{ ref('customers') }}"

That's the example from the documentation with the model name swapped in. It still respects the row limit, and with a select star it still truncates columns. The point is that you now control what goes in the select list.

Investigating a test failure

This is where the command earns its place. In the demo, the customers model has a test on total_revenue_in_usd that requires the value to be positive. To force a failure, I multiplied the column by negative one and ran the model:

dbt run -s customers
dbt test -s customers

A dbt build would have done both steps at once. Either way, the test fails, and now I want to see the rows that caused it.

The old way is to open the compiled test SQL, hop over to the database server, paste it in and work through it there. Instead, I can go back to the inline query and pick the two columns that matter:

dbt show --inline "select customer_name, total_revenue_in_usd from {{ ref('customers') }}"

Right there in the terminal I can see the revenue values are negative, except for the zeros, which is exactly what the test caught.

Copy the test scenario

You can go further and add a where clause that mirrors the test condition:

dbt show --inline "select customer_name, total_revenue_in_usd from {{ ref('customers') }} where total_revenue_in_usd < 0"

Now the preview returns only the rows that fail. You've effectively copied the test scenario into a query and looked at the result without materializing anything or switching tools.

Customize it however you need. Add columns, change the filter, join to another ref. It's a normal query, it just runs from your terminal against your default target.

Where it fits in your workflow

This is a subtle command and it's easy to skip past it in the release notes. In practice it changes how often you leave your editor during a working session.

The three cases I use it for most:

• Preview before deploying. See what a model returns before you run it, since the preview is never materialized.
• Investigate a test failure. Reproduce the failing condition with an inline query and look at the rows.
• Review records without changing screens. A quick check on a column or a count, without opening the warehouse UI.

None of these are hard to do the old way. They just cost a context switch each time, and over a day of development those add up.

Related reading

• A Simple 4-Step Process for Creating dbt Models
• dbt Environments vs Targets: What's the Difference?
• Creating a Data Model with dbt: Tests
• SQL vs dbt Models (and the Value of CTEs)

Final takeaway

Most of what slows down a small dbt team isn't the SQL, it's the switching between tools to check it. dbt show keeps that check in the terminal you're already in, which is why it's the first command I point new dbt Core developers at once they're past run and test.