Skip to main content

MotherDuck

MotherDuck runs DuckDB in the cloud. Views you create there run the same SQL as DuckDB, are shared with your team, and are reachable from BI tools and notebooks that support MotherDuck, such as Hex.

note

This page follows MotherDuck's documentation. The SQL is the same SQL we run in DuckDB against our own sample data.

Bucket destinations​

  1. Store a read-only storage key in MotherDuck, so queries running in MotherDuck can read your bucket:

    CREATE SECRET s2b_bucket IN MOTHERDUCK (
    TYPE s3, KEY_ID '<access_key_id>', SECRET '<secret_access_key>', REGION 'us-east-1');

    For Google Cloud Storage use TYPE gcs with an HMAC key; for S3-compatible storage add ENDPOINT, as in DuckDB.

  2. Create a database and the records view:

    CREATE DATABASE IF NOT EXISTS s2b;
    USE s2b;

    CREATE OR REPLACE VIEW records AS
    SELECT * FROM read_json(
    's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
    format = 'newline_delimited', hive_partitioning = true,
    columns = {v: 'INTEGER', delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR',
    received_at: 'TIMESTAMPTZ', signal: 'VARCHAR',
    resource: 'JSON', scope: 'JSON', data: 'JSON'});
  3. Run the example queries. The view reads your bucket on every query, so it includes the latest deliveries.

To make repeated dashboards faster, materialize a day at a time into a MotherDuck table:

CREATE TABLE IF NOT EXISTS events AS
SELECT received_at, delivery_batch_id, data FROM records WHERE false;

INSERT INTO events
SELECT received_at, delivery_batch_id, data
FROM records
WHERE signal = 'event' AND date = current_date - 1
AND delivery_batch_id NOT IN (SELECT DISTINCT delivery_batch_id FROM events);

Table destinations​

For Iceberg tables, create the views in a local DuckDB session that attaches your catalog as in DuckDB, or copy the table into MotherDuck with CREATE TABLE … AS SELECT from that session. Check MotherDuck's documentation for the Iceberg and DuckLake features its cloud service supports today.

Share with a team​

Use read-only shares (CREATE SHARE) to give other MotherDuck users the s2b database. They query the views without holding storage credentials.