Mapping

Data type mapping

The target column type SQL Flow chooses for each source type, per source and target engine, and the columns it refuses.

When Prepare target schema creates a target table, SQL Flow chooses each column's type from the source column. This page lists those choices. For a table you create yourself, use it as the guide to types that SQL Flow can write without loss.

How the type is chosen

SQL Flow uses one of two kinds of mapping, depending on the source and target engines:

Route	Mapping	Behavior
SQL Server or Azure SQL → PostgreSQL	Exact	Keeps lengths, precision and scale. Refuses a column it can't map without loss, by name, before any table is created.
SQL Server or Azure SQL → SQL Server, Azure SQL, SQL database in Fabric	Exact	Keeps the native type, sizes and collation.
Oracle → PostgreSQL	Exact	Keeps precision, scale and character lengths.
Oracle → SQL Server, Azure SQL, SQL database in Fabric	Exact	Keeps precision, scale and lengths where T-SQL can hold them. Values T-SQL has no exact type for, such as `NaN` and intervals, are kept as exact text.
Oracle → Oracle	Native	Keeps the source's own type definitions.
Every other route	General	Chooses a type from the kind of value (integer, decimal, text, …). Never refuses a column, but can lose lengths, decimal places or time zone offsets.

The general mapping applies to MySQL, PostgreSQL and Sybase ASE sources, and to MySQL, Sybase ASE and Oracle targets (except from Oracle).

An Azure SQL source uses the same exact mappings as a SQL Server source. Wherever this page says SQL Server source, it covers Azure SQL too.

Key columns are always NOT NULL, and the source key becomes the target primary key. Other columns keep their nullability. Defaults and identity properties aren't copied, except where noted below.

SQL Server source

SQL Server type	PostgreSQL	SQL Server, Azure SQL, SQL database in Fabric
`tinyint`	`smallint`	`tinyint`
`smallint`, `int`, `bigint`	`smallint`, `integer`, `bigint`	Same
`decimal(p,s)`, `numeric(p,s)`	`numeric(p,s)`	Same
`money`, `smallmoney`	`numeric(19,4)`, `numeric(10,4)`	Same
`real`, `float`	`real`, `double precision`	`real`, `float(53)`
`bit`	`boolean`	`bit`
`char(n)`, `nchar(n)`	`character(n)`	Same, with the source collation
`varchar(n)`, `nvarchar(n)`	`character varying(n)`	Same, with the source collation
`varchar(max)`, `nvarchar(max)`, `text`, `ntext`	`text`	Same, with the source collation
`binary(n)`, `varbinary(n)`, `varbinary(max)`, `image`	`bytea`	Same
`date`	`date`	`date`
`time(p)`	`time(p) without time zone`	`time(p)`
`smalldatetime`	`timestamp(0) without time zone`	`smalldatetime`
`datetime`	`timestamp(3) without time zone`	`datetime`
`datetime2(p)`	`timestamp(p) without time zone`	`datetime2(p)`
`datetimeoffset(p)`	`timestamp(p) with time zone`; the instant is kept, the original offset isn't	`datetimeoffset(p)`
`uniqueidentifier`	`uuid`	`uniqueidentifier`
`xml` (untyped)	`xml`	`xml`
`json`	`json`	`json`
`vector(n)`	`text`, as a JSON array	`vector(n)`
`hierarchyid`	`text`, as the path such as `/1/2/`	`hierarchyid`
`rowversion`, `timestamp`	`bytea`	`binary(8)`
`geography`, `geometry`	Refused	Same
`sql_variant`	Refused	Same, on SQL Server and Azure SQL targets from a DirectLog or Reconstruction source, outside the key. See `sql_variant`.

On PostgreSQL, a precision of 7 on time, datetime2 or datetimeoffset becomes 6, because PostgreSQL stores microseconds. Values are rounded half away from zero. A key column with precision 7 is refused, because two keys less than a microsecond apart would collide. The SQL Server-to-PostgreSQL mapping covers PostgreSQL 14 through 18.

On SQL Server targets, a character column always gets its source collation. A source collation that the target server doesn't have is refused; UTF-8 collations need SQL Server 2019 or later. A non-persisted computed column is created as a computed column with the source definition. Sparse columns stay sparse, and a column set is declared again beside them as xml COLUMN_SET FOR ALL_SPARSE_COLUMNS. On PostgreSQL, sparse columns become ordinary columns and no column set is created.

