Types

SQLLoad reasons about columns in a small set of portable types. You write them in columns, plan shows them next to each warehouse type, and one table can load into different warehouses with the same declarations.

[sources.app.tables."public.orders"]
description = "Orders, one row per order."
columns = { total = "decimal(18,2)", closes_at = "timestamp_tz" }

Portable types

Type	Snowflake	Databricks	BigQuery	ClickHouse	Postgres	DuckDB	Parquet (Arrow)
bool	BOOLEAN	BOOLEAN	BOOL	Bool	boolean	BOOLEAN	Boolean
smallint	NUMBER(38,0) ¹	SMALLINT	INT64 ¹	Int16	smallint	SMALLINT	Int16
int (64-bit)	NUMBER(38,0)	BIGINT	INT64	Int64	bigint	BIGINT	Int64
decimal(p,s)	NUMBER(p,s)	DECIMAL(p,s)	NUMERIC(p,s) ²	Decimal(p, s)	numeric(p,s)	DECIMAL(p,s)	Decimal128(p,s)
float (64-bit)	FLOAT	DOUBLE	FLOAT64	Float64	double precision	DOUBLE	Float64
text	VARCHAR	STRING	STRING	String	text	VARCHAR	Utf8
varchar(n)	VARCHAR(n)	STRING ¹	STRING(n)	String ¹	varchar(n)	VARCHAR ¹	Utf8
date	DATE	DATE	DATE	Date32 ¹	date	DATE	Date32
time	TIME(9)	STRING ¹	TIME	String ¹	time	TIME	Time64
timestamp_tz	TIMESTAMP_TZ(9)	TIMESTAMP ¹	TIMESTAMP ¹	DateTime64(6, 'UTC') ¹	timestamptz ¹	TIMESTAMP WITH TIME ZONE ¹	Timestamp(UTC)
timestamp_ntz	TIMESTAMP_NTZ(9)	TIMESTAMP_NTZ	DATETIME	DateTime64(6) ¹	timestamp	TIMESTAMP	Timestamp
json	VARIANT	VARIANT	JSON	String ¹	jsonb ¹	JSON	Utf8
uuid	VARCHAR(36) ¹	STRING ¹	STRING ¹	UUID	uuid	UUID	Utf8
binary	BINARY	BINARY	BYTES	String	bytea	BLOB	Binary
native("...")	passed through	passed through	passed through	passed through	passed through	passed through	not available
array(T) ³	ARRAY ¹	ARRAY<T>	ARRAY<T> ¹	Array(T) ¹	T[]	T[]	List(T)

¹ Held with a loss, which plan notes: Snowflake stores smallint as a 38-digit integer and uuid as text; Databricks doesn’t enforce varchar lengths, stores time and uuid as text, and keeps timestamp_tz as an instant without its offset; BigQuery stores smallint as a 64-bit integer, uuid as text, and timestamp_tz as a UTC instant without its offset; ClickHouse doesn’t enforce varchar lengths, stores time and json as text, keeps timestamp_tz as a UTC instant without its offset, shows timestamp_ntz in the server’s time zone, and holds dates and timestamps from 1900 to 2299; Postgres keeps timestamp_tz as a UTC instant without its offset, and its jsonb keeps neither key order nor duplicate keys; DuckDB doesn’t enforce varchar lengths, and stores timestamp_tz as a UTC instant without its offset.

² BIGNUMERIC(p,s) for more than 29 digits before the point or a scale over 9.

³ From 0.27.0. See Arrays.

Every ClickHouse column is Nullable, such as Nullable(Int64).

native("GEOGRAPHY") passes a warehouse-only type through unchanged. SQLLoad doesn’t widen or convert such columns. Files targets can’t hold one: from 0.29.0, plan fails on a native type pinned in columns instead of the load failing. A files source’s partition_columns, path_columns and constant_columns can’t be native for any target, and plan refuses them from 0.29.0 too; from 0.37.0, neither can any source’s constant_columns.

Arrays

From 0.27.0. array(T) is a list of values of one portable type, such as array(int) or array(decimal(10,2)). T is one of bool, smallint, int, float, decimal(p,s), text, date, timestamp_tz, timestamp_ntz or uuid. Arrays of arrays, json or other types are refused.

An array column is always one you pin in columns. Without a pin, lists still load as json. Pinning an array over a column that already holds json fails plan on every destination, as for any type the column can’t hold. From 0.29.0, that includes a Snowflake VARIANT column; recreate it with --reload.

