Querying The Dbt Information Schema
Use when answering questions about a dbt v2 project's own metadata, which models...
Install
npx skills add dbt-labs/dbt-agent-skills --skill querying-the-dbt-information-schema -g -a claude-code -y- Repository
- dbt-labs/dbt-agent-skills750
- Category
- Testing
- Updated
- 6d ago
- License
- Apache-2.0
- Context cost
- Mediumabout 3,000 tokens
- Runs scripts
- No, instructions only
What it does
Use when answering questions about a dbt v2 project's own metadata, which models, sources, tests, columns, configs, tags, packages or lineage exist, what is untested or undocumented, what depends on what, how long models took in the last run, or when using `dbt show --info`, `{{ info_schema() }}`, `--generate-info-schema`, `target/info_schema/`, or writing `dbt check` SQL. Prefer this over grepping YAML or parsing `manifest.json` on dbt v2.
Querying the dbt Information Schema
The dbt Information Schema is a set of SQL views over your project's metadata: models, sources, tests, columns, DAG edges, configs, and run results. You query it with DuckDB SQL, locally, without touching the warehouse.
Use it to answer "what is in this project" questions with one SQL query, instead of grepping YAML or loading a 70 MB manifest.json.
Contents
| You want to… | Go to |
|---|---|
| Check the project can use this | Prerequisites |
| Answer a one-off question | Route A: dbt show (default) |
Run many queries, script them, or use run_results_latest | Route B: DuckDB on the parquet files |
| Avoid wrong answers from tricky columns | Column gotchas |
| Understand why a view is empty | What is populated when |
| Copy a working query | Common queries |
| Enforce a rule on every build | Turning a query into a check |
Which route? Use dbt show unless you have a reason not to. It always reads the metadata from the latest dbt command, and it needs nothing installed. Switch to DuckDB when it is installed and you will run many queries, because each dbt show call costs about 0.6 s versus about 0.1 s for DuckDB. The trade-off is that you must refresh the parquet files yourself.
Route A: dbt show | Route B: DuckDB on parquet | |
|---|---|---|
| Table names | {{ info_schema('models') }} (Jinja, bare view name) | dbt.models, dbt_rt.run_results (after .read views.sql), or 'dbt.models.parquet' |
| Freshness | Updated by build, run, check by default | Updated only by a command run with --generate-info-schema |
| Needs | dbt v2 | dbt v2 once, plus the duckdb CLI or a parquet library |
dbt_rt.run_results_latest | Not available | Available |
| Speed per query | ~0.2, 0.7 s | ~0.1 s |
