drive-snowflake
Drive's marketplace turns dealer vehicle stock into consumer traffic and chargeable leads — a dealer is billed when a listing generates a qualifying lead.
drive-snowflake is the data platform underneath that. It has two jobs:
- Get dealer stock onto the platform in a form consumers can actually find and trust.
- Measure whether the marketing spend meant to bring consumers to that stock is working.
Every page is generated from Markdown checked into the drive-snowflake repository under docs/.
What this repo does
drive-snowflake is Drive's Snowflake data platform — three independent dbt projects on shared Terraform-managed infrastructure, each fed a different way:
- Stock & Redbook — dealer stock feeds and Redbook vehicle data land in S3, get ingested through AWS Lambda into Snowflake via Snowpipe, then transformed by dbt (staging → intermediate → marts).
- Marketplace & Ads Analytics — ad-platform data (Google Ads, Meta Ads, TikTok Ads, Bing Ads, RTB House, Salesforce LMS) is loaded via Integrate.io directly into per-platform Snowflake schemas, no S3/Lambda involved; GA4 lands separately via Snowflake's own native connector. Its dbt models also join against the Stock pipeline's own marts.
- Infrastructure — Terraform provisions the Snowflake databases/schemas/warehouses/roles and the AWS resources (S3, SQS, Lambda wiring) both pipelines above run on.
None of that data is useful sitting in a mart. BI & Reporting sits on top of all three: Sigma connects with a dedicated, read-only Snowflake role and turns the marts into the dashboards and reports the rest of the business actually reads.
The Stock & Redbook ingestion path, in detail:
See Infrastructure for the full path, including where it can fail.
The pieces, and what they're for
Stock pipeline (src/stock/dbt)
Raw STOCK_RAW ingestion, Redbook Vehicle Key resolution, rejection/validation tracking, and the fact/dimension marts consumed downstream.
This is the gate a dealer's listing has to pass to go live at all:
- No resolvable Redbook Vehicle Key → the listing is silently dropped, never becomes visible inventory, and can never generate a lead.
- Rejection-tracking and key-resolution work — including a VIN → Redbook key fallback that resolves a listing's key from its VIN when the dealer feed doesn't supply one — exists to recover stock that would otherwise be lost revenue, not as a data-quality nicety.
| Layer | What's there |
|---|---|
| Raw | STOCK_RAW — Snowpipe-ingested, one row shape per dealer feed provider |
| Staging | Typed, provider-mapped models (models/staging/stock) |
| Intermediate | Redbook Vehicle Key resolution, rejection/validation, change-history tracking (models/intermediate/stock) |
| Marts | Dimension and fact tables (models/marts/dims, models/marts/facts) — what downstream consumers query |
Redbook reference data (src/redbook/dbt)
Vehicle, make, and family master data that the stock pipeline resolves against.
Redbook is the industry-standard vehicle identity and valuation reference. A listing is only as trustworthy and comparable as the Redbook key it resolves to — it's what lets a consumer filter by make/model/year and see consistent specs, and it's the shared vocabulary the rest of the platform's vehicle data is built on.
| Layer | What's there |
|---|---|
| Raw | REDBOOK_VEHICLES_RAW and siblings — Snowpipe-ingested Redbook feed |
| Staging | Typed vehicle/make/family staging models (models/staging/redbook) |
| Intermediate | Cleaned, deduplicated reference data (models/intermediate/redbook) |
| Marts | Fact tables the Stock pipeline resolves against (models/marts/facts) |
Marketplace & Ads Analytics (src/marketplace)
A separate dbt project ("UDP") covering staging and SCD Type 2 snapshot models per ad platform — Google Ads, Meta Ads, TikTok Ads, Bing Ads, RTB House, Insider — plus GA4 web analytics, rolled into lightweight reporting marts.
Independent of the Stock/Redbook data flow, but it answers the other half of the same business question: getting stock listed is only useful if consumers actually see it, and Drive spends on advertising across six platforms to make that happen.
- Canonicalizes cost and performance data across all six platforms into one comparable shape.
- Joins that with GA4 traffic on individual listings, so spend can be attributed down to "did this bring a consumer to a specific piece of stock."
- Feeds a separate downstream stock-appeal scoring system with traffic and lead metrics per listing — closing the loop between marketing spend, listing performance, and which stock is actually converting.
| Layer | What's there |
|---|---|
| Sources | Per-platform tables declared in models/sources (bing_ads, google_ads, meta_ads, tiktok_ads, rtb_house, insider, ga4, salesforce_lms, manifold) — landed via Integrate.io, Snowflake's native GA4 connector, or a separate database, depending on the source; see Raw sources & permissions |
| Staging | Typed, per-platform staging models (models/staging/<platform>) |
| Intermediate | Grouped by business domain, not platform: crm, paid_media, web_analytics (models/intermediate) |
| Marts | Domain-level dimension and fact tables (models/marts/crm, models/marts/paid_media, models/marts/web_analytics) |
| Reporting | Final _rpt models queried directly by BI — business, crm, inventory, paid_media, stock (models/reporting) |
This is the only one of the three dbt projects with a dedicated reporting layer on top of marts — stock and Redbook's marts are the terminal layer BI queries directly.
BI & Reporting (Sigma)
A dedicated Snowflake role (DRIVE_{ENV}_ROLE_BI) gives Sigma read-only access to the raw and processed data, plus one writable sandbox schema for Sigma's own derived tables.
This is the last mile: none of the pipelines above generate business value on their own, they generate marts. BI & Reporting is where those marts turn into the dashboards and reports that sales, marketing, and leadership actually look at — the read-only/sandbox split exists so Sigma can build its own working tables without ever being able to touch source data.
Stock Ingestion & Redbook Ingestion (platform/serverless)
Two separate ingestion paths, split into two sections because they solve different problems. Stock Ingestion: per-provider CSV parsing and column mapping, Autograb's AGVI enrichment lookup, and handoff to Snowpipe. Redbook Ingestion: a single copy-and-rename Lambda, since there's exactly one upstream source.
Dealers and dealer groups run more than a dozen different stock-management systems — Carsales, Autograb, EasyCars, Dealer Solutions, and others — each exporting a differently-shaped feed. Stock Ingestion absorbs that variety so onboarding a dealer's existing system is Drive's problem to solve, not a requirement the dealer has to meet. It's the friction-remover on the supply side of the marketplace.
Infrastructure (platform/terraform)
Snowflake databases, schemas, warehouses, roles, and Snowpipe definitions; AWS infrastructure (S3, Lambda wiring, SQS); shared secrets via SSM.
None of the above generates revenue if it's down, insecure, or expensive to run for no reason:
- Uptime — keeps stock flowing continuously.
- Access control — keeps dealer and business data appropriately restricted.
- Cost — keeps warehouse spend proportionate to actual load.
How to use this site
- Start here for orientation, then use search (⌘K) for anything specific.
- The right rail lists in-page headings once a page has any.
- This site is still growing — Getting Started is complete; the rest of the sections in the sidebar are next.
Contributing
Edit the source under docs/site/pages/, add new pages to docs/site/lib/sections.ts so they appear in the sidebar, and open a PR. See docs/site/README.md for local development, and .claude/skills/writing-docs/ for how pages here should be written.