Data Modeling Troubleshoot

Field not found/not exist/not recognized

In general, it means that the field you does not exist in the table/CTE you are referring to. This error can happen in the following cases:

Normal SQL syntax is mixed with Holistics's syntax.

In a query, when you used {{ #alias.field_name }} syntax to refer to some fields, only those fields are selected in the source CTE and can be referred with alias.field_name. If you happen to use alias.field_name to refer to a field outside of the available, a "column not found/not exist" error will be raised.

In the example below, when querying model ecommerce_orders and refer to ID field with {{#o.id}}, only the ID field is available in the base CTE, and field created_at is not available.

Custom fields/measures in the upstream CTE are not named

Calculated fields and measures are actually SQL statements that will be inserted into the final query. You need to name the column resulted from the statement, or the column name will revert to the database's default.

In the example above, because we did not name the calculated field age_group, the resulted column name falls back to PostgreSQL's default and of course the name age_group does not correspond to any column.

A field is missing in an upstream model

If you have two models like this:

    {
        model_name: 'model_1'
        query: '''select 1 as field_1, 2 as field_2'''
    }
    
    -- Not using Holistics's syntax
    {
        model_name: 'model_2'
        query: '''select m.field_1, m.field_2 from {{#model_1 m}}'''    
    }
    
    -- Using Holistics's syntax
    {
        model_name: 'model_22'
        query: ''' select {{#m.field_1}}, {{#m.field_2}} from {{#model_1 m}}''' 
    }

model_2 depends on model_1 and refers to both field_1 and field_2. If for some reason one of the referred field in model_1 is removed:

    {
        model_name: 'model_1'
        query: '''
            select 
                1 as field_1
                -- 2 as field_2 -- Removed field_2
        '''
    }

All subsequent models referring to the removed field will be broken:

If the downstream model uses Holistics's syntax, you will receive a more descriptive error message that makes it easier to trace the bug:

Error line number does not match

When you use Holistics's query syntax, what you write will be parsed into a full query in your database's SQL flavor. This means that the actual query run against your database will be longer than what you write in the SQL editor, especially when your custom fields and measures are complicated:

In this example, the error happens at line 16 in the executed query but the cause of it lies in line 4 in the query editor, where we missed a comma.

Therefore, when debugging the query, it is best to check the Executed Query panel for the final SQL error, then trace it back to what you write in the query editor.

Illegal model/field naming

When naming your models and fields, it is best to avoid SQL/database keywords like user, order, limit, select... as it may raise Unexpected keyword error.