Dbt: Jinja Comments included in dbt run SQL causing Database Error

[bcolbert978]

Describe the bug

In previous versions of dbt, Jinja comments were supported, allowing us to leave blocks of notes inside this templating pattern: {# comment #} and have those notes excluded from the compiled and executed SQL.
The SQL output of dbt compile looks fine, but the output of dbt run or dbt run --full-refresh both seem to include the comments, including the braces, which is resulting in Database errors.

Steps To Reproduce

create a simple model with a Jinja comment, such as:

{# link to source: https://www.source_data_spreadhseet.com #}
select * from source_data_raw.spreadsheet_data

Expected behavior

DBT run should execute only select * from source_data_raw.spreadsheet_data and exclude the comment.

Screenshots and log output

2020-08-14 20:49:29.325609 (Thread-2): On model.ov.stg_discounted_skus: /* {"app": "dbt", "dbt_version": "0.17.2", "profile_name": "ov_redshift", "target_name": "dev", "node_id": "model.ov.stg_discounted_skus"} */


  create view "analytics"."dbt_brad"."stg_discounted_skus__dbt_tmp" as (
    {# Link to Google Sheet: https://docs.google.com/spreadsheets/d/aaaaaaaaaaaaa-LKCEs/edit #}

    select * from google_sheets_raw.discounted_sku_exclusion_list
  ) with no schema binding;

2020-08-14 20:49:29.373295 (Thread-2): Postgres error: syntax error at or near "{"
LINE 5:     {# Link to Google Sheet: https://docs.google.com/spreads...
            ^

2020-08-14 20:49:29.373740 (Thread-2): On model.ov.stg_discounted_skus: ROLLBACK
2020-08-14 20:49:29.420368 (Thread-2): On model.ov.stg_discounted_skus: Close
2020-08-14 20:49:29.421006 (Thread-2): Database Error in model stg_discounted_skus (models/staging/google_sheets/stg_discounted_skus.sql)
  syntax error at or near "{"
  LINE 5:     {# Link to Google Sheet: https://docs.google.com/spreads...
              ^
  compiled SQL at target/run/ov/models/staging/google_sheets/stg_discounted_skus.sql
Traceback (most recent call last):
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/adapters/postgres/connections.py", line 46, in exception_handler
    yield
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/adapters/sql/connections.py", line 77, in add_query
    cursor.execute(sql, bindings)
psycopg2.errors.SyntaxError: syntax error at or near "{"
LINE 5:     {# Link to Google Sheet: https://docs.google.com/spreads...
            ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/node_runners.py", line 228, in safe_run
    result = self.compile_and_execute(manifest, ctx)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/node_runners.py", line 171, in compile_and_execute
    result = self.run(ctx.node, manifest)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/node_runners.py", line 273, in run
    return self.execute(compiled_node, manifest)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/node_runners.py", line 459, in execute
    result = MacroGenerator(materialization_macro, context)()
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/clients/jinja.py", line 327, in __call__
    return self.call_macro(*args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/clients/jinja.py", line 257, in call_macro
    return macro(*args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/runtime.py", line 675, in __call__
    return self._invoke(arguments, autoescape)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 61, in macro
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/runtime.py", line 290, in call
    return __obj(*args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/clients/jinja.py", line 327, in __call__
    return self.call_macro(*args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/clients/jinja.py", line 257, in call_macro
    return macro(*args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/runtime.py", line 675, in __call__
    return self._invoke(arguments, autoescape)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/runtime.py", line 679, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 41, in macro
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/sandbox.py", line 462, in call
    return __context.call(__obj, *args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/jinja2/runtime.py", line 290, in call
    return __obj(*args, **kwargs)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/adapters/base/impl.py", line 228, in execute
    fetch=fetch
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/adapters/sql/connections.py", line 122, in execute
    _, cursor = self.add_query(sql, auto_begin)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/adapters/sql/connections.py", line 85, in add_query
    return connection, cursor
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/contextlib.py", line 99, in __exit__
    self.gen.throw(type, value, traceback)
  File "/Applications/anaconda3/envs/ov-dbt/lib/python3.6/site-packages/dbt/adapters/postgres/connections.py", line 57, in exception_handler
    raise dbt.exceptions.DatabaseException(str(e).strip()) from e
dbt.exceptions.DatabaseException: Database Error in model stg_discounted_skus (models/staging/google_sheets/stg_discounted_skus.sql)
  syntax error at or near "{"
  LINE 5:     {# Link to Google Sheet: https://docs.google.com/spreads...
              ^
  compiled SQL at target/run/ov/models/staging/google_sheets/stg_discounted_skus.sql
2020-08-14 20:49:29.433043 (Thread-2): 15:49:29 | 1 of 1 ERROR creating view model dbt_brad.stg_discounted_skus........ [ERROR in 1.22s]
2020-08-14 20:49:29.433334 (Thread-2): Finished running node model.ov.stg_discounted_skus

System information

Which database are you using dbt with?

• [ ] postgres
• [X] redshift
• [ ] bigquery
• [ ] snowflake
• [ ] other (specify: ____________)

The output of dbt --version:

installed version: 0.17.2
   latest version: 0.17.2

Up to date!

Plugins:
  - bigquery: 0.17.2
  - snowflake: 0.17.2
  - redshift: 0.17.2
  - postgres: 0.17.2

The operating system you're using:
MacOS Catalina, 10.15.6 (19G2021)

The output of python --version:
Python 3.6.6 :: Anaconda, Inc.

Additional context

originally posted in #support on dbt slack: link

[jtcohen6]

@bcolbert978 Thanks for raising this, it's definitely a bug.

I noticed that this error only crops if there is _no other Jinja code_ in the file. If there is other Jinja (set statements, macro calls, etc.) the comment is appropriate scrubbed from the compiled SQL. If the only Jinja code is Jinja comments, then the comment is included in the compiled code.

That's likely why we didn't see this bug crop up sooner: most models have at least one of ref, source, and/or config.

I confirmed that the Jinja comment is scrubbed out in v0.16.1, so this appears to be a v0.17 regression. I imagine the fix is straightforward, so I'd like to fit it in for v0.18.0 if possible. It's an awkward bug, even if it doesn't affect a lot of projects.

[bcolbert978]

Makes sense - yes, we came across it on our slow-moving Google Sheet imports which don't do any ref or source calls given that we don't track freshness on them. Thanks for the quick triage!

[beckjake]

I noticed that this error only crops if there is no other Jinja code in the file.

Thank you for narrowing it down Jeremy, that tells me exactly what's wrong.

This is almost certainly caused by this regex, which is the basis for a performance optimization added on 0.17. We bypass rendering if there's no {{, }}, {%, or %} in the text. That pattern should definitely include {# and #}!

[jtcohen6]

This may also be the cause of https://github.com/fishtown-analytics/dbt/issues/2621