I expected the <snowflake target> to support table...
# troubleshootings
sterling_paramore
09/12/2022, 5:23 PMI expected the snowflake target to support table schema changes. However, it looks like I’m getting an error when a new column is added to my source. Should this target be able to handle schema changes and is there something I might be missing to make this work properly?
Also, it appears that something is deleting the internal stage files so I can’t investigate them directly. Anyone know tricks to turn off auto-deleting of these stage files?
Copy code
2022-09-12T17:07:03.599035Z [info ] snowflake.connector.errors.ProgrammingError: 100080 (22000): Number of columns in file (104) does not match that of the corresponding table (105), use file format option error_on_column_count_mismatch=false to ignore this error cmd_type=elb consumer=True name=target-snowflake--etahub producer=False stdio=stderr string_id=target-snowflake--etahub
2022-09-12T17:07:03.599148Z [info ] File '@LGENS/batch_spn8i2_5.csv.gz', line 2, character 1 cmd_type=elb consumer=True name=target-snowflake--etahub producer=False stdio=stderr string_id=target-snowflake--etahubp
pat_nadolny
09/12/2022, 5:41 PM@sterling_paramore I dont know all the details but it does support schema changes, checkout the transferwise documentation on how they do it. Also check out the archive-load-files setting for archiving the files in S3, I havent used it but its in the readme.
s
sterling_paramore
09/12/2022, 6:59 PMWe don’t use S3 and are just relying on the internal stage.
p
pat_nadolny
09/13/2022, 4:33 PM@sterling_paramore Ah ok yeah I do the same. No you've got me curious too 😄. I see that it cleans up after itself after the load. I wonder if you run a list for you stage
list @%mytables
sterling_paramore
09/13/2022, 4:38 PMI don’t really know what’s going on because I actually can see them today. I must have made some mistake with with the database/schema I was using that I didn’t appreciate at the time.
The root issue though is that in my source data, a column was renamed. It doesn’t appear there’s anything in this target that can handle this situation. This section about columns to replace, only considers columns whose datatypes have changed. I would have expected the target to drop (or version) the old column name and create a new one. However, there doesn’t appear to be anything in the code that would detect that a column exists in snowflake, but not in the data stream.
p
pat_nadolny
09/13/2022, 5:18 PMThat makes sense, do you have a work around to get it fixed now that you know whats happening? I suppose you could alter your existing column name manually. Another thing to note is that we're planning to build and maintain Meltano SDK based versions of some of the most used taps/targets so theyre stable, feel free to add any context related to this that might be helpful in the target-snowflake issue!
s
sterling_paramore
09/13/2022, 5:29 PMWorking on it…
sterling_paramore
09/13/2022, 6:07 PMHmmmm….. so maybe I don’t actually need to drop the column. The solution was right there in the error message:
use file format option error_on_column_count_mismatch=false to ignore this error