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.