Dagster Scheduled Job Failure Analysis Report

Date: December 6, 2025
Investigation Time: 23:40-23:43 UTC
Status: 🔴 Critical Issues Identified

Executive Summary

The scheduled bronze_sync job is experiencing multiple critical failures across different assets. The primary issues are:

DLT Pipeline File Management Issues - FileNotFoundError for missing DLT pipeline state files
Schema Mismatch Errors - Products sync failing due to Iceberg table schema field mismatches
Thread Pool Cleanup Issues - DLT thread pools not shutting down cleanly, causing cascading failures
Retry Exhaustion - Multiple assets exhausting their 3-retry limit

Critical Issues

Issue #1: DLT Pipeline FileNotFoundError (HIGH PRIORITY)

Affected Assets:

bronze__shipping_addresses (multiple connections)
bronze__cross_references (connection: 973f72c8-fd3e-4aee-a6bc-8267c119a748)

Error Pattern:

FileNotFoundError: [Errno 2] No such file or directory:
'/app/.dlt/pipelines/dlt_shipping_<connection_id>/load/normalized/<load_id>/new_jobs'

Example Errors:

dlt_shipping_e8847315-f998-40f4-9675-76b4346fdacd/load/normalized/1765063588.5478535/new_jobs
dlt_xrefs_973f72c8-fd3e-4aee-a6bc-8267c119a748/load/new/fbb7c1dee187f3eb → schema.json

Root Cause Analysis:

DLT creates pipeline state files in /app/.dlt/pipelines/ during execution
These files are used to track load state, normalized data, and job status
Files are being deleted or not created properly between retries
Possible causes:
1. Container volume persistence: .dlt directory may not be properly persisted
2. Concurrent execution: Multiple runs for same connection may be interfering
3. Cleanup race conditions: DLT cleanup happening before load completes
4. File system issues: Docker volume mount issues or permissions

Impact:

Shipping addresses sync failing after 3 retries
Cross-references sync failing after retries
Data not being loaded to Iceberg tables

Evidence:

2025-12-06 23:38:09 - STEP_UP_FOR_RETRY - bronze__shipping_addresses
RuntimeError: Shipping addresses sync failed: Pipeline execution failed at `step=load`
when processing package with `load_id=1765063588.5478535` with exception:
FileNotFoundError: [Errno 2] No such file or directory:
'/app/.dlt/pipelines/dlt_shipping_e8847315-f998-40f4-9675-76b4346fdacd/load/normalized/1765063588.5478535/new_jobs'

Issue #2: Schema Mismatch in Products Sync (HIGH PRIORITY)

Affected Asset:

bronze__products (connection: da2f05f8-20a2-4c0b-b1bb-a24778229847, type: demo)

Error:

LoadClientJobRetry: Job with `job_id=products.da2a460c01.reference` had 15 retries
which is a multiple of `max_retry_count=5`. Exiting retry loop.

Target schema's field names are not matching the table's field names:
Expected: ['connection_id', 'list_price', 'erp_product_id', 'name', 'description',
           'unit_of_measure', 'delete_flag', 'active', 'weight', 'weight_uom',
           'manufacturer', 'category_code', 'pricing__price1', 'pricing__price2',
           'pricing__price3', 'pricing__price4', 'pricing__price5',
           'has_product_image', 'date_last_modified', '_sync_source',
           '_dlt_load_id', '_dlt_id']

Actual: ['connection_id', 'list_price', 'erp_product_id', 'name', 'description',
         'unit_of_measure', 'delete_flag', 'active', 'weight', 'weight_uom',
         'manufacturer', 'category_code', 'pricing__price1', 'pricing__price2',
         'pricing__price3', 'pricing__price4', 'pricing__price5',
         'has_product_image', 'date_last_modified', '_sync_source',
         '_dlt_load_id', '_dlt_id', 'image_url', 'r2_image_key', 'last_synced_at',
         'created_at', 'pricing__price6', 'pricing__price7', 'pricing__price8',
         'pricing__price9', 'pricing__price10', 'updated_at']

Root Cause:

Iceberg table schema has additional fields that DLT transformer is not providing:
- image_url, r2_image_key, last_synced_at, created_at
- pricing__price6 through pricing__price10
- updated_at
Transformer is producing a subset of fields, but Iceberg table expects all fields
This is a schema evolution issue - table was created with more fields than transformer provides

Impact:

Products sync completely failing (exhausted 3 retries)
Entire bronze_sync job failing when products step fails
No product data being loaded

Evidence:

2025-12-06 23:40:37 - STEP_FAILURE - bronze__products
Exceeded max_retries of 3:
RuntimeError: Products sync failed: Pipeline execution failed at `step=load`

Issue #3: Thread Pool Shutdown Issues (MEDIUM PRIORITY)

Affected Assets:

bronze__shipping_addresses (multiple connections)
bronze__cross_references

Error Pattern:

[ERROR] dlt|pool_runner.py|shutdown:88|Shutdown of dlt thread pool dlt-pool-<id>
could not be completed in 2.0 seconds. All the tasks in the pool are completed,
nevertheless some threads didn't join.

RuntimeError: cannot schedule new futures after shutdown

Root Cause:

DLT uses thread pools for parallel processing (Iceberg writes, data processing)
Thread pools are being shut down while operations are still in progress
This happens when:
1. Retry logic triggers cleanup before operations complete
2. Multiple concurrent syncs for same connection
3. Iceberg table operations (append/upsert) still running when pool shuts down

Impact:

Cascading failures - thread pool shutdown causes Iceberg write failures
Data corruption risk - partial writes may occur
Resource leaks - threads not properly cleaned up

