Dealer status validation

A listing only gets processed if its dealer has an active "Cars For Sale" subscription in Salesforce — right now, as of the pre-hook that runs before stock_stg. Everything else is skipped, with a specific status recorded for why.

Five statuses, not a boolean

Earlier versions of this check may have been a simple active/inactive boolean; the current logic is a 5-status hierarchy that tells you exactly why a dealer didn't qualify:

CASE
    WHEN COUNT(acc.ID) = 0 THEN 'NOT_FOUND'
    WHEN COUNT(CASE WHEN sub.ACTIVE__C = true AND sub.RECORD_TYPE_NAME__C = 'Cars For Sale' THEN 1 END) > 0
         THEN 'ACTIVE'
    WHEN COUNT(CASE WHEN sub.RECORD_TYPE_NAME__C = 'Cars For Sale' THEN 1 END) > 0
         THEN 'INACTIVE'
    WHEN COUNT(CASE WHEN acc.STATUS__C != 'Active' THEN 1 END) > 0
         THEN 'ACCOUNT_INACTIVE'
    ELSE 'NO_SUBSCRIPTION'
END AS dealer_status

Only ACTIVE results in processing. The other four all skip the record, but each is distinguishable in STOCK_RAW and the monitoring views — a dealer with no Salesforce record at all looks different from a dealer whose subscription lapsed, which matters when you're trying to explain to a dealer why their stock isn't showing.

The Salesforce source tables

Two tables, both under RAW_DB.SALESFORCE_LMS_SCHEMA:

ACCOUNT — one row per dealer. Key fields: ID (PK), NAME, STATUS__C (Active/Inactive/Cancelled/On Hold/Prospecting), THIRD_PARTY_DATA_PROVIDER__C, THIRD_PARTY_ACCOUNT_ID__C — the external dealer identifier that correlates back to data_provider_dealer_id in stock data.

SUBSCRIPTION__C — one row per subscription. Key fields: ID (PK), ACCOUNT__C (FK to ACCOUNT), RECORD_TYPE_NAME__C (Cars For Sale/New Car/Finance/Instant Offer/Novated Lease — only Cars For Sale matters for stock validation), ACTIVE__C, START_DATE__C, END_DATE__C (NULL while active).

The core validation query, simplified:

SELECT DISTINCT acc.THIRD_PARTY_ACCOUNT_ID__C AS valid_dealer_id
FROM RAW_DB.SALESFORCE_LMS_SCHEMA.ACCOUNT acc
JOIN RAW_DB.SALESFORCE_LMS_SCHEMA.SUBSCRIPTION__C sub ON acc.ID = sub.ACCOUNT__C
WHERE acc.ISDELETED = false
  AND sub.ACTIVE__C = true
  AND sub.RECORD_TYPE_NAME__C = 'Cars For Sale'

The full hierarchical version (the CASE above) runs as a stored procedure, VALIDATE_DEALER_STATUS(), called from deploy_dealer_status_validation_procedure.sql — see STOCK_RAW schema & file superseding for exactly when it runs relative to the rest of the pipeline.

Monitoring dealer health

Two views exist specifically for this:

• monitoring_stock_ingestion_summary_view — batch-level dealer metrics: total_invalid_dealer_records, total_dealer_status_skipped, files_with_invalid_dealers, dealer_skip_percentage. Start here for a quick "is dealer validation eating a lot of records right now" check.
• monitoring_stock_queued_status_view — per-dealer health (HEALTHY/WARNING/CRITICAL) and activity trends (ACTIVE_TODAY/ACTIVE_WEEK/INACTIVE) over a 30-day window. Use this to find specific dealers worth following up with, not just aggregate volume.

-- Dealers worth investigating right now
SELECT data_provider_dealer_id, dealer_health_status, activity_status,
       total_records_submitted, invalid_percentage, business_impact_records_lost
FROM VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.monitoring_stock_queued_status_view
WHERE dealer_health_status IN ('WARNING', 'CRITICAL')
   OR (activity_status = 'INACTIVE' AND latest_submission_at >= CURRENT_DATE - INTERVAL '7 DAYS')
ORDER BY invalid_percentage DESC;

See also

• STOCK_RAW schema & file superseding
• Rejection & validation tracking