Step 1: Build storage staging
Direct Claude Code to write the SQL for stg_storage. This staging table takes raw storage readings from both elevators and produces clean, consistently formatted output.
The transformation work: standardize bin IDs so both formats map to a consistent scheme with elevator identification. Cast types -- moisture readings as floats, dates as proper date types. Rename columns to match your naming convention. Add the elevator column if it is not already present from loading.
Pay attention to the bin ID standardization. Both formats need to coexist in the same table while remaining identifiable. You decided the schema; now the staging layer implements it.
Step 2: Verify storage staging
Run the staging SQL. Count the rows in stg_storage.
This number must match the raw storage row count exactly. No records should be lost at this stage -- staging cleans data, it does not filter it. If the count is off, a row was silently dropped during type casting or a bin ID failed to parse. Check which records are missing.
Step 3: Build weather staging
Direct Claude Code to write the SQL for stg_weather. This staging table takes the raw weather data extracted from the API and converts it to match your schema.
The critical transformation: convert timestamps from UTC to UTC+6 (Kazakhstan time). You identified this in Unit 2. If you skip it, the join in the mart will pair storage readings with weather from the wrong day -- and the spoilage correlations will be off by six hours. For daily grain, this means some readings pair with yesterday's weather instead of today's.
Step 4: Verify weather staging
Run the weather staging SQL. Count the rows in stg_weather. This must match the raw weather row count exactly.
Spot-check a few dates. Pick a date from the storage data and verify that the corresponding weather record in stg_weather shows the date in Kazakhstan time, not UTC.
Step 5: Build the mart
Direct Claude Code to write the SQL for the fact table. This joins storage readings with weather conditions on date -- the reading date from stg_storage matched to the observation date from stg_weather.
The grain you decided in Unit 3 becomes concrete here. One row per bin per day with both storage and weather fields on the same row. Every storage reading that has a matching weather date should appear in the mart.
Step 6: Verify the mart
Check the mart row count against the verification checklist.
Then spot-check a specific case. Find a spoilage event in the mart and look at the weather conditions for that date. The verification checklist includes specific dates where spoilage occurred and what the weather should show.
If the spot-check fails -- if the weather conditions do not match what the checklist says -- the most likely cause is the timezone alignment. A spoilage event on January 15 paired with January 14 weather would show the wrong temperature. Check whether the staging layer's timezone conversion is working correctly.
Step 7: Self-review
Direct AI to review its own work. Use a specific prompt: "Review the staging and mart SQL against the pipeline spec. List every gap in bin ID standardization, timezone conversion, join keys, and grain."
A vague request like "does this look right?" produces reassurance, not findings. A specific review prompt forces AI to compare its output against a reference. Evaluate what the review catches. Check whether it found real issues or produced confident agreement. If it missed something you caught through the row count check or the spot-check, that tells you about the technique's limits at this stage.
Check your understanding: Staging counts match raw. Mart count matches checklist. Spoilage event correlates correctly with weather.