Model Data in MotherDuck
Core Behavior
For multi-model work, keep transformations in reviewable SQL files using the project's existing dbt, SQLMesh, or local conventions. If none exist, use stage directories and a model_manifest.yml recording dependencies, materialization, and target database. A single-table request needs only the requested SQL or change.
Prerequisites
Use the known source schema and connection. Discover missing types, grain, and join keys before implementing; planning can use supplied schema without a live connection.
Default Posture
- Design for analytical reads, not transactional writes.
- Prefer wide denormalized tables and pre-aggregated serving tables over highly normalized OLTP-style schemas.
- Use fully qualified names and add comments to tables and columns. Preserve stable object names so Guides can reference the intended catalog objects reliably.
- Use
NOT NULL aggressively; do not assume primary keys or foreign keys are enforced.
- Reuse an existing dbt, SQLMesh, or repo-local modeling convention when one is already present; create the lightweight scaffold only when there is no established project shape.
- Separate
raw, staging, and analytics lifecycle stages when the project is non-trivial.
Workflow
- Inspect the current source tables and actual column types before designing new models.
- Choose the target lifecycle stage and grain for each modeled table. Map dependencies between models.
- Place SQL in the existing project, or use the scaffold reference for a new multi-model project.
- Author each model as a standalone SQL file. Use explicit types, nullability, comments, and fully qualified names. Decide between a table, CTAS rebuild, or view based on freshness and cost.
- Record dependencies and materializations in the project's framework or lightweight manifest, not both.
- For implementation, run the in-scope models and verify grain and row counts; MCP DDL and CTAS require
query_rw. For an answer, review, or plan, return the requested explanation or SQL without creating a project or mutating the warehouse unless requested.
References
Read only the reference sections needed for the current task.
- Read
references/MODELING_PLAYBOOK.md for schema patterns, data-type guidance, CTAS/view decisions, complex types, constraints, project scaffold conventions, and common modeling mistakes.
Related Skills
Load related skills only for missing capabilities; reuse established context.
motherduck-duckdb-sql for type syntax and function detailsmotherduck-query for executing DDL, rebuilds, and validation queriesmotherduck-explore for understanding the source schema before remodelingmotherduck-load-data for ingestion paths that feed the modeled tablesmotherduck-manage-guides for durable business definitions and join rules that do not belong in transformation code
1---2name: motherduck-model-data3description: Design or implement MotherDuck analytical schemas and transformation models, including grain, types, and materialization.4license: MIT5---6
7# Model Data in MotherDuck
8
9## Core Behavior
10
11For multi-model work, keep transformations in reviewable SQL files using the project's existing dbt, SQLMesh, or local conventions. If none exist, use stage directories and a `model_manifest.yml` recording dependencies, materialization, and target database. A single-table request needs only the requested SQL or change.
12
13## Prerequisites
14
15Use the known source schema and connection. Discover missing types, grain, and join keys before implementing; planning can use supplied schema without a live connection.
16
17## Default Posture
18
19- Design for analytical reads, not transactional writes.
20- Prefer wide denormalized tables and pre-aggregated serving tables over highly normalized OLTP-style schemas.
21- Use fully qualified names and add comments to tables and columns. Preserve stable object names so Guides can reference the intended catalog objects reliably.
22- Use `NOT NULL` aggressively; do not assume primary keys or foreign keys are enforced.
23- Reuse an existing dbt, SQLMesh, or repo-local modeling convention when one is already present; create the lightweight scaffold only when there is no established project shape.
24- Separate `raw`, `staging`, and `analytics` lifecycle stages when the project is non-trivial.
25
26## Workflow
27
281. Inspect the current source tables and actual column types before designing new models.
292. Choose the target lifecycle stage and grain for each modeled table. Map dependencies between models.
303. Place SQL in the existing project, or use the scaffold reference for a new multi-model project.
314. Author each model as a standalone SQL file. Use explicit types, nullability, comments, and fully qualified names. Decide between a table, CTAS rebuild, or view based on freshness and cost.
325. Record dependencies and materializations in the project's framework or lightweight manifest, not both.
336. For implementation, run the in-scope models and verify grain and row counts; MCP DDL and CTAS require `query_rw`. For an answer, review, or plan, return the requested explanation or SQL without creating a project or mutating the warehouse unless requested.
34
35## References
36
37Read only the reference sections needed for the current task.
38
39- Read `references/MODELING_PLAYBOOK.md` for schema patterns, data-type guidance, CTAS/view decisions, complex types, constraints, project scaffold conventions, and common modeling mistakes.
40
41## Related Skills
42
43Load related skills only for missing capabilities; reuse established context.
44
45- `motherduck-duckdb-sql` for type syntax and function details
46- `motherduck-query` for executing DDL, rebuilds, and validation queries
47- `motherduck-explore` for understanding the source schema before remodeling
48- `motherduck-load-data` for ingestion paths that feed the modeled tables
49- `motherduck-manage-guides` for durable business definitions and join rules that do not belong in transformation code
motherduckdb