Motherduck Query

Query MotherDuck

Use this skill when executing SQL queries for analytics, aggregations, transformations, or data exploration against MotherDuck databases.

Prerequisites

• An established MotherDuck connection (or an active MotherDuck MCP server)
• Target database and tables identified

Default Posture

• Write DuckDB SQL, not PostgreSQL SQL, even when using the PG endpoint.
• Always use fully qualified "database"."schema"."table" names.
• Preserve the intended grain of every result set; state the grain before optimizing or materializing a query.
• Filter early, aggregate early, and prefer serving tables or summaries for repeated reads.
• Keep SQL obvious, multi-line, and explicit about grain, filters, and output shape.
• Treat DDL, DML, ATTACH, DETACH, recovery commands such as CREATE SNAPSHOT, ALTER DATABASE ... SET SNAPSHOT, UNDROP DATABASE, and lifecycle commands such as SHUTDOWN as writes. Use the MotherDuck MCP query_rw tool only when the user explicitly asks for the change and confirms it.
• Tag long-lived integrations with custom_user_agent when the connection path supports it.

Workflow

1. Confirm the actual tables, columns, and grain before writing SQL. 2. Write the query in SQL first, then wrap it in Python or TypeScript only if needed. 3. Use CTEs and DuckDB-native patterns such as GROUP BY ALL, QUALIFY, and arg_max. 4. Check the plan, row count, and shape for pushdown, unnecessary sorts, or repeated raw rescans. 5. Materialize expensive repeated queries into serving tables or light views when warranted.

Open Next

• Read references/QUERY_PLAYBOOK.md for DuckDB query patterns, exploration SQL, performance rules, common analytical shapes, and common mistakes

Related Skills

• motherduck-connect for session setup
• motherduck-duckdb-sql for syntax and function reference
• motherduck-explore for understanding the source schema before writing queries

Query Playbook

Reference for writing DuckDB SQL against MotherDuck, choosing the right query patterns, and avoiding common analytical-query mistakes.

Contents

SQL-First Posture

• Keep the query logic in SQL rather than pushing grouping, filtering, and reshaping into the caller.
• Write multi-line SQL with explicit aliases, explicit grain, and explicit fully qualified table names.
• Leave value binding to the caller, but keep the SQL itself obvious and production-readable.
• Return pre-aggregated results when the workload is a repeated dashboard, app-serving endpoint, or shared analytical surface.

Compute and Storage Posture

• Filter early and aggregate early.
• Prefer curated tables, views, or pre-aggregated summary tables for repeated dashboards and app-serving queries.
• Use LIMIT or aggregates during exploration.
• Tag long-lived integrations with custom_user_agent so query history can attribute cost and workload shape later.
• When validating multi-database patterns in the native DuckDB API, use a workspace connection (md:) and fully qualified names.

SQL Starter

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM "analytics"."main"."orders"
WHERE order_date >= DATE '2025-01-01'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 20;

Always Use Fully Qualified Table Names

SELECT * FROM "my_db"."main"."orders" LIMIT 10;

Use double quotes for identifiers and single quotes for string literals.

Query Structure Best Practices

Use CTEs Over Subqueries

