The problem I’m having
SQL syntax error since usernames are not escaped
The context of why I’m trying to do this
I’m creating incremental materialization, the config block looks like so:
config(
materialized="incremental",
unique_key=["dbt_scd_id", "snapshot_date"],
incremental_strategy="delete+insert"
)
}}
We have Redshift usernames which are firstname.lastname,
I am aware that this is a bad practice and we’re working on moving away from here.
However when it comes to dbt, I’m trying to creating an incremental model, and it throws a SQL syntax error since the usernames are not escaped.
I dove into the source code a little bit and have managed to find where this happens but I am not sure how to move past it.
Essentially, in the materialization macro everything continues to the point of the apply_grants macro.
Here:
It goes through the apply_grants macro and then tries to revoke access for some users.
And of course, when it runs into a SQL statement like:
revoke select on db.schema.table from firstname.lastname;
It runs into a syntax error.
What I’ve already tried
Have no idea how to move past this.
Some example code or error messages
syntax error at or near "."
This is the error that gets bubbled up, had to trawl through the logs and go step by step to find where the error originates from.