Version: Next

Data Connectors

Data Connectors provide connections to databases, data warehouses, and data lakes for federated SQL queries and data replication.

Each connector is configured using the from field in a dataset definition. For example:

datasets:
  - from: postgres:public.orders    # Database connector
    name: orders
    params:
      pg_host: localhost
      pg_db: mydb
      pg_user: reader
      pg_pass: ${secrets:PG_PASS}

  - from: s3://my-bucket/events/    # Object storage connector
    name: events
    params:
      file_format: parquet
      s3_auth: iam_role

Supported Data Connectors include:

Name	Description	Status	Protocol/Format
`adbc`	ADBC	Stable	Arrow
`databricks (mode: delta_lake)`	Databricks	Stable	S3/Delta Lake
`databricks (mode: spark_connect)`	Databricks	Stable	Spark Connect
`databricks (mode: sql_warehouse)`	Databricks	Stable	SQL Statement Execution API
`delta_lake`	Delta Lake	Stable	Delta Lake
`dremio`	Dremio	Stable	Arrow Flight
`duckdb`	DuckDB	Stable	Embedded
`file`	File	Stable	Parquet, CSV
`github`	GitHub	Stable	GitHub API
`http`, `https`	HTTP(s) (dynamic headers, pagination)	Stable	Parquet, CSV, JSON
`localpod`	Local dataset replication	Stable
`postgres`	PostgreSQL (with native WAL CDC)	Stable
`s3`	S3	Stable	Parquet, CSV
`mysql`	MySQL (with native binlog CDC)	Stable
`spice.ai`	Spice.ai	Stable	Arrow Flight
`dynamodb`	Amazon DynamoDB (with Streams)	Stable
`iceberg`	Apache Iceberg (read+write)	Stable	Parquet
`flightsql`	FlightSQL	Stable	Arrow Flight SQL
`glue`	AWS Glue	Stable	Iceberg, Parquet, CSV
`mongodb`	MongoDB (with native Change Streams CDC)	Stable
`graphql`	GraphQL	Release Candidate	JSON
`cosmosdb`	Azure Cosmos DB (NoSQL)	Release Candidate
`git`	Git repositories	Release Candidate
`snowflake`	Snowflake	Release Candidate	Arrow
`oracle`	Oracle	Release Candidate	Oracle ODPI-C
`ducklake`	[DuckLake][ducklake]	Beta	Parquet
`mssql`	Microsoft SQL Server	Beta	Tabular Data Stream (TDS)
`odbc`	ODBC (Spice.ai Enterprise)	Beta	ODBC
`spark`	Spark	Beta	Spark Connect
`sharepoint`	Microsoft SharePoint	Beta	Object-store listing
`kafka`	Kafka	Beta	Kafka + JSON
`abfs`	Azure BlobFS	Alpha	Parquet, CSV
`clickhouse`	ClickHouse	Alpha
`debezium`	Debezium CDC	Alpha	Kafka + JSON
`elasticsearch`	Elasticsearch (BM25 + kNN + RRF) (Spice.ai Enterprise)	Alpha
`gcs`, `gs`	[Google Cloud Storage][gcs]	Alpha	Parquet, CSV, JSON
`ftp`, `sftp`	FTP/SFTP	Alpha	Parquet, CSV
`imap`	IMAP	Alpha	IMAP Emails
`scylladb`	ScyllaDB (Spice.ai Enterprise)	Alpha
`smb`	SMB 3.1.1	Alpha	SMB
`nfs`	NFS (Spice.ai Enterprise)	Alpha	Parquet, CSV, JSON

File Formats

Data connectors that read files from object stores (S3, Azure Blob, GCS) or network-attached storage (FTP, SFTP, SMB, NFS) support a variety of file formats. These connectors work with both structured data formats (Parquet, CSV) and document formats (Markdown, PDF).

Specifying File Format

When connecting to a directory, specify the file format using params.file_format:

datasets:
  - from: s3://bucket/data/sales/
    name: sales
    params:
      file_format: parquet

When connecting to a specific file, the format is inferred from the file extension:

