Skip to main content

query-comment

dbt_project.yml
query-comment: string

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

dbt_project.yml
models:
my_dbt_project:
+materialized: table

query-comment:
comment: string
append: true | false
job-label: true | false # BigQuery only

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.

Default

By default, dbt will insert a JSONJSON (JavaScript Object Notation) is a minimal format for semi-structured data used to capture relationships between fields and values. 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 query). 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"} */
;

BigQuery: include query comment items as job labels

If query-comment.job-label is set to true, dbt will include the query comment items, if a dictionary, or the comment string, as job labels on the query it executes. These will be included in addition to labels specified in the BigQuery-specific config.

dbt_project.yml

query-comment:
job-label: True

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. For details about release versioning, refer to Versioning.
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
0