Snowflake destination has no way to emit TIMESTAMP_LTZ -- only TIMESTAMP_TZ/TIMESTAMP_NTZ

[geforce123]

Title

Snowflake destination has no way to emit TIMESTAMP_LTZ -- only TIMESTAMP_TZ/TIMESTAMP_NTZ

Environment

• dlt 1.29.1

Description

SnowflakeTypeMapper.to_db_datetime_type (dlt/destinations/impl/snowflake/factory.py)
only ever produces two possible column types for a timestamp-typed column:

timestamp = "TIMESTAMP_TZ" if timezone else "TIMESTAMP_NTZ"

Snowflake has three timestamp variants, not two:

• TIMESTAMP_NTZ -- no timezone.
• TIMESTAMP_TZ -- stores an explicit offset captured at write time; querying it
  later always returns that same stored offset, regardless of the querying
  session's TIMEZONE setting.
• TIMESTAMP_LTZ -- stores an absolute instant (UTC-normalized internally); querying
  it re-localizes the displayed value to whatever the querying session's TIMEZONE
  parameter is set to.

There's no timezone/hint combination that reaches TIMESTAMP_LTZ today. This
matters concretely for any source system whose native timezone-aware type has
TIMESTAMP_LTZ's semantics rather than TIMESTAMP_TZ's -- e.g. Oracle's
TIMESTAMP WITH LOCAL TIME ZONE, which behaves exactly like TIMESTAMP_LTZ
(absolute instant, session-timezone-based display) and not like TIMESTAMP_TZ
(fixed offset). Mapping such a source column to TIMESTAMP_TZ is not just a
different type name -- it's a different, weaker contract: every consumer gets the
same fixed offset forever, instead of the source's actual "each client sees it in
their own timezone" behavior.

We verified this concretely end-to-end: loading an Oracle TIMESTAMP(6) WITH LOCAL TIME ZONE column through dlt into Snowflake produces TIMESTAMP_TZ(6), and
querying that column under two different Snowflake session TIMEZONE settings
returns the same fixed offset both times -- confirming it does not re-localize,
unlike the source column's actual behavior.

Suggested fix

Add a third value to the timezone column hint's effective range, or a separate
destination-specific hint (e.g. a snowflake_adapter-style column adapter, similar
to how other destinations expose destination-specific type overrides) that lets a
caller request TIMESTAMP_LTZ explicitly.

Workaround we're using in the meantime

A subclassed SnowflakeTypeMapper/Destination factory that overrides
to_db_datetime_type (and the sct_to_unbound_dbt/sct_to_dbt/dbt_to_sct lookup
tables) to emit TIMESTAMP_LTZ wherever the stock mapper would emit TIMESTAMP_TZ.
type_mapper is already a pluggable field on DestinationCapabilitiesContext, so
this is possible without patching dlt itself -- filing this mainly so the gap is
tracked upstream and others hitting the same Oracle/Postgres-style
"local timezone" source type don't have to rediscover it.

[ignatNakhai-rc]

We have run into the same issue during an Oracle → Snowflake migration.

Our Oracle source uses TIMESTAMP WITH LOCAL TIME ZONE, but the Snowflake destination created by DLT maps these columns to TIMESTAMP_TZ. According to the Snowflake migration documentation, the recommended mapping is:

TIMESTAMP WITH TIME ZONE → TIMESTAMP_TZ
TIMESTAMP WITH LOCAL TIME ZONE → TIMESTAMP_LTZ

https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/data-migration-validation/validate-oracle#data-type-mappings

This preserves the semantics of Oracle TIMESTAMP WITH LOCAL TIME ZONE, where values are normalized internally but presented in the session time zone. DLT currently only supports mapping timezone-aware timestamps to TIMESTAMP_TZ, with no option to use TIMESTAMP_LTZ, which results in different behavior from Oracle.

It would be great if the Snowflake destination supported TIMESTAMP_LTZ as a configurable mapping option for Oracle TIMESTAMP WITH LOCAL TIME ZONE.

[rudolfix]

@geforce123 yeah I see. and tbh. I think local timezone (UTC offset) timestamps should be the default... what I'd do is to add snowflake configuration option to prefer those timestamps over timestamps that store offsets with values.