datasets:
  - from: sftp://files.example.com/reports/quarterly.parquet
    name: quarterly_report

Supported Formats

Name	Parameter	Status	Description
Apache Parquet	`file_format: parquet`	Stable	Columnar format optimized for analytics
Apache ORC	`file_format: orc`	Stable	Columnar format. Read-only; a directory of ORC objects infers its schema by merging every object's footer.
CSV	`file_format: csv`	Stable	Comma-separated values
JSON	`file_format: json`	Stable	JavaScript Object Notation
Delta Lake	`file_format: delta`	Stable	Open table format with ACID transactions. Object stores only.
Apache Iceberg	`file_format: iceberg`	Stable	Open table format for large analytic datasets. Object stores only. Requires a catalog.
Microsoft Excel	`file_format: xlsx`	Alpha	Excel spreadsheet format (document format)
Markdown	`file_format: md`	Stable	Plain text with formatting (document format)
Text	`file_format: txt`	Stable	Plain text files (document format)
PDF	`file_format: pdf`	Beta	Portable Document Format (document format)
Microsoft Word	`file_format: docx`	Alpha	Word document format (document format)
Microsoft PowerPoint	`file_format: pptx`	Alpha	PowerPoint presentation format (document format)

Format-Specific Parameters

File formats support additional parameters for fine-grained control. Common examples include:

Parameter	Applies To	Description
`csv_has_header`	CSV	Whether the first row contains column headers
`csv_delimiter`	CSV	Field delimiter character (default: `,`)
`csv_quote`	CSV	Quote character for fields containing delimiters

For complete format options, see File Formats Reference.

Applicable Connectors

The following data connectors support file format configuration:

Connector Type	Connectors
Object Stores	S3, Azure Blob (ABFS), GCS, HTTP/HTTPS
Network-Attached Storage	FTP, SFTP, SMB, NFS
Local Storage	File

Hive Partitioning

File-based connectors support Hive-style partitioning, which extracts partition columns from folder names. Enable with hive_partitioning_enabled: true.

Given a folder structure:

/data/
  year=2024/
    month=01/
      data.parquet
    month=02/
      data.parquet

Configure the dataset:

datasets:
  - from: s3://bucket/data/
    name: partitioned_data
    params:
      file_format: parquet
      hive_partitioning_enabled: true

Query with partition filters:

SELECT * FROM partitioned_data WHERE year = '2024' AND month = '01';

Partition pruning improves query performance by reading only the relevant files.

Metadata Columns

File-based connectors can expose per-file object store metadata as virtual columns in the dataset schema. These columns are not stored in the data files — they are derived from object store file metadata at query time.

Available Columns

Column	Type	Description
`_location`	`Utf8`	Full URI of the source file
`_last_modified`	`Timestamp(µs, "UTC")`	When the file was last modified
`_size`	`UInt64`	File size in bytes

Enabling Metadata Columns

Metadata columns are enabled by adding a metadata section to the dataset definition with each desired column set to enabled:

datasets:
  - from: s3://bucket/data/
    name: my_data
    params:
      file_format: parquet
    metadata:
      _location: enabled
      _last_modified: enabled
      _size: enabled

Each column can be individually enabled or omitted:

metadata:
  _location: enabled     # Only add the _location column

note

If the data files already contain a column with the same name as a metadata column (e.g., a Parquet file with a _size column), the metadata column is not added to avoid conflicts.

Querying Metadata Columns

Once enabled, metadata columns appear alongside the regular data columns:

SELECT * FROM my_data LIMIT 3;

+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| id | value   | year | month | day | _last_modified       | _location                                                    | _size |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+
| 0  | value_0 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 1  | value_1 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
| 2  | value_2 | 2022 | 1     | 1   | 2024-10-10T05:36:59Z | s3://bucket/data/year=2022/month=1/day=1/data_0.parquet      | 2317  |
+----+---------+------+-------+-----+----------------------+--------------------------------------------------------------+-------+

Metadata columns can be used in filters, projections, aggregations, and joins like any other column:

