SQL Generation

Rocky builds every statement it sends to a warehouse. A model’s statements come out of the compiler’s typed IR. The IR (intermediate representation) is one typed record per model: its SQL, its columns, its target table, and its materialization strategy.

A replication pipeline needs no model. Rocky renders its catalog, schema, copy and other replication statements from the pipeline config instead. Both paths end in the dialect the target warehouse speaks. See the glossary for both terms.

  models/fct_orders.sql  +  models/fct_orders.toml
                     │
                     │ compile: parse, resolve the DAG, type check
                     ▼
          ┌──────────────────────┐
          │ typed IR             │  one record per model:
          │                      │  its SQL, columns, target, strategy
          └──────────┬───────────┘
                     │
  rocky.toml ────────┤  replication needs no model: its catalog,
  (a replication     │  schema, copy and other statements are
   pipeline)         │  rendered from the pipeline config
                     │
                     │ generate, in the target warehouse's dialect
     ┌───────────┬───┴────────┬────────────┬───────────┐
     ▼           ▼            ▼            ▼           ▼
  Databricks  Snowflake   BigQuery      Trino      DuckDB
                  the SQL below, ready to execute

The diagram shows five dialects. PostgreSQL, Redshift, SQL Server, ClickHouse and Spark have their own dialects too. This page shows the Databricks form unless it names another warehouse.

The rest of this page is the catalog of statements Rocky emits. Rocky validates every identifier before it reaches a statement — see SQL Safety at the end.

Catalog Lifecycle

CREATE CATALOG IF NOT EXISTS <catalog>

ALTER CATALOG <catalog> SET TAGS ('managed_by' = 'rocky', 'tenant' = 'acme')

DESCRIBE CATALOG <catalog>

Schema Lifecycle

CREATE SCHEMA IF NOT EXISTS <catalog>.<schema>

ALTER SCHEMA <catalog>.<schema> SET TAGS ('managed_by' = 'rocky', 'source' = 'shopify')

SHOW SCHEMAS IN <catalog>

Table Tagging

Rocky applies the governance tags you configure to each replicated table:

ALTER TABLE <catalog>.<schema>.<table> SET TAGS ('managed_by' = 'rocky')

Incremental Copy

This is the core replication statement. It copies the rows newer than the last watermark Rocky recorded for the table, and older than the newest source timestamp. The _loaded_by column appears only when the pipeline declares it in metadata_columns:

INSERT INTO <target_catalog>.<target_schema>.<table>
SELECT *, CAST(NULL AS STRING) AS _loaded_by
FROM <source_catalog>.<source_schema>.<table>
WHERE _fivetran_synced > TIMESTAMP '<last watermark>'
  AND _fivetran_synced < TIMESTAMP '<newest source timestamp>'

Rocky reads the previous run’s MAX(_fivetran_synced) from its state store and writes it in as a literal. There is no correlated subquery against the target table. Just before the copy, Rocky reads MAX(_fivetran_synced) over the source and writes it in as the second literal. The newest batch waits for a later run, so a late row with the same timestamp is not skipped. rocky plan shows the statement without the second bound. See Incremental Processing.

On DuckDB, each literal is a string with a UTC offset and no TIMESTAMP keyword: '2026-03-02 10:00:00.123456789+00:00'. DuckDB converts it to the column’s type. So it keeps all nine digits on a TIMESTAMP_NS column, and it means the same instant on a TIMESTAMPTZ column in any session time zone.

Full Refresh

Rocky rebuilds the whole table when it finds drift it cannot fix in place, and when you configure a full refresh:

CREATE OR REPLACE TABLE <target> AS SELECT * FROM <source>

Merge (Upsert)

For tables that require key-based deduplication:

MERGE INTO <target> AS t
USING (SELECT ... FROM <source>) AS s
ON t.key = s.key
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Transformation SQL

Rocky wraps the SQL you wrote in a statement chosen by the model’s materialization strategy:

• Table: CREATE OR REPLACE TABLE ... AS <user_sql>
• Incremental: INSERT INTO ... <user_sql>
• Merge: MERGE INTO ... USING (<user_sql>) ...
• Materialized View: CREATE OR REPLACE MATERIALIZED VIEW ... AS <user_sql> (Databricks, Snowflake, BigQuery; PostgreSQL and Redshift drop and re-create the view)
• Dynamic Table: CREATE OR REPLACE DYNAMIC TABLE ... TARGET_LAG = '<lag>' AS <user_sql> (Snowflake)
• Time Interval: Per-partition INSERT OVERWRITE with @start_date/@end_date substitution

Surrogate Key Columns

A model declares deterministic surrogate keys with [[surrogate_key]] blocks in its .toml sidecar:

[[surrogate_key]]
name = "order_key"
columns = ["order_id"]

At rocky run and rocky emit-sql, Rocky wraps the model’s SELECT and appends each key as a top-level column. DuckDB is shown here:

