Yesterday's Sales Missing After Late Source Arrival

Diagnose why yesterday's sales are late, fix the PySpark transformation so sales are assigned to the correct business_date, and propose an Airflow scheduling change that waits for the required source partition before loading the warehouse. For executable validation, create a Spark DataFrame named daily_sales or fixed_daily_sales with business_date, order_count, and gross_sales. Do not write to the warehouse from the practice runner.

Choose scenario

Reference

Problem

The pipeline expected yesterday's sales export to be available before the 2 AM Airflow run. The source file arrived late, so the first run failed. A later retry picked up data, but the PySpark transformation filters by ingestion date instead of sales business date, so late-arriving rows for yesterday still do not land in the correct dashboard partition.

Required outcome

INC-2026-05-08-DAILY-SALES

P1 - Executive sales dashboard incorrect

Morning sales review is blocked and Finance cannot reconcile yesterday's daily revenue.

Pipeline: daily_sales_to_bigquery · Owner: Data Platform On-call

Evidence

Business ticket

Incident

Sales Ops reports yesterday's sales are missing from the 9 AM dashboard.

Expected 2 paid orders totaling 420.00 for business_date=2026-05-07. Dashboard currently shows 0 orders and 0.00 gross sales.

Airflow run history

Orchestration

The 2 AM run failed waiting for the upstream source partition.

The source success marker for dt=2026-05-07 was not available before the sensor timeout. A retry started after the file arrived, but the transform still loaded zero rows.

Source landing check

Storage

The source file did arrive, but after the scheduled run window.

The sales file for source_file_date=2026-05-07 landed at 03:21. Valid records have sale_ts_utc on 2026-05-07 but ingested_at on 2026-05-08.

PySpark transform review

Code

The job derives business_date from ingested_at instead of the sale timestamp.

Late-arriving records are filtered out because ingest_date becomes 2026-05-08 while the dashboard partition being rebuilt is 2026-05-07.

Warehouse validation

Data Quality

The warehouse partition was overwritten with an empty result.

The load step completed, but records_loaded=0 should have failed the data quality gate for a paid-sales business date.

Your diagnosis

In progress

Your attempt

Write the corrected PySpark transformation

Create daily_sales or fixed_daily_sales, then run the visible sample before submitting.

Preparing save...