If column is set to TIMESTAMP_NTZ in snowflake it cannot be converted to Date

[visch]

Error:

NotImplementedError: Default TypeEngine.as_generic() heuristic method was unsuccessful for target_snowflake.snowflake_types.TIMESTAMP_NTZ. A custom as_generic() method must be implemented for this type class.

To reproduce

1. Take any table that's already in Snowflake smoke-test works, run meltano run tap-smoke-test target-snowflake

  - name: tap-smoke-test
    variant: meltano
    pip_url: git+https://github.com/meltano/tap-smoke-test.git
    config:
      streams:
      - stream_name: animals
        input_filename: https://gitlab.com/meltano/tap-smoke-test/-/raw/main/demo-data/animals-data.jsonl

2. Change one of the metadata fields that has the column type of TIMESTAMP_NTZ to DATE see below for an example

UPDATE MELTANO_DATABASE.ZENOTI.ANIMALS  SET _SDC_EXTRACTED_AT_TEMP = TO_DATE(SDC_EXTRACTED_AT);
ALTER TABLE MELTANO_DATABASE.ZENOTI.ANIMALS  DROP COLUMN _SDC_EXTRACTED_AT;
ALTER TABLE MELTANO_DATABASE.ZENOTI.ANIMALS  RENAME COLUMN _SDC_EXTRACTED_AT_TEMP to _SDC_EXTRACTED_AT;

3. Run it again meltano run tap-smoke-test target-snowflake You'll get the same error

[pnadolny13]

I wonder if this is related to MeltanoLabs/tap-snowflake#34. I believe the context is that the SDK treats all dates as datetimes.

[visch]

I'm not so sure. Converting between the two in snowflake (ignore the target) takes some migration as well like

UPDATE MELTANO_DATABASE.ZENOTI.ANIMALS  SET _SDC_EXTRACTED_AT_TEMP = TO_DATE(SDC_EXTRACTED_AT);
ALTER TABLE MELTANO_DATABASE.ZENOTI.ANIMALS  DROP COLUMN _SDC_EXTRACTED_AT;
ALTER TABLE MELTANO_DATABASE.ZENOTI.ANIMALS  RENAME COLUMN _SDC_EXTRACTED_AT_TEMP to _SDC_EXTRACTED_AT;

There's really 2 questions I think with this issue

1. Should we migrate an existing TIMESTAMP_NTZ column to a Date Column if the schemas are different?
2. If yes - We need to detect the difference and make an auto migration plan (lossless I think is rename the old column, migrate the data from the old column into the new column, use the new column going forward)
3. If no (seems incorrect) - We need to convert the data from a timestamp to a date field

Similar to #120