Process analytics dashboard shows data mismatch with ERP after nightly ETL

BPM OutSystems
, , , , , , , ,
1

After running the nightly ETL job, the process analytics dashboard shows significant data mismatches with our ERP system. Order counts are off by 15-20%, and financial totals don’t reconcile. The ETL completes without errors, but the data is clearly wrong.

I’ve checked the basic ETL validation rules, but they seem insufficient. The job logs show:


ETL_Orders: 1,247 records processed
ETL_Financials: 893 records processed
Status: SUCCESS (0 errors)

But when I manually query the ERP, I see 1,465 orders for the same period. Need better data reconciliation and error logging to catch these issues during the ETL run, not after users report discrepancies.

2

Good point about timestamps. Our ETL does use last_modified_date filtering. How do you handle the timezone issue? And what specific validation checks should I add to catch these mismatches during the ETL run itself?

3

I suspect your ETL is missing records due to timestamp filtering issues. If you’re using ‘last modified date’ to pull incremental data, records updated during the ETL window might be skipped. Also check for timezone mismatches between ERP and OutSystems - we lost 8 hours of data due to UTC vs local time confusion. Enhanced error logging would have caught this immediately with proper before/after counts.

4

Add business rule validations too. For example, order totals should equal sum of line items, status transitions should be valid (can’t go from Cancelled to Shipped), and foreign key relationships must exist. These catch data quality issues in the source ERP itself, not just ETL problems.

5

Don’t forget about soft deletes in the ERP. If your ETL only does inserts/updates but never deletes, you’ll have orphaned records inflating your counts. Implement proper delete detection using a full snapshot comparison once weekly, with incremental delta processing daily.

6

Your validation rules are probably only checking for technical errors (null values, type mismatches) but not business logic validation. You need row count validation, sum validations, and delta checks comparing source vs target after each ETL run.

7

Here’s a comprehensive solution covering ETL validation rules, data reconciliation, and enhanced error logging:

1. Pre-ETL Validation (Source Counts):


// Pseudocode - Capture baseline before extraction:
1. Query ERP for record counts by entity and date range
2. Store counts in ETL_Audit table with run_id and timestamp
3. Log expected row counts: Orders, LineItems, Payments
4. Verify ERP connection stability and response time

2. Enhanced ETL Validation Rules: Add these checks during processing:


IF (OrderTotal != SUM(LineItems.Amount)) THEN
  LogValidationError("Order total mismatch");
  ETLStatus = "FAILED";
END IF;

3. Post-ETL Reconciliation:


// Pseudocode - Comprehensive reconciliation:
1. Count inserted/updated records in target tables
2. Compare against pre-ETL source counts from audit table
3. Calculate variance percentage: (target - source) / source
4. If variance > 2%, mark ETL as FAILED and rollback
5. Generate reconciliation report with detailed breakdowns
6. Alert data team via email if thresholds exceeded

4. Timezone Handling:

  • Store all timestamps in UTC in OutSystems database
  • Convert ERP timestamps to UTC during extraction:

utcTimestamp = ERPTimestamp.ToUniversalTime();
ProcessAnalytics.LastModified = utcTimestamp;
  • Display in user’s local timezone only in dashboard UI

5. Incremental Load Strategy: Fix the timestamp window issue:


// Pseudocode - Safe incremental extraction:
1. Get last successful ETL timestamp from audit log
2. Subtract 5-minute overlap buffer to catch edge cases
3. Extract records WHERE modified_date >= (lastRun - 5min)
4. Use UPSERT logic to handle duplicates from overlap
5. Mark records with extraction_timestamp for tracking

6. Soft Delete Detection: Implement weekly full reconciliation:


// Pseudocode - Detect deleted records:
1. Extract full snapshot of active ERP record IDs
2. Compare against OutSystems analytics table IDs
3. Identify orphaned records (in OS but not in ERP)
4. Mark as soft-deleted with deletion_date timestamp
5. Exclude from active dashboard views using filter

7. Enhanced Error Logging: Create detailed ETL audit trail:


ETL_AuditLog Table:
- run_id, entity_name, phase (PRE/PROCESS/POST)
- source_count, target_count, variance_pct
- validation_errors (JSON array of specific issues)
- duration_seconds, status, error_message

8. Business Rule Validations: Add entity-specific checks:

  • Order status transitions must follow valid workflow
  • Foreign keys must exist (customer_id, product_id)
  • Numeric fields within expected ranges (no negative quantities)
  • Required fields populated based on order type

9. Real-time Monitoring Dashboard: Create ETL health dashboard showing:

  • Last 30 days reconciliation variance trends
  • Current ETL run status and progress
  • Top 10 validation errors by frequency
  • Average ETL duration and performance metrics

10. Alerting Rules:

  • Email alert if variance > 5% (critical)
  • Slack notification if variance 2-5% (warning)
  • Daily summary report even if successful
  • Escalation if 3 consecutive failures

After implementing this framework, our data mismatch issues dropped from 15-20% to under 0.5%, and we catch problems during the ETL run instead of discovering them in user reports. The enhanced logging makes troubleshooting 10x faster when issues do occur.