Skip to content

Information Schema Checks: Query#

Note

The below checks require manifest.json and the dbt Information Schema (info_schema/v1/ in the dbt target directory) to be present. dbt 2.0 and later write the Information Schema when a command runs with --generate-info-schema. Add --static-analysis strict to include column types and column-level lineage. See Information Schema checks for details.

A check that runs custom SQL against the dbt Information Schema.

Functions:

Name Description
check_info_schema_query

A SQL query against the dbt Information Schema must return no rows.

check_info_schema_query #

A SQL query against the dbt Information Schema must return no rows.

Rationale

Some project rules are easiest to express as a query, for example "no model joins more than three sources" or "every metric has a label". dbt 2.0 stores project metadata as tables in the dbt Information Schema, so a rule becomes a SELECT that returns the offending rows. This check runs such a query and fails when it returns any rows, with the same severity, selection and reporting as every other dbt-bouncer check. Queries written for dbt's own dbt check command can be reused by replacing {{ info_schema('<table>') }} with the table name, e.g. dbt.models.

Note

This check requires the dbt Information Schema (dbt 2.0+, --generate-info-schema). Tables are named <schema>.<table> as in the Parquet file names, e.g. dbt.models, dbt.column_lineage, dbt_rt.run_results. See the dbt Information Schema reference for the available tables. The query runs in an in-memory DuckDB database with file access disabled.

Parameters:

Name Type Description Default
sql str

A single DuckDB SELECT statement. Each row returned is a failure.

 required

Receives at execution time:

Name Type Description
info_schema InfoSchema

The dbt Information Schema tables.

Other Parameters (passed via config file):

Name Type Description
description str | None

Description of what the check does and why it is implemented.
severity Literal[error, warn] | None

Severity level of the check. Default: error.

Example(s): 

info_schema_checks:
    - name: check_info_schema_query
      description: Public models must have a description.
      sql: |
          SELECT unique_id
          FROM dbt.models
          WHERE access = 'public'
            AND coalesce(description, '') = ''

Source code in src/dbt_bouncer/checks/info_schema/query.py 
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
@check(code="IS001", validate=_require_single_select)
def check_info_schema_query(ctx, *, sql: str):
    """A SQL query against the dbt Information Schema must return no rows.

    !!! info "Rationale"

        Some project rules are easiest to express as a query, for example "no model joins more than three sources" or "every metric has a label". dbt 2.0 stores project metadata as tables in the dbt Information Schema, so a rule becomes a SELECT that returns the offending rows. This check runs such a query and fails when it returns any rows, with the same severity, selection and reporting as every other dbt-bouncer check. Queries written for dbt's own `dbt check` command can be reused by replacing `{{ info_schema('<table>') }}` with the table name, e.g. `dbt.models`.

    !!! note

        This check requires the dbt Information Schema (dbt 2.0+, `--generate-info-schema`). Tables are named `<schema>.<table>` as in the Parquet file names, e.g. `dbt.models`, `dbt.column_lineage`, `dbt_rt.run_results`. See the [dbt Information Schema reference](https://docs.getdbt.com/reference/info-schema) for the available tables. The query runs in an in-memory DuckDB database with file access disabled.

    Parameters:
        sql (str): A single DuckDB SELECT statement. Each row returned is a failure.

    Receives:
        info_schema (InfoSchema): The dbt Information Schema tables.

    Other Parameters:
        description (str | None): Description of what the check does and why it is implemented.
        severity (Literal["error", "warn"] | None): Severity level of the check. Default: `error`.

    Example(s):
        ```yaml
        info_schema_checks:
            - name: check_info_schema_query
              description: Public models must have a description.
              sql: |
                  SELECT unique_id
                  FROM dbt.models
                  WHERE access = 'public'
                    AND coalesce(description, '') = ''
        ```

    """
    columns, rows = ctx.info_schema.query(sql)
    if rows:
        shown = "; ".join(
            ", ".join(f"{c}={v}" for c, v in zip(columns, row, strict=True))
            for row in rows[:_MAX_ROWS_IN_MESSAGE]
        )
        more = (
            f" (first {_MAX_ROWS_IN_MESSAGE} shown)"
            if len(rows) > _MAX_ROWS_IN_MESSAGE
            else ""
        )
        fail(f"The query returned {len(rows)} row(s){more}: {shown}.")