motherduck-query

Write DuckDB SQL, not PostgreSQL SQL, and always use fully qualified database.schema.table names in double quotes. Structure queries with CTEs, filter and aggregate early, and use DuckDB features like GROUP BY ALL and QUALIFY for cleaner logic.

Use QUALIFY with a window function such as ROW_NUMBER() or RANK() partitioned by the group column and ordered by the ranking metric. For example, QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 5 returns the top 5 per category.

No, MotherDuck runs DuckDB SQL even when connecting through the PostgreSQL endpoint. PostgreSQL-specific syntax will fail or behave differently, so use DuckDB functions and patterns like arg_max, FILTER, and list comprehensions instead.

Use the QUALIFY clause instead of WHERE, since WHERE cannot reference window function results. QUALIFY evaluates after window functions, making it the correct tool for deduplication and top-N queries.

Common causes include missing early filters, SELECT * in production queries, functions applied to filtered columns blocking pushdown, and repeated raw-table rescans. Use EXPLAIN to inspect the plan and materialize repeated queries into pre-aggregated serving tables.

Use SHUTDOWN only for explicit operational control, such as stopping a Duckling after batch or CI work completes. SHUTDOWN TERMINATE interrupts running queries and both commands require query_rw access with explicit user confirmation.