Motherduck Model Data

Model Data in MotherDuck

Use this skill when creating data models, tables, designing schemas, choosing data types, defining relationships between tables, or restructuring data for analytical workloads.

Core Behavior

When a user asks questions like "build a data model", "model my data", or "create a transformation layer", the default output is a file-based project scaffold — not just SQL executed directly in the warehouse.

The project scaffold includes:

• SQL files organized by lifecycle stage (raw/, staging/, analytics/)
• A manifest (model_manifest.yml) defining the DAG: model names, dependencies, materialization strategy, and target database

This is a lightweight framework-agnostic convention for organizing SQL transformations that can be reviewed, versioned, and rerun.

Prerequisites

• MotherDuck connection established via motherduck-connect
• Existing source shape understood via motherduck-explore
• DuckDB SQL syntax available via motherduck-duckdb-sql

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.
• 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.
• Always produce SQL files — never execute transformations directly in the warehouse without first writing them to files.
• Always produce a manifest — every model must declare its dependencies so the DAG is explicit and reproducible.

Workflow

1. Inspect the current source tables and actual column types before designing new models. 2. Choose the target lifecycle stage and grain for each modeled table. Map dependencies between models. 3. Create the project directory structure with SQL files and manifest. 4. 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. 5. Fill in the manifest with model metadata: name, path, stage, materialization, database, and depends_on references. 6. Run the models against the warehouse and verify the resulting tables match expected grain and row counts. If MCP is the runner, DDL or CTAS execution uses query_rw only after explicit user approval; the default deliverable remains checked-in SQL files plus the manifest.

Expected Project Structure

<project-name>/
  models/
    raw/
      raw_<entity>.sql           -- DDL for raw landing tables
    staging/
      stg_<entity>.sql           -- Deduplicated, typed, filtered
    analytics/
      dim_<entity>.sql           -- Dimension tables
      fct_<entity>.sql           -- Fact / metric tables
  model_manifest.yml             -- DAG: names, deps, materialization

When to Skip the Scaffold

If the user explicitly asks for a single table, a quick DDL statement, or an ad-hoc exploration query, produce the SQL directly. The scaffold is the default for modeling work — multi-table, multi-stage transformations with dependencies.

Open Next

• 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

• motherduck-duckdb-sql for type syntax and function details
• motherduck-query for executing DDL, rebuilds, and validation queries
• motherduck-explore for understanding the source schema before remodeling
• motherduck-load-data for ingestion paths that feed the modeled tables

Modeling Playbook

Reference for analytical schema design, data-type selection, CTAS and view patterns, complex types, and DuckDB constraint behavior in MotherDuck.

Contents

SQL-First Modeling Posture

• Keep model definitions as explicit SQL DDL or CTAS statements, not dynamic code generation.
• Make grain, lifecycle stage, and output shape obvious in the SQL itself.
• Prefer checked-in SQL that can be reviewed, rebuilt, and rerun.
• Use comments, fully qualified names, and explicit data types so the model remains understandable outside the application code that executes it.

SQL Starter

CREATE OR REPLACE TABLE "analytics"."main"."daily_metrics" AS
SELECT
    date_trunc('day', event_timestamp) AS day,
    event_type,
    COUNT(*) AS event_count
FROM "raw"."main"."events"
GROUP BY ALL;

Schema Design Principles

• MotherDuck is an OLAP system. Design for read-heavy analytical queries.
• Prefer wide, denormalized tables over highly normalized schemas.
• Pre-aggregate where possible.
• Use descriptive snake_case names.
• Add comments to every table and column.
• Use fully qualified names in all DDL statements.

CREATE TABLE Patterns

Basic Table Creation

CREATE TABLE "my_db"."main"."events" (
    event_id VARCHAR NOT NULL,
    user_id VARCHAR NOT NULL,
    event_type VARCHAR NOT NULL,
    event_timestamp TIMESTAMP NOT NULL,
    properties JSON,
    created_at TIMESTAMP DEFAULT current_timestamp
);
COMMENT ON TABLE "my_db"."main"."events" IS 'Raw user interaction events from the web and mobile apps';
COMMENT ON COLUMN "my_db"."main"."events".event_type IS 'One of: pageview, click, purchase, signup';
COMMENT ON COLUMN "my_db"."main"."events".properties IS 'Event-specific metadata as JSON (varies by event_type)';

CREATE TABLE AS SELECT

CREATE TABLE "analytics"."main"."order_summary" AS
SELECT o.customer_id, c.customer_name, c.segment,
    COUNT(*) AS total_orders, SUM(o.amount) AS total_revenue,
    AVG(o.amount) AS avg_order_value,
    MIN(o.order_date) AS first_order_date, MAX(o.order_date) AS last_order_date
