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

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"} */;

BigQuery: include query comment items as job labels#

Changelog

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