Welcome to my newest series- debugging Dbt. As an analytics engineer, I use dbt every day in my job to write robust data models. It is a great tool for simplifying and containerizing the way data models run. While it makes things easier, there are still things I am constantly learning about the tool. I wanted to use my struggles and learnings as a way to teach others within the community that may run into the same issues.
Today I will be addressing a problem I had using Snowflake tables with the same name as reserved keywords. If you are using Fivetran to ingest your data, then you may experience a similar issue.
Two data sources that we ingest using Fivetran are Zendesk and Shopify. Both of these sources contain tables with the names order and group. Because these words are considered reserved keywords in many databases, in my case Snowflake, then we are going to run into some issues running them with dbt.
This may show up in the form of an error that looks like this:
SQL compilation error: syntax error line 1 at position 26 unexpected 'group'. syntax error line 1 at position 31 unexpected '<EOF>'.
How to fix this:
- Go to the src.yml file that contains the database/schema information for your corresponding source table.
- Under the name of you table add identifier and the name of the table.
identifier: ORDER
# or
identifier: GROUP
- Also add another line that says quoting and sets the nested identifier to be true.
quoting:
identifier: true
Adding these few things to your src.yml file for these sources changes the way the SQL is compiled in dbt. Instead of reading your source tables as
select * from raw.zendesk.group
it will now be read as
select * from raw.zendesk."GROUP"
The changes simply add quotes to the table name which lets SQL know that this is actually the table’s name rather than a reserved keyword.
Issues like these often manifest themselves in many different types of error messages. It can be hard to get to exactly what is causing a certain issue, but it often ends up being fairly simple. Be sure to follow to stay updated on the rest of my Debugging Dbt series. Next up is incremental models!