Turner & Townsend / Fabric Medallion

Sub Contractor Invoicing Reconciliation

← Back to Projects
Reconciliation Summary
Fixes applied

Layer-by-layer reconciliation

Layer Metric SQL Views Notebook Diff (NB − Duf) % Diff Balance
Base Rows 20,725,661 20,725,661 0 0.00000% Match
Fact Rows 1,091,811 1,091,811 0 0.00000% Match
Level 2 Rows 166,164 166,396 +232 +0.13962% Amounts Matched
Level 2 SUM 62,948,788,740.34 62,948,744,322.29 −44,418.05 −0.00007% Match (drift)
Final SUM 62,947,556,151.27 STATIC 62,948,744,322.29 LIVE +1,188,171.02 +0.00189% SQL Final stale
Final SUM
(vs SQL Live Level 2)		 62,948,788,740.34 LIVE 62,948,744,322.29 LIVE −44,418.05 −0.00007% Match (drift)

LIVE reads through to bronze each query. STATIC reads from [DW].[tblVwFactSubContractorInvoicing_Customer], refreshed on ETL schedule.

Notebook Final is a pure pass-through of Notebook Level 2

Notebook table SUM(AccountingSubconInvoiceAmount)
legacy.vwfactsubcontractorinvoicing_subcon62,948,744,322.288660
legacy.vwfactsubcontractorinvoicing_pmsummary_nonpo62,948,744,322.288660
Diff0.000000

Fixes applied

  1. Cell 35

    Base — align WHERE filter with SQL Views

    Added col("recid").isNotNull() to the filter and removed .distinct(). Matches the SQL view's (recid IS NOT NULL OR recid <> 0) AND IsDelete IS NULL.
    Creates notebook temp view df_vwprojpostedtranstable  |  equivalent of SQL view [ax].[vwPROJPOSTEDTRANSTABLE] 
    .filter(
    col("IsDelete").isNull() & col("recid").isNotNull()
)
    Base row count balanced at 20,725,661
  2. Cell 147

    Final — remove DISTINCT from pmsummary_nonpo CTE and outer SELECT

    The WITH a AS (SELECT DISTINCT ...) block at the top of pmsummary_nonpo included the amount columns in its DISTINCT, and dropped VendorKey, ProjectCategoryKey, InternalVendorName, SubConInvoiceDate, and CustomerInvoiceDate from the projection. Any two Level 2 rows that shared identical values across the selected columns (including the amounts) but differed on one of the dropped columns were collapsed into a single row, silently losing the second row's amount contribution. The outer GROUP BY already aggregates to the correct grain on its own, so the DISTINCT was both redundant and destructive.
    Creates notebook delta table legacy.vwfactsubcontractorinvoicing_pmsummary_nonpo  |  equivalent of SQL view [TNTCore].[VwFactSubContractorInvoicing_Customer] 
    WITH a AS (
    SELECT                        -- was: SELECT DISTINCT
        sc.ProjectKey, ..., sc.PurchaseOrderNumber,
        SubConRevenue, ..., AccountingSubconInvoiceAmount, ...
    FROM subcon sc
)
SELECT                            -- was: SELECT DISTINCT
    ProjectKey, ..., SUM(AccountingSubconInvoiceAmount), ...
FROM a
GROUP BY ProjectKey, ...
    Recovered £1,075,252,864.39 — Final SUM went from 61,873,491,457.90 to 62,948,744,322.29