Data types

Understanding Prequel data types

Because data from any source can be synced to any destination, and because data types are not necessarily synonymous across data stores, a common mapping must be used to specify the source data type and predict the destination data type. As part of Prequel configuration, the expected data type must be defined ahead of time.

Source type mapping

If you are configuring a source and need to decide what PREQUEL type to use, reference this table.

Prequel Type	Supported	Athena	BigQuery	Clickhouse	Databricks	MongoDB	MotherDuck	MySQL	Postgres	Oracle	Redshift	Snowflake	SQL Server
`bytes`	✔️	binary	bytes	blob, mediumblob, longblob	binary	binData	binary, blob	binary, varbinary, blob, mediumblob, longblob	bytea	blob	VARBYTE, VARBINARY, BINARY VARYING	binary, varbinary	binary, image, varbinary
`string`	✔️	char, varchar, string	string	string, fixedstring, text, mediumtext, longtext, varchar	string	String, ObjectId	text, varchar	string, char, text, longtext, varchar	char, character, character varying, text, uuid, varchar	nchar, char, varchar2, nvarchar2	CHAR, CHARACTER, NCHAR, BPCHAR, CHARACTER VARYING, NVARCHAR, TEXT	CHAR, CHARACTER, NCHAR, BPCHAR, CHARACTER VARYING, NVARCHAR, TEXT	char, varchar, text, nvarchar, ntext, nchar
`boolean`	✔️	boolean	bool	bool, boolean	boolean	Boolean	boolean	boolean	boolean	number	boolean	boolean	bit
`integer`	✔️	smallint, integer	N/A	int, int8, int16, int32	smallint, int	Int32	smallint, int	smallint, mediumint, int	smallint, integer	int	smallint, integer	smallint, integer	smallint, int
`bigint`	✔️	bigint	int64, int, smallint, integer, bigint	int64, bigint	bigint	Int64, Long	bigint	bigint	bigint	bigint	bigint	bigint	bigint
`decimal`	✔️	decimal	decimal, numeric	decimal, numeric	decimal, dec, numeric	Decimal128	decimal	decimal, numeric	decimal, numeric	decimal	decimal, numeric	decimal, numeric	decimal, numeric
`float`	✔️	real, float	float64	float32, float64, double	float, double	Double	float, double, float4, float8	float, double	real, double precision	float, double	real, float4, float8, double precision	real, float4, float8, double precision	float, real
`timestamp`	✔️	timestamp	timestamp	datetime, Datetime64	timestamp	Date, Timestamp	timestamp, timestamptz	timestamp	timestamp, timestamptz	timestamp(n)	timestamp, timestamptz	timestamp, timestamp_ntz, timestamp_tz, timestamp_ltz	datetime2
`date`	✔️	date	date	date	date	N/A	date	date	date	date	date	date	date
`json`	✔️	varchar, string	json	string*	string	Object	json	json	json, jsonb	nchar, char, varchar2, nvarchar2	varchar(MAX)	varchar, variant	nvarchar(MAX)
`time`	✔️	time	time	time	N/A	N/A	time	N/A	time	N/A	time	time	time
`geography`	✔️	geometry	geography	Point, Ring, Linestring, Multilinestring, Polygon, Multipolygon	N/A	geojson	geometry	geometry, point, ring, linestring, multilinestring, polygon, multipolygon	geometry, point, ring, linestring, multilinestring, polygon, multipolygon	N/A	geometry, point, ring, linestring, multilinestring, polygon, multipolygon	geography, geometry	geography, geometry
`vector`	✔️	array<double>	array<float64>	Array(Float64)	array<double>	array(float)	double[]	N/A	double precision[]	N/A	array(double precision)	array(double precision)	N/A

🚩
Scale & Precision limitations for decimal types
In order to support broad compatibility across destinations, decimal typed values must fit within the DECIMAL(38,19) scale and precision. decimal values transferred to BigQuery will be truncated to DECIMAL(38,9), as this is the maximum supported size.

🚧
Precision limitations for floating point types
Due to technical constraints, Prequel is unable to guarantee the absence of a loss of precision for any floating point (eg float) type. For use-cases in which precision is key, such as when transferring financial data, we strongly recommend leveraging fixed precision types instead such as decimal.

🌎
Geography type is limited to World Geodetic System
Every geometric shape has a spatial reference system associated with it. The geography type is limited to geospatial data that corresponds to an SRID of 4326, which represents spatial data using longitude and latitude coordinates on the Earth's surface as defined in the WGS84 standard.

* An asterisk indicates partial support. Ask us about any specific data type limitations.

Destination type mapping

If you are interested in what data type your recipient destination will receive, reference this table.

Prequel Type	Supported	Athena	BigQuery	Clickhouse	Databricks	MongoDB	MotherDuck	MySQL	Postgres	Oracle	Redshift	Snowflake	SQL Server
`bytes`	✔️	string	bytes	blob	binary	binData	blob	binary	bytea	blob	varbyte(16777216)	binary	blob
`string`	✔️	string	string	string	string	string	text	text	text	clob	varchar(MAX)	text	nvarchar(MAX)
`boolean`	✔️	boolean	bool	boolean	boolean	boolean	boolean	boolean	boolean	number	boolean	boolean	bit
`integer`	✔️	integer	int64	integer	int	int	smallint, int	int	integer	int	bigint	integer	int
`bigint`	✔️	bigint	bigint	bigint	bigint	long	bigint	bigint	bigint	bigint	bigint	bigint	bigint
`decimal`	✔️	decimal	decimal	decimal	decimal	decimal128	decimal	decimal	decimal	decimal	decimal	decimal	decimal
`float`	✔️	float	float64	double	double	double	double	double	double precision	double	double precision	double precision	float
`timestamp`	✔️	timestamp	timestamp	Datetime64	timestamp	date	timestamptz	timestamp	timestamptz	timestamp(9)	timestamptz	timestamp_tz	datetime2
`date`	✔️	date	date	date	date	string	date	date	date	date	date	date	date
`json`	✔️	string	json	string*	string	json	json	json	jsonb	clob	varchar(MAX)	variant	nvarchar(MAX)
`time`	✔️	time	time	time	time	string	time	time	time	varchar2(8)	varchar(MAX)	time	time
`geography`	✔️	geometry	geography	string (wkt)	string (geojson)	geojson	geometry	geometry	geometry	clob	geometry	geography	geography
`vector`	✔️	array<double>	array<float64>	Array(Float64)	array<double>	array(float)	double[]	json	double precision[]	clob (json)	super	array(double precision)	nvarchar(MAX) (json)

* An asterisk indicates partial or incomplete support. Ask us about any specific data type limitations.