Evidence:

2025-12-06 23:41:42 - [ERROR] dlt|pool_runner.py|shutdown:88
Shutdown of dlt thread pool dlt-pool-281473341747232- could not be completed in 2.0 seconds

RuntimeError: cannot schedule new futures after shutdown
  File "/app/.venv/lib/python3.11/site-packages/pyiceberg/table/__init__.py", line 485, in append
    data_files = executor.map(write_parquet, tasks)

Issue #4: Retry Exhaustion (MEDIUM PRIORITY)

Affected Assets:

bronze__products - Exceeded 3 retries
bronze__shipping_addresses - Multiple connections exceeded 3 retries
bronze__cross_references - Retries occurring but eventually succeeding

Current Retry Policy:

Max retries: 3
Delay: 30 seconds
Backoff: Exponential (30s, 60s, 120s)

Issues:

Transient errors not resolving: FileNotFoundError and schema mismatches are not transient - they won’t resolve on retry
Retry delay too short: 30 seconds may not be enough for DLT pipeline cleanup
No differentiation: All errors get same retry treatment, even non-retryable errors

Impact:

Legitimate transient errors (network, API rate limits) may not get enough retries
Non-retryable errors waste time with unnecessary retries
Jobs fail after exhausting retries instead of failing fast for known issues

Additional Observations

Successful Syncs

Some syncs are completing successfully:

bronze__shipping_addresses for connections: 49fc4591-1eb5-41d7-88e7-d8a88730cd83, 996523e0-aee0-452e-8ac7-35a44f2e8812
bronze__cross_references eventually succeeding after retries
bronze__ship_to_links completing successfully

Warning Messages

Schema warnings about missing columns (phone_number, email_address) in shipping addresses - these are expected and handled

Alert System

sync_failure_alert sensor is working and logging failures correctly
Failures are being detected and logged

Recommended Fixes (Priority Order)

1. Fix Schema Mismatch (CRITICAL - Blocks Products Sync)

Action Items:

Update product transformer to include all Iceberg table fields:
- image_url, r2_image_key (if image sync enabled)
- last_synced_at, created_at, updated_at (timestamps)
- pricing__price6 through pricing__price10 (all 10 price levels)
OR: Update Iceberg table schema to match transformer output (schema evolution)
Add schema validation before DLT load to catch mismatches early
Consider using DLT’s schema evolution features

Files to Check:

apps/dagster/erp_pipeline/transformers/products.py
apps/dagster/erp_pipeline/implementations/p21_sync_orchestrator.py
apps/dagster/erp_pipeline/implementations/demo_sync_orchestrator.py

2. Fix DLT Pipeline File Management (HIGH PRIORITY)

Action Items:

Ensure /app/.dlt directory is properly persisted (check Docker volume mounts)
Add cleanup logic to remove stale DLT pipeline files before new runs
Implement proper file locking to prevent concurrent access to same pipeline
Add retry logic specifically for FileNotFoundError with file recreation
Consider using DLT’s pipeline.run() with proper state management

Investigation Needed:

Check if .dlt directory is in Docker volume mount
Verify file permissions and ownership
Check for concurrent runs for same connection_id

Files to Check:

docker-compose.yml (volume mounts)
apps/dagster/erp_pipeline/implementations/*_sync_orchestrator.py
DLT pipeline initialization code

3. Fix Thread Pool Cleanup (MEDIUM PRIORITY)

Action Items:

Increase DLT thread pool shutdown timeout (currently 2.0 seconds)
Ensure all Iceberg operations complete before pool shutdown
Add proper cleanup sequence: wait for operations → shutdown pool → cleanup files
Consider using context managers for thread pool lifecycle

Configuration:

Check DLT configuration for thread pool settings
Review PyIceberg executor configuration

4. Improve Retry Logic (MEDIUM PRIORITY)

Action Items:

Differentiate retryable vs non-retryable errors:
- Retryable: Network errors, API rate limits, transient file issues
- Non-retryable: Schema mismatches, authentication failures, missing required data
Increase retry delay for DLT operations (60-120 seconds)
Add exponential backoff with jitter
Fail fast for known non-retryable errors

Implementation:

Update retry policies in apps/dagster/erp_pipeline/assets/bronze.py
Add error classification logic
Consider using Dagster’s RetryPolicy with custom conditions

Statistics

Failed Runs:

bronze_sync run b9dd58b9-90b4-484f-ab5b-93d080209af5: Failed due to bronze__products step failure

Retry Patterns:

bronze__shipping_addresses: Multiple retries across different connections
bronze__cross_references: Retries occurring but eventually succeeding
bronze__products: Exhausted retries, permanent failure

Success Rate:

Some connections completing successfully
Pattern suggests connection-specific or data-specific issues

Next Steps

Immediate: Fix schema mismatch for products (blocking all product syncs)
Short-term: Investigate and fix DLT file management issues
Medium-term: Improve retry logic and error handling
Long-term: Add monitoring and alerting for schema drift

Files Requiring Investigation

apps/dagster/erp_pipeline/transformers/products.py - Check field output
apps/dagster/erp_pipeline/implementations/p21_sync_orchestrator.py - DLT pipeline setup
apps/dagster/erp_pipeline/implementations/demo_sync_orchestrator.py - DLT pipeline setup
docker-compose.yml - Volume mount configuration for .dlt directory
apps/dagster/erp_pipeline/assets/bronze.py - Retry policies and error handling

Report Generated: 2025-12-06 23:50 UTC
Investigator: AI Assistant
Status: Ready for Engineering Team Review