Creating Models from SQL¶

Creating a model from SQL allows you to define a custom query as the basis for your model. This is useful for complex transformations, multi-table joins, or when you need precise control over the underlying data.

When to Use This Method¶

Use "From SQL" when you need:

• Complex transformations - CTEs, window functions, or custom calculations
• Multi-table joins - Combine data from multiple tables into one model
• Filtered base data - Apply WHERE clauses before model creation
• Derived columns - Create calculated fields in the base query
• Views or subqueries - Model data that doesn't exist as a single table

How to Create a Model from SQL¶

1. Navigate to Models in the left sidebar
2. Click Create Model
3. Select From SQL
4. Enter a model name (this will be the model's identifier)
5. Choose a data source from the dropdown
6. Write your SQL query in the editor
7. Click Execute query to test
8. Review results and click Create Model

Model Name¶

Enter a descriptive name for your model:

• Use lowercase with underscores (e.g., active_customers, monthly_revenue)
• Choose a name that reflects the business entity
• Keep it concise but meaningful

Writing Your SQL Query¶

The SQL editor supports your data warehouse's SQL dialect. Write a query that returns the data you want to model.

Simple example:

SELECT *
FROM public.orders
WHERE status = 'completed'

Join example:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    c.customer_name,
    c.customer_tier
FROM public.orders o
JOIN public.customers c ON o.customer_id = c.customer_id

Complex transformation example:

WITH monthly_stats AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as monthly_revenue,
        COUNT(*) as order_count
    FROM public.orders
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT
    customer_id,
    month,
    monthly_revenue,
    order_count,
    AVG(monthly_revenue) OVER (
        PARTITION BY customer_id
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as rolling_avg_revenue
FROM monthly_stats

Testing Your Query¶

Before creating the model:

1. Click Execute query to run a test
2. Review the results table showing sample data
3. Check column names and data types
4. Verify the data looks correct

The test shows:

• Total row count
• Execution time
• First 10 rows of results

Error Handling¶

If your query fails, you'll see:

• Error message from the database
• Details about what went wrong
• Suggestions for fixing common issues

Common errors:

• Syntax errors - Check SQL syntax for your database
• Missing tables - Verify table names and schemas
• Permission errors - Ensure connection has read access

Important Notes¶

• The SQL query becomes the model's base table
• Dimensions and measures reference columns from your query
• Changes to the query require recreating the model
• Complex queries may impact query performance

Example Use Cases¶

Denormalized Customer View¶

SELECT
    c.customer_id,
    c.customer_name,
    c.signup_date,
    c.tier,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.amount) as lifetime_value,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.signup_date, c.tier

Time-Based Aggregation¶

SELECT
    DATE_TRUNC('day', event_timestamp) as event_date,
    event_type,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY DATE_TRUNC('day', event_timestamp), event_type

Filtered Subset¶

SELECT *
FROM transactions
WHERE status = 'completed'
  AND amount > 0
  AND test_mode = false