A json or vector column needs a SQL Server family target that has that data type, and a float16 vector on SQL Server also needs the PREVIEW_FEATURES database scoped configuration turned on in the target database. Otherwise Prepare target schema refuses the column.

Typed xml columns, with an XML schema collection, are refused. CLR user-defined types are refused on SQL Server, Azure SQL, SQL database in Fabric and PostgreSQL targets.

Key columns. A rowversion or timestamp key column is refused on PostgreSQL, SQL Server, Azure SQL and SQL database in Fabric targets. On SQL Server, Azure SQL and SQL database in Fabric, a key column also can't be hierarchyid, sql_variant, xml, json, vector, geography, geometry or a MAX type. On PostgreSQL, a hierarchyid key works, as its path text.

sql_variant columns

Only DirectLog and Reconstruction capture each sql_variant value with its own base type, so only they replicate the column, and only into SQL Server and Azure SQL targets. The Prepare step refuses the column by name:

from a Polling or Native CDC source;
on SQL database in Fabric and PostgreSQL targets;
as a key column, as a sparse, computed or alias-typed column, or in a table with Always Encrypted columns, whatever the capture mode.

A DirectLog or Reconstruction pipeline whose tables have a sql_variant column needs ordered apply: untick Collapse row versions (faster). Every target of that pipeline must be a SQL Server or Azure SQL target.

Oracle source

Oracle type	PostgreSQL	SQL Server, Azure SQL, SQL database in Fabric
`NUMBER(p,s)`	`numeric(p,s)`; a negative scale, or a scale above `p`, needs PostgreSQL 15 or later	`decimal(p,s)`. A negative scale widens the precision, so `NUMBER(5,-2)` becomes `decimal(7,0)`, and a scale above `p` becomes `decimal(s,s)`. A column that needs more than 38 digits becomes `varchar(172)`, holding the exact number as text.
`NUMBER` without precision or scale	`numeric`	`varchar(172)`, holding the exact number as text
`NUMBER(*,s)`, with a scale but no precision	Refused	`varchar(172)`, holding the exact number as text
`FLOAT(b)`	Refused	`varchar(172)`, holding the exact number as text
`BINARY_FLOAT`, `BINARY_DOUBLE`	`real`, `double precision`	`varchar(15)`, `varchar(24)`, as text that keeps `NaN`, `Infinity` and `-Infinity`
`CHAR(n CHAR)`, `VARCHAR2(n CHAR)`	`character(n)`, `character varying(n)`	`nvarchar(2n)`, or `nvarchar(max)` when `2n` is above 4,000
`CHAR`, `VARCHAR2` with byte length semantics	`text`	`nvarchar(max)`; `nvarchar(n)` in a key, refused when `n` is above 4,000 bytes
`NCHAR(n)`, `NVARCHAR2(n)`	`character(n)`, `character varying(n)`	`nchar(n)`, `nvarchar(n)`; `nvarchar(max)` above 4,000
`CLOB`, `NCLOB`, `LONG`	`text`	`nvarchar(max)`
`RAW(n)`	`bytea`	`varbinary(n)`, or `varbinary(max)` above 8,000
`BLOB`, `LONG RAW`	`bytea`	`varbinary(max)`
`DATE`	`timestamp(0) without time zone`	`datetime2(0)`
`TIMESTAMP(p)`	`timestamp(p) without time zone`; `p` above 6 is refused	`datetime2(p)`; `p` above 7 is refused
`TIMESTAMP(p) WITH TIME ZONE`	`timestamp(p) with time zone`; the time zone isn't kept; `p` above 6 is refused	`datetimeoffset(p)`; a region such as `Europe/Paris` is kept as its offset; `p` above 7 is refused
`TIMESTAMP WITH LOCAL TIME ZONE`	Refused	Refused
`INTERVAL YEAR TO MONTH`	`interval year to month`	`varchar(13)`, as text such as `+000000001-02`
`INTERVAL DAY TO SECOND`	`interval day to second`; more than 6 fractional digits are refused	`varchar(29)`, as text such as `+000000001 02:03:04.123456000`
`BOOLEAN`	`boolean`	`bit`
`ROWID`, `UROWID`	`text`; refused in a key	`varchar(4000)`; refused in a key
`XMLTYPE` (stored as CLOB)	`xml`	`xml`
`JSON`	Refused	Refused
`VECTOR`	`text`, as a JSON array	`varchar(max)`
`VARRAY`	`text`, as a JSON array; refused in a key	`nvarchar(max)`, as a JSON array; refused in a key

