Database Error: 001003 (42000): SQL compilation error

Question

I am getting this error when using dbt cli 0.21.1 with Snowflake:

Encountered an error: Database Error 001003 (42000): SQL compilation error: syntax error line 1 at position 31 unexpected '<EOF>'. syntax error line 1 at position 30 unexpected '.'.

... when running "dbt build". I have stripped down my models to the minimum and get the same error whatever I do. I have done "dbt clean". The results of "dbt debug" and "dbt parse" look fine.

In the dbt.log file, I can see it fails on a Snowflake command:

create schema if not exists FT.

... with the SQL compilation error message shown above.

I found the same error in the Snowflake query history, with the same command text. So I tried it by hand in the Snowflake console. Lo and behold, it works fine without the trailing period, but Snowflake produces exactly this error with that trailing period.

So it seems like dbt and Snowflake disagree about whether a SQL command should end with a period. Which one has a bug? Or is there a way to tell one or the other to change?

Answer 1

I forgot that I had created a generate_schema_name macro -- and it was messing up the schema name, turning it into an empty string. The create schema command was trying to create a schema named "FT." but because the schema name was blank, it ended up as "FT." -- and the trailing period confused me.

Mea culpa.

Hope this description ends up helping someone else who makes the same mistake.