ETL Troubleshooting Guide

The ETL (Extract, Transform, Load) process in ACE streamlines data imports by enabling users to upload, transform, and integrate large datasets. ETL jobs rely on configurations, profiles, and proper file formats to ensure accurate data loads. This guide explains common ETL challenges, provides troubleshooting steps, and highlights essential tools such as ETL Import History, Delta Logs, and Multithreading.

Common ETL Issues

1. File Formatting Errors: The imported file does not meet ACE’s layout requirements or contains invalid data.

2. Transformation Failures: Mismatched or missing field mappings in ETL profiles lead to incomplete imports.

3. Load Errors: Duplicate records, schema mismatches, or bundling issues disrupt the data load process.

4. Performance Bottlenecks: ETL jobs take too long to complete or fail to finish due to large file sizes or system overload.

5. Corrupted Files: Files fail to upload or load properly due to file corruption.

Step-by-Step Troubleshooting

1. Locating ETL Import History

To troubleshoot a failed ETL job, start by reviewing its Import History:

1. Navigate to Transactions → ETL Import → History.

2. On the Imports list screen, locate the file you want to analyze.

3. Click the file name to open the Delta Log page, which contains:

   1. Delta Status: Completion status (e.g., COMPLETED, FAILED).

   2. Start Time, End Time, and Duration: Identify timing issues.

   3. Error Count: Review the number of errors logged.

   4. Duplicate Count: Track duplicates that may disrupt imports.

4. Use the Download Messages button to export detailed logs, including warnings, errors, and impacted rows.

2. Resolving File Formatting Issues

Symptoms: Errors like "File format invalid" or "Missing required fields."

Steps:

1. Verify file layout against ACE’s Import Layouts documentation:

   1. File must be in a supported format (e.g., CSV, TSV, or PSV).

   2. Include mandatory fields like account numbers, dates, or client IDs.

2. Check for common formatting errors:

   1. Invalid characters (e.g., special symbols).

   2. Incorrect column headers or missing columns.

3. Correct the file and reload the data.

3. Fixing Transformation Failures

Symptoms: Errors such as "Field mapping failed" or incorrect data placement.

Steps:

1. Navigate to the ETL Profile used for the job under Accounts → ETL Import → Profiles.

2. Review and correct field mappings:

   1. Open the Data Map section to map fields from the file to ACE fields.

   2. Confirm correct mappings for required fields (e.g., client codes, demographic data).

3. Apply changes and re-run the ETL job.

4. For custom columns:

   1. If prompted to update columns, confirm changes and map new fields in the Data Map section. See Handling Custom Columns in ETL Profile Updates for details.

4. Resolving Load Errors

Symptoms: Errors such as "Duplicate key violation" or "Schema mismatch."

Steps:

1. Review the Delta Log to identify problematic rows:

   1. Use Download Messages to locate duplicates or mismatched data.

   2. Focus on fields like Debt ID or Client Code.

2. Deduplicate the file:

   1. Use Excel, SQL queries, or data-cleaning tools to remove duplicate records.

3. Check schema alignment:

   1. Ensure source fields match ACE’s field types, lengths, and formats.

4. Disable problematic features:

   1. Temporarily disable Bundling or SOL (Statute of Limitations) for legacy data imports.

5. Optimizing Performance

Symptoms: ETL jobs take too long or fail mid-process.

Steps:

1. Enable Multithreading:

   1. Go to Setup → System and set the Maximum ETL Threads (recommended: 10–30).

   2. Enable Multithreading in the ETL profile for faster imports. See ETL Multithreading for details.

2. Split large files:

   1. Divide data into smaller batches for faster processing.

3. Schedule during off-peak hours:

   1. Run ETL jobs when system activity is low.

4. Monitor system resources:

   1. Check CPU and memory usage during the job. Adjust resources as needed. See ACE System Metrics: Tracking CPU and Memory Usage for more information.

6. Fixing Corrupted Files

Symptoms: Errors such as "File could not be read" or "Unexpected file termination."

Steps:

1. Detect Corruption:

   1. Check if the file opens properly in a text editor or spreadsheet application.

   2. Look for signs of corruption, such as:

      1. Garbled text or unreadable characters.

      2. Unexpected file size (too large or too small).

      3. Missing data or rows that appear incomplete.

2. Resolve Corruption:

   1. Re-export the file: If possible, regenerate the file from the original data source.

   2. Clean the file:

      1. Open the file in a spreadsheet tool (e.g., Excel).

      2. Remove invalid characters or problematic rows.

      3. Save the file as a new CSV or TSV file.

3. Test the File:

   1. Upload a smaller, clean version of the file to verify it loads correctly.

   2. If successful, upload the full file.

Key Tools and Features

Delta Log Details

The Delta Log provides a comprehensive summary of ETL jobs:

• General Information:

  • Delta Status: COMPLETED, FAILED, or ROLLED BACK.

  • Error Count: Total errors during the job.

  • Records Loaded by Type: Breakdown of records imported (e.g., debts, transactions, demographics).

• Rollback:

  • If errors are found, use the Rollback button to undo the data load.

  • Note: Accounts with existing transaction history cannot be rolled back.

Multithreading

• Enables parallel processing of large files, reducing load times.

• Best for large data imports like client lists, transactions, and account flags.

• Caution: Do not use multithreading if the order of rows in the file matters.

ETL Profiles

• Reusable configurations for consistent imports of similarly formatted files.

• Steps to create an ETL profile:

  • Navigate to Accounts → ETL Import → Profiles → New.

  • Upload a sample file to map fields.

  • Save the profile for future imports.

Preventative Measures

1. Standardize File Formats:

   1. Use ACE-approved layouts and templates to reduce formatting errors. See our Import Layout Category for details.

2. Audit ETL Profiles Regularly:

   1. Verify that field mappings are accurate and up to date.

3. Schedule Imports Strategically:

   1. Run large imports during off-peak hours to optimize performance.

4. Enable Notifications:

   1. Use the On Errors Notify User ID feature to alert administrators of failed jobs.

Example Scenarios

Scenario 1: Duplicate Key Violation

• Cause: The source file contains duplicate Debt IDs.

• Solution:

  • Open the Delta Log and download error messages.

  • Deduplicate the file using Excel or SQL.

  • Reload the data.

Scenario 2: File Fails to Load Due to Schema Mismatch

• Cause: Field types in the file (e.g., text instead of numeric) don’t match ACE requirements.

• Solution:

  • Correct the mismatched fields in the file.

  • Reload the job and verify the data mapping.

Reporting Issues to Support

If you encounter errors or unexpected behavior on any page in the ACE system, follow the steps below to ensure that our support team has the information needed to assist you effectively.

Steps to Report an Issue

1. Take a Screenshot:

   1. Capture the entire page, including:

      1. The details visible on the page (e.g., fields, statuses, or tasks).

      2. Any error messages displayed.

      3. The URL in your browser’s address bar.

2. Include Key Details:

   1. In addition to the screenshot, provide the following information:

      1. What you were doing: A brief explanation of the actions you were taking when the issue occurred.

      2. Steps to reproduce: A clear description of how the issue can be replicated (if applicable).

      3. Error messages: Copy or include the exact error message text, if any.

3. Contact Support:

   1. Submit the screenshot and all relevant details to support.