/querying-the-dbt-information-schema

name: querying-the-dbt-information-schema
description: 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.
user-invocable: false
metadata:
  author: dbt-labs

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](#prerequisites) | | Answer a one-off question | [Route A: `dbt show`](#route-a-dbt-show-default) (default) | | Run many queries, script them, or use `run_results_latest` | [Route B: DuckDB on the parquet files](#route-b-duckdb-on-the-parquet-files) | | Avoid wrong answers from tricky columns | [Column gotchas](#column-gotchas) | | Understand why a view is empty | [What is populated when](#what-is-populated-when) | | Copy a working query | [Common queries](#common-queries) | | Enforce a rule on every build | [Turning a query into a check](#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 |

Prerequisites

• **dbt v2 only.** Run `dbt --version` first. The docs mark this feature "Available in v2". On dbt Core 1.x, fall back to `manifest.json`.
• **Project metadata must exist.** `dbt build`, `dbt run`, and `dbt check` write it by default. `dbt parse` and `dbt compile` write it with `--generate-info-schema`. If none of these has run, `dbt show --info` fails with `InfoSchemaUnavailable` (dbt1656) and names a command to run.
• **Know what the metadata reflects.** `select command, generated_at from {{ info_schema('invocations') }} order by generated_at desc` lists the dbt commands behind it. Mention the latest one in your answer.
• If you get 0 rows from a view that should have data, refresh the metadata with `dbt parse --generate-info-schema` and retry. `parse` does not connect to the warehouse. `compile`, `run` and `build` do, so ask before running them.
• **Only local runs are here.** Runs from dbt platform jobs or another machine are not in the local metadata. For those, use the job's own artifacts.

Route A: `dbt show` (default)

# List every available view (unknown names print the full list)
dbt show --info nonexistent

# One view, all rows, clean JSON on stdout
dbt show --quiet --info models --limit -1 --output json

# Ad-hoc SQL (DuckDB dialect)
dbt show --quiet --output json --limit -1 --inline "
  select resource_type, count(*) n
  from {{ info_schema('dag_nodes') }}
  group by 1 order by 2 desc"

# Discover a view's columns yourself
dbt show --limit -1 --inline "describe select * from {{ info_schema('node_columns') }}"

`--info <view>` is shorthand for `--inline "select * from {{ info_schema('<view>') }}"`. dbt runs the query on an embedded DuckDB, so you don't need to install DuckDB.

Rules that prevent wrong answers

| Rule | Why | |---|---| | Put a **literal** `{{ info_schema('view') }}` call in every `--inline` query. | dbt routes the query to DuckDB only when the SQL contains a literal call. `info_schema(my_var)` or a bare `dbt.models` sends it to the **warehouse**. It then takes 10+ seconds and fails with errors like `Schema '<db>.DBT' does not exist`. A slow query or a warehouse error means the query went to the wrong engine. A misrouted query also writes `target/inline_<hash>.sql` and overwrites `target/run_results.json`; a correctly routed one leaves `target/` alone. | | Pass bare view names: `--info models`, `info_schema('models')`. | `dbt.models` is rejected as an unknown view. | | Use `--limit -1` when you need every row. | The default limit is 10, so counts and lists are silently truncated. | | Use `--quiet --output json` when you will parse the output. | Without `--quiet`, a version banner and an execution summary wrap the JSON. The table output also truncates wide columns. Errors still print under `--quiet`, and the exit code is 1. | | Filter `enabled` on **both sides** when counting resources. | `models` and `data_tests` include **disabled** rows. `dag_nodes` holds only enabled resources. The counts will not match. | | Filter on `package_name` to separate your project from installed packages. | Package models appear in the same views. Read your project's name with `select project_name from {{ info_schema('project') }}`. | | No `ref()`, `source()` or other project macros. | They fail with `unknown function: Jinja macro or function ref is unknown`. You can't join metadata with warehouse data in one query. Run two queries, or export to JSON or CS