2022-10-18 14:30:45 normalization > Database Error in model RESTAURANT_SCD (models/generated/airbyte_incremental/scd/READ_MIRROR_V3/RESTAURANT_SCD.sql)
2022-10-18 14:30:45 normalization >   100035 (22007): Timestamp '+192153-11-18T12:06:13.000000' is not recognized

this is in the snowflake destination, which i understand won't support the timestamp... but im not sure where this timstamp is coming from, on the db source, i search or it but can't find it:

SELECT *
FROM restaurant
WHERE updated_at = '192153-11-18 12:06:13.000000';

Is there an easy way to each for the airbyte row id in the raw table in snowflake to see the entire row with the issue?

@[DEPRECATED] Marcos Marx turned this message into Zendesk ticket 2914 to ensure timely resolution!

49842 |         11816 | 128777-06-08 11:14:30 | 2022-06-25 04:08:30.91141  | 2022-06-25 04:08:30.91141

Database Error in model RESTAURANT_SCD (models/generated/airbyte_incremental/scd/READ_MIRROR_V3/RESTAURANT_BLACKOUTS_SCD.sql)
  100035 (22007): Timestamp '+128777-06-08T11:14:30.000000' is not recognized
  compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_incremental/scd/READ_MIRROR_V3/RESTAURANT_SCD.sql

Hi. not sure i fully understand here the issue. if you run into:

For DATE and TIMESTAMP data, Snowflake recommends using years between 1582 and 9999. Snowflake accepts some years outside this range, but years prior to 1582 should be avoided due to limitations on the Gregorian Calendar.

it means that the logic you have that converts unix or epoc time into timestamp is incorrect since as you can understand there is no 1582 data available unless it is artificially created which is possible in some cases. In any case the workaround is to work within snowflake limits and create a dimension that identifies the timestamp by era\ decade\ century etc.. This is how it usually goes. If you have this error:

2022-10-18 14:30:45 normalization > Database Error in model RESTAURANT_SCD (models/generated/airbyte_incremental/scd/READ_MIRROR_V3/RESTAURANT_SCD.sql)
2022-10-18 14:30:45 normalization >   100035 (22007): Timestamp '+192153-11-18T12:06:13.000000' is not recognized

try this query since the issue is from the source also. snowflake dialact

SELECT *
FROM restaurant
WHERE updated_at REGEXP '192153-11-18';

or

SELECT *
FROM restaurant
WHERE RLIKE(updated_at, '[0-9]{5}-11-18');

Hi: I did find the row though:

id   | restaurant_id |      expires_at       |         created_at         |         updated_at 
49842 |         11816 | 128777-06-08 11:14:30 | 2022-06-25 04:08:30.91141  | 2022-06-25 04:08:30.91141

Only by custom query since dbt airbyte normalization takes json extracted data and flattens it- so either dont do dbt normalization and do it your self or create dbt model that does this correctly

i decided to fix the root of the issues in the source db, no one needs timetamps that large haha

yea hehe 🙂