ETL Process Auditing: From Technical Logs to Quality Management

Metadata Management / ETL Audit / Change Management

Why it is not enough for a company to know that an ETL process has completed successfully

Large corporate information systems use dozens or hundreds of data exchange procedures.

The ERP transfers operations to the corporate data warehouse, CRM synchronizes customer directories, electronic document management exchanges contract details, and analytical systems receive prepared information sets. A significant portion of these operations are automated through ETL platforms, specialized integration services, and scheduled database tasks.

Technical logs are typically used for their operation. They allow determining when a process was started, how long it took to execute, and whether it completed with an error.

However, this is not enough for full control of corporate information. For example, an ETL task may complete successfully but load only half of the expected records.

Another process may correctly transfer all rows but use an outdated product catalog. Third, perform the calculation using a new formula that has not been agreed upon with the finance department.

In all three cases, the technical result may appear successful, although the information will be incomplete or incorrect. To solve such problems, it is necessary to combine Metadata Management, ETL execution auditing, data quality control, and change management.

This allows the enterprise to track not only the performance of the integration process but also the characteristics of the information it processes.

Three categories of metadata for enterprise integration

It is advisable to divide metadata into technical, business, and operational.

Technical metadata

Technical metadata describes the structure of information resources. For example, table names, attribute types, indexes, file formats, connection parameters, and transformation rules.

For an ETL process, the source table, target set, and field mappings can be defined. For example:

Source: ERP.InvoiceLines
Target: DWH.FactSales

invoice_id  -> document_id
product_id  -> product_key
quantity    -> quantity
net_amount  -> revenue_amount

This is just an initial schema.

For a real process, it is also necessary to describe the rules for searching corporate keys, handling missing values, and other transformations.

Business Metadata

Business metadata explains the meaning of information. For example, the revenue_amount field may contain net revenue excluding VAT in the corporate reporting currency.

This definition must be documented separately from the technical field type. After all, the DECIMAL(18,2) type does not explain the economic meaning of the indicator.

Business Glossary is used to support business definitions.

Operational Metadata

Operational metadata describes the actual execution of information processes. They may include start time, processing duration, number of records received, number of rejected rows, process version, and the result of control checks.

It is this level that allows the transition from ordinary technical logging to full-fledged information integration auditing.

ETL Audit: Actual Execution Control

For each important ETL process, it is advisable to create a separate audit record. For example, loading sales from ERP to DWH is performed daily.

After completion, the system should store not only the ‘Success’ message but also information about the actual processing volume.

Conditional example of an audit log; the values provided are not statistics from a real enterprise. Such a record allows evaluating what exactly happened during the load.

For example, 28 rejected records might be acceptable if the company has an approved procedure for handling them. However, if there are critical financial transactions among them, the process should not be automatically considered accepted.

Therefore, the technical status and business outcome must be distinguished.

Record Count and Completeness Control

One of the basic checks is comparing the number of records in the source and target datasets. However, the requirement for complete equality is not always correct.

For example, ETL might merge duplicate records or load only active customers. In such cases, the number of rows after transformation will legitimately differ.

Therefore, the control check must align with the process logic.

Batch Loading

For batch integration, you can check the number of source documents and the number of corresponding target records.

If one document is converted into multiple rows of an analytical table, appropriate control groupings must be used.

Incremental Loading

For incremental processes, it is important to define the boundaries for retrieving changes. For example, the system can use a timestamp of the last update.

However, late adjustments to historical documents and possible differences in system time must be taken into account. If the process only uses the creation date, it may miss changes to old records.

Checksums

For financial datasets, it is advisable to compare summary totals. For example, the total sum of confirmed operations for a defined period should match the approved control indicator.

However, even an identical sum does not prove the complete correctness of the details. Therefore, it is necessary to combine quantitative and sample checks.

Data Quality Rules as part of ETL

In corporate integrations, quality control should be performed directly during processing. For example, before loading customer records, it is necessary to check mandatory identifiers, status validity, and compliance with the corporate directory.

Formalized rules are used for this purpose.

Mandatory Field Validation

If a critical attribute is missing in the document, the system may reject the record or transfer it for manual verification.

Directory Validation

The product code must comply with the approved corporate nomenclature.

If the code is unknown, the processing order must be determined. For example, it should not be automatically linked to a random product category solely for successful upload completion.

Business Rule Verification

For financial documents, rules for currency, date, and valid status matching may be used.

However, the rules must be approved by the responsible business unit.

The IT team performs the technical verification but should not independently change the accounting methodology.

Quarantine: Isolating Problematic Data

For large data streams, it is important not to halt the entire process due to every incorrect record.

