Staging, dedup & marts

Every one of the 5 staging models follows the same pattern. Once you've read one, you've effectively read all five.

The dedup pattern

Every staging model wraps its source query in QUALIFY ROW_NUMBER() OVER (PARTITION BY <natural key> ORDER BY ingested_at DESC) = 1, keeping only the newest row per key. From redbook_vehicles_stg.sql:

WITH source_data AS (
  SELECT *
  FROM {{ source('redbook_raw', 'REDBOOK_VEHICLES_RAW') }}
  {% if is_incremental() %}
  WHERE processing_status = 'pending'
  {% endif %}
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY vehicle_key
    ORDER BY ingested_at DESC
  ) = 1
)

The natural key differs per model — it's whatever uniquely identifies a row in that table:

Nothing in the codebase names a specific root cause for why the same key can appear twice in a pending batch. The mechanism that would produce it: Redbook delivers full weekly dumps, and if a prior batch's rows never flipped from pending to complete before the next week's file lands, two pending rows for the same key would coexist — the dedup keeps the newest one. This is inferred from how the batching mechanism works, not stated directly anywhere.

How rows get marked as processed

A shared macro, redbook_mark_processed, runs as every staging model's post-hook:

UPDATE {{ source('redbook_raw', raw_table_name) }}
SET processing_status = 'complete'
WHERE processing_status = 'pending'
  AND batch_id IS NOT NULL

Paired with a pre-hook on each model that stamps batch_id = invocation_id onto pending, unbatched rows before the query runs. The macro's own comment says it replaces what used to be separate per-domain stored procedures (update_raw_redbook_*_status) — one shared macro instead of five near-identical procedures.

Intermediate is a pure pass-through

Every _int model is exactly this shape — no business logic, just a timestamp:

SELECT *, CURRENT_TIMESTAMP() AS updated_at
FROM {{ ref('redbook_vehicles_stg') }}

The project's own README states this directly: staging does the light transformation, intermediate only adds updated_at. If you're looking for where a Redbook value gets transformed, it's in the _stg model, never the _int model.

The materialization config doesn't match what's actually running

dbt_project.yml sets project-wide defaults of materialized: view for staging, intermediate, and marts alike. In practice, every staging and intermediate model overrides this individually with its own config() block:

{{
  config(
    materialized='incremental',
    unique_key=['REDBOOK_UNIQUE_KEY'],
    incremental_strategy='delete+insert',
    pre_hook="...",
    post_hook="{{ redbook_mark_processed('REDBOOK_VEHICLES_RAW') }}",
    alias='REDBOOK_VEHICLES_STG'
  )
}}

So staging and intermediate are genuinely incremental tables, not views — the project-level view default only actually applies where nothing overrides it, which turns out to be just the marts layer. If you're reading dbt_project.yml to understand how this project materializes its models, it will tell you the wrong thing for 4 of the 5 layers that matter.

Marts

Three of the five raw tables reach a mart — vehicles, makes, families. Legacy map and cipher key stop at intermediate; nothing needs a curated gold-layer view of either.

Only redbook_vehicles_fact_mart has any dbt tests: redbook_unique_key and vehicle_key are both not-null + unique; the FK columns (make_unique_key, family_unique_key) are not-null only, with no relationship test back to their parent mart. The makes and families marts have no tests at all.

The marts aren't what Stock actually joins against

This is worth knowing if you're tracing how a dealer's listing resolves to a Redbook Vehicle Key: Stock's enrichment model (stock_redbook_enrichment_ref.sql) reads the intermediate layer directly —

redbook_data AS (
  SELECT * FROM {{ source('redbook', 'redbook_vehicles_int') }}
),
redbook_makes AS (
  SELECT make_code, description AS make_description, slug AS make_slug
  FROM {{ source('redbook', 'redbook_vehicle_makes_int') }}
),

not redbook_vehicles_fact_mart or the other fact marts. The marts layer exists as a curated view for other consumers, but as wired today, Stock's own key-resolution path bypasses it entirely and reads one layer earlier.

Tests fail quietly

dbt_project.yml sets +store_failures: true and +severity: warn project-wide. Every test in this project — the not-null/unique checks on raw sources, the uniqueness test on the vehicles mart — warns rather than fails the run. A broken invariant here doesn't stop REDBOOK_DBT_RUN_WEEKLY; it just gets logged. There's also no tests/ directory at all in this project, unlike Stock's dedicated tests/unit/ and tests/integration/ suites — the dedup and join logic here is exercised only by the source/mart schema tests above, nothing custom.

See also

• Raw ingestion & the Redbook data model
• NVIC mappings