Connect Sidemantic to databases using ADBC (Arrow Database Connectivity) for high-performance Arrow-native data transfer

ADBC (Arrow Database Connectivity) provides a unified database interface that leverages Apache Arrow's columnar format for efficient data transfer. Sidemantic's ADBC adapter lets you connect to any database with an ADBC driver, including databases that don't have native Sidemantic adapters.

Why Use ADBC?

ADBC offers several advantages over traditional database connectors:

• Arrow-native: Data is transferred in Arrow format, eliminating row-to-column conversion overhead
• Unified interface: One connection method works across many databases
• High performance: Columnar data transfer is especially efficient for analytical workloads
• Driver ecosystem: Leverage the growing ADBC driver ecosystem

Supported Databases

Any database with an ADBC driver works with Sidemantic. Common drivers include:

Driver	Database	Install
postgresql	PostgreSQL	dbc install postgresql
snowflake	Snowflake	dbc install snowflake
bigquery	Google BigQuery	dbc install bigquery
duckdb	DuckDB	dbc install duckdb
mysql	MySQL	dbc install mysql
sqlite	SQLite	dbc install sqlite
trino	Trino	dbc install trino
mssql	SQL Server	dbc install mssql
redshift	Amazon Redshift	dbc install redshift
flightsql	Flight SQL	dbc install flightsql

Installation

Install the ADBC driver manager and your database driver:

# Install Sidemantic with ADBC support
uv add 'sidemantic[adbc]'

# Install DBC CLI (recommended for driver management)
uv tool install dbc
# or on macOS:
brew install columnar-tech/tap/dbc

# Then install drivers
dbc install postgresql
dbc install snowflake

# Or install drivers as Python packages
uv add adbc-driver-postgresql
uv add adbc-driver-snowflake

See dbc documentation for more installation options.

Connection Formats

Sidemantic provides two ways to configure ADBC connections: URL strings and YAML dict format.

URL String Format

Sidemantic uses the adbc:// URL scheme for ADBC connections:

# Basic driver (uses defaults)
connection: adbc://sqlite

# With database URI
connection: adbc://postgresql/postgresql://localhost/mydb

# With URI as query parameter
connection: adbc://postgresql?uri=postgresql://localhost/mydb

# With connection parameters
connection: adbc://snowflake?account=myaccount&database=mydb&warehouse=mywh

CLI example:

sidemantic query "SELECT revenue FROM orders" \
  --models ./models \
  --connection "adbc://postgresql?uri=postgresql://localhost/analytics"

YAML Dict Format

For complex configurations (like key-pair authentication), use the expanded YAML dict format:

# semantic_layer.yml
connection:
  type: adbc
  driver: postgresql
  uri: postgresql://localhost/mydb

models:
  - name: orders
    table: orders
    # ...

Database-Specific Configuration

PostgreSQL

# URL format
connection: adbc://postgresql?uri=postgresql://user:pass@localhost:5432/mydb

# Dict format
connection:
  type: adbc
  driver: postgresql
  uri: postgresql://user:pass@localhost:5432/mydb

Snowflake

Snowflake ADBC supports multiple authentication methods. The adbc.snowflake.sql.auth_type option controls which method is used:

Auth Type	Description
auth_snowflake	Username/password (default)
auth_jwt	JWT key-pair authentication
auth_ext_browser	SSO via browser
auth_oauth	OAuth token
auth_okta	Native Okta SSO

Key-pair authentication example:

connection:
  type: adbc
  driver: snowflake
  adbc.snowflake.sql.account: ORG-ACCOUNT
  adbc.snowflake.sql.db: MY_DATABASE
  adbc.snowflake.sql.warehouse: COMPUTE_WH
  adbc.snowflake.sql.auth_type: auth_jwt
  adbc.snowflake.sql.client_option.jwt_private_key: /path/to/snowflake_key.p8
  username: my_service_user

Common options:

• adbc.snowflake.sql.account - Account identifier (required)
• adbc.snowflake.sql.db - Default database
• adbc.snowflake.sql.schema - Default schema
• adbc.snowflake.sql.warehouse - Default warehouse
• adbc.snowflake.sql.role - Role for authentication

