Creating Models from SQL¶

A Model from SQL is built on a custom query, which gives you precise control over the underlying data.

Availability

Who can do this: members with the Edit capability in the active Workspace.

When to use this method¶

Use From SQL when the data you want to model isn't a single table: CTEs, window functions or calculated columns, joins across several tables, subqueries, or a WHERE clause applied before the Model is built.

If your data already lives in one table, From Table is quicker.

Create a Model from SQL¶

In Studio, click Models in the sidebar.
Click + (New model) at the top of the Models list. The Create Model dialog opens.
Select From SQL.
Enter a Model name (lowercase snake_case, for example active_customers). The field starts as new_model.
Choose a Data connection.
Write your query in the SQL Query editor.
Click Test query.
Review the results and click Create model. AI drafts the description, guidance, grain, dimensions and measures from your query's columns and sample rows, then opens the new Model so you can review and refine it.

Create model is enabled only after a successful test. If you edit the query or change the connection, test again.

While the Model is being created, the button shows Generating… and then Creating….

Model name¶

Use lowercase letters, digits and underscores, starting with a letter (for example active_customers, monthly_revenue), and name the business entity the Model represents. The name must be unique among the Models in your Workspace. An invalid or duplicate name is rejected when you click Create model, and you'll see Invalid model name or A model with this name already exists.

Writing your SQL query¶

Write your query in your data warehouse's SQL dialect. It should return the data you want to model.

The query must be a single statement: no ;-separated batches (one trailing ; is fine). Functions that read files, call other servers or otherwise have side effects are blocked, and the query is rejected before it runs.

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¶

Click Test query (it shows Testing… while it runs) and check the sample rows and column names before you create the Model. The test shows a Query Results preview with up to 20 sample rows, the number of rows returned and the execution time (for example 20 rows • 840ms). The row count is the number of sample rows returned, not the total size of the result. If the query returns nothing, you'll see Query returned no rows.

The warehouse read for a test is limited to 15 seconds. A query that runs past the limit is stopped and the test fails. If a test succeeds but takes longer than 15 seconds end to end, you'll see a warning that slow Models can hurt AI Analyst and Metric performance. Consider simplifying the SQL.

What AI drafts¶

When you click Create model, the query runs again to read its columns and up to 20 sample rows. Your query, the column names and data types, and up to 10 of the sample rows are sent to the language model your organization uses. AI drafts:

A description and guidance
The grain (which columns identify a row)
Dimensions and measures, with expressions that reference your query's output columns, and a unit and precision on measures where they're obvious

The Model uses the name you typed, not a name proposed by AI. For dimensions that AI marks as Enum, the allowed values are read from your warehouse, up to 50 distinct values. A column with more distinct values than that becomes an ordinary dimension instead.

No relationships are created. Add them on the Model's Relationships tab, or ask Steward to propose them.

If AI drafting isn't available, for example because the language model timed out or returned an error, the Model is still created with your query's columns imported as dimensions only: no measures, and an empty description and guidance. You'll see: AI couldn't draft this model, so only its columns were imported. Review it before use.

Error handling¶

If your query fails, you'll see Query failed with the error message from your warehouse. If the connection itself can't be reached or the warehouse user lacks access, a message explains the problem, with an Open data source link to the connection.

Common errors:

Syntax errors: check the SQL syntax for your warehouse.
Missing tables: verify table names and schemas.
Permission errors: make sure the connection's warehouse user has read access to the tables in your query.
More than one statement: remove extra ; separators so the query is a single statement.
Disallowed functions: remove calls to blocked functions.

If the warehouse fails while the Model is being created, nothing is saved. Fix the problem and try again.

Important notes¶

The SQL query becomes the Model's data source. On the Model's Overview tab, click SQL under Data source to see it.
Dimension and measure expressions are written in your warehouse's SQL dialect and reference the columns your query returns.
You can't change the query after the Model is created. To use a different query, create a new Model.
Complex queries may slow down every query that uses the Model.

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