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.
This page follows MotherDuck's documentation. The SQL is the same SQL we run in DuckDB against our own sample data.
Bucket destinations
-
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 gcswith an HMAC key; for S3-compatible storage addENDPOINT, as in DuckDB. -
Create a database and the
recordsview:CREATE DATABASE IF NOT EXISTS s2b;USE s2b;CREATE OR REPLACE VIEW records ASSELECT * 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'}); -
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.