Categories:

System functions (System Information)

SYSTEM$LOCATE_DBT_ARCHIVE

Returns the URL from which you can retrieve zipped dbt run artifacts for a specified dbt project.

Use this function with the DBT_PROJECT_EXECUTION_HISTORY function to access dbt artifacts and logs programmatically.

Syntax

SYSTEM$LOCATE_DBT_ARCHIVE ( '<query_id>' )
Copy

Arguments

query_id

The query ID of the dbt project run whose files you want to locate.

Returns

This function returns the URL from which you can retrieve the zipped contents of the results of a specified dbt Project.

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

Access control requirements

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

  • OWNERSHIP or USAGE on workspaces

  • OWNERSHIP, USAGE, or MONITOR on dbt Projects

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema. Note that a role granted any privilege on a schema allows that role to resolve the schema. For example, a role granted CREATE privilege on a schema can create objects on that schema without also having USAGE granted on that schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes

  • This system function works only on dbt project objects; it isn’t available for workspaces.

  • Query IDs generated from CREATE DBT PROJECT or ALTER DBT PROJECT … ADD VERSION aren’t supported for this system function.

  • Direct querying of file content (for example, Query Examples) isn’t supported.

  • If query_id is NULL or not a dbt execution, you’ll get an error.

  • dbt project results are available for up to 14 days.

  • Files might be unavailable if a run times out, is canceled, or fails before they are uploaded. In such cases, runs appear as UNHANDLED ERROR in dbt history.

  • You can’t use this function to get logs for runs that are in progress because the logs file is only available after the run in complete.

Examples

The following example returns the snow:// URL of the zipped artifacts (for example, dbt_artifacts.zip) for the specified execution.

You can use this URL with GET to download the ZIP file (or COPY FILES to move it to your own stage). For the folder path instead of the ZIP, use SYSTEM$LOCATE_DBT_ARTIFACTS.

SELECT SYSTEM$LOCATE_DBT_ARCHIVE($latest_query_id);
Copy

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

Language: English