dbt-gizmosql

A dbt adapter for GizmoSQL

dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.

dbt is the T in ELT. Organize, cleanse, denormalize, filter, rename, and pre-aggregate the raw data in your warehouse so that it's ready for analysis.

GizmoSQL

GizmoSQL is an Apache Arrow Flight-based SQL engine for data warehouses. It is designed to be fast, scalable, and easy to use.

It has DuckDB and SQLite back-ends. You can see more information about GizmoSQL here.

Features

This adapter provides feature parity with dbt-duckdb for all features applicable to a remote Flight SQL connection:

Materializations

• Table and View (SQL and Python)
• Incremental with four strategies:
  • append -- simple record additions
  • delete+insert -- key-based upserts with DuckDB's DELETE...USING syntax
  • merge -- uses DuckDB's MERGE with UPDATE BY NAME / INSERT BY NAME
  • microbatch -- time-based batch processing via event_time windows
• Snapshot (check and timestamp modes) using UPDATE+INSERT pattern
• Schema change handling: ignore, append_new_columns, sync_all_columns, fail

Python Models

Python models execute client-side using a local DuckDB instance for full API compatibility, then ship results to GizmoSQL via ADBC bulk ingest:

def model(dbt, session):
    dbt.config(materialized="table")
    df = dbt.ref("upstream_model")
    df = df.filter(df.amount > 100)
    return df

• Supports DuckDB relations, pandas DataFrames, PyArrow Tables, and PyArrow record batches as return types (see below)
• dbt.ref() and dbt.source() fetch data from GizmoSQL as Arrow and expose it as DuckDB relations
• Incremental Python models supported (with proper dbt.is_incremental handling)

Streaming record batches

In addition to full pyarrow.Tables, a Python model can return Arrow record batches:

• a single pyarrow.RecordBatch
• a pyarrow.RecordBatchReader
• a list or generator of pyarrow.RecordBatches
• any object implementing the Arrow PyCapsule stream protocol (__arrow_c_stream__, e.g. a polars DataFrame)

Readers, generators, and stream-protocol objects are streamed to GizmoSQL through ADBC bulk ingest batch by batch, so the full result never has to be collected into a single table in client memory:

import pyarrow as pa

def model(dbt, session):
    dbt.config(materialized="table")
    schema = pa.schema(fields=[("id", pa.int64()), ("name", pa.string())])

    def batches():
        for start in (0, 1000, 2000):
            ids = list(range(start, start + 1000))
            yield pa.record_batch(
                data={"id": ids, "name": [f"n{i}" for i in ids]}, schema=schema
            )

    return pa.RecordBatchReader.from_batches(schema=schema, batches=batches())

dbt requires model() itself to return exactly once, so put any yields in an inner function and return its generator (returning batches() directly works too). A stream with no rows still creates the (empty) table, but an empty generator or list has no schema to create it from — return an empty RecordBatchReader or Table with an explicit schema instead.

Streaming from an external source (ADBC)

A returned reader is streamed after model() returns, so whatever produces it must still be open then. A reader from an ADBC cursor.fetch_record_batch() is closed as soon as its cursor or connection closes — including when a with block exits — so don't return it from inside a with block (the model fails with "returned a record batch stream that was already closed"). Instead, yield it: dbt-gizmosql streams the reader to GizmoSQL, then resumes the generator so the with block closes the connection. The same works with any ADBC driver (e.g. Db2 via adbc-driver-db2):

import os

from adbc_driver_gizmosql import dbapi as gizmosql


def extract(query):
    with gizmosql.connect(
        uri="gizmosql://source-host:31337",
        username=os.environ["SOURCE_USERNAME"],
        password=os.environ["SOURCE_PASSWORD"],
    ) as conn, conn.cursor() as cursor:
        cursor.execute(operation=query)
        yield cursor.fetch_record_batch()  # `yield`, not `return`


def model(dbt, session):
    dbt.config(materialized="table")
    return extract(query="select * from sales.orders")

This works for incremental models too, and an empty result (e.g. an incremental run with no new rows) still works — the reader's schema creates the (empty) table. If you'd rather collect the whole result in memory first, return cursor.fetch_arrow_table() instead.

Server-side pushdown with session.remote_sql()

Because Python models run client-side, dbt.ref('big_table').filter(...) pulls the entire upstream table over the network before filtering locally. When you only need a small slice of a large server-side table, use session.remote_sql(query) to push the query down to the GizmoSQL server — the filter/aggregation runs server-side and only the result crosses the wire:

def model(dbt, session):
    dbt.config(materialized="table")
    schema = dbt.this.schema
    # Runs on the GizmoSQL server; only matching rows come back.
    return session.remote_sql(
        f"select * from {schema}.big_table where name = 'Joe'"
    )

remote_sql() returns a chainable local DuckDB relation, so you can combine it with the usual .filter(), .project(), .df(), pandas, etc. The rest of the session object behaves exactly like a local DuckDB connection (session.sql(...), session.register(...), …) — remote_sql is an additive escape hatch, not a replacement.

Seed Loading

Seeds are loaded using DuckDB's CSV reader on the client side with ADBC bulk ingest to the server:

• Correct null handling (empty CSV fields become SQL NULL, not the string 'null')
• Proper type inference (dates detected as DATE, integers as BIGINT, etc.)
• Supports column_types overrides and custom delimiters
• Significantly faster than dbt's default batch INSERT path

Constraints

