Currently, Azure Synapse target endpoint does not allow flexibility over changing the table data type. When replicating data from Oracle number column (no precision and scale) to the target, by default it casts the data into an external table/temp table before loading it into a target decimal column.
When the target decimal column is changed to BIGINT/SMALLINT/INT, Replicate cannot load the data because it fails to cast the external table accordingly.
A customer is looking to see whether the external table creation behaviour (for polybase) to utilise the default data types rather than target table data types.
e.g. Oracle (Number) -> Replicate (External Table use Decimal(38,10) as per Replicate settings) -> Target (INT/BIGINT on INSERT)
- Replicate is set to expose NUMBER as DECIMAL(38,10)…
- External table created with DECIMAL(38,10) as per initial creation/load instead of customised target table data types
- Insert into target table (any error to insert into target table could be caught – there is no issue inserting decimal into an int column)