On a target other than Oracle, an object-type column, including SDO_GEOMETRY, becomes one column per attribute, named <column>_<attribute>, and each attribute is mapped by its own type.

An Oracle → Oracle table keeps the source's own type definitions, including object types and VARRAY columns. On PostgreSQL, Oracle column defaults that are literals, SYSTIMESTAMP, TRUNC(SYSDATE) or a sequence's NEXTVAL are kept; other defaults, DEFAULT ON NULL, identity columns and virtual columns are refused.

General mapping

On the routes without an exact mapping, the type follows the kind of value the source column holds:

Value kind	PostgreSQL	SQL Server family	MySQL	Oracle	Sybase ASE
Boolean	`boolean`	`bit`	`tinyint(1)`	`number(1)`	`tinyint`
8-bit integer	`smallint`	`tinyint`	`tinyint unsigned`	`number(3)`	`tinyint`
16-bit integer	`smallint`	`smallint`	`smallint`	`number(5)`	`smallint`
32-bit integer	`integer`	`int`	`int`	`number(10)`	`int`
64-bit integer	`bigint`	`bigint`	`bigint`	`number(19)`	`bigint`
Unsigned 16-bit, 32-bit, 64-bit integer	`integer`, `bigint`, `numeric(20)`	`int`, `bigint`, `decimal(20)`	`smallint unsigned`, `int unsigned`, `bigint unsigned`	`number(5)`, `number(10)`, `number(20)`	`smallint`, `int`, `decimal(20)`
Single-precision float	`real`	`real`	`float`	`binary_float`	`real`
Double-precision float	`double precision`	`float`	`double`	`binary_double`	`float`
Decimal	`numeric`	`decimal(p,s)` from the source, else `decimal(38,18)`	`decimal(38,10)`	`number`	`decimal(38,18)`
GUID	`uuid`	`uniqueidentifier`	`char(36)`	`raw(16)`	`varchar(36)`
Date and time	`timestamp`	`datetime2`	`datetime(6)`	`timestamp`	`datetime`
Date and time with offset	`timestamptz`	`datetimeoffset`	`datetime(6)`, offset lost	`timestamp with time zone`	`datetime`, offset lost
Time of day or interval	`interval`	`time`	`time(6)`	`interval day(9) to second(6)`	`time`
Binary	`bytea`	`varbinary(max)`; `varbinary(900)` in a key	`longblob`; `varbinary(255)` in a key	`blob`; `raw(2000)` in a key	`image`; `varbinary(255)` in a key
Text and anything else	`text`	`nvarchar(max)`; `nvarchar(450)` in a key	`longtext`; `varchar(255)` in a key	`clob`; `varchar2(4000)` in a key	`varchar(1024)`; `varchar(255)` in a key

Things to watch on these routes:

Text lengths aren't kept. A MySQL varchar(50) becomes text on PostgreSQL and longtext on MySQL.
MySQL targets keep 10 decimal places and 28 digits before the point, and Sybase ASE targets 18 and 20. A value with more digits before the point, such as a large numeric(38,0) value, doesn't fit.
MySQL and Sybase ASE targets lose a time zone offset.
A time span that is negative or a day or longer, such as a MySQL TIME of 100:00:00 or a PostgreSQL interval of 2 days, doesn't fit the time column of a SQL Server family or Sybase ASE target.
A MySQL TIMESTAMP arrives as UTC, so its target column holds UTC time.
Sybase ASE smallint and int can't hold the upper half of MySQL's unsigned smallint and int ranges.
From an Oracle source, NUMBER without a precision, FLOAT, BINARY_FLOAT, BINARY_DOUBLE and interval columns arrive as exact text, so MySQL and Sybase ASE targets get a text column for them.

A Sybase ASE source column takes these rows:

