Silver Layer (Models)

The silver layer is where you write your own SQL transformations. A model is one SQL query plus a TOML file. The TOML declares the model’s dependencies, its materialization strategy, and its target table.

Models are plain SQL

Rocky models are plain SQL files. Dependencies and materialization live in a sidecar TOML file. Your .sql is what the warehouse sees.

  models/fct_orders.sql     the query you wrote
  models/fct_orders.toml    name, depends_on, strategy, target
            │
            │ rocky run
            ▼
  ┌─────────────────────────────────────────────────────┐
  │ the strategy decides the statement                  │
  │   full_refresh → CREATE OR REPLACE TABLE …          │
  │   merge        → MERGE INTO … USING (…)             │
  │   … and the other strategies listed below           │
  └──────────────────────────┬──────────────────────────┘
                             ▼
           acme_warehouse.analytics.fct_orders

Model formats

Sidecar format (recommended)

Each model is two files with the same base name:

models/
├── fct_orders.sql    # Pure SQL; opens cleanly in any SQL editor
└── fct_orders.toml   # Configuration

Inline format (legacy)

A single SQL file with TOML frontmatter at the top. Rocky still reads it. Prefer the sidecar format, because embedded TOML breaks SQL editor tooling.

---toml
name = "fct_orders"
depends_on = ["stg_orders"]

[strategy]
type = "full_refresh"

[target]
catalog = "acme_warehouse"
schema = "analytics"
table = "fct_orders"
---

SELECT ...

Configuration

Model TOML fields (full reference: Model Format):

Field	Required	Description
name	Yes	Model identifier, used in depends_on references
depends_on	No	List of upstream model names (execution order)
[strategy]	No	Materialization config (see below); defaults to full_refresh
[target]	Yes	Output table: { catalog, schema, table }
[[sources]]	No	Input tables (for documentation and lineage)

[strategy]

A sidecar declares at most one [strategy]. If it declares none, Rocky takes one from the config group, then from the directory defaults, and otherwise uses full_refresh. Pick the block that matches what you need.

Merge. update_columns is optional and defaults to all non-key columns.

[strategy]
type = "merge"
unique_key = ["customer_id"]
update_columns = ["name", "email", "updated_at"]

Example: sidecar model

models/fct_orders.toml

name = "fct_orders"
depends_on = ["stg_orders", "dim_customers"]

[strategy]
type = "full_refresh"

[target]
catalog = "acme_warehouse"
schema = "analytics"
table = "fct_orders"

models/fct_orders.sql

SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    o.total_amount,
    o.order_date
FROM acme_warehouse.staging__us_west__shopify.orders o
JOIN acme_warehouse.analytics.dim_customers c
    ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'

Example: merge model

models/dim_customers.toml

name = "dim_customers"
depends_on = ["stg_customers"]

[strategy]
type = "merge"
unique_key = ["customer_id"]

[target]
catalog = "acme_warehouse"
schema = "analytics"
table = "dim_customers"

models/dim_customers.sql

SELECT
    customer_id,
    customer_name,
    email,
    signup_date,
    current_timestamp() AS updated_at
FROM acme_warehouse.staging__us_west__shopify.customers

This generates a MERGE statement:

MERGE INTO acme_warehouse.analytics.dim_customers AS target
USING (
    SELECT
        customer_id,
        customer_name,
        email,
        signup_date,
        current_timestamp() AS updated_at
    FROM acme_warehouse.staging__us_west__shopify.customers
) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Materialization strategies

Strategy	When to use	Adapters
full_refresh	Small tables, complex transforms, guaranteed consistency	All
merge	SCDs, upserts by key	All
time_interval	Partition-keyed reprocessing with @start_date / @end_date	All
materialized_view	Warehouse-managed view refresh	Databricks, Snowflake, BigQuery
dynamic_table	Target-lag managed tables	Snowflake

full_refresh (default)

Rocky rebuilds the whole table on every run:

CREATE OR REPLACE TABLE target AS SELECT ...

incremental

A silver model loads only rows newer than its target’s watermark. Declare the watermark column with timestamp_column, and put @incremental_filter where the filter belongs in the SQL. Rocky resolves the placeholder to TRUE on the first run and on rocky run --full-refresh. On every later run it becomes a comparison with MAX(<watermark>) read from the target. A model with no watermark is refused with E037. See Incremental in the model format reference.

In the bronze layer, incremental copies source tables and filters each copy on a watermark in the state store.

merge

Rocky upserts by unique key:

MERGE INTO target USING (...) AS source
ON target.key = source.key
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Validation

Run rocky validate to load every model and check the dependency graph before you execute anything:

rocky validate

It checks that:

• Every model file parses
• Every depends_on reference points to a model that exists
• No model depends on itself, directly or through a cycle
• Every target table identifier passes SQL validation