Raw ingestion & the Redbook data model
The 5 raw tables this project builds on, where they come from, and how they relate — both in Redbook's full vendor data model and in the narrower subset this repo actually ingests.
Where the data comes from
Redbook emails a notification every Thursday, which lands on Drive's SNS topic and triggers extraction. The actual files sit on a Redbook-operated SFTP server; Manifold downloads the archive and uploads it (plus its extracted contents) to the manifold-prod.drive.com.au S3 bucket as a .zip. See Redbook Ingestion for what happens once the individual CSVs land from there.
The full vendor data model (ERD)
Redbook's vendor archive contains 7 interconnected CSV files, not 5 — VEPaint.csv and VEPhoto.csv and VEYear.csv exist in the vendor's data model but aren't part of this repo's ingestion at all (see the next subsection). The full relationship structure, keyed on VehicleKey:
VehicleTypeCode matters more than it looks: (MakeCode, FamilyCode) alone isn't unique in Redbook's data — the same make+family combination can exist as more than one vehicle type across different year ranges (Redbook's own example: a Citroen C4 exists as both a passenger car and an SUV). VehicleTypeCode is part of the natural key precisely to resolve that.
The Redbook Vehicle Key itself — e.g. AUVABAR2011AEZZ — is the identifier that ties this whole structure together; it's what the Stock pipeline resolves a dealer's listing to (see Redbook key resolution hierarchy, once written). VEVehicleMap.csv exists purely for backward compatibility: Redbook's older format was an 8-character key (e.g. ABAR11ZZ); the map table translates between the two so nothing downstream that still sends a legacy key breaks.
What drive-snowflake actually ingests
Of the vendor's 7 CSVs plus the separate monthly cipher key file, this repo's ingestion Lambda only picks up 5:
| Vendor file | Ingested here? | Raw table |
|---|---|---|
VEVehicle.csv | Yes | REDBOOK_VEHICLES_RAW |
VEVehicleMap.csv | Yes | REDBOOK_LEGACY_MAP_RAW |
VEMake.csv | Yes | REDBOOK_VEHICLE_MAKES_RAW |
VEFamily.csv | Yes | REDBOOK_VEHICLE_FAMILIES_RAW |
RBCipherKey.txt | Yes | REDBOOK_CIPHER_KEY_RAW |
VEPaint.csv | No | — not ingested |
VEPhoto.csv | No | — not ingested |
VEYear.csv | No | — not ingested |
Paint options, photo references, and production-period detail (VEYear) exist in the vendor's feed but never reach Snowflake — this pipeline only needs vehicle identity and make/family reference data to resolve a Redbook Vehicle Key, not presentation data like paint swatches or photos.
The cipher key isn't part of the vehicle hierarchy at all
RBCipherKey.txt looks like it belongs in the ERD above, but it doesn't — REDBOOK_CIPHER_KEY_RAW's natural key is just key_date, with no join to VehicleKey, MakeCode, or anything else in the hierarchy. It's a DES-CBC decryption key store, one row per date the key became effective, rotated monthly by Redbook.
Its purpose is contractual, not technical: Redbook's contract requires that a Redbook Vehicle Key only ever be transmitted to a third party in encrypted form. The cipher key is what lets an authorised consumer — the docs mention drive-axle specifically — decrypt a key it receives. It's never needed to parse the CSV files themselves; the raw data arrives in plaintext regardless of the cipher key's rotation.
What's tested on the raw layer
Source-level tests exist on all 5 raw tables (models/staging/redbook/_redbook__sources.yml):
REDBOOK_VEHICLE_MAKES_RAW.make_code— not-null, uniqueREDBOOK_LEGACY_MAP_RAW.vehicle_key— not-null, uniqueREDBOOK_CIPHER_KEY_RAW.key_date— not-null, uniqueREDBOOK_VEHICLE_FAMILIES_RAW— adbt_utils.unique_combination_of_columnstest across(make_code, family_code, vehicle_type_code)together, since no single column is unique on its own- Every raw table's
processing_statusis checked againstaccepted_values: [pending, complete, failed, skipped]
A source-level freshness check applies across the whole redbook_raw source: warn after 24 hours without a new row, error after 48. Data only lands weekly — so this check errors on every single cycle between Thursday drops, not just when something is actually wrong. Either the freshness window was copied from a more frequently-updated source without adjusting for Redbook's real cadence, or freshness checks aren't actually being run/alerted on for this source — either way, don't take a freshness error here as a real signal without checking ingested_at directly first.