See the Snowflake ADBC driver documentation for all available options.

SQLite

# URL format (in-memory)
connection: adbc://sqlite

# URL format (file)
connection: adbc://sqlite/:memory:
connection: adbc://sqlite/path/to/database.db

# Dict format
connection:
  type: adbc
  driver: sqlite
  uri: /path/to/database.db

DuckDB

# URL format
connection: adbc://duckdb/:memory:
connection: adbc://duckdb/path/to/database.duckdb

# Dict format
connection:
  type: adbc
  driver: duckdb
  uri: /path/to/database.duckdb

Other Databases

For MySQL, Trino, BigQuery, Redshift, and other ADBC drivers, consult the driver-specific documentation for connection options:

• ADBC Driver Documentation

The general pattern is:

connection:
  type: adbc
  driver: <driver_name>
  uri: <connection_uri>  # If the driver supports URI connections
  # Or driver-specific options:
  # adbc.<driver>.option_name: value

Python API

Use ADBC connections directly in Python:

from sidemantic import SemanticLayer

# URL format
layer = SemanticLayer(connection="adbc://postgresql?uri=postgresql://localhost/mydb")

# Or use the adapter directly
from sidemantic.db.adbc import ADBCAdapter

adapter = ADBCAdapter(
    driver="postgresql",
    uri="postgresql://localhost/mydb"
)

# Execute queries
result = adapter.execute("SELECT * FROM orders LIMIT 10")
rows = result.fetchall()

# Or fetch as Arrow for maximum performance
arrow_reader = result.fetch_record_batch()

Explicit Connection Parameters

For databases that don't use URIs:

from sidemantic.db.adbc import ADBCAdapter

adapter = ADBCAdapter(
    driver="snowflake",
    db_kwargs={
        "adbc.snowflake.sql.account": "ORG-ACCOUNT",
        "adbc.snowflake.sql.warehouse": "COMPUTE_WH",
        "adbc.snowflake.sql.db": "MY_DATABASE",
        "username": "my_user",
        "password": "my_password",
    }
)

Arrow Data Access

ADBC's main advantage is native Arrow support. Sidemantic's ADBC adapter wraps the standard ADBC cursor methods for Arrow access:

from sidemantic import SemanticLayer

layer = SemanticLayer(connection="adbc://postgresql?uri=postgresql://localhost/mydb")

# Query returns an ADBCResult (wrapper around ADBC cursor)
result = layer.query(
    metrics=["orders.revenue"],
    dimensions=["orders.status"]
)

# Option 1: Fetch as Arrow RecordBatch
arrow_reader = result.fetch_record_batch()

# Option 2: Fetch as rows (standard DB-API style)
rows = result.fetchall()

# Convert Arrow to polars
import polars as pl
df = pl.from_arrow(arrow_reader.read_all())

Note: Each result can only be consumed once. Call either fetch_record_batch() or fetchall(), not both.

Troubleshooting

Driver Not Found

ImportError: adbc_driver_manager is required for ADBC support

Install the ADBC driver manager:

uv add 'sidemantic[adbc]'
# or
uv add adbc-driver-manager

Missing Database Driver

Error: Driver 'postgresql' not found

Install the driver via DBC CLI or as a Python package:

# Via DBC CLI
dbc install postgresql

# Or as Python package
uv add adbc-driver-postgresql

Connection Failed

If you see connection errors:

1. Verify the driver is installed: dbc list or check Python packages
2. Test the connection URI with native tools first (e.g., psql for PostgreSQL)
3. Check authentication credentials and network access
4. For Snowflake key-pair auth, verify the key file path and permissions

When to Use ADBC vs Native Adapters

Use ADBC when:

• You need databases without native Sidemantic adapters
• You want unified driver management across databases
• You need Arrow-native data transfer
• You're using key-pair or other advanced authentication

Use native adapters when:

• You want the simplest setup (DuckDB, PostgreSQL)
• You need database-specific optimizations
• You prefer connection string URLs without extra dependencies

Related Documentation

• Database Connections - Native database adapters
• Configuration - YAML configuration options
• Python API - Complete API reference