motherduck

export motherduck_token="$(cat ~/.secrets/motherduck_token)"   # never commit this value

import os
import duckdb

# Without a token in the environment, the first connect opens a browser to log in
conn = duckdb.connect("md:")          # workspace mode: attaches all your databases

conn.sql("CREATE DATABASE IF NOT EXISTS analytics")
conn.sql("USE analytics")             # cloud databases persist across machines

# Single mode (one database, nothing persisted in the workspace):
conn = duckdb.connect("md:analytics")

# Explicit token in config (same value as the environment variable):
conn = duckdb.connect("md:analytics", config={"motherduck_token": os.environ["motherduck_token"]})

# Query that combines local files with cloud tables
import duckdb

conn = duckdb.connect("md:analytics")

# Upload a local Parquet file to MotherDuck
conn.sql("""
  CREATE OR REPLACE TABLE events AS
  SELECT * FROM read_parquet('local_data/events_2026_q3.parquet')
""")

# Query cloud data alongside local files (MotherDuck's "dual execution" decides
# which side runs each part of the plan)
result = conn.sql("""
  SELECT
    e.event_type,
    COUNT(*) AS event_count,
    AVG(e.duration_ms) AS avg_duration,
    u.plan AS user_plan
  FROM analytics.events e                              -- Cloud table
  JOIN read_parquet('local_data/users.parquet') u      -- Local file
    ON e.user_id = u.id
  WHERE e.created_at >= '2026-07-01'
  GROUP BY 1, 4
  ORDER BY 2 DESC
""").df()

print(result)

conn = duckdb.connect("md:analytics")

# CSV from a URL or local path
conn.sql("""
  CREATE OR REPLACE TABLE sales AS
  SELECT * FROM read_csv('https://datasets.northwindlogistics.com/sales_2026.csv', header=true)
""")

# Parquet on S3 needs credentials. Store them once as a MotherDuck secret:
import os
conn.sql(f"""
  CREATE OR REPLACE SECRET northwind_s3 IN MOTHERDUCK (
    TYPE S3, KEY_ID '{os.environ["AWS_ACCESS_KEY_ID"]}',
    SECRET '{os.environ["AWS_SECRET_ACCESS_KEY"]}', REGION 'eu-west-1'
  )
""")
conn.sql("""
  CREATE OR REPLACE TABLE events AS
  SELECT * FROM read_parquet('s3://northwind-events/events/*.parquet', hive_partitioning=true)
""")

# JSON
conn.sql("CREATE OR REPLACE TABLE logs AS SELECT * FROM read_json_auto('logs/*.json')")

# Incremental load: append only rows that are not there yet
conn.sql("""
  INSERT INTO events
  SELECT * FROM read_parquet('s3://northwind-events/events/2026-10/*.parquet') n
  WHERE NOT EXISTS (SELECT 1 FROM events e WHERE e.event_id = n.event_id)
""")

conn = duckdb.connect("md:")

conn.sql("""
  CREATE OR REPLACE TABLE team_analytics.monthly_metrics AS
  SELECT date_trunc('month', created_at) AS month,
         COUNT(DISTINCT user_id) AS mau, SUM(amount) AS total_revenue
  FROM analytics.events GROUP BY 1 ORDER BY 1
""")   # team_analytics must already exist (CREATE DATABASE team_analytics)

# Restricted share, visible to the org, readable by every preset role
share_url = conn.sql("""
  CREATE SHARE team_analytics_share FROM team_analytics
    (ACCESS RESTRICTED, VISIBILITY DISCOVERABLE, UPDATE AUTOMATIC)
""").fetchone()
conn.sql("GRANT READ ON SHARE team_analytics_share TO ROLE explorer")

conn = duckdb.connect("md:")
conn.sql("ATTACH 'md:_share/team_analytics/0a9a026ec5a55946a9de39851087ed81' AS team_analytics")  # the URL CREATE SHARE returned
conn.sql("SELECT * FROM team_analytics.monthly_metrics").show()

// src/analytics/motherduck.ts
import { DuckDBInstance } from "@duckdb/node-api";

const instance = await DuckDBInstance.fromCache("md:analytics", {
  motherduck_token: process.env.motherduck_token!,
});

export async function getMonthlyRevenue(months = 12) {
  const connection = await instance.connect();
  const reader = await connection.runAndReadAll(
    `SELECT date_trunc('month', created_at) AS month,
            SUM(amount) AS revenue,
            COUNT(DISTINCT user_id) AS paying_users
     FROM orders
     WHERE created_at >= current_date - to_months($1::INTEGER)
       AND status = 'completed'
     GROUP BY 1 ORDER BY 1`,
    [months],
  );
  return reader.getRowObjectsJson();
}

# scripts/daily_transform.py — Run as a cron job to update materialized tables
import duckdb

conn = duckdb.connect("md:analytics")

# Daily aggregation — rebuild summary tables
conn.sql("""
  CREATE OR REPLACE TABLE daily_kpis AS
  SELECT
    DATE(created_at) AS date,
    COUNT(DISTINCT user_id) AS dau,
    COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) AS signups,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS purchasers,
    SUM(CASE WHEN event_type = 'purchase' THEN amount ELSE 0 END) AS revenue,
    COUNT(*) AS total_events
  FROM events
  WHERE created_at >= current_date - interval '90 days'
  GROUP BY 1
  ORDER BY 1
""")

# Cohort analysis table
conn.sql("""
  CREATE OR REPLACE TABLE cohort_retention AS
  WITH first_seen AS (
    SELECT user_id, MIN(DATE(created_at)) AS cohort_date
    FROM events
    GROUP BY 1
  )
  SELECT
    f.cohort_date,
    DATE_DIFF('week', f.cohort_date, DATE(e.created_at)) AS week_number,
    COUNT(DISTINCT e.user_id) AS active_users
  FROM events e
  JOIN first_seen f ON e.user_id = f.user_id
  WHERE f.cohort_date >= current_date - interval '12 weeks'
  GROUP BY 1, 2
""")

print("Daily transformations complete")

# Use the DuckDB version MotherDuck supports (1.5.6 at the time of writing; see its release notes).
# The newest duckdb on PyPI (2.0.0) is not yet on that list.
pip install "duckdb==1.5.6"                 # Python 3.10+
npm install @duckdb/node-api@1.5.6-r.2     # Node.js
brew install duckdb                # DuckDB CLI (macOS); then: duckdb "md:analytics"

I have last month's orders in orders_2026_09.parquet on my laptop and the customers table is in MotherDuck. Give me revenue per customer segment.

Put the monthly metrics where finance can query them live instead of emailing a CSV.