Base Queries / Data Models
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') }}.
The filters must be located within the same SQL block where the database tables are referenced. Filters placed outside could lead to errors.
You can create a base query from the card explorer as shown below.
The name of the base_query should be unique and must not match the name of
any inner tables within the query. This ensures that there are no conflicts or
ambiguities between the base_query and the tables it references.
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 visualize. 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_segmentAfter defining the base query, you can reference it in your card SQL as shown below.
SELECT category, COUNT(*)
FROM {{ ref('consumer_segment_bq') }}
GROUP BY category ORDER BY category DESC LIMIT 100Notice that that card SQL does not have any filters. The filters must be passed to the base query where tables are referenced.