-- Filter by file location
SELECT id, value FROM my_data
WHERE _location = 's3://bucket/data/year=2022/month=1/day=1/data_0.parquet';

-- Find recently modified files
SELECT DISTINCT _location, _last_modified FROM my_data
WHERE _last_modified > '2024-01-01T00:00:00Z';

-- Aggregate by file
SELECT _location, COUNT(*) AS row_count, _size
FROM my_data
GROUP BY _location, _size
ORDER BY _location;

File Listing Pruning with `_last_modified`

When _last_modified is enabled and a query filters on it, Spice uses the object store listing to skip files before opening them. Only files whose last-modified time satisfies the filter are read. A file that the filter excludes is never opened, so its footer is not read and a compressed file such as jsonl.gz is not decompressed. Spice still applies the filter to each row after the scan, so query results are unchanged.

This helps an append refresh that uses _last_modified as its time_column, which enables the column automatically. Each refresh filters on _last_modified > <last refresh watermark>, so a refresh with no new files reads no data files.

Pruning applies when the _last_modified conditions are combined with AND and each one compares the column with a constant timestamp using >, >=, <, <=, =, or BETWEEN. A condition that casts _last_modified to a coarser precision, such as Timestamp(Second), does not prune files, because the truncated value can match rows that the file's exact last-modified time would exclude.

Spice reads every file in the listing when a filter references _last_modified under OR or NOT, or compares it with a value that is not a constant, such as another column. The results are the same, but the query does not skip any files.

Queries That Read Only Partition or Metadata Columns

Hive partition columns and metadata columns have the same value in every row of a file. When a query reads only these columns and answers them with GROUP BY, DISTINCT, MAX, or MIN, Spice needs one row per file instead of every row. Two common queries have this shape:

-- Find the newest partition
SELECT year FROM partitioned_data GROUP BY year ORDER BY year DESC LIMIT 1;

-- Find the most recent file change
SELECT MAX(_last_modified) FROM my_data;

Spice answers these queries in one of two ways:

When every file reports an exact row count, such as Parquet, and the query reads only partition columns, Spice builds the result from the file listing and opens no data files.
Otherwise, including JSON, CSV, and compressed files, and any query that reads a metadata column, Spice reads at most the first record of each file. EXPLAIN shows first_record_probe=true on the scan.

Files with no rows are skipped in both cases, so an empty partition does not appear in the result. A filter on partition or metadata columns keeps the optimization. A filter on a data column, or an aggregate that depends on row counts, such as COUNT, SUM, or AVG, reads the files in full.

Applicable Connectors

Metadata columns are supported by all file-based connectors:

Connector Type	Connectors
Object Stores	S3, Azure Blob (ABFS), HTTP/HTTPS
Network-Attached Storage	FTP, SFTP, SMB, NFS
Local Storage	File

Schema Inference

Spice infers the schema for each dataset from its data source at startup. The inferred schema defines the column names, data types, and nullability used by the dataset for the lifetime of that runtime process.

Schema inference happens once, when the dataset is first registered. Some connectors support tuning the inference behavior with connector-specific parameters:

Connector	Parameter	Default	Description
Kafka	`schema_infer_max_records`	1	Number of messages sampled to infer the JSON schema
DynamoDB	`schema_infer_max_records`	10	Number of items sampled to infer the schema
MongoDB	`mongodb_schema_infer_max_records`	400	Number of documents sampled to infer the schema
CSV files	`csv_schema_infer_max_records`	1000	Number of rows sampled to infer the CSV schema

For connectors that read self-describing formats (Parquet, Arrow, Avro), the schema is read directly from file metadata and does not require sampling.

Runtime Schema Changes

Accelerated datasets keep the schema registered at startup. The default on_schema_change: block does not adopt a later source change. The dataset stays healthy and continues to serve queries on the registered schema. A refresh that cannot write the new source rows into that schema fails, for example:

Failed to load data for dataset <name>: Cannot cast struct field ...

That failure is intentional. Incompatible source changes stay blocked until an explicit policy accepts them. Plan the rollout, then set one of:

