Snowflake loader error - column already exists

I’ve just added link click tracking to a tracker instance, and I’m getting an ETL failure with the following:

Error during unstruct_event_com_snowplowanalytics_snowplow_link_click_1 column creation, while loading enriched/run=2020-03-04-15-15-20/. SQL compilation error:
column 'UNSTRUCT_EVENT_COM_SNOWPLOWANALYTICS_SNOWPLOW_LINK_CLICK_1' already exists
No new columns were added, safe to rerun. Trying to rollback and exit

It does exist in the database ( we have used this event in the past). How do I get the loader to stop from trying to re-create the column?

Hi @iain,

Loader takes the current state from DynamoDB manifest table and it seems that you ran into an invalid state. You need to edit the manifest table manually in order to trick Loader. Here’s a short description of table structure: https://github.com/snowplow-incubator/snowplow-snowflake-loader/wiki/Snowflake-Manifest. Loader tries to create new columns that don’t yet known from ShredTypes columns.

What you will need to do is to add unstruct_event_com_snowplowanalytics_snowplow_link_click_1 to any previous item.

However, I’m wondering how did you end up with invalid state. Did you re-create the manifest table by any chance? Asking because I afraid that if you add this shredded type - you eventually run into a similar problem with next one.

Thanks for the pointer Anton.

I approached the problem from the other end, and took a clone of the atomic.events table, then dropped the column:

unstruct_event_com_snowplowanalytics_snowplow_link_click_1 

I then re-ran the snowflake import and it’s run successfully. I can then re-add the data from the column using an UPDATE based on event_id from the cloned table.

As to how it got that way - that’s my fault, I did something hacky when setting up the table originally and copied an existing database table structure because I was having problems with the snowflake loader setup at the time. I’ll know not to do that in future! :slight_smile:

1 Like