Reapit — Orchestration

← Back to Projects

Bronze — Daily Incremental Load

Pipeline load_bronze runs the update_watermark Script activity. It sets CONFIG.Processes.WatermarkValue = MAX(SYNCHDATE) per table from Reapit_Bronze_DWH.ODS.synchdates. This is the value the next run will filter on. Currently deactivated (state: Inactive) — the watermark is not being refreshed automatically.
get_bronze_tables Script activity reads SELECT * FROM CONFIG.Processes WHERE IngnoreInBronzeETL = 0.
for_each_bronze_table ForEach iterates the returned rows sequentially.
For each row, the source SELECT is built dynamically. The date used in the WHERE clause is CONFIG.Processes.WatermarkValue for that row — unless it is empty or earlier than the pipeline variable v_MinWatermarkValue (2023-09-20 17:03:14), in which case that floor is used instead.
Query filter:
- isIncremental = 1 → WHERE {WatermarkField} > '{date from step 4}' AND SynchDel = 0
- isIncremental = 0 → WHERE SynchDel = 0
- TableName = 'CONFIGSETTING' → no SynchDel filter (column does not exist)
land_bronze Copy activity runs the query against Snowflake and writes to Lakehouse {TargetSchemaName}.{TableName}.
Sink action is OverwriteSchema. The Bronze table is dropped and recreated on every run — it holds only the rows returned by this iteration's query, not accumulated history. Persistent history lives downstream in Silver / ODS.
Loop advances to the next row; on completion the pipeline ends. Step 1's watermark refresh will run at the start of the next scheduled execution (once reactivated).

Silver — Load ODS

Pipeline load_ods runs the get_notebooks_to_run Script activity: SELECT * FROM CONFIG.Processes WHERE IngnoreInSilverETL = 0.
for_each_notebook ForEach iterates the returned rows sequentially.
For each row, a TridentNotebook activity invokes the dispatcher notebook 00_run_notebook, passing NotebookName = item().SilverNotebookName. Activity timeout: 12 hours.
00_run_notebook calls notebookutils.notebook.run(NotebookName, 10800) — 180 minute timeout per notebook.
The target merge notebook (e.g. 01_merge_lookup, 03_merge_cnt) runs against the Reapit_Silver_LH lakehouse. It reads the current Bronze landing tables (LANDING.* or LANDING_PP.*) using spark.table(...), projects and reshapes the columns, and combines active / archive sources where relevant.
The notebook then performs a Delta MERGE into the corresponding ODS.{table}: whenMatchedUpdate refreshes columns and sets LASTUPDATED = current_timestamp(); whenNotMatchedInsert adds new rows. ODS tables are the persistent history — unlike Bronze, they are upserted, not overwritten.
After the merge, the notebook computes MAX(_FIVETRAN_SYNCED) per source landing table and MERGEs those values into ODS.SYNCHDATES (columns REAPITTABLE, SYNCHDATE). This is the table Bronze's update_watermark reads at the start of the next daily run.
If the merge or synchdate update raises an exception, the notebook re-raises — the ForEach iteration fails and the pipeline moves on to the next row (no stopOnFailure). ODS.SYNCHDATES is only updated when the corresponding merge succeeds, so a failing table cannot advance its own watermark.
Loop advances to the next row; on completion the pipeline ends.

Gold

To be documented.

End-to-end sync review

Data sync coverage

Inserts and updates propagate. Bronze filters WHERE {WatermarkField} > '{WatermarkValue}' (typically _FIVETRAN_SYNCED), Silver upserts into ODS on primary key.
Deletes do not propagate. Bronze filter WHERE SynchDel = 0 excludes Reapit soft-deletes. Silver MERGE only does whenMatchedUpdate / whenNotMatchedInsert — no whenMatchedDelete. Fivetran-flagged deletes (_FIVETRAN_DELETED) are copied as a column but not acted on. Placeholder notebook 09 Update Deleted Records - TO Do confirms this is a known open item.
CONFIGSETTING has no SynchDel column — deletes cannot even be detected on that table.
Watermark boundary is > not >=. Rows sharing the exact boundary timestamp could be skipped — low risk with millisecond-precision Fivetran timestamps, but not zero.
New source tables added to CONFIG without a matching Silver merge notebook will land in Bronze but never reach ODS.

Bronze → Silver → Bronze feedback loop

Design is correct. Silver only updates ODS.SYNCHDATES after its MERGE succeeds (same try block, exception re-raises). Bronze's update_watermark reads from ODS.SYNCHDATES at the start of the next run, so a table's watermark cannot advance past what has actually been merged. A failing Silver table stays on the old watermark and re-attempts next run.
Confirm the shortcut wiring. Silver notebooks run with default lakehouse Reapit_Silver_LH but call spark.table("LANDING.LOOKUP") (a Bronze schema) and DeltaTable.forName(spark, "ODS.SYNCHDATES"). Bronze's update_watermark reads [Reapit_Bronze_DWH].[ODS].[synchdates]. Both references only line up if LANDING and ODS are shortcuts in Reapit_Silver_LH pointing at Bronze. A missing shortcut breaks the loop silently. Confirm via the Create Shortcuts.Notebook in Bronze.
Currently update_watermark is deactivated. Bronze falls back to v_MinWatermarkValue = 2023-09-20 17:03:14, so every run pulls everything since that date. Correct but expensive — reactivate once the shortcut check above is confirmed.

SQL Endpoint / Lakehouse refresh risks

SQL Endpoint metadata lag. Spark writes land in Delta immediately; the SQL view can lag from seconds to a few minutes. Daily schedule — not an issue. Back-to-back manual runs of Silver then Bronze — Bronze could read a stale synchdates and re-pull the same window (idempotent, just wasteful). Same behaviour we hit when the initial-load count check showed 12 tables and then 20 a minute later.
OverwriteSchema on Bronze drops and recreates the Delta table every run. The SQL Endpoint briefly reports "no such table" during the recreate; any downstream Power BI Import or SQL Endpoint query hitting the table during that window will hiccup. Silver reads via spark.table() directly on Delta so it is immune.
Cross-artifact reads via SQL Endpoint (the Bronze update_watermark join to Reapit_Bronze_DWH.ODS.synchdates) are the most exposed to metadata lag. For tighter chaining, run the watermark update as a Spark task inside a Silver notebook — direct Delta reads bypass the SQL Endpoint entirely.
applyVOrder: false on Bronze sink — no impact on sync, just means Delta files aren't reordered for Power BI read performance. Fine for a landing zone.
180-minute timeout in 00_run_notebook for Silver merges. Not a sync bug but a run-time risk on very large tables (JNL 36M rows, wide fact tables).

Recommended actions (in order)

Fix deletes: either widen Bronze filter to include soft-deleted rows and teach Silver MERGE to whenMatchedDelete on SynchDel = 1 / _FIVETRAN_DELETED = true, or complete notebook 09 Update Deleted Records as a separate diff sweep.
Verify LANDING and ODS shortcuts exist in Reapit_Silver_LH before reactivating update_watermark.
Reactivate update_watermark in load_bronze.
Handle CONFIGSETTING deletes with a full-diff notebook (no SynchDel to filter on, so needs a set-difference against source).
Add a pre-flight guardrail: fail load_ods if get_notebooks_to_run returns a row whose SilverNotebookName does not exist as a notebook in the workspace.