Error when using Sequence: Object '...' does not exist or not authorized.

[Philipp]

I found this article referencing to this gist and tried using that macro. But I’m always getting an error on line 15 where the sequence is used. I created that sequence manually in Snowflake and made sure the permissions were set correctly. Then I also tried creating the sequence from within dbt. Creating the Sequence this way also worked without error, but still I’m getting an error when trying to use that sequence.

I’m new to dbt so possible it is something simple I’m forgetting, but the last hours of research have been without success.

In case I should share some more details let me know.
Thanks for the support.

Philipp

[a_slack_user]

Hey Philipp, that article is for Redshift, which didn’t (at the time of writing) have a native way to unnest json. I think that’s changed, but still a moot point since you’re using Snowflake.

On Snowflake, you should be able to use a combination of flatten() and the lateral keyword. There’s an example here: https://community.snowflake.com/s/question/0D50Z00006uSiEUSA0/how-to-implement-unnest-function-in-snowflake

_{Note: @joellabes (dbt Labs) originally posted this reply in Slack. It might not have transferred perfectly.}

[Philipp]

Thanks for the feedback.

I got it working doing the following:

select my_table.*, flat_table.value as flat_value
from my_table
left join
    lateral flatten(strtok_to_array(my_table.netesd_value, ',')) as flat_table