motherduck-model-data

Create dimension tables (dim_customers, dim_products, dim_dates) and a fact table (fact_orders) using CREATE TABLE with explicit types and NOT NULL constraints. Add table and column comments, use fully qualified names, and organize models into raw, staging, and analytics databases.

Use views when queries are fast (under 1s) and must reflect the latest data. Use CTAS tables with CREATE OR REPLACE when queries are expensive (over 5s), accessed frequently, or when periodic rebuilds are acceptable.

No. MotherDuck only enforces NOT NULL constraints; PRIMARY KEY, UNIQUE, and CHECK are informational only, and FOREIGN KEY is not supported. Use NOT NULL aggressively and handle uniqueness in your transformation logic.

Use VARCHAR for IDs to handle UUIDs and external keys, DECIMAL(18,2) for money to avoid floating-point rounding errors, and TIMESTAMP or TIMESTAMPTZ for times to preserve date arithmetic. Use BOOLEAN for flags rather than integer 0/1.

Skip the scaffold when the request is a single table, a quick DDL statement, or an ad-hoc exploration query. The file-based scaffold with a manifest is the default only for multi-table, multi-stage transformation work with dependencies.