Skip to content

Understanding a Failing Check

This tutorial walks through a real investigation of the check_for_missing_dim_data_in_pnl integrity check. It is based on an actual incident (BUG-2026-02-007) in which the check correctly identified P&L rows with missing dimensional data. By the end, you will understand the general pattern of investigation that applies to any failing check.

The scenario

You open this morning's load report and see the following in fct_integrity_check:

check_name                        num_errors   loaded_at
check_for_missing_dim_data_in_pnl          3   2026-02-26 06:14:00

Three rows returned means three deal-level P&L records in the last 7 days have no valid dimensional data attached to them. The check is Error-severity, so the load has been flagged.

Step 1: Look it up in the catalogue

You open the Check Catalogue and find the entry for check_for_missing_dim_data_in_pnl.

The catalogue tells you:

  • What it checks: rows in fct_pnl_deal within the past 7 days where dim_deal_attribute_id = 0 or dim_deal_sub_attribute_id = 0 — sentinel values meaning "dimension not resolved."
  • What failure means: one or more deals in the P&L have no dimensional attributes (portfolio, instrument class, counterparty) attached. Reporting against those deals will be incomplete or attributed to an "unknown" category.
  • Suggested steps: query fct_pnl_deal for rows with zero dimension IDs; check whether the affected deals appear in dim_deal_attribute; if they are missing there, a dimensional model refresh is required.

Step 2: Understand what the check is telling you

The invariant is: every deal that appears in fct_pnl_deal in the last 7 days must have been joined to a valid dim_deal_attribute row. A dim_deal_attribute_id of 0 is the sentinel value the pipeline uses when the join fails — it means the deal was booked in Endur but its dimensional attributes have not yet been loaded into the dimensional model.

This is an important operational distinction. The P&L number itself may be correct (the deal was simulated), but without dimensional data you cannot attribute it to a portfolio, instrument type, or counterparty. Reports that group or filter by those dimensions will either exclude the deal or place it in an "unknown" bucket.

Step 3: Run the diagnostic query

Query fct_pnl_deal to find which deals are affected:

SELECT
    deal_id,
    MIN(metric_date) AS missing_from,
    MAX(metric_date) AS missing_to,
    COUNT(*)         AS affected_rows
FROM fct_pnl_deal
WHERE (dim_deal_attribute_id = 0 OR dim_deal_sub_attribute_id = 0)
  AND metric_date >= DATEADD('day', -7, CURRENT_DATE())
GROUP BY deal_id
ORDER BY missing_from;

In the BUG-2026-02-007 incident, this returned a single deal — deal_id = 42545696 — appearing across six metric dates (2026-02-19 to 2026-02-24) with sentinel dimension IDs in both columns.

Step 4: Interpret the results

Look at the deal IDs that appear. Consider:

  • Are these deal IDs new? A deal booked in Endur that same week may not yet have had its dimensional attributes loaded. New deals require a run of the dimensional model refresh to be picked up.
  • Are these deal IDs familiar? An established deal appearing with zero dimension IDs suggests a pipeline ordering problem — the dimensional data may have been temporarily unavailable when the P&L model ran.
  • How many metric dates are affected? A single day suggests a transient issue; multiple days suggest the gap has persisted across several loads.

In the BUG-2026-02-007 case, deal 42545696 appeared across six consecutive dates. This ruled out a single-load timing issue and pointed to a structural problem in the pipeline's exclusion logic — specifically, a model was routing the deal through a "cancelled" branch of the P&L pipeline while the main BAV branch was also carrying it, producing a duplicate row with zero sentinel keys.

Step 5: Check whether the deal appears in dim_deal_attribute

For each affected deal ID, query the dimensional model:

SELECT *
FROM dim_deal_attribute
WHERE tran_id IN (<affected deal IDs>)
ORDER BY tran_version DESC;
  • If rows exist: the dimensional data is loaded; the problem is that the P&L pipeline is not joining to it correctly. This is a pipeline logic issue — escalate to the data engineering team with the deal IDs and affected metric dates.
  • If no rows exist: the dimensional data has not been loaded for these deals. The most common cause is a new deal that has not yet been processed by the dimensional model refresh. In this case:
  • Confirm the deal exists in Endur (check with the trading desk or operations team).
  • Wait for the next full load cycle and re-check fct_pnl_deal.
  • If the deal still has zero dimension IDs after one full load cycle, escalate.

Step 6: Resolution

In the BUG-2026-02-007 incident, the investigation found that deal 42545696 was appearing in both the BAV (best-available-value) pipeline and the cancelled-deal pipeline simultaneously — the exclusion logic between the two was checking against the wrong upstream model, allowing the deal to pass through both branches. The cancelled branch produced zero sentinel dimension IDs because it could not resolve dimension keys for what was, in fact, an active deal.

The fix was a pipeline code change in core_endur_metric_deal_cancelled. After the fix was deployed and the model rebuilt, check_for_missing_dim_data_in_pnl passed cleanly.

If you escalate a failing instance of this check to the data engineering team, include:

  • The deal IDs returned by the diagnostic query
  • Whether those deal IDs appear in dim_deal_attribute
  • The date range of affected metric dates
  • Whether this is a new check failure or has persisted across multiple loads

What you have learned

Every failing integrity check follows the same pattern:

  1. Find the check name in fct_integrity_check.
  2. Look it up in the Check Catalogue to understand what invariant has been violated.
  3. Run targeted SQL against the named models to identify the specific rows, deals, or dates that are failing.
  4. Determine the cause — is this a new deal waiting for dimensional data, a timing artefact, or a pipeline logic issue?
  5. Remediate or escalate — self-heal if the cause is timing, escalate with evidence if it requires engineering intervention.

The Investigate a Failing Check guide generalises this process for any check in the catalogue.