All constraint types are enforced: CHECK, NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY.

Documentation

• persist_docs support (COMMENT ON for relations and columns)
• Full catalog generation with dbt docs generate

Utility Macros

DuckDB-compatible overrides for: dateadd, last_day, listagg, split_part.

Writing to External Files (server-side)

Because GizmoSQL is essentially a remote, server-side DuckDB, dbt-gizmosql supports the external materialization — models that are backed by Parquet, CSV, or JSON files rather than database tables — with the same config surface as dbt-duckdb.

The important distinction: the COPY runs on the GizmoSQL server, not on your dbt client. That's usually what you want. A production GizmoSQL deployment typically sits on a powerful cloud VM (lots of CPU, lots of RAM, fast local disks, a fat NIC, IAM role for blob storage). Pushing the write to that box is far faster than streaming a multi-GB result set back to the client just to write it out again — and the server's credentials/network topology are exactly what's needed to reach the destination.

{{ config(materialized='external', location='/data/warehouse/fact_orders.parquet') }}
select m.*, s.id is not null as has_source_id
from {{ ref('upstream_model') }} m
left join {{ source('upstream', 'source') }} s using (id)

Configuration

If location is omitted, the file is written to {external_root}/{model_name}.{format}. Set external_root in your profile to control the default write location (local path or cloud URI):

my-gizmosql-db:
  target: dev
  outputs:
    dev:
      type: gizmosql
      host: gizmosql.prod.example.com
      port: 31337
      auth_type: external
      use_encryption: True
      external_root: "s3://my-warehouse/dbt-output"

The external_root is resolved on the GizmoSQL server, so any path/URI the server's DuckDB backend can reach works — local filesystem paths, s3://..., gs://..., azure://..., etc. Any credentials needed to write there live on the server, not on your dbt client.

After the write, dbt-gizmosql creates a view over the file via read_parquet / read_csv / read_json, so downstream models can ref() the external model like any other relation.

Partitioning example

{{ config(
    materialized='external',
    format='parquet',
    options={'partition_by': 'year, month', 'compression': 'zstd'}
) }}
select * from {{ ref('fact_events') }}

Notes and limitations

• The directory referenced by external_root (or the parent directory of an explicit location) must already exist on the server — DuckDB's COPY ... TO '<file>' does not create parent directories for single-file writes. Cloud URIs like s3://bucket/prefix don't have this constraint.
• Incremental strategies are not supported on external models — each run fully replaces the file(s).
• dbt-duckdb's plugin / glue_register options are not supported: those are a client-side feature of dbt-duckdb with no analogue on the server. Setting either will produce a clear compile-time error.

Installation

Option 1 - from PyPi

# Create the virtual environment
python3 -m venv .venv

# Activate the virtual environment
. .venv/bin/activate

pip install --upgrade pip

python -m pip install dbt-core dbt-gizmosql

Option 2 - from source (for development)

git clone https://github.com/gizmodata/dbt-gizmosql

cd dbt-gizmosql

# Create the virtual environment
python3 -m venv .venv

# Activate the virtual environment
. .venv/bin/activate

# Upgrade pip, setuptools, and wheel
pip install --upgrade pip setuptools wheel

# Install the dbt GizmoSQL adapter - in editable mode with dev dependencies
pip install --editable .[dev]

Configuration

Profile setup

Add the following to your ~/.dbt/profiles.yml (change values to match your environment):

my-gizmosql-db:
  target: dev
  outputs:
    dev:
      type: gizmosql
      host: localhost
      port: 31337
      database: dbt
      user: [username]
      password: [password]
      use_encryption: True
      tls_skip_verify: True
      threads: 2

OAuth/SSO Authentication

For browser-based OAuth/SSO, use auth_type: external -- no username or password needed:

my-gizmosql-db:
  target: dev
  outputs:
    dev:
      type: gizmosql
      host: gizmosql.example.com
      port: 31337
      auth_type: external
      use_encryption: True
      threads: 2

Architecture

This adapter connects to GizmoSQL via Apache Arrow Flight SQL using the ADBC driver (adbc-driver-gizmosql). As of v1.12.0, the adapter runs on adbc-driver-gizmosql 2.0, powered by the new native Go GizmoSQL ADBC driver -- same API as before, with GizmoSQL's DDL/DML immediate-execution handling, RETURNING support, gizmosql:// URIs, and OAuth/SSO now provided by the shared Go driver library used across all languages. Key architectural decisions:

• Autocommit mode: Each statement auto-commits immediately. Flight SQL's PREPARE phase validates against committed catalog state, so explicit transactions would cause DDL from earlier statements to be invisible to later ones.
• Client-side DuckDB: Seeds and Python models use a local DuckDB instance for processing, with results shipped to the server via ADBC bulk ingest (Arrow columnar format over gRPC).
• MERGE BY NAME: Incremental merges use DuckDB's UPDATE BY NAME / INSERT BY NAME syntax, which is resilient to column ordering differences.

Versioning

This adapter follows semantic versioning. The major.minor version tracks dbt-core (e.g., dbt-core 1.11.x -> dbt-gizmosql 1.11.x).

Reporting bugs and contributing code

• Want to report a bug or request a feature? Open an issue
• Want to contribute? Pull requests are welcome

Code of Conduct

Everyone interacting in the dbt project's codebases, issue trackers, chat rooms, and mailing lists is expected to follow the dbt Code of Conduct.