How to - debugging "SQL compilation errors" with Snowflake + view + persist_docs + non-dbtonic column names

[jeremyyeo]

This is tangentially related to dbt-labs/dbt-snowflake#73

1. You're using Snowflake.
2. Your model is a view.
3. You have persist_docs is turned on.
4. You have non-dbtonic column names.

-- models/my_model.sql

select 1 as "badly/NAMED"

# dbt_project.yml

...
models:
  my_dbt_project:
    +materialized: view
    +persist_docs:
      relation: true
      columns: true

When we dbt run:

00:40:52  Running with dbt=1.0.8
00:40:53  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 179 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
00:40:53  
00:41:02  Concurrency: 1 threads (target='dev')
00:41:02  
00:41:02  1 of 1 START view model dbt_jyeo.my_model....................................... [RUN]
00:41:04  1 of 1 ERROR creating view model dbt_jyeo.my_model.............................. [ERROR in 2.87s]
00:41:04  
00:41:04  Finished running 1 view model in 11.18s.
00:41:04  
00:41:04  Completed with 1 error and 0 warnings:
00:41:04  
00:41:04  Database Error in model my_model (models/my_model.sql)
00:41:04    001003 (42000): SQL compilation error:
00:41:04    syntax error line 8 at position 6 unexpected 'badly'.
00:41:04    compiled SQL at target/run/my_dbt_project/models/my_model.sql
00:41:04  
00:41:04  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Now let's take a look at an excerpt of the debug logs:

...
00:50:36.897506 [debug] [Thread-1  ]: On model.my_dbt_project.my_model: /* {"app": "dbt", "dbt_version": "1.0.8", "profile_name": "snowflake", "target_name": "dev", "node_id": "model.my_dbt_project.my_model"} */

  create or replace  view development.dbt_jyeo.my_model 
  
    
    
(
  
    
      badly/NAMED     #⚠️⚠️⚠️⚠️⚠️ THINGS GO BAD HERE ⚠️⚠️⚠️⚠️⚠️#
    
    
  
)

   as (
    select 1 as "badly/NAMED"
  );
00:50:37.240375 [debug] [Thread-1  ]: Snowflake adapter: Snowflake query id: 01a54df2-0401-9f33-000d-378308a3a83e
00:50:37.241112 [debug] [Thread-1  ]: Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 8 at position 6 unexpected 'badly'.
00:50:37.241812 [debug] [Thread-1  ]: finished collecting timing info
00:50:37.242583 [debug] [Thread-1  ]: On model.my_dbt_project.my_model: Close
00:50:37.852198 [debug] [Thread-1  ]: Database Error in model my_model (models/my_model.sql)
  001003 (42000): SQL compilation error:
  syntax error line 8 at position 6 unexpected 'badly'.
  compiled SQL at target/run/my_dbt_project/models/my_model.sql
...

Because you have got persist_docs turned on, and are using non-dbtonic column names, we have to turn on the quote property on the column by adding a schema yml file:

# models/schema.yml
version: 2
models:
  - name: my_model
    columns:
      - name: "badly/NAMED"
        quote: true

Once, we have set quote to true on the badly named column, an excerpt from the debug logs of a dbt run now yield:

00:49:44.522684 [debug] [Thread-1  ]: On model.my_dbt_project.my_model: /* {"app": "dbt", "dbt_version": "1.0.8", "profile_name": "snowflake", "target_name": "dev", "node_id": "model.my_dbt_project.my_model"} */

  create or replace  view development.dbt_jyeo.my_model 
  
    
    
(
  
    
      
  "badly/NAMED" COMMENT $$$$

    
    
  
)

   as (
    select 1 as "badly/NAMED"
  );
00:49:44.936069 [debug] [Thread-1  ]: SQL status: SUCCESS 1 in 0.41 seconds
00:49:44.952504 [debug] [Thread-1  ]: finished collecting timing info
00:49:44.953226 [debug] [Thread-1  ]: On model.my_dbt_project.my_model: Close

The moral of the story is to essentially be tidy when you have column names¹:

1. No special characters.
2. No quotes.
3. No reserved keywords as column names (e.g. from).
4. snake_case everwhere.

Which would have avoided the entire situation above :)

Footnotes

1. See our style guide here: https://github.com/dbt-labs/corp/blob/main/dbt_style_guide.md ↩