[sources.app.tables."public.items"]
description = "Items, one row per item."
columns = { tags = "array(text)", prices = "array(decimal(10,2))" }

Each destination stores an array in its own array type:

Destination	Stored as	What it can’t hold
DuckDB, Postgres	T[]	Nothing; NULL arrays, empty arrays and NULL elements stay apart.
Databricks	ARRAY<T>	Nothing.
Parquet	List(T)	Nothing.
ClickHouse	Array(Nullable(T))	NULL arrays: a NULL array loads as an empty one.
BigQuery	ARRAY<T>	NULLs: a NULL array loads as an empty one, and a NULL element fails the load. array(timestamp_tz) is refused; use array(timestamp_ntz).
Snowflake	ARRAY	Typed elements: each one is a JSON value, so decimals, dates, timestamps and uuids are strings, such as "1.50".

Arrays come from:

• Postgres array columns, such as bigint[] or numeric(10,2)[].
• Parquet list columns, and JSON arrays in CSV and JSON files (as the JSON text of a CSV field).
• Python lists, in dicts or in Arrow data.

A value that doesn’t convert to T, such as "abc" in an array(int), fails the load rather than loading as NULL. Elements convert as a pinned column of T would, so from 0.45.0 an element that T can’t hold exactly fails the load, naming it: 1.5 in an array(int), or 1.239 in an array(decimal(10,2)), from JSON text, Python lists, Arrow lists or Parquet lists alike. Before 0.45.0, float elements of Arrow and Parquet lists pinned array(int) were cut to whole numbers (which plan noted), and extra decimal places were rounded to the scale of decimal(p,s). A value that isn’t a list or a JSON array fails. From 0.29.0, a list column of Python Arrow data (pyarrow, polars, pandas) pinned as an array converts as a Parquet list does, so NaN and infinities in an array(float) keep their values. In Python dicts, in Arrow lists that load as json, and in a table whose batches mix dicts and Arrow data, they load as NULL elements. Into Snowflake, NaN and infinities in a pinned Parquet list fail the load, and from 0.29.0 so do those in a pinned Arrow list; from dicts they load as NULL. Partition columns, path and constant columns, and Postgres change capture don’t take arrays.

Names SQLLoad accepts

Type names are parsed strictly. An unknown or malformed name fails plan with the nearest valid one.

Written	Means
integer, bigint	int
boolean	bool
string, varchar, character varying	text
double, double precision	float
numeric(p,s)	decimal(p,s)
timestamp with time zone, timestamp without time zone, datetime	timestamp_tz, timestamp_ntz, timestamp_ntz
varbinary	binary

Warehouse-specific names, such as Snowflake’s NUMBER or VARIANT or Postgres’s jsonb, are accepted only when every destination of the table is that warehouse.

Some names are rejected because they mean different things in different places:

Name	Use instead
timestamp	timestamp_tz or timestamp_ntz
timestamp_ltz	timestamp_tz (it depends on each session’s time zone)
real	float (64-bit)
tinyint	smallint
char, character	text or varchar(n) (they pad with spaces)

decimal needs a precision and a scale, such as decimal(18,2).

Postgres source types

Postgres	Portable type
boolean	bool
smallint	smallint
integer, bigint, oid	int
real, double precision	float
numeric(p,s)	decimal(p,s)
numeric without a precision	Pin a type, such as decimal(38,10) or text; plan asks for one.
text	text
varchar(n)	varchar(n)
date, time	date, time
timestamp, timestamptz	timestamp_ntz, timestamp_tz
json, jsonb	json
uuid	uuid
bytea	binary
arrays	json; from 0.27.0, array(T) when pinned
other types (enums, interval, network types, timetz, extension types)	text

Columns pinned in columns are read as the pinned type, when the source type can be read that way.

Python and files

Python values are typed as described in the Python API. Parquet columns keep their Arrow types. CSV and JSON columns are inferred from a sample of records; pin any you want typed differently.

From 0.45.0, a value that a pinned decimal(p,s) or integer type can’t hold exactly fails the read, naming the column and the value, on every source: 12.345 pinned decimal(10,2) and 1.5 pinned int fail rather than load as 12.35 and 1. Before 0.45.0, Python, Parquet, CSV, JSON and BigQuery values were rounded or cut to fit. Pin a type that holds every value, such as a wider scale or text.