FROM "raw"."main"."orders" o
JOIN "raw"."main"."customers" c ON o.customer_id = c.customer_id
GROUP BY ALL;
COMMENT ON TABLE "analytics"."main"."order_summary" IS 'Pre-aggregated customer order metrics, refreshed daily';

CREATE OR REPLACE TABLE

CREATE OR REPLACE TABLE "analytics"."main"."daily_metrics" AS
SELECT date_trunc('day', event_timestamp) AS day, event_type,
    COUNT(*) AS event_count, COUNT(DISTINCT user_id) AS unique_users
FROM "raw"."main"."events" GROUP BY ALL;

Data Type Selection Guide

Schema Organization

Multi-Database Pattern

CREATE DATABASE IF NOT EXISTS raw;
CREATE DATABASE IF NOT EXISTS staging;
CREATE DATABASE IF NOT EXISTS analytics;

Schema Usage

Use the main schema unless you have a specific reason for multiple schemas.

CREATE TABLE "analytics"."main"."revenue_by_region" ( ... );

CREATE SCHEMA IF NOT EXISTS "analytics"."marketing";
CREATE TABLE "analytics"."marketing"."campaign_performance" ( ... );

Analytical Modeling Patterns

Pattern 1: Wide Denormalized Table

CREATE TABLE "analytics"."main"."orders_wide" AS
SELECT o.order_id, o.order_date, o.amount, o.status,
    c.customer_name, c.segment, c.region,
    p.product_name, p.category, p.unit_price
FROM "raw"."main"."orders" o
JOIN "raw"."main"."customers" c ON o.customer_id = c.customer_id
JOIN "raw"."main"."order_items" oi ON o.order_id = oi.order_id
JOIN "raw"."main"."products" p ON oi.product_id = p.product_id;
COMMENT ON TABLE "analytics"."main"."orders_wide" IS 'Denormalized order data with customer and product attributes';

Pattern 2: Star Schema

CREATE TABLE "analytics"."main"."dim_customers" (
    customer_id VARCHAR NOT NULL, customer_name VARCHAR NOT NULL,
    segment VARCHAR, region VARCHAR, created_at TIMESTAMP
);
COMMENT ON TABLE "analytics"."main"."dim_customers" IS 'Customer dimension with current attributes';

CREATE TABLE "analytics"."main"."dim_products" (
    product_id VARCHAR NOT NULL, product_name VARCHAR NOT NULL,
    category VARCHAR, subcategory VARCHAR, unit_price DECIMAL(18,2)
);
COMMENT ON TABLE "analytics"."main"."dim_products" IS 'Product catalog dimension';

CREATE TABLE "analytics"."main"."dim_dates" AS
SELECT date::DATE AS date_key, EXTRACT(YEAR FROM date) AS year,
    EXTRACT(QUARTER FROM date) AS quarter, EXTRACT(MONTH FROM date) AS month,
    dayname(date) AS day_name, dayofweek(date) IN (0, 6) AS is_weekend
FROM generate_series(DATE '2020-01-01', DATE '2030-12-31', INTERVAL 1 DAY) AS t(date);

CREATE TABLE "analytics"."main"."fact_orders" (
    order_id VARCHAR NOT NULL, customer_id VARCHAR NOT NULL,
    order_date DATE NOT NULL, product_id VARCHAR NOT NULL,
    quantity INTEGER NOT NULL, unit_price DECIMAL(18,2) NOT NULL,
    total_amount DECIMAL(18,2) NOT NULL
);
COMMENT ON TABLE "analytics"."main"."fact_orders" IS 'Order line-level fact table';

Pattern 3: Materialized Summary Tables

CREATE OR REPLACE TABLE "analytics"."main"."daily_revenue_summary" AS
SELECT date_trunc('day', order_date) AS day, region, category,
    COUNT(*) AS order_count, SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value, COUNT(DISTINCT customer_id) AS unique_customers
FROM "analytics"."main"."fact_orders" f
JOIN "analytics"."main"."dim_customers" c USING (customer_id)
JOIN "analytics"."main"."dim_products" p USING (product_id)
GROUP BY ALL;
COMMENT ON TABLE "analytics"."main"."daily_revenue_summary" IS 'Daily revenue by region and category, rebuilt nightly';

Views vs Tables

Use Views for Reusable Logic

