Reapit

Silver Layer

← Back to Projects

Load ODS Pipeline

How load_ods Works
The Silver ODS load is driven from configuration — the pipeline itself contains no notebook list. All notebook names live in CONFIG.Processes.SilverNotebookName, and the pipeline iterates whatever the query returns. Keeping that table aligned with what actually exists in the Load ODS folder is the single point of maintenance.
  1. Script activity get_notebooks_to_run runs against Bronze OrchestrationDB warehouse:
    SELECT * FROM CONFIG.Processes WHERE IngnoreInSilverETL = 0
  2. ForEach activity for_each_notebook iterates the returned rows sequentially.
  3. For each row, it invokes the orchestrator notebook 00_run_notebook (Trident notebookId bb1d3bda-f3f4-baf3-4e0d-2c416a224efb), passing SilverNotebookName as parameter NotebookName.
  4. 00_run_notebook resolves the name to a notebook in the Load ODS folder and invokes it — that’s where the actual merge/insert SQL runs against the Silver Lakehouse.
Orchestration Sync Rules
For every notebook that should run as part of Silver ODS, there must be a row in CONFIG.Processes where:
Notebooks not in the list, or with IngnoreInSilverETL = 1, are skipped. The order the pipeline executes them is the order returned by the SELECT — add ORDER BY to the query if strict sequencing matters.

Silver Load Progress (DEV / QA / UAT)

Manual Notebook Runs
Running each Silver ODS notebook one at a time via LoadSilverManually. Records Bronze source counts (from initial load), Silver target counts (DEV and QA), Gold view counts (UAT) and status per notebook.
# Notebook Bronze Source(s) Bronze Count Silver Count (DEV) Silver Count (QA) Gold Count (UAT) Delta Status
01 01_merge_lookup LANDING.LOOKUP + LANDING.LOOKUP_A 8,628,002 8,627,786 8,627,786 8,627,786 −216 Loaded
02 02_merge_lld LANDING.LLD + LANDING.LLD_A 190,019 190,019 190,019 190,019 0 Loaded
03 03_merge_cnt LANDING.CNT + LANDING.CNT_A 4,674,756 4,674,756 4,674,756 4,674,756 0 Loaded
04 04_merge_invhead LANDING.INVHEAD 427,186 427,186 427,186 427,186 0 Loaded
05 05_merge_negs LANDING.NEGS 9,754 9,754 9,754 9,754 0 Loaded
06 06_merge_office LANDING.OFFICE 725 725 725 725 0 Loaded
07 07_merge_prp LANDING.PRP + LANDING.PRP_A 1,219,531 1,219,531 1,219,531 1,219,531 0 Loaded
08 08_merge_appimp LANDING.APPIMP 3,169,223 3,169,223 3,169,223 3,169,223 0 Loaded
10 10_insert_jnl LANDING.JNL 36,119,141 36,100,709 36,100,709 36,100,709 −18,432 Loaded
11 11_merge_app LANDING.APP + LANDING.APP_A 3,821,976 3,821,976 3,821,976 3,821,976 0 Loaded
12 12_merge_areas LANDING.AREAS 3,798 3,798 3,798 3,798 0 Loaded
13 13_merge_diary LANDING.DIARY + LANDING.DIARY_A 6,258,216 6,258,215 6,258,215 6,258,215 −1 Loaded
14 14_merge_extra LANDING.EXTRA 4,091,216 4,091,216 4,091,216 4,091,216 0 Loaded
15 15_merge_idcheck LANDING.IDCHECK 615,378 615,378 615,378 615,378 0 Loaded
16 16_merge_intl LANDING.INTL 422 422 422 422 0 Loaded
17 17_merge_mailings LANDING.MAILINGS 1,830,348 1,830,348 1,830,348 1,830,348 0 Loaded
18 18_merge_offers LANDING.OFFERS + LANDING.OFFERS_A 734,715 734,715 734,715 734,715 0 Loaded
19 19_merge_referrals LANDING.REFERRALS 51,843 51,843 51,843 51,843 0 Loaded
20 20_merge_renewals LANDING.RENEWALS 72,379 72,379 72,379 72,379 0 Loaded
21 21_merge_src LANDING.SRC 1,678 1,678 1,678 1,678 0 Loaded
22 22_merge_pp_cnt LANDING_PP.CNT + LANDING_PP.CNT_A 11,621 11,608 11,608 11,608 −13 Loaded
23 23_merge_pp_idcheck LANDING_PP.IDCHECK 1,856 1,856 1,856 1,856 0 Loaded
24 24_merge_pp_lookup LANDING_PP.LOOKUP + LANDING_PP.LOOKUP_A 64,686 45,689 45,689 45,689 −18,997 Loaded
25 25_merge_tenext LANDING.TENEXT 493,132 493,132 493,132 493,132 0 Loaded
26 26_merge_workflow LANDING.WORKFLOW 248 248 248 248 0 Loaded
27 27_merge_pic LANDING.PIC + LANDING.PIC_A 4,914,747 4,914,731 4,914,731 4,914,731 −16 Loaded
28 28_merge_fileman LANDING.FILEMAN 13,580,097 13,580,097 13,580,097 13,580,097 0 Loaded
29 29_merge_pp_negs LANDING_PP.NEGS 247 247 247 247 0 Loaded
30 30_merge_configsettings LANDING.CONFIGSETTING 6,021 6,021 6,021 6,021 0 Loaded
31 31_merge_permissionset LANDING.PERMISSIONSET 396 396 396 396 0 Loaded
32 32_merge_permissiongrp LANDING.PERMISSIONGRP 270 270 270 270 0 Loaded
33 33_merge_bankstat LANDING.BANKSTAT 321,387 321,387 321,387 321,387 0 Loaded
34 34_merge_nomalloc LANDING.NOMALLOC 2,001,438 2,001,438 2,001,438 2,001,438 0 Loaded
35 35_merge_nomtran LANDING.NOMTRAN 3,103,809 3,103,809 3,103,809 3,103,809 0 Loaded
36 36_merge_paid LANDING.PAID 7,328 7,328 7,328 7,328 0 Loaded
37 37_merge_payments LANDING.PAYMENTS 754,791 754,791 754,791 754,791 0 Loaded
38 38_merge_ten LANDING.TEN — — 153,802 153,802 — Loaded

