snowflake incremental models with virtual columns failures

Show and Tell
,
1

I was recently working on converting a table managed outside of dbt to our dbt project. It is an incremental model, and the table in question contains a couple of virtual fields. While testing my dbt incremental model implementation, I was getting odd “ambiguous column” errors. It turned out the reason for the errors had to do with the virtual columns. Our resolution was to add pre-hooks to drop the virtual columns and post hooks to re-add them, and then the model ran fine. Just thought I’d share, especially since its kind of an obscure issue and took a while to track down.

3 Likes
2

Thanks for taking the time to share this solution, @nadelman ! :clap:

1 Like
3

I’m having this issue with “ambiguous column” in an incremental using dbt, but I don’t have any virtual columns. (That I’m aware of.) Can you be more specific about what your “virtual columns” contained?

4

Thanks for sharing this @nadelman.

I’ve been facing what seems to be a similar issue with amazon athena incremental models. Any possibility you could share some of the implementation details and/or config syntax?

5

Forgot to mention in the initial post, we are using snowflake. Basically, our incremental models in this case were pulling snowflake metadata from the account usage views in to a table to make sure we maintain full history on them. For query history, the snowflake metadata provides timestamps for the start/end times of queries. The virtual columns we add in just convert the timestamps to less granular time units, for convenience and to support some downstream reporting. So, putting the hooks in the config block in the model file itself, we added:

            pre_hook=[
                "ALTER TABLE {{ this }} drop column IF EXISTS START_TIME_HOUR",
                "ALTER TABLE {{ this }} drop column IF EXISTS START_TIME_DATE"
            ],
            post_hook=[
                "ALTER TABLE {{ this }} add column if not exists START_TIME_HOUR TIMESTAMP_LTZ(9) as (TRUNCTIMESTAMPTOHOUR(CAST(START_TIME AS TIMESTAMP_LTZ(9))))",
                "ALTER TABLE {{ this }} add column if not exists START_TIME_DATE DATE as (CAST(START_TIME AS DATE))"
            ]
1 Like
6

Thank you so much @nadelman. Really appreciate this!