DuckDB CREATE DOMAIN: Building Bulletproof Data Pipelines with Declarative Constraints

# Typical messy work — manual validation for every column
if row['email'] and '@' not in row['email']:
    raise ValueError("Invalid email")
if row['age'] < 0 or row['age'] > 150:
    raise ValueError("Invalid age")
if row['price'] is None or row['price'] <= 0:
    raise ValueError("Invalid price")

-- Basic syntax
CREATE DOMAIN domain_name AS base_type
    CONSTRAINT constraint_name CHECK (condition);

-- Create custom types with constraints
CREATE DOMAIN email_address AS TEXT
    CONSTRAINT valid_email CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN positive_price AS DECIMAL(10,2)
    CONSTRAINT no_zero_price CHECK (VALUE > 0)
    CONSTRAINT max_price CHECK (VALUE <= 999999.99);

CREATE DOMAIN user_age AS INTEGER
    CONSTRAINT valid_age CHECK (VALUE BETWEEN 0 AND 150);

CREATE DOMAIN iso_country_code AS CHAR(2)
    CONSTRAINT valid_country CHECK (VALUE ~ '^[A-Z]{2}$');

CREATE DOMAIN order_id AS VARCHAR(20)
    CONSTRAINT no_empty_order CHECK (LENGTH(VALUE) > 0)
    CONSTRAINT order_prefix CHECK (VALUE LIKE 'ORD-%');

CREATE DOMAIN order_status AS VARCHAR(20)
    CONSTRAINT valid_status CHECK (VALUE IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'));

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email       email_address NOT NULL UNIQUE,
    age         user_age,
    country     iso_country_code DEFAULT 'US'
);

CREATE TABLE orders (
    order_id    order_id PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    product     TEXT NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  positive_price NOT NULL,
    status      order_status DEFAULT 'pending',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ✅ Valid data — insert succeeds
INSERT INTO customers VALUES (1, '[email protected]', 28, 'CN');
INSERT INTO customers VALUES (2, '[email protected]', 35, 'JP');

-- ❌ Invalid email — automatically rejected
INSERT INTO customers VALUES (3, 'not-an-email', 25, 'US');
-- ERROR:  CHECK constraint valid_email of relation customers has been violated

-- ❌ Zero price — automatically rejected
INSERT INTO orders VALUES ('ORD-001', 1, 'MacBook Pro', 1, 0.00, 'pending');
-- ERROR:  CHECK constraint no_zero_price of relation orders has been violated

-- ❌ Invalid status — automatically rejected
INSERT INTO orders VALUES ('ORD-002', 1, 'iPhone', 2, 999.99, 'processing');
-- ERROR:  CHECK constraint valid_status of relation orders has been violated

-- ✅ Valid order — insert succeeds
INSERT INTO orders VALUES ('ORD-001', 1, 'MacBook Pro', 1, 12999.00, 'shipped');
INSERT INTO orders VALUES ('ORD-002', 2, 'iPhone 16', 2, 7999.00, 'confirmed');

-- A domain can have multiple constraints; all must be satisfied
CREATE DOMAIN us_phone_number AS TEXT
    CONSTRAINT phone_format CHECK (VALUE ~ '^\+1?\d{10}$')
    CONSTRAINT phone_length CHECK (LENGTH(REPLACE(VALUE, '+', '')) >= 10);

-- Ensure discount doesn't exceed original price
CREATE VIEW validated_orders AS
SELECT
    order_id, customer_id, product, quantity, unit_price,
    CASE WHEN discount_pct > 100 THEN NULL ELSE discount_pct END AS discount_pct,
    (unit_price * quantity * (1 - COALESCE(discount_pct, 0) / 100.0)) AS total_amount
FROM orders
WHERE unit_price > 0 AND quantity > 0;

-- Assuming you have a messy_orders.csv file
-- Direct import; rows violating constraints are rejected and reported

COPY orders FROM 'messy_orders.csv' (FORMAT CSV, HEADER true);
-- If a row violates a constraint, DuckDB skips it and logs the error

duckdb mydb.duckdb --fail-fast -c "COPY orders FROM 'data.csv'"

-- Define financial domain types
CREATE DOMAIN ticker_symbol AS VARCHAR(5)
    CONSTRAINT ticker_format CHECK (VALUE ~ '^[A-Z]{1,5}$');

CREATE DOMAIN currency_code AS CHAR(3)
    CONSTRAINT valid_currency CHECK (VALUE IN ('USD','EUR','GBP','JPY','CNY','KRW','INR'));

CREATE DOMAIN percentage AS DECIMAL(5,2)
    CONSTRAINT pct_range CHECK (VALUE BETWEEN 0 AND 100);

CREATE DOMAIN market_cap AS BIGINT
    CONSTRAINT min_market_cap CHECK (VALUE >= 0);

-- Stock data table
CREATE TABLE stocks (
    symbol        ticker_symbol PRIMARY KEY,
    name          TEXT NOT NULL,
    currency      currency_code DEFAULT 'USD',
    pe_ratio      DECIMAL(8,2),
    dividend_yield percentage,
    market_cap    market_cap,
    last_updated  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Post-import validation query
SELECT
    symbol,
    pe_ratio,
    dividend_yield,
    market_cap,
    CASE
        WHEN pe_ratio IS NOT NULL AND pe_ratio < 0 THEN 'negative_pe'
        WHEN pe_ratio IS NOT NULL AND pe_ratio > 100 THEN 'high_pe'
        WHEN dividend_yield IS NOT NULL AND dividend_yield > 15 THEN 'high_dividend'
        ELSE 'normal'
    END AS risk_flag
FROM stocks
ORDER BY pe_ratio DESC NULLS LAST;