SQL Models

SQL models are the building blocks of the Soria data pipeline. Each model is a .sql file stored in a workspace and organized into layers — bronze, silver, gold, or platinum. When you save a model, Soria automatically commits it to GitHub, updates the workspace PR, and applies the view to the warehouse.

Model file format

Every SQL model must include a header comment block and a MODEL block. The @category annotation is required — it controls which section the model appears in on the dashboard.

/*
@category: Medicare Advantage
@overview: Brief description of what this model does.
*/
MODEL (
  name silver.stg_enrollment,
  kind FULL,
  grain (member_id, effective_date),
  column_descriptions (
    member_id = 'Unique member identifier',
    effective_date = 'Date the enrollment record became effective'
  )
);

WITH src_enrollment AS (
  SELECT * FROM bronze.raw_enrollment
)
SELECT
  member_id::VARCHAR AS member_id,
  effective_date::DATE AS effective_date
FROM src_enrollment

Optional annotations

Annotation	Applies to	Description
`@category`	All layers	Required. Dashboard category, e.g. `Medicare Advantage`, `NAIC Statutory Filings`, `Star Ratings`. Create new categories as needed for new data domains.
`@overview`	All layers	One-line description shown on the dashboard.
`@dashboard`	Platinum only	Full YAML block for chart configuration.

Layer requirements

Bronze — raw source ingestion

Bronze models represent raw source data as delivered by scrapers. No transformations should occur at this layer.

Silver — typed, cleaned staging

Silver models apply type casting and cleaning to a single source table. Strict rules apply:

Every src_ CTE must be SELECT * FROM source with no column transforms or filters.
All output columns must have explicit type casts using CAST(col AS type) or col::type syntax.
Silver models must not contain JOINs. Joins belong in the gold layer.
Every output column must have an entry in column_descriptions inside the MODEL block.

Gold — joined, business-ready models

Gold models join across silver models to produce business-ready datasets.

Joins across silver models are expected and correct at this layer.
Column descriptions remain required for all output columns.
Gold models are candidates for materialization after promotion (see warehouse_materialize).

Platinum — dashboard-ready outputs

Platinum models power dashboard charts and are the final output layer.

May include a @dashboard YAML annotation block in the header comment to configure chart type, axes, and filters.
Dashboard config is validated on save; warehouse_materialize is run automatically on promote.

sql_model_save

Save (create or update) a SQL model in a workspace.

Parameters

string

required

The ID of the workspace where the model will be saved, e.g. ws_ma_star_ratings_8a3f1b92.

string

required

Path to the model file within the workspace, e.g. medicare_advantage/star_ratings.sql. The path determines the model’s location in the GitHub repository.

string

required

Data layer for the model. Must be one of: bronze, silver, gold, platinum.

string

required

Full SQL source, including the header comment block (with @category) and the MODEL block. Validation runs on every save — see validation rules below.

string

Name or identifier of the person making the edit. Recorded in the model’s metadata and shown in the workspace model list.

What happens on save

Validation

Soria validates the model against layer-specific rules. If validation fails, the model is not saved and a quality report is returned describing the errors.

Saved to workspace

The model is written to the workspace’s model store and versioned.

GitHub commit

Soria commits the file to the workspace branch and updates the open pull request.

View auto-apply

The corresponding view is created or replaced in the warehouse. If auto-apply fails, the model is still saved but you will see a warning — fix the SQL and re-save to retry.

Validation

Every save runs validation against the model’s content. Common failure reasons:

Missing @category annotation in the header comment.
src_ CTEs that do anything other than SELECT * FROM source.
Silver model contains a JOIN.
Silver model columns lack explicit type casts.
Output columns missing from column_descriptions.

The response includes a quality report when warnings or errors are present.

Example

{
  "workspace_id": "ws_ma_star_ratings_8a3f1b92",
  "path": "medicare_advantage/star_ratings.sql",
  "layer": "silver",
  "content": "/*\n@category: Medicare Advantage\n@overview: Staged star ratings data.\n*/\nMODEL (\n  name silver.stg_star_ratings,\n  kind FULL,\n  column_descriptions (\n    contract_id = 'CMS contract identifier',\n    star_rating = 'Overall star rating (1–5)'\n  )\n);\n\nWITH src_ratings AS (\n  SELECT * FROM bronze.raw_star_ratings\n)\nSELECT\n  contract_id::VARCHAR AS contract_id,\n  star_rating::DECIMAL AS star_rating\nFROM src_ratings",
  "edited_by": "analyst@example.com"
}

sql_model_list

List all SQL models saved in a workspace.

Parameters

string

required

The workspace to list models from.

Response

Returns each model’s path, layer, and updated_at timestamp, sorted by last update.

Example

{
  "workspace_id": "ws_ma_star_ratings_8a3f1b92"
}

Example response:

SQL Models (3):
  [silver] medicare_advantage/star_ratings.sql
    Updated: 2025-11-14T10:32:01Z
  [gold] medicare_advantage/ma_enrollment_summary.sql
    Updated: 2025-11-13T15:08:44Z
  [bronze] medicare_advantage/raw_star_ratings.sql
    Updated: 2025-11-10T09:21:17Z

sql_model_get

Retrieve the full content of a SQL model by path.

Parameters

string

required

The workspace the model belongs to.

string

required

Path to the model file, e.g. medicare_advantage/star_ratings.sql.

Example

{
  "workspace_id": "ws_ma_star_ratings_8a3f1b92",
  "path": "medicare_advantage/star_ratings.sql"
}

The response includes the model’s path, layer, who last edited it, the last-updated timestamp, and the full SQL content.

sql_model_delete

Delete a SQL model from a workspace.

Parameters

string

required

The workspace the model belongs to.

string

required

Path to the model file to delete, e.g. medicare_advantage/star_ratings.sql.

Example

{
  "workspace_id": "ws_ma_star_ratings_8a3f1b92",
  "path": "medicare_advantage/star_ratings.sql"
}

Deleting a model removes it from the workspace’s model store and commits the deletion to the GitHub branch. The corresponding view in the warehouse is not automatically dropped — you may need to clean it up manually if it is no longer needed.

Overview

Ingestion Tools

Pipeline Tools

Warehouse & Models

Intelligence Tools

Model file format

Optional annotations

Layer requirements

sql_model_save

Parameters

What happens on save

Validation

Example

sql_model_list

Parameters

Response

Example

sql_model_get

Parameters

Example

sql_model_delete

Parameters

Example

​Model file format

​Optional annotations

​Layer requirements

​sql_model_save

​Parameters

​What happens on save

​Validation

​Example

​sql_model_list

​Parameters

​Response

​Example

​sql_model_get

​Parameters

​Example

​sql_model_delete

​Parameters

​Example

Model file format

Optional annotations

Layer requirements

sql_model_save

Parameters

What happens on save

Validation

Example

sql_model_list

Parameters

Response

Example

sql_model_get

Parameters

Example

sql_model_delete

Parameters

Example