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 executions of dbt project objects¶

When you use a task to run a dbt project on a schedule and have a workspace connected to a dbt project object, 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. From the list, select the schedule (task) that you want to inspect, 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 the 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.

   The list of dbt projects includes columns with the following information. You can filter the list by date range, command, and run status.

   • 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:

   • 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. To view the query details in query history, select the query ID.

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

Access dbt artifacts and logs programmatically¶

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. The OUTPUT_ARCHIVE_URL value that is available in EXECUTE DBT PROJECT output and in the Query details tab of query history provides a reference to this file. You can’t use this OUTPUT_ARCHIVE_URL directly. Instead, you must use the URL to copy the file into a named internal stage. You can then access the file from the named stage.

The following example shows SQL commands that create a named internal stage with the name 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 doesn’t work for EXECUTE DBT PROJECT commands that run from a task. Because tasks run as the system user, accessing results requires the system user.

To access results when using a task, you can 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();