CREATE VIEW "analytics"."main"."daily_revenue" AS
SELECT
    date_trunc('day', order_date) AS day,
    SUM(total_amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM "analytics"."main"."fact_orders"
GROUP BY ALL;

COMMENT ON VIEW "analytics"."main"."daily_revenue" IS 'Daily revenue and unique customer counts, always current';

Use Tables (CTAS) for Materialized Results

CREATE OR REPLACE TABLE "analytics"."main"."monthly_cohort_retention" AS
WITH first_purchase AS (
    SELECT customer_id, date_trunc('month', MIN(order_date)) AS cohort_month
    FROM "analytics"."main"."fact_orders" GROUP BY customer_id
)
SELECT f.cohort_month,
    date_diff('month', f.cohort_month, date_trunc('month', o.order_date)) AS months_since_first,
    COUNT(DISTINCT o.customer_id) AS active_customers
FROM first_purchase f
JOIN "analytics"."main"."fact_orders" o ON f.customer_id = o.customer_id
GROUP BY ALL;

Decision Guide

Complex Types for Semi-Structured Data

STRUCT

CREATE TABLE "my_db"."main"."customers" (
    customer_id VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    address STRUCT(street VARCHAR, city VARCHAR, state VARCHAR, zip VARCHAR, country VARCHAR),
    created_at TIMESTAMP DEFAULT current_timestamp
);

SELECT customer_id, address.city, address.state
FROM "my_db"."main"."customers" WHERE address.country = 'US';

LIST

CREATE TABLE "my_db"."main"."articles" (
    article_id VARCHAR NOT NULL, title VARCHAR NOT NULL, tags VARCHAR[], scores INTEGER[]
);

SELECT title, tags[1] AS primary_tag, list_contains(tags, 'analytics') AS is_analytics
FROM "my_db"."main"."articles";

SELECT article_id, UNNEST(tags) AS tag FROM "my_db"."main"."articles";

MAP and JSON

CREATE TABLE "my_db"."main"."feature_flags" (
    user_id VARCHAR NOT NULL, flags MAP(VARCHAR, BOOLEAN)
);
SELECT user_id, flags['dark_mode'] AS dark_mode_enabled FROM "my_db"."main"."feature_flags";

CREATE TABLE "my_db"."main"."api_responses" (
    request_id VARCHAR NOT NULL, endpoint VARCHAR NOT NULL,
    response_body JSON, received_at TIMESTAMP DEFAULT current_timestamp
);
SELECT request_id, response_body->>'$.status' AS status,
    response_body->'$.data.items' AS items
FROM "my_db"."main"."api_responses";

Complex Type Selection Guide

ALTER TABLE Patterns

ALTER TABLE "my_db"."main"."customers" ADD COLUMN loyalty_tier VARCHAR;
ALTER TABLE "my_db"."main"."orders" ADD COLUMN currency VARCHAR DEFAULT 'USD';
ALTER TABLE "my_db"."main"."customers" DROP COLUMN legacy_code;
ALTER TABLE "my_db"."main"."customers" RENAME COLUMN email TO email_address;
ALTER TABLE "my_db"."main"."customers" RENAME TO clients;

Constraints

Use NOT NULL as the primary constraint mechanism.

CREATE TABLE "my_db"."main"."users" (
    user_id VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    display_name VARCHAR,
    PRIMARY KEY (user_id)
);

Key Rules

• Design for analytics, not OLTP.
• Prefer wide tables over repeated joins for common analytical access paths.
• Always add table and column comments.
• Use fully qualified names in all DDL.
• Use VARCHAR for IDs, DECIMAL for money, TIMESTAMP for times, and BOOLEAN for flags.
• Use NOT NULL liberally.
• Use CTAS and CREATE OR REPLACE for rebuildable analytical tables.
• Separate lifecycle stages across databases.

Project Scaffold Conventions

Each SQL file contains exactly one model and follows a naming convention by stage:

• Raw: raw_<entity>.sql
• Staging: stg_<entity>.sql
• Analytics: dim_<entity>.sql or fct_<entity>.sql

Manifest Format (model_manifest.yml)

The manifest declares every model, its position in the DAG, and how it should be materialized.

project:
  name: my_analytics
  default_database: analytics

models:
  - name: raw_events
    path: models/raw/raw_events.sql
    stage: raw
    materialization: table        # table | view
    database: raw
    depends_on: []

  - name: stg_events
    path: models/staging/stg_events.sql
    stage: staging
    materialization: table
    database: staging
    depends_on: [raw_events]

  - name: dim_users
    path: models/analytics/dim_users.sql
    stage: analytics
    materialization: table
    depends_on: [stg_events]

  - name: fct_daily_activity
    path: models/analytics/fct_daily_activity.sql
    stage: analytics
    materialization: table
    depends_on: [stg_events, dim_users]

When Using Another Framework

If building with dbt, SQLMesh, or similar frameworks, the SQL files and manifest translate directly: each SQL file becomes a model, depends_on becomes {{ ref() }}, and materialization maps to the framework's materialization config.

Common Mistakes

• Over-normalizing
• Using floating point types for money
• Forgetting table and column comments
• Assuming PRIMARY KEY is enforced
• Creating too many small tables
• Using VARCHAR for timestamps
• Skipping the multi-database lifecycle pattern