The Ayvens Medallion Data Platform organizes datasets into logical layers within Azure Databricks Unity Catalog. The database schema design guarantees strict data governance, bi-temporal lineage, historical traceability, and deterministic business aggregations.
aiops_participants_synthethic_data: Read-only reference catalog containing participant test fixtures and validated medallion tables.aiops_hackathon_dev_ai: Read-only development catalog containing historical ticket logs (sql.ticket) and synthetic OpenProject work packages (alenia_team_schema.op_tickets).`alenia-aiops-dev`: Read/write operational sandbox catalog hosting operational tables for autonomous remediation and the official solutions table (solutions.solutions).silver.localvalue_tStores local attribute codes from subsidiary ERPs mapped to canonical corporate holding definitions.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
CLASSID |
INTEGER |
PK, NOT NULL | Entity classification identifier (e.g., 27 = Vehicle Make, 14 = Fuel Type, 15 = Body Type, 18 = Contract Type). |
SUBSIDIARYCODE |
STRING |
PK, NOT NULL | 2-letter ISO subsidiary code (e.g., 'FR', 'DE', 'NL', 'BE', 'ES', 'IT'). |
LOCALCODE |
STRING |
PK, NOT NULL | Subsidiary-specific local code string. |
LOCALVALUE |
STRING |
NOT NULL | Local descriptive value or make/model name as displayed in subsidiary systems. |
HOLDINGID |
STRING |
NULLABLE | Target corporate holding identifier mapped from holdingvalue_t. NULL indicates unmapped code. |
STATUS |
STRING |
NOT NULL | Normalization lifecycle status: 'A' (Active), 'I' (Inactive), 'S' (Suggested / Pending Review). |
(CLASSID, SUBSIDIARYCODE, LOCALCODE)(CLASSID, HOLDINGID) references silver.holdingvalue_t(CLASSID, HOLDINGID)SUBSIDIARYCODE references silver.subsidiary_t(SubsiDiaryCode)silver.holdingvalue_tStores canonical enterprise reference values defined by Ayvens Group holding standards.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
CLASSID |
INTEGER |
PK, NOT NULL | Entity classification identifier matching domain class. |
HOLDINGID |
STRING |
PK, NOT NULL | Unique enterprise holding identifier (e.g., 'H-MAKE-BMW', 'H-FUEL-ELEC'). |
NAME |
STRING |
NOT NULL | Standardized holding name. |
DESCRIPTION |
STRING |
NULLABLE | Technical and operational description of holding entity. |
(CLASSID, HOLDINGID)silver.subsidiary_tMaster table of operational leasing subsidiaries and their escalation contacts.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
SubsiDiaryCode |
STRING |
PK, NOT NULL | Two-letter ISO country code identifying the operating affiliate. |
CompanyName |
STRING |
NOT NULL | Registered legal entity name (e.g., 'Ayvens France SAS', 'Ayvens Deutschland GmbH'). |
EmailContact |
STRING |
NOT NULL | Dedicated operational contact email address for automated incident dispatch. |
SubsiDiaryCodebronze.localvalue_history_tImmutable change-log capturing every mutation applied to localvalue_t.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
CLASSID |
INTEGER |
NOT NULL | Classification identifier of mutated record. |
SUBSIDIARYCODE |
STRING |
NOT NULL | Subsidiary ISO code. |
LOCALCODE |
STRING |
NOT NULL | Local attribute code string. |
LOCALVALUE |
STRING |
NOT NULL | Value of LOCALVALUE at time of event. |
HOLDINGID |
STRING |
NULLABLE | Value of HOLDINGID at time of event. |
STATUS |
STRING |
NOT NULL | Normalization status at time of event ('A', 'I', 'S'). |
IDUSER |
STRING |
NOT NULL | Principal performing the mutation (e.g., 'AIOPS-REMEDIATION-ENGINE'). |
EXTRACTION_DATE |
TIMESTAMP |
NOT NULL | Precise timestamp when historical entry was logged. |
silver.contractConformed lease contract records across all operating affiliates.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
contractcodesynt |
STRING |
PK, NOT NULL | Synthetic global contract identifier. |
subsidiarycodesynt |
STRING |
FK, NOT NULL | Subsidiary country code. |
productfamilycodesynt |
STRING |
NULLABLE | Lease product family code (e.g., Operational Lease, Finance Lease, Sale & Leaseback). |
contractrealstartdatesynt |
TIMESTAMP |
NULLABLE | Effective operational commencement date of the lease contract. |
contractrealenddatesynt |
TIMESTAMP |
NULLABLE | Contract termination date or scheduled lease maturity date. |
status |
STRING |
NOT NULL | Operational status ('ACTIVE', 'TERMINATED', 'PENDING'). |
contractcodesyntsubsidiarycodesynt references silver.subsidiary_t(SubsiDiaryCode)silver.vehicleConformed vehicle asset inventory.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
vehiclecodesynt |
STRING |
PK, NOT NULL | Synthetic global vehicle identifier. |
subsidiarycodesynt |
STRING |
FK, NOT NULL | Subsidiary country code. |
vin |
STRING |
NOT NULL | Vehicle Identification Number (17-character standard). |
make |
STRING |
NOT NULL | Standardized vehicle manufacturer name. |
model |
STRING |
NOT NULL | Standardized vehicle model designation. |
fueltype |
STRING |
NULLABLE | Engine propulsion technology (e.g., Electric, Hybrid, Diesel, Petrol). |
registrationdate |
DATE |
NULLABLE | First road registration date of vehicle. |
vehiclecodesyntsubsidiarycodesynt references silver.subsidiary_t(SubsiDiaryCode)bronze.contract_history & bronze.vehicle_historyBronze ingestion history snapshots.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
contractcodesynt / vehiclecodesynt |
STRING |
NOT NULL | Source entity synthetic identifier. |
subsidiarycodesynt |
STRING |
NOT NULL | Subsidiary country code. |
raw_payload |
STRING |
NULLABLE | Raw JSON/Avro payload as delivered by subsidiary batch extract. |
EXTRACTION_DATE |
TIMESTAMP |
NOT NULL | Batch extract snapshot timestamp. Used to trace when records disappeared from Silver. |
core.fct_lease_contractConformed enterprise dimensional fact table uniting contracts, vehicles, and holding reference data.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
contract_sk |
STRING |
PK, NOT NULL | Surrogate key for the contract dimension. |
vehicle_sk |
STRING |
FK, NOT NULL | Surrogate key linking to the conformed vehicle dimension. |
subsidiary_code |
STRING |
FK, NOT NULL | Operating affiliate ISO code. |
holding_make_id |
STRING |
FK, NOT NULL | Conformed vehicle make holding identifier. |
holding_fuel_id |
STRING |
FK, NOT NULL | Conformed fuel type holding identifier. |
contract_start_date |
DATE |
NOT NULL | Conformed contract start date. |
monthly_rental_eur |
DECIMAL(12,2) |
NOT NULL | Monthly lease payment converted to corporate currency (EUR). |
gold.fleet_report & mart.agg_fleet_utilisationAggregated business tables serving corporate reporting and executive dashboards.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
subsidiary_code |
STRING |
NOT NULL | Affiliate country code. |
reporting_month |
STRING |
NOT NULL | Accounting period (YYYY-MM). |
active_fleet_count |
INTEGER |
NOT NULL | Count of concurrently active leased vehicles on the road. |
total_monthly_revenue |
DECIMAL(16,2) |
NOT NULL | Total recognized lease revenue. |
utilization_rate_pct |
DECIMAL(5,2) |
NOT NULL | Fleet utilization ratio (operational active vs grounded fleet). |
When the AIOps platform diagnoses a data quality issue, missing contract, unmapped code, or pipeline processing defect, it logs the resolution into the official solutions register table located in the sandbox catalog at:
`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
);
| Column Name | SQL Type | Mandatory | Description & Allowed Enumeration |
|---|---|---|---|
TICKET_ID |
STRING |
YES | Unique ticket or incident identifier (e.g., 'SYN-0014', 'OC-022', 'TKT-40892'). |
LAYER |
STRING |
YES | Medallion layer experiencing the defect: 'bronze', 'silver', 'gold', 'input_file'. |
TABLE_NAME |
STRING |
YES | Target table: 'contract', 'vehicle', 'localvalue_t', 'fleet_report', 'localvalue_history_t'. |
ERROR_TYPE |
STRING |
YES | Category of anomaly: 'missing_contract', 'missing_vehicle', 'unmapped_code', 'inactive_status', 'missing_product_family', 'data_corruption'. |
ERROR_COLUMN |
STRING |
YES | Specific column triggering the break: 'HOLDINGID', 'productfamilycodesynt', 'contractrealstartdatesynt', 'LOCALVALUE', 'STATUS'. |
PK_COLUMN |
STRING |
YES | Primary key column name of affected record: 'contractcodesynt', 'vehiclecodesynt', 'LOCALCODE'. |
PK_VALUE |
STRING |
YES | Value of primary key for affected entity (e.g., 'CNT-NL-90812', 'VH-FR-44109', 'BMW_320D'). |
ACTION |
STRING |
YES | Prescribed operational remediation action: 'bronze_to_silver', 'file_to_bronze', 'input_data_reload', 'update_holding_id', 'insert_local_code'. |
EMAIL_RECIPIENT |
STRING |
YES | Operations contact: 'dataintegration@team.io' or subsidiary contact resolved from silver.subsidiary_t. |
All updates to solutions.solutions must be idempotent. The remediation connector executes a transactional delete followed by an insert:
-- Step 1: Purge any stale solution for the target ticket
DELETE FROM `alenia-aiops-dev`.solutions.solutions
WHERE TICKET_ID = 'SYN-0014';
-- Step 2: Insert verified solution record
INSERT INTO `alenia-aiops-dev`.solutions.solutions
(TICKET_ID, LAYER, TABLE_NAME, ERROR_TYPE, ERROR_COLUMN, PK_COLUMN, PK_VALUE, ACTION, EMAIL_RECIPIENT)
VALUES
('SYN-0014', 'silver', 'contract', 'missing_product_family', 'productfamilycodesynt', 'contractcodesynt', 'CNT-FR-10492', 'input_data_reload', 'support.france@ayvens.com');
In OpenProject and Cockpit ticketing records, these 9 technical solution columns map directly to the standardized ITSM incident representation:
| Solution Table Column | OpenProject ITSM Field | Description |
|---|---|---|
TICKET_ID |
ticket_id / code |
Unique external work package key. |
LAYER |
category |
High-level architectural tier. |
TABLE_NAME |
sub_category |
Impacted data entity. |
ERROR_TYPE |
subject / description |
Diagnostic anomaly classification. |
ERROR_COLUMN |
custom_field_affected_col |
Defective schema attribute. |
PK_COLUMN & PK_VALUE |
description (Body text) |
Entity identifier and key coordinates. |
ACTION |
solution |
Prescribed automated or manual workflow. |
EMAIL_RECIPIENT |
custom_field_escalation |
Target notification group or affiliate contact. |
SELECT
lv.CLASSID,
lv.SUBSIDIARYCODE,
lv.LOCALCODE,
lv.LOCALVALUE,
lv.HOLDINGID,
hv.NAME AS HOLDING_NAME,
lv.STATUS
FROM `alenia-aiops-dev`.silver.localvalue_t lv
LEFT JOIN `alenia-aiops-dev`.silver.holdingvalue_t hv
ON lv.CLASSID = hv.CLASSID
AND lv.HOLDINGID = hv.HOLDINGID
WHERE lv.CLASSID = 27
AND lv.SUBSIDIARYCODE = 'FR'
AND (lv.HOLDINGID IS NULL OR lv.STATUS = 'S');
SELECT
c.contractcodesynt,
c.subsidiarycodesynt,
c.productfamilycodesynt,
c.contractrealstartdatesynt,
c.status AS contract_status,
lv.STATUS AS localvalue_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 = 'CNT-DE-88201';
SELECT
b.contractcodesynt,
b.subsidiarycodesynt,
b.EXTRACTION_DATE,
s.contractcodesynt AS silver_contract_id,
CASE
WHEN s.contractcodesynt IS NOT NULL THEN 'CONFORMED_IN_SILVER'
WHEN b.contractcodesynt IS NOT NULL THEN 'DROPPED_IN_BRONZE_TO_SILVER'
ELSE 'NEVER_RECEIVED_FROM_SOURCE'
END AS medallion_lineage_status
FROM `alenia-aiops-dev`.bronze.contract_history b
LEFT JOIN `alenia-aiops-dev`.silver.contract s
ON b.contractcodesynt = s.contractcodesynt
AND b.subsidiarycodesynt = s.subsidiarycodesynt
WHERE b.contractcodesynt = 'CNT-ES-55102'
ORDER BY b.EXTRACTION_DATE DESC
LIMIT 1;