Monitor dbt Projects on Snowflake¶

Enable monitoring features for dbt projects¶

To enable monitoring features for your dbt project object, set LOG_LEVEL, TRACE_LEVEL, and METRIC_LEVEL on the database and schema where your dbt project object is created, as shown in the following SQL example:

ALTER SCHEMA my_db.my_dbt_project_schema SET LOG_LEVEL = 'INFO';
ALTER SCHEMA my_db.my_dbt_project_schema SET TRACE_LEVEL = 'ALWAYS';
ALTER SCHEMA my_db.my_dbt_project_schema SET METRIC_LEVEL = 'ALL';

Monitor scheduled dbt project executions¶

When you run a dbt project on a schedule by using a task, you can use the workspace for dbt Projects on Snowflake to quickly access monitoring information for task run history and a task graph, if applicable.

To monitor scheduled execution of a dbt project object from a workspace:

1. From the dbt project menu in the upper right of the workspace editor, under Scheduled runs, choose View schedules.

2. Select the schedule (task) that you want to inspect from the list, and then choose View details.

   The information pane for the task opens, where you can view Task details, the task Graph (if applicable), and Run history of this task. For more information, see Viewing tasks and task graphs in Snowsight.

3. From the Run history for any scheduled dbt project run in the list, select the Open query history button on the far right to view query details, the query profile, and query telemetry for the run. For more information, see Review details and profile of a specific query.

Monitor dbt projects in Snowsight¶

You can use Monitoring in Snowsight to view detailed monitoring information about dbt project executions (runs). You must have privileges to view monitoring information for the dbt project object.

1. From the navigation pane, select Monitoring » dbt projects. A histogram shows the frequency of dbt project runs and a list of projects that have run. You can filter the list by date range, by command, and by run status.

   The list of dbt projects includes columns with the following information:

   • PROJECT - The name of the dbt project object and the number of executions (runs) in the selected time period.

   • LAST COMMAND - The dbt command that executed during the last run.

   • LAST RUN STATUS - The result of the run: Succeeded, Executing, or Failed.

   • LAST RUN - The elapsed time since the last run. To reverse the sort order, select the column header. The most recent run is shown first by default.

   • PREVIOUS RUNS - The number of runs in the selected time period by status.

   • DATABASE and SCHEMA - The database and schema where the dbt project object is saved.

   • LAST RUN PARAMETERS - The dbt command line arguments (ARGS) specified in the EXECUTE DBT PROJECT command for the last dbt project run.

2. To inspect individual project runs, select a dbt project object from the list.

   The dbt project details page in the database object explorer opens for that dbt project object.

   The Run history tab is selected by default, with the following information for each job run in the selected time period:

   • LAST COMMAND - The dbt command that executed during the last run.

   • STATUS - The result of the run: Succeeded, Executing, or Failed.

   • RUN TIME - The elapsed time since the last run. To reverse the sort order, select the column header. The most recent run is shown first by default.

   • PARAMETERS The dbt command line arguments (ARGS) specified in the EXECUTE DBT PROJECT command for the last dbt project run.

3. To see job details for a run, select it from the list.

   The dbt run details pane opens, which includes the following tabs:

   • The Job details tab is selected by default and displays the following information:

     • Status - The result of the run: Succeeded, Executing, or Failed..

     • Start time, End time, and Duration - The time that the run started, the time it ended, and how long it took to run.

     • Warehouse size - The size of the warehouse that was used to execute the run.

     • Query ID - The unique identifier for the query that executed the dbt project command. Select the query ID to view the query details in the query history.

     • SQL Text - The EXECUTE DBT PROJECT command that executed.

     • dbt <command> - For the dbt command that ran (for example, run or build), shows the dbt model, the Time taken for the run to execute, and the status of that model run.

   • The Output tab shows the stdout generated by the dbt project during the run.

   • The Trace tab shows the trace information generated by the dbt project during the run. For more information about traces, see Viewing trace data.

4. To see more detailed query information, from the Job details tab, select the Query ID.

   The query history page for the job run query opens with tabs to view Query Details, the Query Profile, and Query Telemetry for the dbt run that you selected.

   For more information, see Review details and profile of a specific query.

Programmatically accessing dbt artifacts and logs¶

By default, when a dbt project object executes, log files and artifacts that dbt writes to the /target directory are saved in a dbt_results.zip file. This file can be referenced using the OUTPUT_ARCHIVE_URL value that is available in the output when a EXECUTE DBT PROJECT command runs.

The OUTPUT_ARCHIVE_URL cannot be accessed directly and must be copied into a named internal stage. The file can then be accessed from the named stage. This value is also available in the Query details tab of query history for each dbt project run.

The following example shows SQL commands to create a named internal stage named my_dbt_stage, copy the dbt_results.zip file available into the named stage, and then output a presigned URL to access the file. Replace output_archive_url with the actual value that EXECUTE DBT PROJECT returns.

-- Create a named internal stage using encryption

CREATE OR REPLACE STAGE my_dbt_stage
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

-- Copy the dbt results zip file into the named stage

COPY FILES INTO @my_dbt_stage/results/
  FROM (
     SELECT '{output_archive_url}', 'dbt_results.zip');

-- Get a presigned URL from which to access the file directly

SELECT get_presigned_url(@my_dbt_stage, 'results/dbt_results.zip');

The previous example does not work for EXECUTE DBT PROJECT commands that run from a task. Because tasks run as the system user, accessing results requires the system user.

The solution is to create a stored procedure that can be called from a task to execute the dbt project and copy results.

The following example creates a stored procedure named run_dbt_and_copy_results_file that executes a dbt project named my_dbt_project and copies the results to the my_dbt_stage named internal stage, which already exists. The stored procedure uses a timestamp to name each results file that it copies to the named stage.

CREATE OR REPLACE PROCEDURE my_db.public.run_dbt_and_copy_results_file()
RETURNS varchar
LANGUAGE SQL
COMMENT = 'Execute dbt and copy the files in the same sproc call.'
EXECUTE AS CALLER
AS
DECLARE
    output_archive_url varchar;
BEGIN
    EXECUTE DBT PROJECT my_db.my_schema.my_dbt_project args='run';
    output_archive_url := (SELECT output_archive_url FROM TABLE(RESULT_SCAN(last_query_id())));
    COPY FILES INTO @my_dbt_stage/results/ FROM (SELECT :output_archive_url, (to_char(current_timestamp(), 'YYYYMMDD_HH24_MI') || '.zip'));
END;

The following example creates a task named run_my_dbt_project that runs the stored procedure with the execute and copy commands every six hours:

CREATE OR REPLACE TASK my_db.my_schema.run_my_dbt_project
  WAREHOUSE = my_dbt_warehouse
  SCHEDULE = '6 hours'
AS
  CALL my_db.public.run_dbt_and_copy_results_file();