SQL Models

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

Bronze

Raw source tables auto-created from extraction. You don’t write bronze models — Soria creates them when data is ingested.

Silver

Cleaned and typed data. Use explicit CAST or :: type conversions. No JOINs allowed — joins belong in gold.

Gold

Joined data across silver models. This is where you combine related datasets and build richer views.

Platinum

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.

/*
@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:

/*
@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.

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:

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:

sql_model_get(
    workspace_id="ws_test_pipeline_58d28fca",
    path="medicare_advantage/star_ratings.sql",
)

Deleting a model

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.

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.

Get Started

Data Pipeline

Workspaces

Dashboards

News Intelligence

Pipeline layers

Bronze

Silver

Gold

Platinum

Model structure

Annotations

Layer rules

Creating and updating models

Path format

Listing and reading models

Deleting a model

Exploring data before writing models

​Pipeline layers

Bronze

Silver

Gold

Platinum

​Model structure

​Annotations

​Layer rules

​Creating and updating models

​Path format

​Listing and reading models

​Deleting a model

​Exploring data before writing models

Pipeline layers

Model structure

Annotations

Layer rules

Creating and updating models

Path format

Listing and reading models

Deleting a model

Exploring data before writing models