DOCSEARCHbyAlgolia
Submit Docs

Squirrels Analytics Documentation

website·Indexed 29 days ago

https://squirrels-analytics.github.io

Developer documentation for the Squirrels framework, featuring a REST API for project metadata, user management, and data management, as well as a Python SDK for interacting with projects, database connections, and parameters.

Tokens
52.8K
Snippets
275
Records
380
Agent score
75%

What's inside Squirrels

  1. Overview of Federate models in Squirrels

    Federate models are the final layer of a Squirrels data transformation pipeline. They combine data from multiple sources and models, apply final transformations, and expose data to end users. They are specifically designed for user-facing data models that respond to user attributes and parameter selections. Federate models can depend on sources (with load_to_duckdb: true), seeds, build models, dbview models, and other federate models.

  2. Overview of Squirrels API Server Framework

    Squirrels is a low-code API server framework specifically designed for building data analytics APIs. It allows data engineers to create production-ready REST APIs and serve well-described datasets to AI agents. A key feature is its built-in Model Context Protocol (MCP) server, which exposes tools that AI agents can use to interact with data dynamically.

  3. Overview of the Squirrels REST API framework

    Squirrels is a REST API framework designed for data analytics. It allows developers to build APIs that return datasets, dashboards, or parameters for datasets and dashboards. The framework includes built-in APIs for authentication, data cataloging, and a testing UI.

  4. Overview of the Squirrels framework

    Squirrels is an open-source, low-code project framework designed to build parameterizable REST APIs for dynamic data analytics. It is intended for users familiar with SQL and YAML. The framework allows developers to create dynamic datasets and expose them as REST APIs that accept query parameters and return standard JSON structures. This enables self-service data analytics for internal executives or external customers while maintaining control over queryable data.

  5. Understand Squirrels data modeling architecture

    Squirrels uses a layered approach to transform raw data into actionable insights via a logical pipeline:

    1. Base Layer: Consists of Sources (external data) and Seeds (internal reference data).
    2. Transformation Layer: Consists of Build, Dbview, and Federate models that use SQL or Python to prepare data for analysis.

    This modular structure supports both offline ETL and real-time BI analytics.

  6. Supported database connection types

    Squirrels supports three connection types. Note that only sqlalchemy supports SQL parameter placeholders in queries.
    TypeDescription
    sqlalchemyUses SQLAlchemy for database operations
    connectorxUses ConnectorX for high-performance data loading
    adbcUses ADBC (Arrow Database Connectivity)

  7. Accelerate PostgreSQL queries using Squirrels data artifacts

    Squirrels can significantly improve query performance for PostgreSQL, MySQL, or SQLite by creating a 'data artifact'—which is internally a DuckDB database file. When sqrl build is executed, Squirrels loads the source tables into DuckDB. During API requests, Squirrels translates the original SQL query into DuckDB's dialect using SQLGlot and executes it against the local DuckDB file instead of the remote database.

  9. Understand the Squirrels project directory structure

    A Squirrels project uses a specific set of pre-defined files and folders to organize SQL templates, Python configurations, and data models. The only mandatory file is squirrels.yml. All other directories and files are optional and used as needed to support the project's functionality.
    my-squirrels-project/
├─ macros/             # Jinja macro files for SQL templates
├─ models/
│   ├─ builds/          # SQL or Python files for build models (static tables in embedded DB)
│   ├─ dbviews/         # SQL files for database view models (external DB)
│   ├─ federates/       # SQL or Python files for federate models (joining models in embedded DB)
│   └─ sources.yml      # Configuration for source models
├─ pyconfigs/
│   ├─ connections.py   # SQLAlchemy engine database connection configuration
│   ├─ parameters.py    # Widget parameter definitions
│   ├─ context.py       # Parameter processing logic for context variables
│   └─ user.py           # User authentication and management model
├─ seeds/               # CSV and YAML files for seed data
├─ dashboards/           # Dashboard configuration files
├─ sqrl_packages/        # Git projects used as package dependencies
├─ target/             # Generated output files (e.g., compiled SQL)
├─ logs/                # Application logs (squirrels.log)
├─ .env                 # Local environment variables (Git ignored)
├─ .env.example         # Example environment variables
├─ .gitignore
├─ duckdb_init.sql       # Initialization SQL for embedded DuckDB (Git ignored)
└─ squirrels.yml         # Mandatory project configuration file

  10. Use the Parameter base class in Squirrels Python

    The Parameter class is the base class for all parameter classes in Squirrels. It can be imported from either the squirrels.types module or the squirrels module.

    Parameter classes serve two primary purposes:

    1. Configuration: Defining widget parameters in parameters.py using factory methods (CreateSimple, CreateWithOptions, or CreateFromSource). These methods return None as they configure the end-user interface rather than returning an object.
    2. Data Access: Retrieving selected parameter values (such as IDs, labels, or dates) within context.py or data models by calling methods on the parameter object.
    from squirrels import Parameter
# or
from squirrels.types import Parameter

  11. Key features of the Squirrels framework

    Squirrels provides several core capabilities for data API development:

    • AI-Ready Architecture: Includes a built-in MCP server for serving dataset metadata and results to AI agents.
    • Multi-Database Support: Ability to connect to and combine data from multiple databases or data warehouses in one project.
    • Flexible Data Modeling: Supports data models that reference other models using Python or Jinja-templated SQL.
    • Interactive Parameters: Supports the creation of dynamic datasets and dashboards based on user inputs.
    • Built-in Security: Includes a full user management system with authentication, access controls, and dataset permissions.

  12. Define default text values with TextParameterOption

    Use the TextParameterOption class to define the default text value of a text parameter. This class is a subclass of ParameterOption and can be imported from either the squirrels.parameter_options or squirrels module. Note that while only one option can appear at a time, different options can be configured to appear based on user attributes or parent parameter selections.