Policy	What it accepts
`block` (default)	Nothing. The registered schema stays in force.
`fail`	Nothing. The dataset reports an error status with an actionable message while the source schema diverges, and recovers if the source reverts.
`append_new_columns`	New nullable columns. Type and nullability changes stay on `block`.
`sync_all_columns`	Lossless widening changes: new nullable columns, widened types, relaxed nullability. Removals and narrowing stay blocked.
`drop_and_recreate`	Widening changes in place, and a destructive rebuild for incompatible changes. The rebuild runs only with `refresh_mode: full`.

Restarting the runtime re-infers the schema from the source. acceleration.mode: file_update recreates the acceleration file when an incompatible change is found. Federated queries against a dataset that is not accelerated always see the live source schema; on_schema_change does not apply to them.

Recommendation

Pin a known-good schema version in the data source or use the columns configuration to explicitly define the expected columns. This makes schema expectations explicit and produces clear errors if the source drifts.

Name	Parameter	Supported	Is Document Format
Apache Parquet	`file_format: parquet`	✅	❌
Apache ORC	`file_format: orc`	✅	❌
CSV	`file_format: csv`	✅	❌
Delta Lake	`file_format: delta`	✅	❌
Apache Iceberg	`file_format: iceberg`	✅	❌
JSON	`file_format: json`	✅	❌
Microsoft Excel	`file_format: xlsx`	Alpha	✅
Markdown	`file_format: md`	✅	✅
Text	`file_format: txt`	✅	✅
PDF	`file_format: pdf`	Beta	✅
Microsoft Word	`file_format: docx`	Alpha	✅
Microsoft PowerPoint	`file_format: pptx`	Alpha	✅

Document Formats

Document formats (Markdown, Text, PDF, Word, Excel, PowerPoint) are handled differently from structured data formats. Each file becomes a row in the resulting table, with the file contents stored in a content column.

Note

Document formats in Alpha (DOCX, XLSX, PPTX) may not parse all structure or text from the underlying documents correctly.

Document Table Schema

Column	Type	Description
`location`	String	Path to the source file
`content`	String	Full text content of the document

Example

Consider a local filesystem:

>>> ls -la
total 232
drwxr-sr-x@ 22 jeadie  staff    704 30 Jul 13:12 .
drwxr-sr-x@ 18 jeadie  staff    576 30 Jul 13:12 ..
-rw-r--r--@  1 jeadie  staff   1329 15 Jan  2024 DR-000-Template.md
-rw-r--r--@  1 jeadie  staff   4966 11 Aug  2023 DR-001-Dremio-Architecture.md
-rw-r--r--@  1 jeadie  staff   2307 28 Jul  2023 DR-002-Data-Completeness.md

And the spicepod

datasets:
  - name: my_documents
    from: file:docs/decisions/
    params:
      file_format: md

A Document table will be created.

>>> SELECT * FROM my_documents LIMIT 3
+----------------------------------------------------+--------------------------------------------------+
| location                                           | content                                          |
+----------------------------------------------------+--------------------------------------------------+
| Users/docs/decisions/DR-000-Template.md            | # DR-000: DR Template                            |
|                                                    | **Date:** <>                                     |
|                                                    | **Decision Makers:**                             |
|                                                    | - @<>                                            |
|                                                    | - @<>                                            |
|                                                    | ...                                              |
| Users/docs/decisions/DR-001-Dremio-Architecture.md | # DR-001: Add "Cached" Dremio Dataset            |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | We use [Dremio](https://www.dremio.com/) to p... |
| Users/docs/decisions/DR-002-Data-Completeness.md   | # DR-002: Append-Only Data Completeness          |
|                                                    |                                                  |
|                                                    | ## Context                                       |
|                                                    |                                                  |
|                                                    | Our Ethereum append-only dataset is incomple...  |
+----------------------------------------------------+--------------------------------------------------+

Identifier Case Sensitivity and Quoting

Spice follows PostgreSQL conventions for identifier handling: unquoted identifiers are normalized to lowercase. This applies to both the from field in dataset definitions and the name field used for SQL queries.

Quoting in the `from` field

