Redbook key resolution hierarchy

stock_redbook_enrichment_ref.sql is where a dealer-supplied vehicle key — in whatever format that dealer's feed happens to use — gets resolved to a real Redbook Vehicle Key, so the vehicle can be enriched with make/model/spec data. This is the gate a listing has to clear to become fully-specified inventory.

Five tiers, tried in priority order

Every resolved record is tagged with which tier resolved it (resolution_method) — this is what the monitoring view monitoring_stock_redbook_vehicle_key_resolution_tracking_view reports on.

The actual resolution logic

The core CTE, quoted directly:

resolved_keys AS (
  SELECT
    d.*,
    CASE
      WHEN LENGTH(d.redbook_vehicle_key) > 15 AND LENGTH(d.decrypted_key) = 8 THEN 'decrypt_legacy'
      WHEN LENGTH(d.redbook_vehicle_key) > 15 THEN 'decrypt'
      WHEN LENGTH(d.redbook_vehicle_key) = 8 THEN 'legacy'
      WHEN LENGTH(d.redbook_vehicle_key) BETWEEN 10 AND 15
           AND d.redbook_vehicle_key NOT IN ('incorrect_key', 'invalid_key') THEN 'standard'
      WHEN d.nvic_code IS NOT NULL THEN 'nvic'
      ELSE 'none'
    END AS resolution_method,
    CASE
      WHEN LENGTH(d.redbook_vehicle_key) > 15 AND LENGTH(d.decrypted_key) = 8
        THEN decrypted_legacy_map.vehicle_key
      WHEN LENGTH(d.redbook_vehicle_key) > 15
        THEN d.decrypted_key
      WHEN LENGTH(d.redbook_vehicle_key) = 8
        THEN legacy_map.vehicle_key
      WHEN LENGTH(d.redbook_vehicle_key) BETWEEN 10 AND 15
           AND d.redbook_vehicle_key NOT IN ('incorrect_key', 'invalid_key')
        THEN d.redbook_vehicle_key
      WHEN d.nvic_code IS NOT NULL
        THEN nvic_map.vehicle_key
      ELSE NULL
    END AS resolved_key
  FROM decrypted d
  LEFT JOIN {{ source('redbook', 'redbook_legacy_map_int') }} legacy_map
    ON LENGTH(d.redbook_vehicle_key) = 8
    AND UPPER(TRIM(d.redbook_vehicle_key)) = UPPER(TRIM(legacy_map.legacy_vehicle_key))
  LEFT JOIN {{ source('redbook', 'redbook_legacy_map_int') }} decrypted_legacy_map
    ON LENGTH(d.redbook_vehicle_key) > 15
    AND LENGTH(d.decrypted_key) = 8
    AND UPPER(TRIM(d.decrypted_key)) = UPPER(TRIM(decrypted_legacy_map.legacy_vehicle_key))
  LEFT JOIN {{ source('redbook', 'redbook_vehicle_key_nvic_mappings') }} nvic_map
    ON d.nvic_code = nvic_map.nvic
)

Note this joins against redbook_legacy_map_int/redbook_vehicle_key_nvic_mappings directly — Stock's enrichment reads Redbook's intermediate layer, not the fact marts (see Staging, dedup & marts for why that's worth knowing).

The final enrichment join, once a key is resolved:

FROM resolved_keys rk
LEFT JOIN redbook_data r
  ON UPPER(TRIM(rk.resolved_key)) = UPPER(TRIM(r.vehicle_key))

Decryption is a two-stage guess, not a single lookup

An encrypted key doesn't decrypt straight to a usable Redbook Vehicle Key — it decrypts to either a legacy 8-character key (needing a further legacy-map lookup) or a standard-length key (usable directly). That's why tier 1 has two possible outcomes (decrypt vs decrypt_legacy) rather than one.

The decryption itself uses DES-CBC via a Python UDF, DECRYPT_VEHICLE_KEY, fed an array of cipher keys (Python UDFs can't query tables directly, so the wrapper macro decrypt_vehicle_key_wrapper pulls REDBOOK_CIPHER_KEY_INT into an array first, newest-dated first) and tries each key in turn until one produces a plausible result. This is necessary because Redbook rotates the cipher key monthly — an older stock record may have been encrypted under a key that's since rotated out, so the UDF has to try historical keys, not just the current one.

In test mode, decrypt_vehicle_key_wrapper returns mock mappings instead of calling the real UDF — this is what lets the resolution logic be unit-tested without needing real cipher key material in a test environment.

What this doesn't include

This resolution hierarchy has no VIN-based lookup — a dealer-supplied VIN alone doesn't resolve a Redbook key anywhere in this model. The only paths to a resolved key are an already-present key (plain, encrypted, or legacy-format) or an NVIC code. If you're looking for VIN-to-key resolution, it isn't implemented in the current pipeline.

See also

• NVIC mappings
• Rejection & validation tracking