However, unconditionally skipping errors is also dangerous.

One practical approach is to create a separate zone for problematic data. For example, an ETL process might transfer incorrect records to a special table.

CREATE TABLE etl_rejected_rows (
    rejection_id BIGINT PRIMARY KEY,
    execution_id VARCHAR(50),
    source_record_id VARCHAR(100),
    error_code VARCHAR(50),
    error_description VARCHAR(500),
    detected_at TIMESTAMP
);

This is a conceptual structure, not a ready-made universal schema for a specific DBMS.

It demonstrates the main characteristics necessary for analysis. In an industrial system, it is also necessary to store sufficient information for reprocessing or a reference to the protected original record.

Who Fixes Errors

A Data Steward can analyze rejected records and determine the correct values.

If the issue arose due to incorrect input in the ERP, it is advisable to perform corrections in the authoritative source. Otherwise, the company will be forced to repeat the same manual correction after each upload.

Re-processing

After correcting a problematic record, it must be possible to re-process the relevant information.

It is important to control duplicates. For example, re-uploading a financial document should not create a second copy of the same business transaction.

Data Lineage and Impact Analysis

Metadata allows not only describing the current architecture but also assessing the consequences of future changes.

For example, a company plans to change the field type of customer_id from numeric to text.

Technically, the change might seem relatively simple.

However, the corresponding attribute might be used in ERP, ETL, MDM, DWH, BI, and partner APIs. If these dependencies are not documented, the change could break several business processes.

Impact Analysis

Using Data Lineage, it is possible to establish which data components depend on the relevant field.

For example:

ERP.Customers.customer_id
        |
        +--> ETL.CustomerExtract
        |         |
        |         v
        |     DWH.DimCustomer
        |         |
        |         v
        |     BI.CustomerReport
        |
        +--> CRM Integration API
                  |
                  v
             Partner Portal

This schema allows for the creation of a list of components that need to be checked.

However, it is important to consider that not all dependencies can be automatically detected.

For example, an individual user might perform a manual export to Excel. Therefore, Impact Analysis should combine automated metadata with documentation of corporate procedures.

Apache Atlas 0.8: Metadata Platform Development

Apache Atlas 0.8-incubating was released on March 16, 2017.

Therefore, as of the date of this article, March 28, the release is already available.

Key changes include updates to the REST API, search enhancements, the ability to create and modify entities through the interface, and performance improvements.

For enterprises, this creates additional opportunities for building a catalog of Hadoop information resources.

Information Object Catalog

Atlas allows describing tables, processes, files, and other supported entities.

For example, an enterprise can register Hive tables and their associated processing pipelines.

Lineage Relationships

For supported integrations, a data lineage graph can be generated.

This helps to establish which datasets are created from other information resources.

Search

Improved search simplifies finding registered objects.

For large Data Lakes, this is an important feature.

However, it should be noted that Atlas is still in the Apache Incubator.

Also, version 0.8 should not be attributed all the capabilities of later enterprise data catalogs. For example, not every external ETL process will automatically generate a complete lineage graph without additional integration.

Apache NiFi and Data Provenance

A separate area is the control of information flow during the execution of integration flows.

Apache NiFi can be used for this purpose. As of March 2017, version 1.1.2 is available, released on February 20.

One of the important features of NiFi is Data Provenance. It allows logging of defined events in the FlowFile lifecycle.

For example, it is possible to establish when an information object was received, which components processed it, and where it was transferred.

Provenance and Lineage are not identical concepts

It is important to distinguish the operational origin of an individual object from corporate Data Lineage. NiFi can show the FlowFile’s route through the relevant processors.

However, this does not automatically explain the business formula of the final indicator. For example, Provenance will help establish that a file was transferred from ERP to Hadoop.

But to explain the calculation of net revenue, documentation of ETL or SQL transformations is needed. Therefore, for comprehensive control, several metadata sources can be combined.

Metadata as the Basis for Audit and Regulatory Preparation

It is important for enterprises not only to have correct reporting but also to explain how it is formed. This is important for internal audit, financial control, and information risk assessment.

For example, an auditor may ask which operations form a particular indicator. With the availability of a Business Glossary, Data Lineage, and ETL logs, an enterprise can reconstruct the relevant information sequence.

However, it is important to store not only the current configuration. If transformation rules have changed, it is necessary to determine which specific version was used when generating the historical report.

Preparation for GDPR

In 2016, the European Union adopted the General Data Protection Regulation. The new regulation will apply from May 25, 2018.

For enterprises whose activities fall under its requirements, this creates a need to document the processing of personal data. Metadata can help determine where personal information is stored and between which systems it is transferred.

