Development Documentation
View as:

Runbook: Split Bronze Dataverse shortcuts by environment (PROD vs UAT)

Problem

The DEV Bronze lakehouse (Lakehouse_Bronze, workspace DEV-Bronze) had 11 core business-table shortcuts (salestable, salesline, purchtable, purchline, lgslogisticfiletable, lgslogisticfileline, inventtrans, inventdim, inventbatch, inventtransorigin, prodtable) pointing at the UAT Dataverse (operations-geris-uat.crm4.dynamics.com) instead of PROD (operations-geris-prod.crm4.dynamics.com).

Mechanism: the manifest deployment/bronze-dataverse-shortcuts.json contained both a UAT and a PROD entry for each of the 11 tables under the same (path="Tables/dataverse_d365", name=<table>) key. Fabric's shortcut-create API returns 409 on the second POST, so whichever entry was first in the JSON (UAT, for all 11) permanently won.

Effect: DEV Bronze-queried lgslogisticfiletable lagged the PROD GL table by ~200 logistic files (LF012087 onwards). dim_logistics starts from stg_dataverse__lgslogisticfiletable, so those LFs and their joined costs (including ~€1,928 of Iran-diesel EXCEPT surcharge) were silently dropped from every downstream model.

Target state

Two schemas coexist in Bronze:

  • Tables/dataverse_d365 — PROD only (all 96 PROD entries)
  • Tables/dataverse_d365_uat — UAT only (the 11 previously-duplicated tables that the business explicitly wants available for UAT inspection)

Fix — in order

Step 1 — manifest change (already applied by this runbook's commit)

deployment/bronze-dataverse-shortcuts.json now routes the 11 UAT entries to Tables/dataverse_d365_uat. No more duplicate (path, name) keys. scripts/deploy_dataverse_shortcuts.py enforces this going forward via validate_manifest() — failed with exit code 2 if duplicates reappear.

Step 2 — destructive reconciliation against DEV Bronze

The 11 shortcuts currently in Fabric still point at UAT (pre-fix state). They need to be deleted and recreated. Run the reconciler:

# DEV Bronze
export FABRIC_USE_CLI=true
az login  # as geris_fabric_admin@geris.nl

python scripts/deploy_dataverse_shortcuts.py \
    --workspace-id b2fa1b21-7281-48bd-9e08-b4c6eac3f7ff \
    --lakehouse-id 8ed75b41-ce1f-41bb-9a72-160c85138eb3 \
    --reconcile \
    --dry-run

Review the MISMATCH lines in the output — you should see 11 have=...uat... want=...prod... entries. Then:

python scripts/deploy_dataverse_shortcuts.py \
    --workspace-id b2fa1b21-7281-48bd-9e08-b4c6eac3f7ff \
    --lakehouse-id 8ed75b41-ce1f-41bb-9a72-160c85138eb3 \
    --reconcile

Expected output: 0 created, 11 reconciled, 96 up-to-date, 0 mismatched (not reconciled), 0 failed. Plus 11 creates under the new Tables/dataverse_d365_uat path.

Step 3 — refresh Bronze SQL metadata

Fabric SQL endpoints cache delta table metadata. Force a refresh so dbt sees the new shortcut targets:

python scripts/refresh_bronze_tables.py \
    --bronze-server <BRONZE_SQL_ENDPOINT_SERVER> \
    --gold-server <WAREHOUSE_SERVER> \
    --gold-database Gold_Warehouse

(The dbt-dev-build pipeline does this automatically before each run, so you can also just re-trigger that pipeline.)

Step 4 — rebuild dbt

Re-trigger dbt-dev-build (auto-triggered by the next push to main, or run manually). dim_logistics, fact_trade, fact_customerinvoice, fact_vendinvoice, etc. will pick up the ~200 missing LFs and their joined costs.

Step 5 — verify

Run this diagnostic against Gold_Warehouse:

-- Should return 0 (GL LF IDs missing from lgslogisticfiletable)
SELECT COUNT(DISTINCT logistic_file_id_ledger)
FROM dbo.fact_generalledgertransactions gl
LEFT JOIN prep.stg_dataverse__lgslogisticfiletable lft
    ON lft.logistic_file_id = gl.logistic_file_id_ledger
WHERE gl.logistic_file_id_ledger LIKE 'LF%'
  AND gl.accounting_date >= '2025-01-01'
  AND lft.logistic_file_id IS NULL;

Before the fix this returned 202. After Step 4 it should be 0 (or very close — a one-business-day lag is normal if the shortcut mirror is not instantaneous).

Apply the same to UAT / PROD Bronze

release/uat and release/prod branches use their own workspace + lakehouse IDs (see deployment/uat.yml, deployment/prod.yml). Repeat Steps 2–5 against each environment with its own IDs. Expect the PROD Bronze to have been lucky (the 11 UAT entries point at operations-geris-uat, which prod-data queries would never touch) but reconcile regardless so the schema split is consistent across environments.

Prevent recurrence

  • validate_manifest() in deploy_dataverse_shortcuts.py blocks duplicate (path, name) keys with conflicting targets at deploy time.
  • Consider adding a monitoring query to dbt/models/marts/monitoring/ that alerts when the Step-5 count exceeds a threshold.