Skip to content
RockyRockyRocky

Bronze Layer

The bronze layer is Rocky’s config-driven replication inside the warehouse. You write no SQL. Rocky finds which tables are available, writes the SQL itself, and copies rows from the ingestion catalog into the target catalogs and schemas you declare.

The flow

Section titled “The flow”
rocky discover  →  rocky plan  →  rocky apply
  1. Discover. Finds the schemas and tables available for processing. A fivetran adapter calls the Fivetran REST API for connectors and enabled tables. A duckdb adapter queries information_schema. A manual adapter reads schema and table definitions written inline in the config.
  2. Plan. Parses the source schema names, resolves target catalogs and schemas, and writes the SQL statements. Records the result as a deterministic plan, keyed by plan_id.
  3. Apply. Runs one plan by id. It creates catalogs and schemas, copies data, runs quality checks, and updates watermarks. rocky run collapses plan and apply into one call, for local work and automation.

Schema pattern parsing

Section titled “Schema pattern parsing”

Source schemas follow a naming convention. Rocky splits the name into parts using a pattern you configure.

src__acme__us_west__shopify
│    │     │        │
│    │     │        └── source  = "shopify"   (the connector)
│    │     └── regions = ["us_west"]          (variable-length)
│    └── tenant  = "acme"
└── prefix "src__", stripped

Declare the pattern under the pipeline source in rocky.toml:

[pipeline.bronze.source.schema_pattern]
prefix = "src__"
separator = "__"
components = ["tenant", "regions...", "source"]

Target mapping

Section titled “Target mapping”

Templates on the pipeline target decide where the rows land.

[pipeline.bronze.target]
adapter = "prod"
catalog_template = "warehouse"
schema_template = "stage__{source}"

Rocky fills those templates from the parts it just parsed.

  fivetran_catalog.src__acme__us_west__shopify.orders    ← source table
                   └───────────┬─────────────┘
                               │ schema_pattern splits the schema name
                               ▼
                    tenant  = "acme"
                    regions = ["us_west"]
                    source  = "shopify"
                               │ templates fill from those parts
                               │   catalog_template = "warehouse"  (no variable)
                               │   schema_template  = "stage__{source}"
                               ▼
  warehouse.stage__shopify.orders                        ← target table

For a multi-tenant setup where each tenant gets its own catalog, see Schema Patterns for the {tenant}_warehouse plus components = ["tenant", "regions...", "source"] pattern.

Auto-creation

Section titled “Auto-creation”

Set auto_create_catalogs = true and auto_create_schemas = true, and Rocky creates the target catalog and schema before it copies anything:

CREATE CATALOG IF NOT EXISTS warehouse;
CREATE SCHEMA IF NOT EXISTS warehouse.stage__shopify;

Rocky tags the catalogs it creates (for example managed_by = "rocky") so it can find which catalogs it manages later.

Incremental strategy

Section titled “Incremental strategy”

The first run has no watermark (the timestamp of the newest row Rocky has already copied), so it does a full refresh. Every run after that copies only the rows whose timestamp is newer than the stored watermark:

INSERT INTO warehouse.stage__shopify.orders
SELECT *, CAST(NULL AS STRING) AS _loaded_by
FROM fivetran_catalog.src__acme__us_west__shopify.orders
WHERE _fivetran_synced > TIMESTAMP '2026-04-17 09:30:00'

The literal in that WHERE is the previous run’s MAX(_fivetran_synced). Rocky keeps it in the state store and writes it into the query. It does not read the value back from the target with a subquery.

_fivetran_synced is Fivetran’s built-in column recording when each row was synced. Rocky uses it as the watermark column by default. Change it with timestamp_column.

When Rocky detects schema drift, it responds in graded steps. A safe type widening becomes ALTER COLUMN TYPE. A new column becomes ALTER TABLE ADD COLUMN. Only an unsafe type change falls back to a full refresh, which drops and recreates the target table.

Metadata columns

Section titled “Metadata columns”

Rocky can add metadata columns to the copied tables. Declare them on the pipeline, alongside strategy and timestamp_column:

[pipeline.bronze]
type = "replication"
strategy = "incremental"
timestamp_column = "_fivetran_synced"
metadata_columns = [
    { name = "_loaded_by", type = "STRING", value = "NULL" }
]

Rocky appends them to the SELECT: SELECT *, CAST(NULL AS STRING) AS _loaded_by.

Filtering

Section titled “Filtering”

Scope a run to one tenant:

Terminal window
plan_id=$(rocky --config rocky.toml plan --filter tenant=acme --output json | jq -r .plan_id)
rocky apply "$plan_id"

This processes only the schemas whose parsed tenant component is acme.

Section titled “Related”