Load ODS Notebook Inventory

All Notebooks in SilverGold/Orchestration/Load ODS/
38 notebooks total. Each merge/insert notebook should have a corresponding CONFIG.Processes row with SilverNotebookName set to the name in the table below. Confirmed against branch InitialSilverDevelopment.
# Notebook Bronze Source Type Notes
Utility — not driven by CONFIG.Processes
0000_functions—UtilityShared helper functions imported by other notebooks
0000_run_notebook—OrchestratorCalled by pipeline; resolves NotebookName and invokes the target notebook
SAU — merges / inserts (driven by CONFIG.Processes)
0101_merge_lookupLOOKUP (SAU)Merge
0202_merge_lldLLDMerge
0303_merge_cntCNT (SAU)Merge
0404_merge_invheadINVHEADMerge
0505_merge_negsNEGS (SAU)Merge
0606_merge_officeOFFICEMerge
0707_merge_prpPRPMerge
0808_merge_appimpAPPIMPMerge
0909 Update Deleted Records - TO Do—TO DOPlaceholder for tracking deletes across ODS tables
1010_insert_jnlJNLInsertAppend-only, not a merge
1111_merge_appAPPMerge
1212_merge_areasAREASMerge
1313_merge_diaryDIARYMerge
1414_merge_extraEXTRAMerge
1515_merge_idcheckIDCHECK (SAU)Merge
1616_merge_intlINTLMerge
1717_merge_mailingsMAILINGSMerge
1818_merge_offersOFFERSMerge
1919_merge_referralsREFERRALSMerge
2020_merge_renewalsRENEWALSMerge
2121_merge_srcSRCMerge
PP — merges (driven by CONFIG.Processes)
2222_merge_pp_cntCNT (PP)Merge
2323_merge_pp_idcheckIDCHECK (PP)Merge
2424_merge_pp_lookupLOOKUP (PP)Merge
SAU — continued
2525_merge_tenextTENEXTMerge
2626_merge_workflowWORKFLOWMerge
2727_merge_picPICMerge
2828_merge_filemanFILEMANMerge
PP — continued
2929_merge_pp_negsNEGS (PP)Merge
SAU — continued
3030_merge_configsettingsCONFIGSETTINGMerge
3131_merge_permissionsetPERMISSIONSETMerge
3232_merge_permissiongrpPERMISSIONGRPMerge
3333_merge_bankstatBANKSTATMerge
3434_merge_nomallocNOMALLOCMerge
3535_merge_nomtranNOMTRANMerge
3636_merge_paidPAIDMerge
3737_merge_paymentsPAYMENTSMerge

Schema vs Merge — Actions to Address

