[DBT] Add Query comments for better debugging [Tip-3]

Author(s): Karthikeyan Siva Baskaran

Originally published on Towards AI.

Software Engineering

Customize the query comment in each query that triggered from dbt

Using query_comment in dbt, we can customize the comments to inject into each query that gets triggered from dbt. For example, the query run by which user/role, on which warehouse and what profile, etc.

Default comment by dbt

By default, the comment will be prepended for the query, in case if you want to have the comment at the end of the query. Set append mode in dbt_project.yml

Customize the comment using macro

Add below macro in query_comment.sql under macro section in dbt project

And, add the macro to dbt_project.yml

The below comment will be appended to all the queries that get triggered from dbt. You can see this under query history in the Snowflake history tab.

/* {"app": "dbt", "dbt_version": "0.21.0", "profile_name": "transformer_profile", "target_name": "dev", "target_database": "silver", "target_warehouse": "transform_wh", "target_user": "transform_user", "target_role": "transformer", "target_account": "dev-account", "file": "models/silver/fin/silver__fin__invest.sql", "node_id": "model.transformer.silver__fin__invest", "node_name": "silver__fin__invest", "resource_type": "model", "package_name": "transformer", "relation": {"database": "silver", "schema": "fin", "identifier": "invest"}} */

The above same code is also maintained under this ever-growing git repository.

I hope this article helped you to customise the query comments. If you like this blog, please share it with your teammates, friends, and clap U+1F44F in the range of 1 to 50 in Medium. You can clap up to 50 times per post.

Happy Commenting!

This article is part of DBT Tips and Tricks Series. Previous articles like below:

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI