Tracing a stock file end to end

A dealer's file crosses two S3 buckets and one Snowpipe before it's a row in Snowflake — the dealer's own source bucket, then drive-{env}-stock-bronze, which is what Snowflake actually reads from. Every row in STOCK_RAW, stock_stg, and stock_int carries the identity of both stops. This page shows the real path between them, then how to use that: given a dealer's upload, find the rows it produced; given a row you're investigating, find the file it came from.

The full path, bucket by bucket

drive-{env}-stock-bronze (e.g. drive-staging-stock-bronze, drive-prod-stock-bronze — Terraform module snowflake_stock_bronze_bucket) is the bucket Snowflake actually reads from. It exists because Snowpipe's COPY INTO STOCK_RAW is written against one canonical JSON shape — it has no idea how to parse 12 different providers' CSV dialects. stockInboundProcess does that translation once, per file, before anything reaches Snowflake: download the dealer's original CSV from the source bucket, map its columns, then re-upload the result as JSON into this bucket. Nothing about the dealer's original format survives past this point — from here on, every provider looks identical to Snowflake.

The bronze bucket is also where auto-ingest is actually wired up: an ObjectCreated event on a .json key publishes to the SNS topic {client_code}-{env}-stock-bronze-bucket-notifications, which Snowflake is subscribed to (the SNS topic policy explicitly grants Snowflake's own IAM principal sns:Subscribe, alongside S3's sns:Publish). That's what triggers STOCK_BRONZE_PIPE — not a schedule, not the dbt task, the SNS message from this exact bucket.

The source bucket, by contrast, isn't managed in this repo at all. This Terraform only reads its ID/ARN from SSM (/{env}/drive/s3/stock.{domain}/id) to attach the Lambda trigger — it's provisioned by a different stack. Its own versioning and retention policy aren't visible from this codebase; treat "the source object's version is still there" as an assumption to verify, not a guarantee this repo can make.

The bronze bucket's retention is a hard 90-day window. Its lifecycle rule transitions objects to STANDARD_IA after 30 days, then deletes them — current and non-current versions both — after 90. A bronze JSON older than that is gone, whatever the metadata_uuid/file_name columns say. The bronze bucket does have its own S3 versioning enabled, but because every upload gets a unique key already (see below), that versioning is never actually exercised — no bronze key is ever overwritten in normal operation.

The bronze filename encodes the trail

A bronze JSON key looks like this:

dealersolutions/001On00000kpQwJIAU:aGtPlq9YAQjKMXAZw5rzreTYrlbSk34R.json
   provider          salesforce id             s3 version id

The version id segment is the source CSV's own S3 VersionId, captured by stockInboundProcess at the moment it downloads the file — this is what ties a bronze object back to one specific upload, not just one dealer. Snowpipe's COPY INTO STOCK_RAW parses this filename straight into columns — nothing here is read from the JSON payload itself:

data_provider_dealer_id/metadata_uuid/file_name all survive unchanged from STOCK_RAW through stock_stg into stock_int — so any of the three tables can be queried directly, without needing to join back through the others.

One naming quirk to expect: a provider whose source files don't use a .csv extension — Virtualyard uploads .txt — ends up with that extension embedded in the dealer id segment, e.g. 001On00000kpjzuIAA.txt. It's cosmetic, not a data problem.

Forward: from an upload to the rows it produced

Start from a bronze filename (or just the version id from it) and confirm what it did downstream. Take the file above:

1. Confirm it landed in STOCK_RAW:

SELECT provider, data_provider_dealer_id, data_provider_stock_id, processing_status, ingested_at
FROM VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.STOCK_RAW
WHERE metadata_uuid = 'aGtPlq9YAQjKMXAZw5rzreTYrlbSk34R';

processing_status tells you what happened to it: pending (not yet picked up by a dbt run), complete (processed), failed (rejected — see Rejection & validation tracking), or skipped (superseded by a newer file for the same vehicle before it was ever processed — see STOCK_RAW schema & file superseding).

2. Check whether it survived into stock_stg:

SELECT stock_unique_key, batch_id, ingested_at
FROM VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.stock_stg
WHERE metadata_uuid = 'aGtPlq9YAQjKMXAZw5rzreTYrlbSk34R';

Zero rows here despite a STOCK_RAW match usually means a later file for the same vehicle (data_provider_dealer_id + data_provider_stock_id) arrived with a more recent ingested_at — stock_stg keeps one row per vehicle, not one per file.

3. Check whether it made it all the way into stock_int:

SELECT stock_unique_key, redbook_vehicle_key, redbook_vehicle_key_source
FROM VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.stock_int
WHERE metadata_uuid = 'aGtPlq9YAQjKMXAZw5rzreTYrlbSk34R';

Present in stock_stg but missing here means it never resolved a Redbook Vehicle Key — stock_int drops those rows entirely. Confirm it with stock_rejected_per_batch, which is built for exactly this gap (see Rejection & validation tracking).

Backward: from a row to the file it came from

Start from a vehicle you're investigating in stock_int (or a mart built on it) and work back to the exact file — and bucket — that produced it:

1. Pull the file identity off the row:

SELECT file_name, metadata_uuid, ingested_at, batch_id
FROM VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.stock_int
WHERE data_provider_dealer_id = '001On00000kpQwJIAU'
  AND data_provider_stock_id  = '12345';

2. Pull the bronze JSON that was actually ingested, using file_name as the key against the bronze bucket — only if ingested_at is within the last 90 days, past which the bucket's lifecycle rule has deleted it:

aws s3 cp "s3://drive-{env}-stock-bronze/${file_name}" ./bronze.json

3. Pull the dealer's original CSV as it existed at that moment, using metadata_uuid as the S3 VersionId against the source bucket (stock.drive.com.au in prod, stock.drivemustang.com.au in staging/dev):

aws s3api get-object \
  --bucket stock.drive.com.au \
  --key "dealersolutions/001On00000kpQwJIAU.csv" \
  --version-id "${metadata_uuid}" \
  ./original-upload.csv

This depends on the source bucket still retaining that version — since that bucket isn't managed in this repo, there's no lifecycle rule visible here to confirm how long that holds. If both steps 2 and 3 have aged out, the columns on the stock_int row itself are the only surviving record of that upload.

Common investigations

What this trail doesn't tell you

This identifies provenance — which file, which bucket, which version, which run — not business state. It won't tell you whether a vehicle's is_sold or created_at value is correct after a full refresh; see Known issues for what full refresh resets regardless of how well a row's origin is traced. It also can't promise a source-bucket object is still retrievable — that bucket's retention policy lives outside this repo.

See also

• Stock Ingestion — the router/processor split that reads the file and builds the bronze JSON
• STOCK_RAW schema & file superseding
• Rejection & validation tracking
• Known issues