motherduckdb/agent-skills / plugins/motherduck-skills-claude/skills/motherduck-model-data
motherduck-model-data
VerifiedDesign or implement MotherDuck analytical schemas and transformation models, including grain, types, and materialization.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 3.1 KB |
| references/MODELING_PLAYBOOK.md | markdown | 13.3 KB |
references/MODELING_PLAYBOOK.md
View on GitHub# Modeling Playbook
Reference for analytical schema design, data-type selection, CTAS and view patterns, complex types, and DuckDB constraint behavior in MotherDuck.
## Contents
| Section | Covers |
| --- | --- |
| SQL-First Modeling Posture | Explicit, reviewable SQL as the model definition |
| Schema Design Principles | OLAP-first defaults: wide tables, comments, qualified names |
| `CREATE TABLE` Patterns | DDL, CTAS, `CREATE OR REPLACE`, comments |
| Data Type Selection Guide | Recommended types per use case |
| Schema Organization | Multi-database lifecycle pattern, schema usage |
| Analytical Modeling Patterns | Wide denormalized, star schema, materialized summaries |
| Views vs Tables | Decision guide for views vs CTAS materialization |
| Complex Types for Semi-Structured Data | STRUCT, LIST, MAP, JSON selection |
| `ALTER TABLE` Patterns | Column add/drop/rename |
| Constraints | What MotherDuck enforces (`NOT NULL` only) |
| Key Rules | Modeling defaults in one list |
| Project Scaffold Conventions | File naming, `model_manifest.yml` format, framework mapping |
| Common Mistakes | Frequent modeling errors |
## 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
```sql
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
```sql
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`
```sql
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`
```sql
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
| Use Case | Recommended Type | Avoid | Why |
|---|---|---|---|
| IDs and keys | VARCHAR | INTEGER | Handles UUIDs and external IDs |
| Money | DECIMAL(18,2) | FLOAT/DOUBLE | Avoids rounding errors |
| Timestamps | TIMESTAMP or TIMESTAMPTZ | VARCHAR | Preserves date arithmetic |
| Booleans | BOOLEAN | INTEGER 0/1 | Clear intent |
| Categories | VARCHAR | ENUM | More flexible |
| Free text | VARCHAR | TEXT | Same semantics in DuckDB |
| Semi-structured data | JSON or STRUCT | VARCHAR | Preserves queryability |
| Lists | LIST | Comma-separated VARCHAR | Supports indexing and unnesting |
| Nested objects | STRUCT | Flattened columns | Preserves hierarchy |
| Date only | DATE | TIMESTAMP | Clearer semantics |
| Large integers | BIGINT or HUGEINT | INTEGER | Avoids overflow |
## Schema Organization
### Multi-Database Pattern
```sql
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.
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
```sql
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
| Criterion | Use VIEW | Use TABLE (CTAS) |
|---|---|---|
| Must reflect latest data | Yes | No |
| Query is fast (<1s) | Yes | Either |
| Query is expensive (>5s) | No | Yes |
| Accessed many times per day | No | Yes |
| Source data changes frequently | Yes | Rebuild periodically |
## Complex Types for Semi-Structured Data
### `STRUCT`
```sql
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`
```sql
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`
```sql
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
| Scenario | Type | Reason |
|---|---|---|
| Address with known fields | STRUCT | Fixed schema |
| Tags on a blog post | `VARCHAR[]` | Variable-length list |
| User preferences with unknown keys | `MAP(VARCHAR, VARCHAR)` | Dynamic keys |
| Third-party API payload | JSON | Structure varies |
## `ALTER TABLE` Patterns
```sql
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
| Constraint | Enforced? | Behavior |
|---|---|---|
| NOT NULL | Yes | Rejects NULL writes |
| PRIMARY KEY | No | Informational only |
| UNIQUE | No | Informational only |
| CHECK | No | Informational only |
| FOREIGN KEY | No | Not supported |
Use `NOT NULL` as the primary constraint mechanism.
```sql
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
For a new multi-model project without an existing framework, use this layout. Adapt names to the project; a single-table change does not need a scaffold.
```text
<project-name>/
models/
raw/raw_<entity>.sql
staging/stg_<entity>.sql
analytics/dim_<entity>.sql
analytics/fct_<entity>.sql
model_manifest.yml
```
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.
```yaml
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
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| brainstormingobra/superpowersYou MUST use this before any creative work - creating features, building components, adding functionality, modifying behavior, or planning anything new, in software or out of it (a talk, a business, a renovation). | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| brand-guidelinesanthropics/skillsApplies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| frontend-designanthropics/skillsGuidance for distinctive, intentional visual design when building new UI or reshaping an existing one. Helps with aesthetic direction, typography, and making choices that don't read as templated defaults. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| mcp-builderanthropics/skillsGuide for creating high-quality MCP (Model Context Protocol) servers that enable LLMs to interact with external services through well-designed tools. Use when building MCP servers to integrate external APIs or services, whether in Python (FastMCP) or Node/TypeScript (MCP SDK). | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |