Categories:: Information Schema , Table functions

DBT_PROJECT_EXECUTION_HISTORY¶

Returns the execution history of dbt Projects on Snowflake.

Call this function to get metadata and results from past dbt Project executions within seven days of the current time. Optionally, specify the values to filter the results by.

Use this function with the following system functions to access dbt artifacts and logs programmatically:

For more information, see Access dbt artifacts and logs programmatically.

See also:: CREATE DBT PROJECT, EXECUTE DBT PROJECT

Syntax¶

DBT_PROJECT_EXECUTION_HISTORY (
  [ OBJECT_NAME => '<name>' ]
  [ , OBJECT_TYPE = { WORKSPACE | DBT PROJECT }]
  [ , START_TIME_RANGE_START => <start_time> ]
  [ , START_TIME_RANGE_END => <end_time>  ]
  [ , RESULT_LIMIT = <integer> ]
  [ , COMMAND = <dbt_command> ]
  [ , USER_NAME = <user_name> ]
  [ , DATABASE = <db_name> ]
  [ , SCHEMA = <schema_name> ]
)

Arguments¶

OBJECT_NAME = <name>

Name of the workspace or dbt project object that the execution belongs to.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

OBJECT_TYPE = { WORKSPACE | DBT PROJECT }

The type of the object, WORKSPACE or DBT PROJECT, the execution belongs to.

START_TIME_RANGE_START | START_TIME_RANGE_END = timestamp

Timestamp to filter a range of executions.

RESULT_LIMIT = integer

An integer specifying the maximum number of rows returned by the function, from 1 - 10,000 inclusive.

Default: 100

COMMAND = dbt_command

Specifies the dbt command used to execute the dbt project object.

USER_NAME = user_name

Name of the user that initiated the dbt project object execution.

DATABASE = db_name

Return only records for the specified database.

SCHEMA = schema_name

Return only records for the specified schema.

Output¶

The function returns the following columns.

To view these columns, you must use a role with the MONITOR privilege.


Column Name	Data Type	Description
QUERY_ID	TEXT	ID of the query.
QUERY_START_TIME	TIMESTAMP_LTZ	The time the query started.
QUERY_END_TIME	TIMESTAMP_LTZ	The time the query ended.
USER_NAME	TEXT	The user that created the dbt project object.
OBJECT_NAME	TEXT	Name of the workspace or dbt project object the execution belonged to.
OBJECT_TYPE	TEXT	Type of object, such as WORKSPACE or DBT PROJECT.
DATABASE_NAME	TEXT	Database of the object.
SCHEMA_NAME	TEXT	Schema of the object.
COMMAND	TEXT	The command that was run for the object.
ARGS	TEXT	The arguments that were used in the run for the object.
ERROR_CODE	NUMBER	If applicable, the error code for the run.
ERROR_MESSAGE	TEXT	If applicable, error message stating why the run failed.
WAREHOUSE	TEXT	Warehouse used for the object.
STATE	TEXT	State of run, such as HANDLED_ERROR or SUCCESS.
DBT_VERSION	TEXT	The specific version used for this run. For example, `1.9.4`.
DBT_SNOWFLAKE_VERSION	TEXT	The specific dbt Projects on Snowflake version with patch version used for this run. For example, `1.9.4`.

Access control requirements¶

This table function includes only runs from workspaces and dbt Projects in which you have the following privileges:

OWNERSHIP, READ, or WRITE on workspaces
OWNERSHIP, USAGE, or MONITOR on dbt Projects

Usage notes¶

Use the exact dbt project object name (case-sensitive if created with quotes). If no row matches (wrong name or no executions yet), you might get an Inputs may not be null. error.
Use function arguments such as DATABASE, SCHEMA, and OBJECT_NAME to filter results whenever possible. These filters are applied before the RESULT_LIMIT (default: 100 rows), so using them ensures you get the most relevant results rather than filtering a potentially truncated result set with a WHERE clause.

Examples¶

The following example audits which engine version was used for recent runs:

SELECT
    object_name,
    query_start_time,
    query_end_time,
    query_id,
    command,
    state
FROM TABLE (
    SNOWFLAKE.INFORMATION_SCHEMA.DBT_PROJECT_EXECUTION_HISTORY(
      DATABASE => 'ANALYTICS_DB',
      SCHEMA => 'DBT_PROD',
      OBJECT_NAME => 'FINANCE_ANALYTICS'
    )
  )
ORDER BY query_end_time DESC;

For detailed examples of using the DBT_PROJECT_EXECUTION_HISTORY table function with system functions to access dbt artifacts and logs programmatically, see Access dbt artifacts and logs programmatically.