This step-by-step tutorial will walk you through creating your own Squirrels project!
We will modify a working Squirrels project to create a different Squirrels project for weather analytics. Once you’ve completed the tutorial, you will understand many of the key features of Squirrels!
Step 1: Complete the quickstart
Create a new folder for your project, and open it up in your favourite coding editor (such as VS Code). The name of the folder is your choice (one example is squirrels-tutorial).
Follow instructions in Quickstart to quickly get a sample working project going.
Then, in the .env file, set the SQRL_SECRET__ADMIN_PASSWORD environment variable to something of your choice (it is randomly generated by default).
Step 2: Add the weather database
Now add the SQLite database we will use for the rest of the tutorial. Simply run:
This uses the get-file command to add a weather.db file in the resources folder. Feel free to delete the other files in the resources folder.
Note that this is mainly done for tutorial purposes. For most production use cases, you would simply specify the database connection string (more details on this soon) to an external database and not bring a copy of the database into your project.
Step 3: Define project configurations
Open the squirrels.yml file. This is the project configuration file that exists for all Squirrels projects, and it is used to configure many properties of the Squirrels project in yaml.
In this step, we will focus on the project_variables, connections, and datasets sections.
Setting the project variables
The project variables name and major_version are required. All other project variables are optional. You are also free to add any of your own project variables here.
In this tutorial, we will be making datasets for historical weather data. Change the project_variables section to look like the following:
project_variables:
name: weather
label: Weather Analytics
description: Analyze weather statistics from 2012 to 2015.
major_version: 1
auth_type: optional
Setting the database connections
The connections section is where we set all the database connection details that we need. We provide a list of connection names here and refer to them in other files. The connection name default must be provided for models that don’t specify a connection name explicitly.
Under default, change the uri field to sqlite:///{project_path}/resources/weather.db. Change the label to “SQLite Weather Database”.
You can also substitute environment variables defined in the .env file using Jinja. For instance, if there is an environment variable called “SQLITE_CONN_STR” in .env, then you can also set the url to:{{ env_vars.SQLITE_CONN_STR }}
connections:
- name: default
label: SQLite Weather Database
type: sqlalchemy
uri: sqlite:///{project_path}/resources/weather.db
sqlalchemy is the utility used to connect to any database that SQLAlchemy supports. For more information on the different connection types, see the Connections page.
To specify database connections with Python instead of YAML, see the database connections file page.
Defining the datasets
The datasets section is where we define the attributes for all datasets created by the Squirrels project. Every dataset defined will have their own “parameters API” and “dataset result API”.
Currently, you may see two datasets configured. We will only have one dataset for this tutorial.
Change the datasets section to look like the following instead:
datasets:
- name: weather_by_period
label: Weather by Time of Year
description: Gather weather statistics (precipitation, temperature, wind speed, etc.) by time of year or condition
model: weather_by_period # optional if same as dataset name
parameters:
- group_by_dim
Step 4: Create the dataset parameters
Go into the pyconfigs/parameters.py file. This python configuration file contains the definitions of all the widget parameters used in the dataset.
We will rewrite this file. Remove all the existing code in the file and replace it with the following:
from squirrels import parameters as p, parameter_options as po
@p.SingleSelectParameter.create_simple(
name="group_by_dim", label="Group By",
description="Dimension(s) to aggregate by"
)
def group_by_options():
return [
po.SelectParameterOption(
id="year", label="Year",
dim_col="year"
),
po.SelectParameterOption(
id="quarter", label="Quarter",
dim_col="quarter"
),
po.SelectParameterOption(
id="month", label="Month",
dim_col="month_name", order_by_col="month_order"
),
po.SelectParameterOption(
id="day", label="Day of Year",
dim_col="day_of_year"
),
po.SelectParameterOption(
id="cond", label="Condition",
dim_col="condition"
)
]
@p.SingleSelectParameter.create_simple to create a single-select parameter. The function returns a list of parameter options as SelectParameterOption objects.
The id and label parameters to the SelectParameterOption constructor are required. Arbitrary keyword arguments such as “dim_col” and “order_by_col” can be specified to the SelectParameterOption constructor, which will be treated as custom fields to the parameter option.
The SelectParameterOption class has an “is_default” attribute to specify the parameter option(s) that are selected by default. By default, “is_default” is set to False. When none of the parameter options have “is_default” set as True, the first option is selected by default for single-select parameters, and nothing is selected by default for multi-select parameters.
The possible widget parameter types supported today are SingleSelectParameter, MultiSelectParameter, DateParameter, DateRangeParameter, NumberParameter, NumberRangeParameter, and TextParameter. Each parameter type can be created with one of the following decorators: create_simple, create_with_options, or create_from_source. Every decorator takes “name” and “label” as required arguments.For SingleSelectParameter, the arguments for create_simple and create_with_options are similar. The difference is that create_with_options lets you specify a parent parameter for cascading the shown options. For non-select parameter types like DateParameter, there are more differences. Step 5: Create the context file
The context file is a Python file that runs in real-time to transform parameter selections and/or authenticated user attributes into meaningful values that can be used by dynamic data models.
Change the pyconfigs/context.py file to look like the following:
from typing import cast, Any
from squirrels import ContextArgs, parameters as p
from pyconfigs.user import CustomUserFields
def main(ctx: dict[str, Any], sqrl: ContextArgs) -> None:
"""
Define context variables AFTER parameter selections are made by adding entries to the dictionary "ctx".
These context variables can then be used in the models.
"""
# The param_exists method confirms whether the "group_by_dim" parameter exists for the current dataset.
# If it does, we define two context variables called "dim_col" and "order_col".
if sqrl.param_exists("group_by_dim"):
group_by_param = cast(p.SingleSelectParameter, sqrl.prms["group_by_dim"])
ctx["dim_col"] = group_by_param.get_selected("dim_col")
ctx["order_col"] = group_by_param.get_selected("order_by_col", default_field="dim_col")
# Define the context variable "role" based on whether the user is authenticated and its fields(s). See "user.py" for defining custom user fields.
# This is shown for demonstration purposes - the "role" context variable will not be used in any data models in this tutorial.
custom_user_fields = cast(CustomUserFields, sqrl.user.custom_fields)
ctx["role"] = custom_user_fields.role
The available methods on the parameter object depends on the parameter type. For example, SingleSelectParameter objects have a get_selected method to get a custom field from the selected option.By casting the parameter object to type SingleSelectParameter, this makes it easier to explore the available methods on the parameter through an IDE’s linting and auto-complete features.
Step 6: Create sources
The models/sources.yml file lets us document the metadata of sources from our database tables.
The weather.db SQLite database we retrieved earlier contains a table called “weather”. Replace the sources.yml file with the following contents:
sources:
- name: src_weather
description: Source table for weather metrics by day over time
connection: default
table: weather
load_to_vdl: true
columns:
- name: date
type: string
description: The date of the weather statistics in YYYY-MM-DD format
category: dimension
- name: precipitation
type: float
description: The amount of precipitation for the time period in centimeters
category: measure
- name: temp_max
type: float
description: The maximum temperature for the time period in degrees Celsius
category: measure
- name: temp_min
type: float
description: The minimum temperature for the time period in degrees Celsius
category: measure
- name: wind
type: float
description: The average wind speed for the time period in km/h
category: measure
- name: condition
type: string
description: The weather condition of the time period (e.g. "sun", "fog", "rain")
category: dimension
Step 7: Create seeds
Seeds are CSV data files that can be used by other data models. For example, we can create a seed that maps month numbers to month names.
Currently, there are some seeds already defined in the seeds/ folder. We will replace them with our own seeds. Feel free to delete the existing files in the seeds/ folder.
Create a file named seed_month_names.csv in the seeds/ folder with the following contents.
seeds/seed_month_names.csv
month_order,month_name
1,January
2,February
3,March
4,April
5,May
6,June
7,July
8,August
9,September
10,October
11,November
12,December
seeds/seed_month_names.yml file to add metadata for the seed.
seeds/seed_month_names.yml
description: Month number to month name mapping
columns:
- name: month_order
type: integer
description: The month number
category: dimension
- name: month_name
type: string
description: The human-readable month name
category: dimension
- Explicit type casting: By setting
cast_column_types: true (not shown here), the framework will cast the columns to the specified types.
- Documentation: It serves as a central place to document the purpose of the seed and its individual columns.
- Metadata for the API: The
description and category (dimension, measure, etc.) provide context for the data. If surfaced to a dataset, this metadata is exposed via API endpoints, allowing frontend applications to understand the purpose of each column.
Step 8: Create data models from SQL queries
For data models that are created by code, the Squirrels framework supports:
- Creating build models (tables/views to be built offline) from SQL (DuckDB dialect) or Python files
- Creating dbview models (queries that run on an external database in real-time) from SQL files (dialect of the database connection used)
- Creating federate models (queries that run in the API server in real-time) from SQL (DuckDB dialect) or Python files
Sources, seeds, and build models are known as “static data models”.
Dbview models and federate models are known as “dynamic data models”.
For more information on the different model types, see the Data Models page.
We have already configured the “source” and “seed” models. For this tutorial, we will create a build model and a federate model with SQL, but will not create any dbview models.
Since we have replaced the source model in sources.yml, data models that are downstream of the replaced source model will no longer work.
Delete the existing files in the models/builds/, models/dbviews/, and models/federates/ folders. Feel free to delete the existing files in the macros/ folder as well.
Define a macro
First, create a macros/metrics.sql file with the following contents.
{%- macro get_metrics() -%}
CAST(SUM(precipitation) AS DECIMAL(6, 1)) AS precipitation,
CAST(MAX(temp_max) AS DECIMAL(4, 1)) AS temp_max,
CAST(MIN(temp_min) AS DECIMAL(4, 1)) AS temp_min,
CAST(AVG(wind) AS DECIMAL(6, 4)) AS wind
{%- endmacro -%}
Define the build model
“Build models” are defined in the models/builds/ folder.
Create a models/builds/weather_by_date.sql file with the following contents.
models/builds/weather_by_date.sql
WITH
weather_by_date AS (
SELECT
date::VARCHAR AS date,
EXTRACT(YEAR FROM date::DATE)::INT AS year,
EXTRACT(MONTH FROM date::DATE)::INT AS month_order,
EXTRACT(DOY FROM date::DATE)::INT AS day_of_year,
'Q' || CEIL(EXTRACT(MONTH FROM date::DATE) / 3)::INT AS quarter,
MODE(condition)::VARCHAR AS condition,
{{ get_metrics() | indent(4) }}
FROM {{ ref("src_weather") }}
GROUP BY date
)
SELECT
date,
condition,
year,
m.month_order,
m.month_name,
day_of_year,
quarter,
precipitation,
temp_max,
temp_min,
wind
FROM weather_by_date
LEFT JOIN {{ ref("seed_month_names") }} AS m USING (month_order)
The SQL file is templated with Jinja. It calls the macros:
get_metrics() which is defined in the macros/metrics.sql file we created earlierref("src_weather") which references the “src_weather” source we defined earlier
Build models are able to call ref on sources (that have load_to_vdl: true), seeds, and other build models. models/builds/weather_by_date.yml file.
models/builds/weather_by_date.yml
description: |
This build model aggregates weather data by date and adds additional time-based attributes.
materialization: TABLE # one of "TABLE" or "VIEW" - defaults to "VIEW" for SQL models if not specified
depends_on: # optional for SQL models - Squirrels can automatically track dependencies for SQL models via the `ref()` macro
- src_weather
- seed_month_names
columns:
- name: date
depends_on:
- src_weather.date
pass_through: true
- name: year
type: integer
description: The year extracted from the date
category: dimension
depends_on:
- src_weather.date
- name: month_order
type: integer
description: The order of the month
category: dimension
depends_on:
- src_weather.date
- seed_month_names.month_order
- name: month_name
type: string
description: The name of the month
category: dimension
depends_on:
- src_weather.date
- seed_month_names.month_name
- name: day_of_year
type: integer
description: The day of the year (1-366)
category: dimension
depends_on:
- src_weather.date
- name: quarter
type: string
description: The quarter (Q1, Q2, Q3, or Q4) derived from the month
category: dimension
depends_on:
- src_weather.date
- name: condition
type: string
description: The most common weather condition of the date
category: dimension
depends_on:
- src_weather.condition
- name: precipitation
type: decimal
description: Total precipitation of the date in centimeters, rounded to 1 decimal place
category: measure
depends_on:
- src_weather.precipitation
- name: temp_max
type: decimal
description: Maximum temperature of the date in degrees Celsius, rounded to 1 decimal place
category: measure
depends_on:
- src_weather.temp_max
- name: temp_min
type: decimal
description: Minimum temperature of the date in degrees Celsius, rounded to 1 decimal place
category: measure
depends_on:
- src_weather.temp_min
- name: wind
type: decimal
description: Average wind speed of the date in km/h, rounded to 4 decimal places
category: measure
depends_on:
- src_weather.wind
Define the federate model
Federate models are defined in the models/federates/ folder.
Create a models/federates/weather_by_period.sql file with the following contents.
models/federates/weather_by_period.sql
WITH
cte_weather_grouped AS (
SELECT
{{ ctx.dim_col }} AS dim_value,
{{ ctx.order_col }} AS ordering,
{{ get_metrics() | indent(4) }}
FROM {{ ref("weather_by_date") }}
GROUP BY dim_value, ordering
)
SELECT
'{{ ctx.dim_col }}' AS dimension_type,
dim_value AS dimension_value,
precipitation,
temp_max AS temperature_max,
temp_min AS temperature_min,
wind
FROM cte_weather_grouped
ORDER BY ordering
The {{ ctx.dim_col }} and {{ ctx.order_col }} variables are used to reference the context variables defined in the context.py file.Just like the build model, we use the get_metrics macro again. We also use the ref macro to reference the build model created earlier.Federate models are able to call the ref macro on sources (that have load_to_vdl: true), seeds, build models, dbview models, and other federate models.
models/federates/weather_by_period.yml file to add metadata for the federate model.
models/federates/weather_by_period.yml
description: |
This model aggregates weather data by time of year or condition to show weather statistics.
depends_on: # optional for SQL models - Squirrels can automatically track dependencies for SQL models via the `ref()` macro
- weather_by_date
columns:
- name: dimension_type
type: string
description: The type of dimension (e.g. "year", "month", "condition")
category: dimension
- name: dimension_value
type: string
description: The value of the dimension (e.g. "2020", "January", "sun")
category: dimension
- name: precipitation
type: decimal
description: Total precipitation of the time period or condition in centimeters, rounded to 1 decimal place
category: measure
depends_on:
- weather_by_date.precipitation
- name: temperature_max
type: decimal
description: Maximum temperature of the time period or condition in degrees Celsius, rounded to 1 decimal place
category: measure
depends_on:
- weather_by_date.temp_max
- name: temperature_min
type: decimal
description: Minimum temperature of the time period or condition in degrees Celsius, rounded to 1 decimal place
category: measure
depends_on:
- weather_by_date.temp_min
- name: wind
type: decimal
description: Average wind speed of the time period or condition in km/h, rounded to 4 decimal places
category: measure
depends_on:
- weather_by_date.wind
For SQL models that use the ref macro, the depends_on field is optional. However, it is required for Python models, and it is recommended to specify it for SQL models as well.
Similar to federate models, dbview models are also run in real-time and can change behaviour based on parameter selections or authenticated user.The main difference is that dbview models run on an external database (instead of the API server), and can only reference sources that share the same database connection.
Step 9: Development testing
You can build the static data models by running:
This runs the build command to materialize build models into the Virtual Data Lake. Then, activate the API server by running:
This runs the run command to start the API server in standalone mode. Take the time now to explore the project in Squirrels Studio before proceeding.
The Squirrels API server uses FastAPI under the hood. For details on how to mount the Squirrels APIs into an existing FastAPI application, see this how-to guide.
Use AI to ask questions about your datasets! 🤖While the API server is active, you have an MCP server running at:http://localhost:8000/mcp
What’s next?
Now that you’ve built your first Squirrels project, here are some suggested pages to explore next:
- Architecture: Learn more about the lifecycle of a Squirrels application and how data flows through the system.
- Environment variables: See the available environment variables Squirrels can use to adjust certain settings.
- MCP server: Understand the available MCP tools and their behavior for use with AI agents.
- Virtual Data Lake (VDL): Understand how the VDL works. Use it to materialize data models in advance if needed for faster real-time queries.
- Dashboards: Learn how to create custom dashboards to visualize your datasets.
- Authentication: Secure your APIs and implement user-based filtering (or other user-specific behaviours).
- Cascading parameter options: Enable parent-child relationships between parameters where a child parameter’s options depend on a parent selection.
- CLI reference: A complete guide to all Squirrels CLI commands.
