How do you deal with incompatible data types between the source and destination systems ? For example, the source might have a timestamp with timezone data type and the destination could just support timestamp in UTC.
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.