WITH completed_orders AS (
    SELECT customer_id, amount
    FROM "analytics"."main"."orders"
    WHERE status = 'completed'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM completed_orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 1000;

Pre-Aggregate for Repeated Reads

Creating or replacing tables changes state. When the runner is MCP, use query_rw only after the user explicitly asks for the table change and confirms what will be modified.

CREATE OR REPLACE TABLE "analytics"."main"."daily_revenue" AS
SELECT
    order_date,
    region,
    SUM(amount) AS total_amount
FROM "analytics"."main"."orders"
GROUP BY ALL;

Use arg_max / arg_min for Most-Recent Queries

SELECT
    customer_id,
    max(order_date) AS latest_order_date,
    arg_max(amount, order_date) AS latest_amount
FROM "analytics"."main"."orders"
GROUP BY customer_id;

Patterns to Avoid

• correlated subqueries
• cartesian joins
• unnecessary ORDER BY in intermediate CTEs
• SELECT * in production queries
• raw-table rescans for app-serving endpoints

Duckling Lifecycle Commands

Use lifecycle commands only for operational control after the user has explicitly asked to stop a Duckling or optimize batch/CI cost.

SHUTDOWN;
SHUTDOWN TERMINATE (REASON 'batch complete');

• SHUTDOWN registers a graceful shutdown and lets running work complete.
• SHUTDOWN TERMINATE interrupts running queries and should be reserved for stuck or explicitly force-stopped Ducklings.
• Both are subject to the minimum billing period documented for Duckling compute.
• In MCP, lifecycle commands require query_rw and explicit user confirmation.

Recovery Commands

Use database recovery commands only when the user explicitly wants to preserve, clone, restore, or recover database state. Snapshot retention and point-in-time recovery support are plan-specific, so verify the current data-recovery docs before promising a window.

CREATE SNAPSHOT release_cutover OF analytics;

CREATE DATABASE analytics_restore FROM analytics (
    SNAPSHOT_NAME 'release_cutover'
);

ALTER DATABASE analytics SET SNAPSHOT TO (
    SNAPSHOT_NAME 'release_cutover'
);

UNDROP DATABASE analytics;

In MCP, these are write operations and require query_rw plus explicit confirmation.

DuckDB SQL Patterns

FROM-First Queries

FROM "my_db"."main"."users" WHERE active = true LIMIT 10;

GROUP BY ALL

SELECT category, region, SUM(sales) AS total_sales
FROM "my_db"."main"."transactions"
GROUP BY ALL;

QUALIFY

SELECT customer_id, order_date, amount
FROM "analytics"."main"."orders"
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

SELECT category, product_name, revenue
FROM "analytics"."main"."products"
QUALIFY RANK() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3;

EXCLUDE and REPLACE

SELECT * EXCLUDE (internal_id, debug_flag) FROM "my_db"."main"."events";
SELECT * REPLACE (UPPER(name) AS name) FROM "my_db"."main"."customers";
SELECT * EXCLUDE (raw_payload) REPLACE (LOWER(email) AS email) FROM "my_db"."main"."users";

Column Alias Reuse

SELECT price * quantity AS total
FROM "my_db"."main"."line_items"
WHERE total > 100;

PIVOT

PIVOT "analytics"."main"."sales"
ON quarter
USING SUM(revenue)
GROUP BY region;

UNPIVOT

UNPIVOT "analytics"."main"."quarterly_report"
ON Q1, Q2, Q3, Q4
INTO NAME quarter VALUE revenue;

UNION BY NAME

SELECT * FROM "db1"."main"."events_2023"
UNION BY NAME
SELECT * FROM "db1"."main"."events_2024";

List Comprehensions

SELECT [x * 2 FOR x IN scores] AS doubled_scores
FROM "my_db"."main"."students";

Function Chaining

SELECT name.upper().replace(' ', '_') AS clean_name
FROM "my_db"."main"."customers";

Schema Exploration Queries

SELECT alias AS database_name, type
FROM MD_ALL_DATABASES();

SELECT database_name, schema_name, table_name, comment
FROM duckdb_tables()
WHERE database_name = 'my_db';

SELECT column_name, data_type, is_nullable, comment
FROM duckdb_columns()
WHERE database_name = 'my_db'
  AND table_name = 'orders';

SUMMARIZE "my_db"."main"."orders";

Performance Optimization

• Filter early in CTEs, not at the end.
• Prefer aggregate alternatives when a window function is not required.
• Avoid SELECT * in production.
• Use EXPLAIN to understand plans.
• Avoid functions on the left side of WHERE when pushdown matters.

EXPLAIN

EXPLAIN SELECT customer_id, SUM(amount)
FROM "analytics"."main"."orders"
GROUP BY customer_id;

Predicate Pushdown Example

WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

Common Query Patterns

Top N Per Group

SELECT category, product_name, revenue
FROM "analytics"."main"."products"
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 5;

Deduplication

SELECT *
FROM "analytics"."main"."raw_events"
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) = 1;

Running Totals

SELECT order_date, daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue
FROM (
    SELECT order_date, SUM(amount) AS daily_revenue
    FROM "analytics"."main"."orders"
    GROUP BY order_date
);

Year-over-Year Comparison

WITH monthly AS (
    SELECT EXTRACT(YEAR FROM order_date) AS yr,
           EXTRACT(MONTH FROM order_date) AS mo,
           SUM(amount) AS revenue
    FROM "analytics"."main"."orders"
    WHERE order_date >= '2023-01-01'
    GROUP BY ALL
)
SELECT curr.mo AS month, curr.revenue AS revenue_2024,
       prev.revenue AS revenue_2023,
       ROUND(100.0 * (curr.revenue - prev.revenue) / prev.revenue, 1) AS yoy_pct
FROM monthly curr
JOIN monthly prev ON curr.mo = prev.mo
WHERE curr.yr = 2024 AND prev.yr = 2023
ORDER BY curr.mo;

Conditional Aggregation with FILTER

SELECT
    customer_id,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    COUNT(*) FILTER (WHERE status = 'returned') AS returned_orders,
    SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM "analytics"."main"."orders"
GROUP BY customer_id;

Key Rules

• Use DuckDB SQL syntax, never PostgreSQL SQL.
• Always use fully qualified table names.
• Use CTEs for readability and DuckDB-friendly planning.
• Use QUALIFY to filter window-function results.
• Use GROUP BY ALL to avoid duplicated grouping lists.
• Use arg_max and arg_min for latest/first-value patterns where applicable.
• Use FILTER for conditional aggregation.

Common Mistakes

• Using PostgreSQL-specific syntax
• Forgetting fully qualified table names
• Using WHERE to filter window functions instead of QUALIFY
• Over-using intermediate ORDER BY
• Applying functions on the filtered column side of WHERE
• Installing extensions at runtime