Data Models

Overview

Data models in Semaphor allow you to create reusable SQL queries, making it easier to build and manage complex queries across multiple cards. By defining a data model, you can centralize logic, reduce query duplication, and enhance maintainability.

Creating a Data Model

You can create a data model directly from the card explorer by defining a SQL query. A data model can reference multiple tables or a single table. You can either use a sub-query or a CTE for better organization.

WITH consumer_segment AS
    ( SELECT * FROM sales_data
      WHERE segment = 'Consumer'
    )
SELECT * from consumer_segment

Once a data model is created, you can reference it within your card SQL using the dm namespace. The dm namespace is reserved for data models and simplifies query referencing.

SELECT category, COUNT(*)
FROM dm.consumer_segment {{ filters | where }}
GROUP BY category ORDER BY category DESC LIMIT 100

When executed, the SQL dynamically expands to include the referenced data model and the included filters:

SELECT category, COUNT(*)
FROM
(
    WITH consumer_segment AS
    ( SELECT * FROM sales_data 
      WHERE segment = 'Consumer'
    ) 
    select * from consumer_segment 
) where city = 'New York' -- expanded fitlers

This allows for dynamic filtering and optimized query execution while maintaining reusability across multiple cards.

Base Queries (Deprecated)

The base queries serve as the top-level data model that you can reference in your card SQL. It promotes the reusability of SQL logic across all dashboard cards. They come handy in scenarios where you want to create multiple visualizations from the same underlying dataset. You can represent this dataset using a base query and reference it in all your dashboard cards as {{ ref('base_query') }}.

Creating a Base Query

You can create a base query from the card explorer as shown below.

In this example, we create a CTE (Common Table Txpresssion) and place our filter {{ filters | and }} as shown below. The base query represents the top-level data model that you want to analyze. This can be a single table or a result of join between multiple tables.

WITH consumer_segment AS
    ( SELECT * FROM sales_data
      WHERE segment = 'Consumer' {{ filters | and }}
    )
    select * from consumer_segment

Once defined, you can reference the base query in your card SQL. You may need to refresh your page to see the changes.

SELECT category, COUNT(*)
FROM {{ ref('consumer_segment_bq') }}
GROUP BY category ORDER BY category DESC LIMIT 100

When executed, the SQL resolves to:

SELECT category, COUNT(*)
FROM
(
    WITH consumer_segment AS
    ( SELECT * FROM sales_data 
      WHERE segment = 'Consumer' AND discount = 0.2 {{ filters | and }} 
    ) 
    select * from consumer_segment 
)
GROUP BY category ORDER BY category DESC LIMIT 100