However, the metadata catalog itself does not ensure legal compliance. It is also necessary to define the legal grounds for processing, responsible persons, retention periods, and relevant organizational procedures.

Practical Scenario: Managing ETL Changes in a Corporate DWH

Consider a hypothetical enterprise that uses Microsoft SQL Server Integration Services to load information from ERP to DWH. Over several years, more than a hundred ETL packages have been created.

Some of them have documentation, while others are only supported by individual developers. The enterprise plans to modernize the ERP and change the product catalog structure.

Stage One: Inventory

The team forms a registry of ETL processes.

For each, sources, target sets, responsible developer, and execution schedule are defined.

Stage Two: Metadata Collection

SSIS packages and corresponding SQL procedures are analyzed. Connections between information objects are formed.

Manual descriptions are added for non-standard transformations.

Stage Three: Impact Analysis

The enterprise identifies all processes that use ERP product codes.

Dependent DWH tables and analytical reports are identified.

Stage Four: New Model Preparation

MDM specialists approve the mapping rules for old and new codes.

ETL developers modify the corresponding procedures.

Stage Five: Testing

A trial load is performed in the test environment.

The number of records, control totals, and report results are compared.

Stage Six: Implementation

After the changes are approved, the updated processes are deployed to the production environment.

The version number is stored for each run.

Stage Seven: Audit

After the first production load, execution logs and informational control metrics are checked.

As a result, the company gains the ability to explain not only the fact of ETL execution but also changes in information logic.

How to Organize a Register of Information Flow Changes

For critical ETL processes, it is advisable to create a link between technical changes and corresponding requests. For example, a change in the formula of a financial indicator may have its own Change Request identifier.

The registry can store:

• the change number and its purpose;
• the responsible data owner;
• a list of modified ETL processes;
• new transformation rules;
• implementation date;
• testing results;
• rollback or correction procedure;
• links to approvals.

This allows for the restoration of the history of the development of corporate information rules. It is especially important to maintain a link with versions of software code and configurations.

For example, if an ETL process generated revenue according to different rules before and after a certain date, the company must be able to explain this difference.

Why metadata should be part of the development process

One of the common mistakes is creating a metadata catalog only after completing a large integration project. In such a case, the documentation quickly becomes outdated.

To maintain relevance, metadata must be included in the standard development lifecycle. For example, a new ETL process should not go into production without a description of sources, target objects, and the responsible owner.

Similarly, a change in table structure should be accompanied by a dependency analysis. Version control systems, Jenkins, and other continuous integration tools can be used to automate some checks.

However, they do not replace business rule validation. For instance, Jenkins can confirm that an SQL script executed without errors, but it cannot independently determine if a new formula complies with the corporate profit calculation methodology.

Therefore, separate acceptance tests are needed.

Organizational Responsibility

Metadata support should not be the responsibility of a single administrator. The Data Owner is responsible for the content and rules of information usage.

The Data Steward coordinates the quality and support of business descriptions. The Data Architect defines information dependencies.

ETL developers document technical transformations. Administrators monitor system operability and execution logs.

For large enterprises, it is advisable to establish a single minimum set of metadata that is mandatory for all critical integrations. At the same time, an overly complex documentation procedure can slow down development.

Therefore, it is advisable to automate technical descriptions and leave manual work for business semantics and complex rules.

Intecracy Group Competencies in Metadata Management

Corporate metadata links information resources, business processes, and infrastructure.

IQusion IT operates in the fields of system integration, electronic document management, and BPM. These competencies are important for connecting information processes with business procedures and change management. SL Global Service has competencies in cloud integration, virtualization, and hybrid infrastructure. These areas are important for documenting information flows between different computing environments.

In this case, business rules must be defined by responsible departments of the enterprise and maintained throughout the entire lifecycle of information systems.

Practical Implementation Guidelines

Metadata Management and ETL auditing allow enterprises to move from controlling technical execution to managing the quality of corporate information flows.

To achieve this, it is necessary to document sources, target sets, transformation rules, and actual loading results.

Quantitative checks, checksums, handling of rejected records, and versioning of information processes are of particular importance.

Apache Atlas, Apache NiFi, Informatica, and IBM InfoSphere offer various mechanisms for metadata collection and tracking of data lineage.

However, no single platform eliminates the need for organizational governance.

For effective operation, it is necessary to link technical documentation with the Business Glossary, Data Governance, and Change Management.

Mature corporate data auditing should not only answer whether the ETL process completed, but also what data it processed, what rules were applied, what result was obtained, and who approved the corresponding data logic.