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 object.

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

Syntax

SYSTEM$LOCATE_DBT_ARCHIVE ( '<query_id>' )

Arguments

query_id

The query ID of the dbt project object 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 object.

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

Access control requirements

This function can only be used with dbt project objects, not Workspaces, when you have one of the following privileges:

  • OWNERSHIP, USAGE, or MONITOR on the dbt project object

For details about these privileges, see dbt project object privileges.

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent 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 and 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 locate the archive for runs that are in progress because the archive is only available after the run is 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);

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