query-comment

dbt_project.yml
query-comment: string

The query-comment configuration also accepts a dictionary input, like so:

dbt_project.yml
query-comment:
comment: string
append: true | false

Definition

A string to inject as a comment in each query that dbt runs against your database. This comment can be used to attribute SQL statements to specific dbt resources like models and tests.

The query-comment configuration can also call a macro that returns a string.

Changelog

Default

By default, dbt will insert a JSON comment at the top of your query containing the information including the dbt version, profile and target names, and node ids for the resources it runs. For example:

/* {"app": "dbt", "dbt_version": "0.15.0rc2", "profile_name": "debug",
"target_name": "dev", "node_id": "model.dbt2.my_model"} */
create view analytics.analytics.orders as (
select ...
);

Using the dictionary syntax

The dictionary syntax includes two keys:

  • comment (optional, see above for default): The string to be injected to a query as a comment.
  • append (optional, default=false): Whether a comment should be appended (added to the bottom of a query) or not (i.e. added to the top of a comment). By default, comments are added to the top of queries (i.e. append: false).

This syntax is useful on databases like Snowflake which remove leading SQL comments.

Examples

Prepend a static comment

The following example injects a comment that reads /* executed by dbt */ into the header of the SQL queries that dbt runs.

dbt_project.yml
query-comment: "executed by dbt"

Example output:

/* executed by dbt */
select ...

Disable query comments

dbt_project.yml
query-comment:

Or:

dbt_project.yml
query-comment: null

Prepend a dynamic comment

The following example injects a comment that varies based on the configured user specified in the active dbt target.

dbt_project.yml
query-comment: "run by {{ target.user }} in dbt"

Example output:

/* run by drew in dbt */
select ...

Append the default comment

The following example uses the dictionary syntax to append (rather than prepend) the default comment.

Note that the comment: field is omitted to allow the default to be appended.

dbt_project.yml
query-comment:
append: True

Example output:

select ...
/* {"app": "dbt", "dbt_version": "0.16.`0rc2`", "profile_name": "debug", "target_name": "dev", "node_id": "model.dbt2.my_model"} */
;

Append a custom comment

The following example uses the dictionary syntax to append (rather than prepend) a comment that varies based on the configured user specified in the active dbt target.

dbt_project.yml
query-comment:
comment: "run by {{ target.user }} in dbt"
append: True

Example output:

select ...
/* run by drew in dbt */
;

Intermediate: Use a macro to generate a comment

The query-comment config can reference macros in your dbt project. Simply create a macro with any name (query_comment is a good start!) in your macros directory, like so:

macros/query_comment.sql
{% macro query_comment() %}
dbt {{ dbt_version }}: running {{ node.unique_id }} for target {{ target.name }}
{% endmacro %}

Then call the macro in your dbt_project.yml file. Make sure you quote the macro to avoid the yaml parser from trying to interpret the { as the start of a dictionary.

dbt_project.yml
query-comment: "{{ query_comment() }}"

Advanced: Use a macro to generate a comment

The following example shows a JSON query comment which can be parsed to understand the performance characteristics of your dbt project.

macros/query_comment.sql
{% macro query_comment(node) %}
{%- set comment_dict = {} -%}
{%- do comment_dict.update(
app='dbt',
dbt_version=dbt_version,
profile_name=target.get('profile_name'),
target_name=target.get('target_name'),
) -%}
{%- if node is not none -%}
{%- do comment_dict.update(
file=node.original_file_path,
node_id=node.unique_id,
node_name=node.name,
resource_type=node.resource_type,
package_name=node.package_name,
relation={
"database": node.database,
"schema": node.schema,
"identifier": node.identifier
}
) -%}
{% else %}
{%- do comment_dict.update(node_id='internal') -%}
{%- endif -%}
{% do return(tojson(comment_dict)) %}
{% endmacro %}

As above, call this macro as follows:

dbt_project.yml
query-comment: "{{ query_comment(node) }}"

Compilation context

The following context variables are available when generating a query comment:

Context VariableDescription
dbt_versionThe version of dbt being used
env_varSee env_var
modulesSee modules
run_started_atWhen the dbt invocation began
invocation_idA unique ID for the dbt invocation
fromjsonSee fromjson
tojsonSee tojson
logSee log
varSee var
targetSee target
connection_nameA string representing the internal name for the connection. This string is generated by dbt.
nodeA dictionary representation of the parsed node object. Use node.unique_id, node.database, node.schema, etc