motherduck-query

To write DuckDB SQL for MotherDuck analytics queries, use DuckDB-native patterns like CTEs, window functions with QUALIFY, and GROUP BY ALL rather than PostgreSQL syntax. Ensure fully qualified table names and explicit result grains to maintain correctness and predictable execution.

The QUALIFY clause in DuckDB SQL filters results of window functions directly within the query. It enables window-function filtering to enforce correct result grains without wrapping the entire query in a subquery, simplifying analytics logic and improving performance.

Structure complex data exploration queries in DuckDB using CTEs to break down transformations into sequential, readable steps. This CTE-based structuring clarifies explicit result grains and applies early filtering and aggregation to avoid inefficient rescans during analytics processing.

MotherDuck does not support PostgreSQL-style SQL syntax for aggregations and transformations. It requires DuckDB SQL syntax with fully qualified table identifiers, utilizing DuckDB-native patterns like GROUP BY ALL and QUALIFY to ensure correct execution semantics and safer operations.

DDL, DML, and lifecycle or recovery commands are treated as write operations in DuckDB SQL, not safe read operations. These safety-aware writes should only be executed using MCP write-capable operations with explicit user confirmation to prevent unintended modifications.

Optimize performance tuning for DuckDB SQL aggregations by applying early filtering, explicit result grain definitions, and plan or shape checks. These performance-aware patterns prevent unintended sorts and inefficient rescans during analytics queries against MotherDuck databases.