# 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:**

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

**Join example:**

```sql
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:**

```sql
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

```sql
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

```sql
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

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.wobby.ai/semantic-layer/models/creating-models/creating-models-from-sql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.