Audit Summary
Comparison of column definitions in create_or_alter_ods_schema against the columns actually written by the 36 merge/insert notebooks in Load ODS/. Confirmed against branch InitialSilverDevelopment. Result: 28 tables aligned, 8 with issues, 1 orphan table. No merge writes a column entirely absent from the schema — all issues are dead DDL columns, type mismatches, or naming inconsistencies.
Merge Behaviour Fixes
The merge notebook and the schema disagree on what should be written or how.
Priority Target Action
High ODS.INVHEAD Schema declares LASTUPDATED TIMESTAMP but 04_merge_invhead does not set it. Every other table with LASTUPDATED populates it via "LASTUPDATED": "current_timestamp()". Add the same behaviour to the INVHEAD merge.
High ODS.LLD.ARCHIVE Schema DDL declares ARCHIVE STRING, but 02_merge_lld writes F.lit(0) / F.lit(1) (int). Other tables have ARCHIVE INT (LOOKUP, EXTRA) or BOOLEAN (~16 others). LLD is the outlier. Decide the canonical type and align both sides.
Dead Columns in Schema
Declared in the DDL but never populated by any merge. Drop from the DDL, or start populating them.
Priority Column Notes
Med ODS.LLD.SYNCHDEL Declared INT. 02_merge_lld builds its dataframe with .select(...) and never aliases SYNCHDEL.
Med ODS.PRP.PEPCEXEMPT Declared but the merge's dynamic insert_map doesn't include it. Confirm whether the field was renamed or dropped in source.
Med ODS.JNL.JNLID Unused ID column — same pattern as the deliberately-excluded ID columns below.
Med ODS.CONFIGSETTING.CONFIGSETTINGID Declared BIGINT. 30_merge_configsettings selects _FIVETRAN_ID, LEVEL, VALUE, NAME, CODE, _FIVETRAN_INDEX, _FIVETRAN_SYNCED, _FIVETRAN_DELETED — will always be NULL.
Low ODS.PERMISSIONSET.PERMISSIONSETID Declared BIGINT. Deliberately excluded by the merge via exclude_columns = {"PERMISSIONSETID"}.
Low ODS.PERMISSIONGRP.PERMISSIONGRPID Declared BIGINT. Same deliberate-exclusion pattern.
Orphan Schema Table
Declared in the DDL but no merge notebook populates it.
Priority Target Action
Med ODS.TEN 51 columns declared but no Load ODS/*_merge_ten.Notebook exists. 25_merge_tenext targets ODS.TENEXT, not TEN. Also listed as a gap in the Bronze inventory below — decide whether to add a merge or drop the DDL.
Naming Inconsistencies
Cosmetic but worth normalising for readability and grep-ability.
Priority Target Action
Low 01_merge_lookup References ODS.lookup (lowercase); every other merge uses uppercase ODS.<TABLE>. Works because Spark is case-insensitive but should be normalised.
Low 30_merge_configsettings / ODS.CONFIGSETTING Notebook filename is plural (configsettings) but targets a singular table (CONFIGSETTING). Rename one so filename and target match.

Gold Views vs ODS — Actions to Address

Audit Summary
Comparison of the 37 ODS.* views in Reapit_Gold_WH.Warehouse/ODS/Views/ against the ODS table DDL in create_or_alter_ods_schema. Surfaced during the COLLATE pass on branch working-silver. Note: the cross-check was thorough for some views and spot-check for others — findings below are what was flagged, but a full systematic column-by-column reconciliation is still pending.
Type Narrowing (BIGINT → int)
Views cast identifier columns to int when the DDL declares them as BIGINT. Any value above 231−1 would truncate silently. Also flagged earlier as dead columns in the schema (never populated by the merge) — but if that ever changes, this narrowing becomes a live risk.
PriorityTargetAction
Med CONFIGSETTING.CONFIGSETTINGID DDL BIGINT; view casts to int. Widen the view cast to bigint or resolve the dead-column issue.
Med PERMISSIONGRP.PERMISSIONGRPID Same pattern — DDL BIGINT, view casts to int.
Med PERMISSIONSET.PERMISSIONSETID Same pattern — DDL BIGINT, view casts to int.
Views Over Dead Columns / Empty Tables
Gold projects columns / tables that will always be NULL or empty until the upstream merge behaviour is fixed. Cross-references earlier Schema vs Merge findings.
PriorityTargetAction
Accepted PRP view — pEPCexempt Decision: keep column, accept NULL — source does not ship an EPC exempt flag.
Investigation (2026-07-03): Reapit LANDING.PRP has no PEPCEXEMPT column. The EXTRA1..EXTRA9 blob was scanned across all 549,993 rows — zero rows contain a pEPCExempt or pEPCValue key. 15,181 rows do contain EPC-related data, but only under keys pHipEer, pHipEerPot, pHipEir, pHipEirPot, pEpcDate — none represents an exemption flag.
Consequence: Gold view returns NULL for pEPCexempt. Acceptable — accurate reflection of "source does not track this".
Related opportunity (separate task): the ODS DDL also declares EPCVALUE (unpopulated). extra_int("pHipEer") would populate it. Add richer EPC fields (pHipEerPot, pEpcDate etc) if the business wants them — discuss with stakeholders before wiring up.
High TEN view — missing merge Decision: write missing merge notebook.
Investigation (2026-07-03):
  • LANDING.TEN exists: 126,672 rows, 51 columns.
  • LANDING.TEN_A (archive) exists: 27,130 rows.
  • ODS DDL declares 51 columns; Gold view [ODS].[TEN] reads from Reapit_Silver_LH.ODS.ten.
  • No *_merge_ten.Notebook in Load ODS/. 25_merge_tenext targets the extended-attributes table (TENEXT), not this one.
Consequence: Gold view returns 0 rows; ~127k tenancy header records missing from Silver + Gold.
Action — author XX_merge_ten.Notebook following the CNT/PRP pattern:
  • Read LANDING.TEN + LANDING.TEN_A, union with derived ARCHIVE flag.
  • Build EXTRA via get_combined_or_single_column (LANDING has EXTRA1..EXTRA9; ODS has single EXTRA).
  • Direct-cast 42 columns that map 1:1 between LANDING and ODS.
  • Extract 6 fields from EXTRA via extra_string("<key>"): TREASONFORRENTING, TRENTFUNDEDBY, TTENANTPREVIOUSTENURE, HOUSEINCOMEAMOUNT, THOUSEHOLDINCOMETYPE, T1CODE. Confirm exact key names by sampling EXTRA blob first.
  • TWRENTC DECIMAL(19,4) — likely FX-converted weekly rent; needs business rule confirmation before implementation.
  • Set LASTUPDATED = current_timestamp().
  • Add matching row to CONFIG.Processes with IngnoreInSilverETL = 0. Slot logically before 25_merge_tenext.
Silently Dropped Columns
Column exists in the ODS DDL but the Gold view does not project it.
PriorityTargetAction
Accepted LLD view — SYNCHDEL Decision: leave as-is.
Investigation (2026-07-03): Chain is coherent for LLD — DDL declares SYNCHDEL INT, but neither 02_merge_lld populates it nor the Gold view projects it. Column silently exists as NULL in ODS.LLD; nothing downstream sees it. INTL takes a different position for the same-named column (populated and projected).
Note: DDL declares SYNCHDEL INT on 6 ODS tables (schema lines 90, 1506, 1544, 1575, 1630, 1662). Project-wide decision about soft-delete tracking in Silver is deferred until there is a downstream need.
Type Widenings and Cosmetic
Informational only — safe and/or cosmetic. Log for awareness.
PriorityTargetAction
Accepted BANKSTAT view Decision: leave as-is.
Investigation (2026-07-03): DDL declares SYNCHREC, STATDATE, AMEND, REGISTER as DATE; view casts to datetime2. BANKSTAT DDL is the outlier — every other table with these column names uses TIMESTAMP. Cast is safe (no data loss). View is consistent with all other views. No downstream impact.
Accepted PAYMENTS view Decision: leave as-is (same as BANKSTAT).
Investigation (2026-07-03): DDL declares SYNCHREC, PAID, STATDATE, REGISTER, AMEND as DATE; view casts to datetime2. Cast is safe. View consistent with all other views. PAYMENTS DDL is an outlier — other tables use TIMESTAMP. No downstream impact.
Accepted OFFERS view Decision: leave as-is.
Investigation (2026-07-03): Line 39 aliases CAST([PERCENTAGEPURCHASEPRICEREQUIRED] AS int) AS [%PurchasePriceRequired]. The % is a shorthand for "percentage" and is valid inside bracketed identifiers. Rename would be a breaking change for any downstream consumer (Power BI, DAX, reports) already referencing the current alias — not worth the risk for a cosmetic tidy-up.
Outstanding: Full Column Reconciliation
The pass so far mixed thorough and spot-check reviews. For a definitive answer, we still need a systematic column-by-column diff across all 37 views — for every table, list view-columns-not-in-DDL and DDL-columns-not-in-view. Not yet done.

Gaps vs Bronze Inventory

Bronze has 55 source tables. Load ODS currently covers 36 (34 merges, 1 insert, 1 TO DO placeholder). The following Bronze tables have no Silver ODS notebook yet — decide per table whether they need one or should be flagged IngnoreInSilverETL = 1:

Gold

To be documented.