Skip to content

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" }
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.

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[] 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 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.

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 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 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.

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.

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 a T or a space and HH:MM, HH:MM:SS or HH:MM:SS.ffffff, then optionally Z or an offset such as +01:00, +0100 or +01. A date alone is midnight. Under date, "iso" reads YYYY-MM-DD only.
  • 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 %f is refused, and so is %s: write "epoch_s" for seconds since the epoch. A format with an offset uses %z, or %#z to take Z too.
  • "epoch_s", "epoch_ms" or "epoch_us", in a timestamp list.

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 NULL

parse 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.