Option to keep default date columns as TIMESTAMP_NTZ in oracle to snowflake migration

[dat-a-man]

Feature description

Community user : When migrating data from Oracle to Snowflake, I would like the ability to default date columns to TIMESTAMP_NTZ instead of TIMESTAMP_TZ.

Are you a dlt user?

None

Use case

When migrating data from Oracle to Snowflake, the default behavior sets date columns to TIMESTAMP_TZ. In many cases, it is more desirable to use TIMESTAMP_NTZ to avoid timezone-related complications and align with existing data standards in Snowflake.

Proposed solution

• Provide an option to set the default timestamp type to TIMESTAMP_NTZ during the migration process.
• Alternatively, allow users to specify the desired timestamp type in the configuration.

Related issues

No response

[rudolfix]

dlt does not support timestamps without timezones and to be able to pass such timestamps form snowflake to oracle we need to extend the core library first.

1. We'd need a new hint in TColumnType that will say if data_type contains timezone or not (or define time zone as string, with default to "UTC"). See precision and scale hints already there. The idea is the same.
2. Each of our destinations has a type mapper which needs to be be able to interpret this new thing.
3. pyarrow.py contains functions that convert dlt schema into arrow schema and vice versa. those functions needs to be upgraded
4. we'll need plenty of tests: loading non-tz aware datetimes via json and parquet into all possible destinations
5. let's make it work for duckdb+postgres+snowflake first

Note: I didn't research how tz-aware timestamps are stored in our destinations. I think timezone is just a metadata flag on the column and values are regular floats...

[donotpush]

@dat-a-man I am investigating a solution to change the destination timestamp types.

I have two questions about your case.

(1) Source - Oracle, what are you using for timestamps? TIMESTAMP (timezone not included) or TIMESTAMP WITH TIME ZONE (timezone included)?

(2) Destination - Snowflake, which timezone are you seeing in your replicated data? Is it always +00:00?

Thanks!