The Ayvens Medallion pipeline aggregates contracts and vehicle assets into conformed reporting marts. Discrepancies between local subsidiary operational databases and conformed analytics tables surface primarily through two critical failure modes:
gold.fleet_report or mart.agg_fleet_utilisation. The fleet count reported to finance or executive leadership diverges from actual vehicle deployments.silver.vehicle or silver.contract tables, impacting customer self-service portals (lease-portal-app) and remarketing systems.This runbook documents the root-cause diagnostics, multi-tier lineage tracing, and the official 9-column solution register protocol for both use cases.
AYVENS_FLEET_RECONCILIATION)When a contract is missing from Gold fleet reports, the analytical diagnostic engine evaluates two distinct scenarios:
uc2_sc1 / sc1_product_family):
silver.contract, but its product family code (productfamilycodesynt) is NULL or unmapped in localvalue_t, or the mapping status is inactive (STATUS <> 'ACTIVE').STATUS = 'A'). Unmapped or inactive contracts are filtered out.uc2_sc2 / sc2_contract_not_alive):
contractrealstartdatesynt) is NULL, invalid, or scheduled in the future, OR the contract lifecycle status is not set to active in the subsidiary feed.contractrealstartdatesynt <= current_date() AND (contractrealenddatesynt IS NULL OR contractrealenddatesynt > current_date()).SELECT
c.contractcodesynt,
c.subsidiarycodesynt,
c.productfamilycodesynt,
c.contractrealstartdatesynt,
c.contractrealenddatesynt,
c.status AS contract_status,
lv.LOCALVALUE AS product_family_name,
lv.STATUS AS normalization_status,
g.active_fleet_count
FROM `alenia-aiops-dev`.silver.contract c
LEFT JOIN `alenia-aiops-dev`.silver.localvalue_t lv
ON lv.CLASSID = 18
AND lv.SUBSIDIARYCODE = c.subsidiarycodesynt
AND lv.LOCALCODE = c.productfamilycodesynt
LEFT JOIN `alenia-aiops-dev`.gold.fleet_report g
ON g.subsidiary_code = c.subsidiarycodesynt
WHERE c.contractcodesynt = '{CONTRACT_CODE}'
AND c.subsidiarycodesynt = '{SUBSIDIARY_CODE}';
c.productfamilycodesynt IS NULL OR lv.LOCALCODE IS NULL:
input_data_reload dispatched to subsidiary data contact to supply product family classification.lv.STATUS <> 'A':
localvalue_t.c.contractrealstartdatesynt IS NULL OR c.contractrealstartdatesynt > current_timestamp():
input_data_reload dispatched with error column contractrealstartdatesynt.AYVENS_DATA_CORRECTION)In UC3, a vehicle or contract requested by downstream applications (such as lease-portal-app, pricing-analytics, or remarketing) cannot be located in Silver conformed tables. The incident response engine evaluates the multi-tier lineage:
[Subsidiary ERP Batch Extract]
|
v
[1. monitoring.file_log_t] --------> Subcase: 'not_received' (Input file missing)
|
v
[2. bronze.*_history] -------------> Subcase: 'removed_silver_history' (Failed bronze ingestion)
|
v
[3. silver.* conformed] -----------> Subcase: 'removed_silver' (Dropped in Silver transformation)
SELECT
-- Check Silver conformed table
(SELECT COUNT(*)
FROM `alenia-aiops-dev`.silver.{TABLE}
WHERE {PK_COL} = '{ENTITY_ID}'
AND subsidiarycodesynt = '{SUBSIDIARY_CODE}') AS in_silver,
-- Check Bronze ingestion history
(SELECT COUNT(*)
FROM `alenia-aiops-dev`.bronze.{TABLE}_history
WHERE {PK_COL} = '{ENTITY_ID}'
AND subsidiarycodesynt = '{SUBSIDIARY_CODE}') AS in_bronze,
-- Check Staging File Ingestion Log
(SELECT COUNT(*)
FROM `alenia-aiops-dev`.monitoring.file_log_t
WHERE pk_values = '{ENTITY_ID}|{SUBSIDIARY_CODE}') AS in_filelog;
in_silver |
in_bronze |
in_filelog |
Subcase ID | Fault Layer | Prescribed Action | Escalation Recipient |
|---|---|---|---|---|---|---|
0 |
> 0 |
* |
removed_silver |
silver |
bronze_to_silver |
dataintegration@team.io |
0 |
0 |
> 0 |
removed_silver_history |
bronze |
file_to_bronze |
dataintegration@team.io |
0 |
0 |
0 |
not_received |
input_file |
input_data_reload |
Subsidiary Contact (silver.subsidiary_t) |
All automated diagnostic and remediation resolutions for UC2 and UC3 MUST be logged into the official solution table in the sandbox catalog:
`alenia-aiops-dev`.solutions.solutions.
CREATE TABLE IF NOT EXISTS `alenia-aiops-dev`.solutions.solutions (
TICKET_ID STRING,
LAYER STRING,
TABLE_NAME STRING,
ERROR_TYPE STRING,
ERROR_COLUMN STRING,
PK_COLUMN STRING,
PK_VALUE STRING,
ACTION STRING,
EMAIL_RECIPIENT STRING
);
-- Step 1: Idempotent cleanup of prior solution attempt
DELETE FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = 'TKT-UC2-DE-0041';
-- Step 2: Register verified resolution
INSERT INTO `alenia-aiops-dev`.solutions.solutions
(TICKET_ID, LAYER, TABLE_NAME, ERROR_TYPE, ERROR_COLUMN, PK_COLUMN, PK_VALUE, ACTION, EMAIL_RECIPIENT)
VALUES
(
'TKT-UC2-DE-0041',
'silver',
'contract',
'missing_product_family',
'productfamilycodesynt',
'contractcodesynt',
'CNT-DE-99401',
'input_data_reload',
'support.germany@ayvens.com'
);
DELETE FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = 'TKT-UC2-FR-0089';
INSERT INTO `alenia-aiops-dev`.solutions.solutions
(TICKET_ID, LAYER, TABLE_NAME, ERROR_TYPE, ERROR_COLUMN, PK_COLUMN, PK_VALUE, ACTION, EMAIL_RECIPIENT)
VALUES
(
'TKT-UC2-FR-0089',
'silver',
'contract',
'inactive_status',
'contractrealstartdatesynt',
'contractcodesynt',
'CNT-FR-11204',
'input_data_reload',
'support.france@ayvens.com'
);
removed_silver)DELETE FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = 'TKT-UC3-ES-0105';
INSERT INTO `alenia-aiops-dev`.solutions.solutions
(TICKET_ID, LAYER, TABLE_NAME, ERROR_TYPE, ERROR_COLUMN, PK_COLUMN, PK_VALUE, ACTION, EMAIL_RECIPIENT)
VALUES
(
'TKT-UC3-ES-0105',
'silver',
'vehicle',
'missing_vehicle',
'vehiclecodesynt',
'vehiclecodesynt',
'VH-ES-77120',
'bronze_to_silver',
'dataintegration@team.io'
);
removed_silver_history)DELETE FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = 'TKT-UC3-NL-0210';
INSERT INTO `alenia-aiops-dev`.solutions.solutions
(TICKET_ID, LAYER, TABLE_NAME, ERROR_TYPE, ERROR_COLUMN, PK_COLUMN, PK_VALUE, ACTION, EMAIL_RECIPIENT)
VALUES
(
'TKT-UC3-NL-0210',
'bronze',
'contract',
'missing_contract',
'contractcodesynt',
'contractcodesynt',
'CNT-NL-33019',
'file_to_bronze',
'dataintegration@team.io'
);
not_received)DELETE FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = 'TKT-UC3-BE-0312';
INSERT INTO `alenia-aiops-dev`.solutions.solutions
(TICKET_ID, LAYER, TABLE_NAME, ERROR_TYPE, ERROR_COLUMN, PK_COLUMN, PK_VALUE, ACTION, EMAIL_RECIPIENT)
VALUES
(
'TKT-UC3-BE-0312',
'input_file',
'contract',
'missing_contract',
'contractcodesynt',
'contractcodesynt',
'CNT-BE-88190',
'input_data_reload',
'support.belgium@ayvens.com'
);
After the solution record is inserted, verify that the entry is registered:
SELECT *
FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = '{TICKET_ID}';
Assertion: Exactly one row returned matching the target ticket, layer, error type, and action.
If an incident diagnosis is invalidated or superseded by operator input:
DELETE FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = '{TICKET_ID}';
This statement removes the pending automated pipeline reload action, preventing unnecessary batch processing.