SELECT *, CAST(md5(cast(coalesce(cast(order_id as VARCHAR), '_dbt_utils_surrogate_key_null_') as VARCHAR)) AS VARCHAR) AS order_key
FROM (
<user_sql>
) AS __rocky_keyed

The hash expression follows dbt-utils’ generate_surrogate_key. Rocky casts each input to text, replaces a NULL with the _dbt_utils_surrogate_key_null_ sentinel, joins the values with a - separator, and MD5-hashes the result. Most warehouses concatenate with || and differ only in the text type they cast to:

• DuckDB / Snowflake cast to VARCHAR:

  md5(cast(coalesce(cast(a as VARCHAR), '_dbt_utils_surrogate_key_null_') || '-' || coalesce(cast(b as VARCHAR), '_dbt_utils_surrogate_key_null_') as VARCHAR))

• Databricks casts to STRING (Spark SQL rejects an unsized VARCHAR):

  md5(cast(coalesce(cast(a as STRING), '_dbt_utils_surrogate_key_null_') || '-' || coalesce(cast(b as STRING), '_dbt_utils_surrogate_key_null_') as STRING))

• BigQuery wraps the hash in to_hex(...) (its MD5() returns BYTES) and concatenates with concat(...) instead of ||, casting to STRING:

  to_hex(md5(cast(concat(coalesce(cast(a as STRING), '_dbt_utils_surrogate_key_null_'), '-', coalesce(cast(b as STRING), '_dbt_utils_surrogate_key_null_')) as STRING)))

The hash digest is the same one dbt-utils’ generate_surrogate_key produces for the same columns, so a key Rocky computes joins against the same key in an upstream dbt model.

Materialized View

Databricks, Snowflake and BigQuery:

CREATE OR REPLACE MATERIALIZED VIEW <catalog>.<schema>.<table> AS
<user_sql>

Dynamic Table (Snowflake)

CREATE OR REPLACE DYNAMIC TABLE <catalog>.<schema>.<table>
TARGET_LAG = '<lag>'
WAREHOUSE = <warehouse>
AS <user_sql>

Time-Interval Partition Processing

Each warehouse gets its own statement shape for partition-keyed materialization. See Time interval materialization for the strategy itself.

Databricks (Delta, atomic):

INSERT INTO <target>
REPLACE WHERE <time_column> >= '<start>' AND <time_column> < '<end>'
<user_sql with @start_date/@end_date substituted>

Snowflake (multi-statement transaction):

BEGIN;
DELETE FROM <target> WHERE <time_column> >= '<start>' AND <time_column> < '<end>';
INSERT INTO <target> <user_sql>;
COMMIT;

Drift Detection

Rocky reads both schemas before it copies a table:

DESCRIBE TABLE <catalog>.<schema>.<table>

When it finds a change it cannot apply in place, it drops the target and rebuilds it:

DROP TABLE IF EXISTS <target_catalog>.<target_schema>.<table>

Permission Grants

rocky run issues a GRANT for each declared permission. It never revokes. See Permissions.

GRANT <PERMISSION> ON CATALOG <catalog> TO `<principal>`

GRANT <PERMISSION> ON SCHEMA <catalog>.<schema> TO `<principal>`

Data Quality Checks

Row count (batched):

SELECT '<catalog>', '<schema>', '<table>', COUNT(*)
FROM <catalog>.<schema>.<table>
UNION ALL
SELECT '<catalog>', '<schema>', '<table>', COUNT(*)
FROM <catalog>.<schema>.<table>
-- ... up to 200 tables per batch

Freshness:

SELECT COUNT(*), MAX(<timestamp_column>) FROM <catalog>.<schema>.<table>

The count rides beside the maximum so that a NULL maximum can be told apart: zero rows is an empty table with nothing to measure, one or more rows with no value in the column is a check Rocky could not evaluate.

Null rate (sampled):

SELECT ... FROM <catalog>.<schema>.<table> TABLESAMPLE (N PERCENT)

Workspace Isolation

Rocky uses the Databricks REST API for workspace binding and isolation (not SQL):

• PATCH /api/2.1/unity-catalog/bindings/catalog/{name} — Bind catalog to workspace IDs
• PATCH /api/2.1/unity-catalog/catalogs/{name} — Set isolation_mode: "ISOLATED"

Catalog Discovery

Rocky finds the catalogs it manages by reading back a tag you configured, for example managed_by = 'rocky':

SELECT catalog_name
FROM system.information_schema.catalog_tags
WHERE tag_name = 'managed_by' AND tag_value = 'rocky'

SQL Safety

Rocky applies the same rules to every statement it builds:

• Identifiers (catalogs, schemas, tables, tenants, regions, sources) must match ^[a-zA-Z0-9_]+$. A BigQuery project ID may also hold hyphens, and has its own rule
• Principal names must match ^[a-zA-Z0-9_ \-\.@]+$, and Rocky always wraps them in backticks
• Rocky never uses format!() to interpolate untrusted input into SQL
• Every check runs in rocky-sql/validation.rs, before Rocky constructs any SQL