dbt show error: limit must be positive
Fixes dbt show limit validation errors for analytics engineers and repair agents. Use when dbt show fails saying limit must be positive. Not for query errors inside the shown SQL.
TL;DR
You passed --limit 0 or a negative number, or a variable feeding --limit resolved to one. Pass a positive integer like --limit 5. If the limit comes from a var, check the var value.
Error
dbt show error: limit must be positiveSteps
Look at your dbt show invocation and find the --limit value. Expected: You see --limit 0, a negative number, or a var reference.
Replace it with a positive integer: dbt show --select my_model --limit 5. Expected: The command runs and prints sample rows.
If the limit came from a var or env var, print its resolved value and fix the source. Expected: The variable resolves to a positive integer.
Re-run dbt show and confirm you get the expected row sample. Expected: Sample rows print; no limit error.
When to use
- dbt show fails with limit must be positive
- a script wraps dbt show and passes a computed limit
- the limit comes from an environment variable that is unset or zero
When not to use
- the shown query itself errors (that is a SQL problem in the model)
- you want all rows (dbt show always limits; use a warehouse query for full output)
- dbt show fails to compile the model (fix the model first)
Tool compatibility
- dbt-core 1.5+ (dbt show command)
- dbt Cloud IDE has its own preview; this is the CLI command
- --limit accepts any positive integer
Variant phrasings
limit must be positive with --limit 0
Zero is not positive. There is no way to show zero rows meaningfully; use 1 if you only want the schema.
limit error only in a wrapper script
The script computes the limit; log the computed value before calling dbt show to see the bad input.
Why it happens
dbt show compiles the model and runs it with a LIMIT clause for preview. A non-positive limit is rejected up front because the warehouse would either error or return nothing useful. The validation is protecting you from a meaningless preview.
Edge cases
- Some warehouses treat LIMIT 0 as valid SQL returning no rows; dbt rejects it anyway at the CLI layer, so do not rely on warehouse behavior.
- dbt show --inline with custom SQL validates the limit the same way.
Provenance
Resolved from the public thread: https://vectle.com/posts/pstT9pwLBRU5csj7OBDn2eQw