Dbt: Snowflake lowercase environment variable and improper database quoting

[tayloramurphy]

Describe the bug

When the config for a snapshot specifies the database as an environment variable via:

target_database=env_var("SNOWFLAKE_LOAD_DATABASE")

Then the database name is not properly quoted.

When the database is set via target_database='{{ env_var("SNOWFLAKE_LOAD_DATABASE") }}'

Then the environment variable is lower cased and the error returned is

Encountered an error:
Compilation Error in macro list_relations_without_caching (macros/adapters/common.sql)
  Env var required but not provided: 'snowflake_load_database'

  > in macro statement (macros/core.sql)
  > called by macro snowflake__list_relations_without_caching (macros/adapters.sql)
  > called by macro adapter_macro (macros/adapters/common.sql)
  > called by macro list_relations_without_caching (macros/adapters/common.sql)
  > called by macro list_relations_without_caching (macros/adapters/common.sql)

Steps To Reproduce

For improper quoting, start a snowflake database name with a number and reference it in a snapshot. You should see an error like:

Database Error in snapshot sfdc_opportunity_snapshots (snapshots/sfdc/sfdc_opportunity_snapshots.sql)
  001003 (42000): 018e881a-0143-4dac-0000-289d085bb67e: SQL compilation error:
  syntax error line 24 at position 9 unexpected '2206'.

In this case our database name is 2206-BRANCHNAME_RAW.

To reproduce the lower case error, just use the jinja interpolation format for the env_var.

Expected behavior

I would expect it to respect the capitalization of the environment variable name and to quote the database name fully.

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

System information

Which database are you using dbt with?

• [x] snowflake

The output of dbt --version:

± dbt --version
installed version: 0.14.0
   latest version: 0.14.0

Up to date!

The operating system you're using:
Mac OSX 10.14

*The output of python --version:Python 3.7.4*

Additional context

Add any other context about the problem here.

[tayloramurphy]

I've confirmed this isn't limited to just snapshots. Happens when using the jinja env_var() in a model schema config.

[tayloramurphy]

From convo on slack, it seems setting the environmental variable within a second set of curly braces should do the following:

2019-08-30 12:13:24,571 (Thread-1): On sfdc_account_snapshots: select count(*)
        from "{{ env_var("SNOWFLAKE_LOAD_DATABASE") }}".information_schema.schemata
        where upper(schema_name) = upper('snapshots')
            and upper(catalog_name) = upper('{{ env_var("SNOWFLAKE_LOAD_DATABASE") }}')

But it seems it's being parsed out.

[drewbanin]

Hey @tayloramurphy - I wrote up the more generic version of this issue over here: https://github.com/fishtown-analytics/dbt/issues/1717

Please take a look and let me know if you have any questions! A fix for this is prioritized for our next minor release.

Thanks so much for the bug report!

[drewbanin]

For any future visitors to this issue: the correct syntax is

{{ config(target_schema=env_var('ENV_VAR_NAME')) }}

It is _not_:
` {{ config(target_schema="{{ env_var('ENV_VAR_NAME')) }}" }}

In general, you shouldn't nest curly brackets inside of other curly brackets.

1717 and #1719 change dbt's behavior to help dbt fail in more clear and obvious ways when this incorrect syntax is provided.