CDE Orchestration: Late Data, Backfills, and a Schema Change
A Cloudera Data Engineering virtual cluster runs 14 Spark jobs orchestrated by Airflow. The DAG has a row_count_check task that queries Hive and fails if today's orders partition is below 90% of the 7-day average. Last Tuesday the source system loaded 60% of normal rows at 03:00 and the rest at 09:30. The check failed at 04:00, blocked downstream finance jobs, and the SLA was missed. The team disabled the check to recover, but then a schema change added a nullable column and a downstream job wrote nulls into a NOT NULL target. Monthly 14-day backfills currently use INSERT OVERWRITE on dt partitions; some overlap with live loads, causing duplicates and temporary missing partitions. The team wants reliable SLAs, safe backfills, and data quality without blocking on late data.
1. Redesign the data-quality gate and DAG dependencies so late data does not automatically block finance, while still catching genuine data loss.
Read the worked answer
Separate completeness from freshness. A single 90% threshold at 04:00 is too brittle when the source has a known late-load pattern. Add a two-stage gate: at 04:00 check a lower floor, such as 50% of the 7-day median, and raise an alert rather than failing the DAG. At 06:00 check the normal threshold, but evaluate it against a late-data watermark and a trend band, not just a raw average. Finance can run on the validated snapshot if the floor is met; if not, the DAG should quarantine the partition and notify the source owner.
Do not disable the check when it fires. Add schema validation before downstream jobs, and make the row-count task emit metrics so you can see whether a drop is isolated or broad. Use a sensor or event trigger for the late arrival instead of a fixed clock where possible. Keep a separate path for correction loads so finance does not wait indefinitely for the last 10% of rows.
2. How would you make monthly 14-day backfills idempotent and safe when they overlap live loads?
Read the worked answer
Stop using INSERT OVERWRITE directly on production partitions while live loads are running. Stage the backfill into a separate database or table, deduplicate by the business key with the latest ingest timestamp, and write only the affected dt partitions. In Hive ACID or Iceberg, use MERGE or an atomic partition replace. In plain Hive, build a temp table with row_number() over (partition by order_id order by ingest_ts desc), validate counts, then overwrite the specific partition under a lock or workflow mutex.
Coordinate backfill and live load through Airflow so they cannot write the same partition concurrently. Record a backfill run ID and source file checksums in a control table so reruns skip completed work. Validate row counts and key uniqueness before and after each partition swap. If a partition must be temporarily unavailable, write to a shadow location and swap atomically rather than deleting first. Test the overlap scenario on a non-production partition.
3. What controls would prevent the nullable-column incident from reaching the NOT NULL target?
Read the worked answer
The nullable column was allowed to flow into a stricter target because the pipeline used permissive writes and had no contract check. Add an explicit schema contract at ingestion: required columns, types, nullability, and allowed defaults. Validate the contract before the downstream write and fail the job in a quarantine path, not in the finance path. In Spark, use ANSI store assignment policy so nulls into NOT NULL fail fast rather than being silently coerced.
Map columns explicitly instead of relying on positional inserts or permissive mode. Add a unit test that feeds a null in the new column and expects rejection. For genuinely optional fields, define a default or a separate nullable staging column, and make the NOT NULL target require a deliberate transformation. Record schema versions in the control table so a change can be traced to the job run that introduced it.