> ## Documentation Index
> Fetch the complete documentation index at: https://docs.soriaanalytics.com/llms.txt
> Use this file to discover all available pages before exploring further.
# SQL Models
> Write and manage SQL models that power your Soria dashboard pages across the bronze, silver, gold, and platinum pipeline layers.
SQL models are the building blocks of every Soria dashboard. Each model is a SQL file that defines a view or table in your data warehouse. Models are organized into four pipeline layers, and platinum-layer models become the interactive charts you see on the dashboard home page.
## Pipeline layers
Raw source tables auto-created from extraction. You don't write bronze models — Soria creates them when data is ingested.
Cleaned and typed data. Use explicit `CAST` or `::` type conversions. No `JOIN`s allowed — joins belong in gold.
Joined data across silver models. This is where you combine related datasets and build richer views.
Aggregated, dashboard-ready views. Platinum models can include a `@dashboard` chart config that controls how the data is visualized.
## Model structure
Every SQL model must include a header comment with a `@category` annotation. This tells Soria which category section the model belongs to on the dashboard home page.
```sql theme={null}
/*
@category: Medicare Advantage
@overview: Star ratings by contract and plan year, sourced from CMS annual releases.
*/
MODEL (
name silver.stg_star_ratings,
kind FULL,
column_descriptions (
contract_id = 'CMS contract identifier',
plan_year = 'Reporting year for the rating',
star_rating = 'Overall star rating (1.0–5.0)'
)
);
SELECT
CAST(contract_id AS VARCHAR) AS contract_id,
CAST(plan_year AS INTEGER) AS plan_year,
CAST(star_rating AS DOUBLE) AS star_rating
FROM silver.src_star_ratings
```
### Annotations
| Annotation | Required | Description |
| ------------ | -------- | -------------------------------------------------------------------------------------- |
| `@category` | Yes | Dashboard category (e.g., `Medicare Advantage`). Determines grouping on the home page. |
| `@overview` | No | One-line description shown on the dashboard page. |
| `@dashboard` | No | YAML chart config. Platinum models only. |
### Layer rules
**Silver models:**
* Use `SELECT * FROM source` CTEs prefixed with `src_` — no transforms in the CTE itself.
* Apply all type conversions with explicit `CAST(col AS type)` or `col::type` syntax.
* No `JOIN` statements. Silver is for cleaning, not combining.
**Gold models:**
* Join across silver models to build richer datasets.
* Reference silver views using their schema-qualified names.
**Platinum models:**
* Aggregate data into the final shape for charts.
* Optionally include a `@dashboard` annotation with YAML chart configuration:
```sql theme={null}
/*
@category: Medicare Advantage
@overview: Average star rating by plan year.
@dashboard:
type: bar
x: plan_year
y: avg_star_rating
color: contract_type
*/
MODEL (
name platinum.star_ratings_by_year,
kind FULL,
column_descriptions (
plan_year = 'Reporting year',
avg_star_rating = 'Average overall star rating across all contracts',
contract_type = 'HMO, PPO, or other contract type'
)
);
SELECT
plan_year,
contract_type,
AVG(star_rating) AS avg_star_rating
FROM gold.star_ratings
GROUP BY plan_year, contract_type
```
## Creating and updating models
Use `sql_model_save` to create a new model or overwrite an existing one. After saving, Soria automatically:
* Commits the model file to GitHub and updates the pull request.
* Applies the view to your workspace's data warehouse.
```python theme={null}
sql_model_save(
workspace_id="ws_test_pipeline_58d28fca",
path="medicare_advantage/star_ratings.sql",
layer="silver",
content="/* @category: Medicare Advantage\n@overview: ... */\nMODEL (...);",
)
```
If the SQL is invalid, the model is saved to GitHub but the warehouse view is **not** updated. The response will include a warning with the apply error. Fix the SQL and re-save to apply the view.
### Path format
The `path` argument uses the format `category_folder/model_name.sql`. The path determines how the file is organized in the repository.
```
medicare_advantage/star_ratings.sql
naic_statutory_filings/loss_ratios.sql
star_ratings/contract_summary.sql
```
## Listing and reading models
Use `sql_model_list` to see all models saved in a workspace:
```python theme={null}
sql_model_list(workspace_id="ws_test_pipeline_58d28fca")
# Example output:
# SQL Models (4):
# [silver] medicare_advantage/stg_enrollment.sql
# Updated: 2024-11-01T14:23:10
# [gold] medicare_advantage/enrollment_joined.sql
# Updated: 2024-11-01T15:02:44
```
Use `sql_model_get` to read the content of a specific model:
```python theme={null}
sql_model_get(
workspace_id="ws_test_pipeline_58d28fca",
path="medicare_advantage/star_ratings.sql",
)
```
## Deleting a model
```python theme={null}
sql_model_delete(
workspace_id="ws_test_pipeline_58d28fca",
path="medicare_advantage/star_ratings.sql",
)
```
Deleting a model removes it from the repository and drops the corresponding view from the warehouse. This cannot be undone without re-saving the model.
## Exploring data before writing models
Use `warehouse_query` to profile and explore data in the warehouse before you start writing a model. This is especially useful when you're working with a new bronze source and need to understand its shape.
```python theme={null}
warehouse_query(
sql="SUMMARIZE bronze.pa_medicaid_enrollment",
workspace_id="ws_test_pipeline_58d28fca",
)
warehouse_query(
sql="SELECT DISTINCT contract_type FROM bronze.star_ratings",
workspace_id="ws_test_pipeline_58d28fca",
)
```
Omit `workspace_id` to query production data directly.