Bronze, Silver & Gold

End to end

full_orchestration runs the whole chain in order — Bronze, then Silver, then Gold. The daily schedule is applied here, so triggering this one pipeline runs everything below.

Bronze Raw landing

The daily Bronze run is orchestrated by bronze_orchestration, which runs two pipelines in order. Both are controlled by the Orchestration database — the CONFIG.Processes table holds one row per source table, storing its schema, the exact column list to pull, plus the flags and watermark that tell the pipeline what to load.

Bring in each day's new and changed rows from Snowflake.

• Refresh each table's watermark in CONFIG.Processes to the newest data already loaded.
• Read CONFIG.Processes to get the list of tables switched on for Bronze, and each table's column list.
• For each table, use its watermark to copy only newer rows into the Lakehouse, selecting exactly the columns defined in the config.

The load above only brings live rows, so deletes would otherwise be missed.

• Runs after load_bronze succeeds.
• Finds rows flagged deleted in Snowflake.
• Writes them to a LANDING.DELETED table for downstream layers to action.

• Bronze is a staging area, not history — each table is overwritten every run.
• The column list in CONFIG.Processes is where long text fields are originally split into 8,000-character chunks (e.g. extra1–extra9) to fit column limits. Silver joins them back together.

Silver Cleaned ODS

The daily Silver run is orchestrated by silver_orchestration. Like Bronze, it is driven by the Orchestration database — CONFIG.Processes lists the notebook to run for each table, and which tables are switched on for Silver.

Run each table's notebook in turn to clean the raw Bronze rows and build the lasting history.

• Reassemble split columns — Bronze had to chop long text fields into pieces (e.g. extra1–extra9, cntphone1–9); Silver joins them back into the single original column.
• Combine live + archive — the main table and its _A archive twin are merged into one set of rows, each tagged live or archive.
• Parse packed content — usable values are pulled out of packed fields (e.g. contact name and address, email address, settings).
• Merge into ODS — the finished rows are upserted into the matching ODS table: update existing rows, insert new ones.
• Update the sync date — finally, it writes the newest _FIVETRAN_SYNCED back to ODS.SYNCHDATES. This is the watermark Bronze reads at the start of the next run, so it only advances after a successful load.

A separate notebook, 09 Update Deleted Records, handles removals.

• Reads the LANDING.DELETED table produced by Bronze's Load Bronze Deleted step.
• Removes those records from the matching ODS tables, so deletes in the source are reflected in the history.

• Unlike Bronze, ODS is added to, not overwritten — this is the persistent history.

Gold Business-ready

Currently just views over the ODS tables.

• Today: ODS views exposed for reporting.
• Target: a dimensional model (facts and dimensions) for the Power BI semantic model.