To reference a table or schema with mixed-case or uppercase characters in the from field, wrap each case-sensitive part in double quotes:

datasets:
  # Without quoting — "ActionExecutions" is lowercased to "actionexecutions"
  - from: postgres:my_schema.ActionExecutions
    name: action_executions

  # With quoting — case is preserved for the table name
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

  # Quote each part individually as needed
  - from: postgres:"MySchema"."ActionExecutions"
    name: action_executions

Each dotted part of the identifier is treated independently — quote only the parts that require case preservation. For example, postgres:my_schema."ActionExecutions" preserves the case of ActionExecutions while my_schema is normalized to lowercase.

This applies to all federated database connectors where the from field references a table identifier (e.g. postgres, mysql, snowflake, databricks, clickhouse, mssql, duckdb, dremio, flightsql, spark, mongodb, oracle, adbc). Connectors that interpret from as a file path (e.g. s3, delta_lake, ftp, abfs) do not apply identifier normalization.

Quoting in the `name` field

The name field controls the table name used in Spice SQL queries and follows the same lowercase normalization. To preserve case in the dataset name, wrap the value in double quotes. In YAML, use single quotes around the double-quoted value:

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: '"ActionExecutions"'

-- Query using the preserved-case name
SELECT * FROM "ActionExecutions";

If you don't need to preserve case in queries, a lowercase name works without quoting:

datasets:
  - from: postgres:my_schema."ActionExecutions"
    name: action_executions

SELECT * FROM action_executions;

Dataset name quoting works regardless of connector type. See the datasets name reference for more details.

File Formats​

Specifying File Format​

Supported Formats​

Format-Specific Parameters​

Applicable Connectors​

Hive Partitioning​

Metadata Columns​

Available Columns​

Enabling Metadata Columns​

Querying Metadata Columns​

File Listing Pruning with _last_modified​

Queries That Read Only Partition or Metadata Columns​

Applicable Connectors​

Schema Inference​

Runtime Schema Changes​

Document Formats​

Document Table Schema​

Example​

Identifier Case Sensitivity and Quoting​

Quoting in the from field​

Quoting in the name field​

Data Connector Docs​

📄️Redshift Data Connector

📄️Azure BlobFS Data Connector

📄️ADBC Data Connector

📄️ClickHouse Data Connector

🗃Azure Cosmos DB Data Connector

🗃Databricks Data Connector

📄️Debezium Data Connector

🗃Delta Lake Data Connector

🗃Dremio Data Connector

🗃DuckDB Data Connector

📄️DuckLake Data Connector

🗃DynamoDB Data Connector

🗃Elasticsearch Data Connector

🗃File Data Connector

📄️Flight SQL Data Connector

📄️FTP/SFTP Data Connector

📄️GCS Data Connector

🗃GitHub Data Connector

📄️Glue Data Connector

🗃GraphQL Data Connector

🗃HTTP(s) Data Connector

📄️Iceberg Data Connector

📄️IMAP Data Connector

📄️Kafka Data Connector

📄️Localpod Data Connector

📄️Memory Data Connector

📄️MongoDB Data Connector

🗃Microsoft SQL Server

🗃MySQL Data Connector

📄️NFS Data Connector

📄️ODBC Data Connector

📄️Oracle Data Connector

🗃PostgreSQL Data Connector

🗃S3 Data Connector

🗃ScyllaDB Data Connector

📄️SharePoint Data Connector

📄️SMB Data Connector

📄️Snowflake Data Connector

📄️Apache Spark Connector

🗃Spice.ai Data Connector

File Formats

Specifying File Format

Supported Formats

Format-Specific Parameters

Applicable Connectors

Hive Partitioning

Metadata Columns

Available Columns

Enabling Metadata Columns

Querying Metadata Columns

File Listing Pruning with `_last_modified`

Queries That Read Only Partition or Metadata Columns

Applicable Connectors

Schema Inference

Runtime Schema Changes

Document Formats

Document Table Schema

Example

Identifier Case Sensitivity and Quoting

Quoting in the `from` field

Quoting in the `name` field

Data Connector Docs