Skip to content

Investigate a Failing Check

This guide walks through the standard six-step process for investigating any integrity check failure. The process is the same regardless of which check has fired.

Before you start

Confirm that the failure is real by checking fct_integrity_check in Snowflake. A check that shows num_errors > 0 in the most recent load is active. Note the check_name and num_errors before proceeding.

Step 1: Identify the check

Find the check name from the load report or from fct_integrity_check. Look it up in the Check Catalogue using your browser's find function (Ctrl+F / Cmd+F) to search for the check name.

The catalogue entry gives you:

  • The invariant the check tests
  • The severity (Error or Warning)
  • The models and tables involved
  • Suggested investigation steps specific to that check

Step 2: Note the severity

Error: The load has been flagged as failed. The data for the period covered by the check's lookback window may be incomplete or incorrect. Do not rely on the affected models for reporting until the issue is resolved or confirmed as a timing artefact.

Warning: The load completed. The check is advisory. Monitor whether the count remains small and stable across load cycles. Persistent or growing warning counts should be escalated.

Three checks emit Warning-level signals: two are Warning-only (check_bav_dim_tran_info_coverage_scd_endur_deal_enhanced, check_cardinality_dim_tran_info); check_fct_pnl_deal_vs_pnl_detail is dual-severity — it warns at >0 rows and escalates to Error at >5,000 rows. All others are Error-only.

Step 3: Consider timing

Ask yourself whether any of the following apply:

  • Is this the first load after a planned maintenance window, source system outage, or Numera upgrade?
  • Is this the first load of the day, and could source data still be in transit?
  • Is the num_errors count small and consistent with a single missing metric date or a few deals?

Many checks include a lookback window and a grace period (see How Checks Work). A check that fires on the first load after an upgrade may self-heal on the next load once data has had time to propagate through the pipeline. Allow one full load cycle before treating a new failure as a confirmed incident.

Step 4: Run the diagnostic SQL

The catalogue entry for the failing check names the relevant models. Open Snowflake and query those models for the latest metric_date or sim_date to confirm the failure pattern.

For example, if check_for_missing_dim_data_in_pnl has fired, query:

SELECT deal_id, MIN(metric_date) AS missing_from, MAX(metric_date) AS missing_to
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;

Identify which specific deal IDs, metric dates, or portfolios are affected. This scope information is what you will need if you escalate.

Step 5: Follow the suggested steps

Each catalogue entry has specific remediation steps based on the SQL logic and known failure modes. Work through them in order:

  • Check whether the affected deals or records appear in the upstream models named in the catalogue entry.
  • If the catalogue entry identifies a specific failure mode (for example, a re-run overwriting an overnight sim, or a deal missing from a dimension), follow the mode-specific guidance.
  • For checks marked with a "Remediation steps need verification" warning, the steps are best-effort and have not been validated against a live failure — share what you find with the data engineering team so the guidance can be improved.

Step 6: Escalate if needed

If the check is still firing after one full load cycle and the suggested steps have not identified a clear cause, contact the data engineering team with the following information:

  • Check name (exact, as shown in fct_integrity_check)
  • Number of failing rows (num_errors)
  • Latest metric_date or sim_date visible in the affected model
  • Any source system alerts or scheduled Endur maintenance you are aware of
  • The output of the diagnostic SQL you ran in Step 4

This information allows the engineering team to diagnose the issue against the live data without needing to re-run the investigation from scratch.