Skip to main content

ETL Process New Files and Debug File Processing Errors

Steps to Configure the Processing of New Files and Debug File Processing Errors

  1. Ensure your external data source is set up to generate delimited file extracts and save them to the designated Raw Data Layer location (Azure Blob Storage, zone-data-in folder). Alternatively, you may manually upload files to the appropriate folder within the Raw Data Layer.
  2. Configure Zone Data Portal to Process a new file
    1. Add a new File Configuration (Note we usually have processes to handle delta and full files - see below - when this happens and changes need to be made to this file config please ensure you make these changes to both files)
      1. filenamePattern - The pattern used to identify the files that should be processed using this configuration. Supports wildcards or regular expressions.  Example: SalesOrder_*.csv
      2. stageTableName - The staging table used when initially loading the data from the file. The schema is always stg, and the table name should match the production table. Example: [stg].[SalesOrder]
      3. productionTableName - The destination table where data is loaded after validation and transformation. The schema is dbo, and the table name matches the actual dataset. Example: [dbo].[SalesOrder]
      4. fileType - this is used to determine what type of processing the system will do on these files
        1. Delete – Removes records from the Data Warehouse. Used when records have been deleted in the source system (e.g., NetSuite).
        2. Delta – Captures only new, changed, or deleted records from the last two days. Minimizes processing by only syncing what's changed.
        3. Full – Loads the entire dataset without partitioning. Ideal for initial loads or recovery when deltas have been missed.
        4. Partial – Loads only a portion of the dataset based on a partition field (commonly a date). Used to manage large files efficiently.
        5. Snapshot – Captures the dataset as it existed at a point in time. Useful for auditing or historical comparisons.
      5. fieldTerminator - The character used to separate fields in each row of the file. The character used to separate fields in each row of the file.  If left blank we default to using a comma. Example: , for CSVs or \t for tab-delimited files.
      6. rowTerminator -  The character(s) that indicate the end of a row. Example: 0x0a (LF) or 0x0d0a (CRLF)
      7. uniqueColumnName - The column (or set of columns??) used to uniquely identify each record. This is used for deduplication and merging logic.  Example: [Transaction Internal ID].
      8. formatFileName - The name of the format file (if using one), typically for parsing fixed-width or complex delimited files.  Example: DelimitedFormat.xml and within this file it will have the format specs.
      9. makeSourceDistinct - Boolean flag (true/false) that enforces deduplication at the source level before loading to staging.
      10. useColumnNameMatching - Boolean flag indicating whether to map columns from the file to the staging table based on column names rather than column order.  If this is True then you must define the columnNameTransformFunction to use.  The items, inventory_levels, transaction_lines, transaction_related, projects, demand_plan tables use this function. transaction_lines uses the function. Without the function then the column name from the file must exactly match the column name from the table. Using a function lets you make changes between the two. For example, the transaction_lines file outputs a field named * that we map to a column in the table named Header.
      11. columnNameTransformFunction - A function or rule applied to incoming column names (e.g., to standardize casing or replace spaces with underscores) before mapping them to database columns.  Example: [stg].[udf_TransactionLinesHeader] where the function is defined in sql to do the mapping.
      12. filenameAntiPattern - A pattern used to exclude files from being processed, even if they match the filenamePattern.  See ETL Configuration article for more details.  Example: %!_temp.csv
      13. doSoftDelete - Boolean flag that, if enabled, marks records as deleted in the production table instead of physically removing them.
      14.  isRfc4180Compliant - Boolean flag that indicates whether the file adheres to RFC 4180 CSV formatting rules (e.g., properly escaped quotes, consistent delimiters).
      15. lastRow - Used to define a condition or pattern that identifies the final data row in the file, useful for ignoring trailing footers or summary lines.
      16. fieldQuote - The character used to enclose fields, typically for strings containing delimiters. Example: " or '
  3. The FlowTasks “Load DW - Process Files (ProcessFiles)” will run through all File Configurations that are in the system and process the files.  You can specify what files to process by changing the FlowTasks.type.ProcessFiles.Files configuration field. File pattern or specific files this task should handle. The default is to use customsearch. See ETL Configuration article for more details.

  1. Add the new field into Zone Data Warehouse with the corresponding information (see Schema Change Management in Knowledge Base)
  2. Follow deployments steps here (Deployment of Changes in Knowledge Base)
  3. Confirm this is in your replicated SQL Instance!  As a reminder the Server and Database information for your environment can be found by going to the Admin -> Settings -> General page in Zone Data Portal.

Diagnosing file processing errors

Understanding the archive-error and bulk-load-error Folders in Your Raw Data Layer

As part of the Raw Data Layer processing, errors encountered during file ingestion are captured and categorized into two key folders:

archive-error Folder

This folder contains files that encountered errors during processing. Each file retains:

  • The original data timestamp.

  • The same name as the original source file.

  • The contents from the original file, including the rows that caused the error.

bulk-load-error Folder

This folder holds a variety of error-related files, which can be used to diagnose specific failures:

  1. Files with ERROR in the filename and the original file extension
     These contain only the rows that failed to load during the bulk import process.

  2. Files with ERROR in the middle and ending with Error.Txt
     These provide error messages and diagnostic details, including error codes and positions.

Example Error Message Breakdown

You might encounter an error message like the following:

Row 4974 File Offset 7565589 ErrorFile Offset 0 - HRESULT 0x80004005

Here’s what it means:

  • Row 4974: The failure occurred while processing the 4974th row of the file.

  • File Offset 7565589: This is the byte position (7,565,589) in the file where the issue was detected.

  • ErrorFile Offset 0: Indicates there’s no additional error file information, or the error log failed at its first byte.

  • HRESULT 0x80004005: A generic “unspecified error” from Windows, often used when a failure occurs without a more specific message.

What’s Next?

We are actively working on enhancing system visibility by:

  • Providing clearer, more actionable error messages.

  • Surfacing error details in the Zone Data Portal for quicker diagnosis and resolution.

In the meantime, if you encounter a file processing error or need help interpreting these logs, please contact Zone Support for assistance.

Was this article helpful?