That’s something that we spend a lot of time working on. Our general approach is to do both the simple and predictable thing (ie what format would I want to get

That’s something that we spend a lot of time working on. Our general approach is to do both the simple and predictable thing (ie what format would I want to get the data in if I were the data team receiving it). For the specific example you give, we’d convert the timestamptz into a timestamp before writing it to the destination. Another type where this comes up is JSON. Some warehouses support the type whereas others don’t. In those cases, we typically write the data to a text/varchar column instead. The way it works under the hood is we’ve effectively written our own query planner. It takes in the source database flavor (eg Snowflake) and types, the destination database flavor, and figures out the happy path for handling all the specified types.