unsigned smallint and unsigned int take the next larger signed integer row, and unsigned bigint the unsigned 64-bit row.
date, datetime, smalldatetime and bigdatetime take the date and time row, so a date column becomes datetime2 on SQL Server and timestamp on PostgreSQL. time and bigtime take the time row.
numeric and decimal keep their precision and scale on SQL Server family targets, where money is decimal(19,4) and smallmoney is decimal(10,4).
timestamp, binary, varbinary and image take the binary row. Character types, including unichar, univarchar, sysname and longsysname, take the text row.

On a MySQL, Oracle or Sybase ASE target, you can create the table yourself with the exact types you need before the pipeline starts, because these targets don't compare the types of an existing table. See Existing target tables.

Event and file targets

Kafka and Google Pub/Sub carry values in JSON: numbers as JSON numbers, binary values as base64 strings, dates and times as ISO 8601 strings, and GUIDs as strings. A SQL Server CLR user-defined type arrives as a base64 string of its bytes. From an Oracle source, NUMBER without a precision, FLOAT, BINARY_FLOAT, BINARY_DOUBLE and interval values are strings that hold the exact value, such as NaN or +000000001-02. See Kafka and Google Pub/Sub.

Fabric Open Mirroring writes Parquet:

Source type	Parquet type
`bit`	`BOOLEAN`
`tinyint`	`INT32`, annotated as an unsigned 8-bit integer
`smallint`, `int`	`INT32`
`bigint`	`INT64`
`real`, `float`	`FLOAT`, `DOUBLE`
`decimal`, `numeric`, `money`	`DECIMAL(38,18)`
`date`, `datetime`, `smalldatetime`, `datetime2`	`INT96` timestamp
`datetimeoffset`	`INT96` timestamp in UTC; the offset isn't kept
`time`	`INT64` time in microseconds
`uniqueidentifier`	`FIXED_LEN_BYTE_ARRAY(16)` UUID
Text types, `xml`, `json`, `hierarchyid`, `sql_variant`	`BYTE_ARRAY` string
Binary types, `rowversion`, `geography`, `geometry`	`BYTE_ARRAY`

A decimal that DECIMAL(38,18) can't hold exactly stops the pipeline rather than being rounded. See Fabric Open Mirroring.

When a column is refused

On an exact route, the Prepare step checks every selected column before any table is created. A column that can't be mapped without loss is refused by name, and nothing is created.

From a SQL Server or Azure SQL source into a SQL Server, Azure SQL or SQL database in Fabric target, the step lists every refused column with its table, type, reason and what to do:

Target schema cannot be provisioned: the <target> target cannot hold <n> selected columns exactly, and SqlFlow does not declare another type, which would change the values.
Table '<table>', column '<column>' (<type>): <reason> To continue: <what to do>

What to do depends on the column:

A key column: deselect the table on the Tables step. A key column can't be left out.
A sql_variant column from SQL Server Polling or Native CDC: capture the source with DirectLog or Reconstruction, or deselect the table.
Any other column: leave it out (Configure on the table, then Choose output columns) where the source's capture mode offers output columns, or deselect the table.

On the other exact routes, the message names the column and the reason:

SQL Server column '<column>' (<type>) is not losslessly mappable to PostgreSQL: <reason>

Oracle column '<column>' (<type>) is not losslessly mappable to SQL Server: <reason>

Oracle column '<column>' (<type>) is not losslessly mappable to PostgreSQL: <reason>

To replicate the table anyway, leave the column out with output columns, cast it to a supported type there, or replicate the table to a target that supports the type.

Existing target tables

Prepare target schema never changes the columns of a table that already exists, and never adds missing columns. It only adds the primary key or unique index that apply needs. Its checks of an existing table differ by target:

PostgreSQL: each column's type must equal the mapped type.
SQL Server, Azure SQL, SQL database in Fabric: types are compared for SQL Server, Azure SQL and Oracle sources, and collations for SQL Server and Azure SQL sources.
MySQL, Oracle, Sybase ASE: columns and keys must be present; types aren't compared.

The Prepare step lists an existing table whose columns differ from the source separately, as a table that Prepare target schema can't fix, and says why. Change the table back to match the source, drop it so Prepare target schema can create it again, or deselect it on the Tables step, then select Check again.

If this was useful,
our newsletter will be too.

Get monthly insights on data engineering, AI, and building critical infrastructure - direct from the Spectral Core team and CEO Damir Bulic.