Concepts
Types
SQLLoad's portable column types, how each maps to Snowflake, Databricks, BigQuery, ClickHouse, Postgres, DuckDB and Parquet, and how source types are read.
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
Section titled “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.
³ See Arrays.
⁴ VARIANT with json_type = "variant" on the target. See VARIANT columns.
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: 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 too; neither can any source’s
constant_columns.
Arrays
Section titled “Arrays”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. 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[]ornumeric(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 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. A value that isn’t a list or a JSON array fails. 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, and
so do those in the lists of Python dicts. In Arrow lists that load as json, and in the
Arrow batches of a table that mixes dicts and Arrow data, they load as NULL elements; a dict’s list
that loads as json fails the load instead, as JSON can’t hold them (see
NaN and infinities). Into Snowflake, NaN and infinities
in a pinned Parquet list fail the load, and so do those in a pinned Arrow list, and
those from dicts. Partition columns, path and constant columns, and Postgres change capture don’t take
arrays.
Names SQLLoad accepts
Section titled “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
Section titled “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; 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
Section titled “Python and files”Python values are typed as described in the Python API; NaN and infinite floats in dicts keep their values, as Postgres and Parquet floats do (see NaN and infinities). Parquet columns keep their Arrow types. CSV and JSON columns are inferred from a sample of records; pin any you want typed differently.
Loading every column as text
Section titled “Loading every column as text”infer_types = false on a source or table loads every column SQLLoad would infer as
text. Use it for codes that are numeric in some rows and not in others, such as 1099999500115812
and 16ZN102RSC, or that must keep their leading zeros. Columns pinned in columns keep their
types:
[sources.products]kind = "files"path = "drop/products/"format = "csv"infer_types = false
[sources.products.tables.products]columns = { price = "decimal(18,2)", updated = "date" }Each value is the text a column pinned text gets: a CSV field exactly as written, the text a
Google Sheets cell shows, a JSON number as written in the file, and a Python dict value as its text
(true, 1.5, a string as it is). Objects and lists stay json, so flatten and unnest work
as usual, and the child tables unnest makes infer their columns. Files may disagree on a
column’s type, since every one loads as text.
It applies to CSV, JSON, NDJSON, Excel and XML files, Google Sheets, and the dict rows of Python
tables and transforms. Parquet files, Arrow tables and DataFrames, and database sources already
carry declared types and keep them. plan says it on the table’s schema line:
├── schema every column text except columns pinned, objects and lists (infer_types = false)Turning it on for a table that exists changes its inferred columns to text, a type change that
plan shows column by column and that runs as
type_change_rebuild allows. Values
already loaded become their text in the warehouse, so 123 stays 123; later loads keep a
field’s leading zeros.
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. Pin a type that holds every value, such as a wider
scale or text.
Parsing text into times
Section titled “Parsing text into times”A table’s parse reads text columns as times, on every source: files, Google Sheets,
databases, Python tables and sl.http jobs. Each column lists the formats its values may be in.
Each value takes the first format that reads it.
[sources.orders]kind = "files"connection = "lake"path = "drop/orders/"format = "csv"description = "Orders, one file a month."write_strategy = "append"timezone = "Europe/Paris"
[sources.orders.tables.orders]parse = { "Updated At" = { timestamp = ["iso", "%m/%d/%Y %H:%M:%S", "%m/%d/%Y %H:%M", "%m/%d/%Y"] }, due = { date = "%d.%m.%Y", on_error = "null" } }plan shows each parsed column, its formats in the order they’re tried, and the zone that times
without an offset are read in:
Loads (1) sqlload load└── raw.orders append ├── source files ./drop/orders/ csv, compression by file name ├── route read csv into Arrow -> DuckDB takes Arrow ├── columns 3, 1 named by snake_case: Updated At -> updated_at ├── parse due date from "%d.%m.%Y"; a value no format reads loads NULL │ updated_at timestamp_tz from iso, "%m/%d/%Y %H:%M:%S", "%m/%d/%Y %H:%M", "%m/%d/%Y"; times without an offset in Europe/Paris (the source's timezone) …These rows load as the UTC instants below. Paris moved its clocks from 02:00 to 03:00 on 29 March 2026:
Updated At in the file |
updated_at |
|---|---|
2026-03-29T01:30:00 |
2026-03-29 00:30:00+00 |
03/29/2026 03:30:00 |
2026-03-29 01:30:00+00 |
03/29/2026 12:00 |
2026-03-29 10:00:00+00 |
03/30/2026 |
2026-03-29 22:00:00+00 |
2026-03-29T09:00:00Z |
2026-03-29 09:00:00+00 |
Each entry takes one of these:
| Key | Meaning |
|---|---|
timestamp |
A format, or a list of formats tried in turn. The column loads as timestamp_tz, stored as UTC. |
date |
A format, or a list of formats tried in turn. The column loads as date. |
tz |
With timestamp: the IANA zone, such as "Europe/Paris", of times without an offset. |
on_error |
"fail" (default) or "null": what a value that no format reads does. |
The whole entry may also be "epoch_s", "epoch_ms" or "epoch_us", such as
parse = { created = "epoch_ms" }, for a number or numeric text counting from 1970-01-01 UTC.
A format is one of these:
"iso"reads ISO 8601 text: a date, then optionally aTor a space andHH:MM,HH:MM:SSorHH:MM:SS.ffffff, then optionallyZor an offset such as+01:00,+0100or+01. A date alone is midnight. Underdate,"iso"readsYYYY-MM-DDonly.- A strftime format, such as
"%m/%d/%Y %H:%M". A fraction of a second is%.f, as in"%H:%M:%S%.f". Python’s%fis refused, and so is%s: write"epoch_s"for seconds since the epoch. A format with an offset uses%z, or%#zto takeZtoo. "epoch_s","epoch_ms"or"epoch_us", in atimestamplist.
A time without an offset is read in the column’s tz, else in the table’s or source’s timezone,
else in UTC. plan names which one applies: its tz, the table's timezone,
the source's timezone, or the default, as neither tz nor timezone is set. A value with an
offset keeps its instant, whatever tz says. A date-only format gives midnight in the zone.
Where the clocks change:
- A time the clocks skip, such as 02:30 in Paris on 29 March 2026, doesn’t convert. It fails, or
loads NULL with
on_error = "null". - A time that happens twice, when the clocks go back, takes the earlier instant.
A value that no format reads fails the load, naming the row, the column and the value. In a files source, the row is named by its file and its row there:
failed raw.orders 2026-03.csv: ./drop/orders/2026-03.csv row 3: column due holds "soon", which is not a date in the format "%d.%m.%Y"; fix the value, or set on_error = "null" in its parse to load NULLparse keys name a column by its loaded name or by its name in the source, as columns keys do.
It runs after transform, flatten, naming and rename,
on the columns as they load. plan fails on a key that names no column, on a column that is also
pinned in columns, on a format it can’t read, and on a zone that isn’t an IANA zone:
Errors (3)├── raw.orders│ └── reason parse "updated" is not a column of the source table (names are case-sensitive); did you mean "updated_at"?├── raw.orders│ └── reason parse due: format "%d.%m.%Q" has a directive that is not a strftime one such as %Y, %m, %d, %H, %M, %S or %z└── raw.orders └── reason timezone: "Europe/Pari" is not an IANA time zone such as "Europe/Paris" or "America/New_York"parse reads text, and numbers with an epoch format. A column the source already types as a date
or a time without a zone, such as a Python datetime, fails the load; drop its parse. Text with
an offset that a Python table or an sl.http job yields arrives as timestamp_tz already, and
keeps its instant. Number formats such as 1,234.5 belong in a
transform.
A Python table takes the same entries, as @source.table(parse={...}, timezone="Europe/Paris").
An sl.http job’s sl.field(parse=...) takes them for one field.
A files table with parse is read by SQLLoad, never by the destination itself.