Keeping ERP and the data warehouse running during lockdown

Business Continuity / Data Management / Remote Infrastructure / Data Reliability

Pandemic Forces Businesses to Rethink Information System Organization Principles

Spring 2020 became a serious test for corporate information infrastructure.

The spread of COVID-19 and the introduction of quarantine restrictions changed the usual business operations. Some employees switched to remote work, certain production processes were limited, and interaction with clients and suppliers began to change rapidly.

This created an unusual situation for corporate IT systems.

For many years, the infrastructure of large enterprises was designed primarily for operation in offices and local networks. ERP, CRM, electronic document management systems, and corporate Data Warehouses were accessible to employees according to defined rules. Some important operations were performed with the direct involvement of administrators.

Information processes could depend on individual employees, physical access to servers, or manual file transfers. In normal operation, such limitations did not always create noticeable problems.

However, during the lockdown, they became obvious. For example, the CFO needs to monitor the company’s liquidity daily, but the reports are only accessible from the corporate network.

A Data Engineer needs to check for an error in the nightly ETL, however, some administrative tools only work from the office computer. A sales department employee needs to approve a document, but the relevant procedure involves physically handing over a paper copy.

In another situation, the company has to change warehouse operating hours, but corporate analytics continues to use the previous time rules. In each case, the problem extends beyond a specific technology.

It concerns the overall ability of the company to continue critical business processes. This is why Business Continuity Planning becomes especially important – it must cover not only servers but also information flows, users, responsible specialists, and recovery procedures.

For Data Management, the main task is to ensure the availability of corporate information without loss of its quality, historical accuracy, and proper protection.

Business Continuity starts with identifying critical data

One of the first tasks is to identify the information resources on which the company’s operations depend. For example, an industrial holding company may use dozens of information systems and thousands of analytical reports.

However, not all of them have the same criticality. The finance department must control cash flows.

The logistics department is responsible for shipments and inventory availability. The commercial department manages current orders and accounts receivable.

At the same time, some research reports may temporarily be executed with lower frequency. Therefore, it is advisable to create a registry of critical information services.

For each service, it is necessary to define the owner, required dependencies, and availability requirements. For example, if financial reporting depends on four ERPs, it is not enough to check only the availability of the DWH.

It is also necessary to control data acquisition from each source.

Business Impact Analysis

It is necessary to determine the consequences of the unavailability of a specific service.

Dependencies

Technical and organizational dependencies need to be described.

Service Ownership

For each critical process, there must be a responsible party.

Recovery Requirements

Permissible recovery times should be defined.

Alternative Procedures

If necessary, prepare a temporary procedure for performing the relevant business function.

The main result of such an audit should not be just a list of servers, but a map of the enterprise’s information services.

Remote Access: How to Ensure Operation Without Opening Corporate Databases to the Internet

One of the most noticeable consequences of the quarantine has been a sharp increase in the need for remote work.

For businesses, this means the need to organize controlled access to corporate applications. However, not all information systems were designed for such a mode.

For example, a BI platform might only operate within the internal network. The administrative interface of the ETL can be accessed from defined servers.

And individual corporate applications might use local file resources. Simply opening the relevant ports to the external network is not a proper solution.

A multi-layered protection model is needed.

Remote employee

Managed device · account · MFA as needed

Secure access

VPN / application gateway / session control

Identity & Access Management

Roles · Permissions · Auditing

• ERP / CRM
• BI / DWH
• EDMS / BPM

Conceptual architecture for remote access. The user is not required to provide a direct connection to the production DBMS if their task is performed through the appropriate corporate application.

Secure Channels

VPN and specialized gateways can be used for relevant scenarios.

Multi-factor Authentication

Can enhance the protection of critical accounts.

Principle of Least Privilege

The user should only receive necessary access.

Administrative Operation Separation

Administration of the production infrastructure must have separate rules.

Endpoint Security

Device security for employee workstations must be considered.

Bandwidth

It is necessary to verify if relevant gateways and network channels are capable of handling an increased number of connections. It is important that remote work does not automatically increase user privileges.

For example, a financial analyst may be granted access to a relevant BI dashboard without the right to export all original personal records.

Corporate Analytics: Report Availability Does Not Imply Data Authenticity of Data

During times of crisis, information relevance becomes particularly important.

For example, management wants to monitor liquidity, sales, and order fulfillment daily. A BI dashboard might be technically accessible, but its data is already outdated.

Suppose the DWH receives updates from four ERP systems. Three sources have completed their overnight export.

The fourth ERP did not transmit the required file due to a change in the work schedule of the relevant department. ETL can generate a report from the available data.

However, such a result should not be automatically considered a complete consolidated indicator. Therefore, the enterprise needs to define several independent characteristics.

For critical reports, it is advisable to display the readiness status.

• For example:
• Preliminary — preliminary data.
• Validated — passed defined technical checks.
• Certified — necessary business criteria confirmed.

This is a corporate model of statuses that needs to be implemented according to the specifics of the enterprise. The main principle is that the user should know how up-to-date and complete the result is.

DataOps: How to Eliminate Dependence on Manual ETL Execution

The pandemic revealed a problem that might have gone unnoticed before: critical information processes often depend on individual specialists.

For example, daily DWH updates are manually initiated by an administrator.

Or only one Data Engineer knows the procedure for restoring ETL after an error. In a typical office setting, such a model might work.

However, under remote work conditions, it creates a risk of information process downtime. Therefore, it is necessary to develop DataOps.

Git

SQL, Python, DAGs, and configurations must have version control.

CI/CD

Updates to information processes must undergo appropriate automated checks.

Orchestration

Scheduled tasks must be executed according to defined dependency logic.

Monitoring

Incidents must be detected without manual review of each system.

Recovery Runbooks

Clear recovery instructions are required for critical processes.

Cross-Training

It is desirable that the operational procedure does not depend on a single person. For example, if the primary administrator is temporarily unavailable, another authorized specialist should be able to determine the cause of the error and safely restore processing.

It is important that the documentation contains not only the startup command but also the conditions for correct re-execution.

Apache Airflow 1.10.10: New Features for Remote Support

On April 9, 2020, Apache Airflow 1.10.10 was released. The release contains 199 changes since the previous version, including new features, bug fixes, and operational improvements.

Several areas are of interest for enterprise DataOps.

Production Docker Image

Official support for the corresponding production Docker image is now available.

This can simplify the standardization of deployment environments.

Secrets Backends

Capabilities for retrieving Connections and Variables from supported external secret stores are being developed.

Stateless Webserver

DAG serialization support enables the use of the corresponding webserver model.

RBAC UI

Time zone handling in the UI is being improved.

Task Launch Management

Capabilities for passing parameters during manual DAG runs are being developed.

These changes are beneficial for companies aiming to standardize ETL operations. However, one should not assume that an Airflow update automatically ensures business continuity.

For example, if the ETL performs an uncontrolled INSERT, a rerun might create duplicates.

Therefore, idempotency is required.

Practical Example

Let’s consider a DAG:

from datetime import datetimefrom airflow import DAGfrom airflow.operators.bash_operator import BashOperatordag = DAG(    "critical_finance_dwh",    start_date=datetime(2020, 4, 1),    schedule_interval="0 5 * * *",    catchup=False)extract = BashOperator(    task_id="extract_sources",    bash_command="/opt/etl/extract_finance.sh",    dag=dag)validate = BashOperator(    task_id="validate_sources",    bash_command="/opt/etl/validate_finance.sh",    dag=dag)publish = BashOperator(    task_id="publish_certified",    bash_command="/opt/etl/publish_finance.sh",    dag=dag)extract >> validate >> publish

This is a simplified example for Airflow 1.10.

It is important that validate_finance.sh returns an error in case critical rules are violated.

Otherwise, Airflow will consider the check successful and continue execution.

Apache Kafka 2.5: resilience of event processing

On April 15, 2020, Apache Kafka 2.5.0 was released.

The new release includes the development of cooperative rebalancing for consumers, new metrics, TLS 1.3 support, Kafka Streams improvements, and ZooKeeper update to 3.5.7.

This is important for enterprises, as Kafka is increasingly used for information exchange between independent systems.

For example, an ERP publishes relevant order events.

A logistics system receives them asynchronously.

DWH generates operational metrics.

Asynchronous Model

Temporary unavailability of one consumer does not necessarily have to stop a business transaction in ERP.

Retention

Messages can remain available for a defined period.

Consumer Recovery

After recovery, the consumer can resume processing from the appropriate position.

Replication

Supports broker fault tolerance with proper configuration. However, the limitations of the technology must be considered.

Kafka does not automatically guarantee full business consistency for all independent systems. For example, a message might be received again after a failure.

Therefore, idempotency of consumers is required. Similarly, retention should be controlled in accordance with actual recovery times.

Data Quality during operational mode changes

The quarantine affected not only the availability of information systems but also the business data itself.

For example, warehouse operating schedules are changing.

Individual orders are being postponed.

Some deliveries are being executed via alternative routes.

New statuses may appear in the ERP. If ETL continues to use old business rules, analytics may become incorrect.

Therefore, Data Quality must consider not only technical formats.

Schema Validation

Structure control.

Semantic Validation

Business value check.

Timeliness

Information relevance.

Distribution Monitoring

Detection of significant indicator changes.

Reconciliation

Comparison with authoritative sources. For example, a sharp decrease in sales does not necessarily mean an ETL failure.

It could be a real business outcome. Instead, the absence of a file from one ERP may indicate an incomplete load.

Therefore, it is necessary to distinguish between information incidents and real business changes.

Data Catalog: how to speed up recovery in the absence of key specialists

For Business Continuity, documenting information dependencies is of particular importance. For example, after an ERP change, one financial indicator stopped being generated.

The usual ETL developer is temporarily unavailable. Another team needs to quickly identify which tables and procedures are involved in generating the result.

Data Catalog and Data Lineage can be used for this purpose.

Technical Metadata

Describes information resources.

Business Glossary

Defines business meaning.

Data Lineage

Documents the origin.

Data Ownership

Shows the responsible party.

Change History

Helps to establish what changes have occurred. For example, Apache Atlas can be used for relevant Hadoop resources.

However, additional integrations may be required for external ERP and specialized SQL processes. Therefore, the main task is not only to establish a catalog but also to maintain its relevance.

Disaster Recovery: Why Backups Aren’t Enough

During remote work, an enterprise must be able to restore critical information services without the mandatory physical presence of the entire team.

Verified procedures are necessary for this.

Backup

Stores relevant data and configurations.

Restore

Returns the system to a defined state.

RPO

Defines the acceptable data loss.

RTO

Defines the target recovery time.

Reconciliation

Confirms the correctness of results.

Business Acceptance

The responsible owner confirms the service readiness for use. For example, a DWH can technically be recovered in two hours.

However, if the financial reconciliation is not completed after that, the official reporting may still not be ready. Therefore, the technical RTO is not always identical to the recovery time of a full analytical service.

For critical information sets, both characteristics must be defined.

Practical Scenario: Ensuring Holding Company Operations During Lockdown

Let’s consider a hypothetical holding company with multiple ERPs, a centralized CRM, an electronic document management system, and a DWH.

The company is transitioning a significant portion of its office staff to remote work.

Stage 1. Business Impact Analysis

Critical processes are identified.

Stage 2. Data Ownership

Responsible parties for information services are appointed.

Stage 3. Remote Access

Secure channels are configured.

Stage 4. Access Review

Permissions are verified.

Stage 5. Infrastructure Capacity

Network and server load are assessed.

Stage 6. ETL Inventory

Critical pipelines are documented.

Stage 7. Airflow

Routine processing is automated.

Stage 8. Data Quality Gates

Necessary checks are configured.

Stage 9. Data Catalog

Information dependencies are documented.

Stage 10. Data Stewardship

Substitutes for responsible parties are identified.

Stage 11. Monitoring

Remote monitoring is ensured.

Stage 12. Recovery Runbooks

Recovery procedures are being prepared.

Stage 13. Testing

An appropriate check is being conducted.

Stage 14. Business Communication

Users receive information about the relevance of the sets.

As a result, the enterprise reduces the dependence of critical information services on the physical location of employees’ work.

KPI Business Continuity for Corporate Data

These metrics need to be evaluated comprehensively. For example, a successful VPN connection does not guarantee the readiness of a financial report.

And stable ETL operation does not imply the correctness of all business metrics. Therefore, success is determined by the readiness of the final information service.

Intecracy Group Competencies in Business Continuity

Maintaining business continuity requires several technological specializations. Data Management IG (dmig.com.ua) specializes in ETL, MDM, Data Quality, DWH, and corporate analytics. These competencies are important for supporting critical information pipelines, automated reconciliations, analytical datasets, and the accuracy of corporate metrics.

IQusion IT (iqusion.com) operates in the fields of system integration, electronic document management, and BPM. These areas can be used for automating remote approvals, integrating business processes, and reducing reliance on paper documents. Softline IT (softline.kiev.ua) has expertise in server, network infrastructure, and storage systems. These are important for organizing secure access, backup, performance of corporate DBMS, and recovery of information infrastructure.

SL Global Service (sgs4business.com) specializes in cloud integration, virtualization, and hybrid infrastructure. Such competencies can be used to organize backup computing resources and controlled access to relevant information services.

At the same time, business priorities, readiness criteria, and acceptable recovery times must be determined by the enterprise itself.

Practical Guidelines for Implementation

The quarantine restrictions of spring 2020 demonstrated how important the resilience of corporate information architecture has become.

Enterprises need to ensure remote access to ERP, CRM, DWH, and relevant business applications.

However, technical accessibility is not enough.

Timely, complete, and verified data are required for making management decisions.

• Apache Airflow 1.10.10 can be used to automate information processes.
• Apache Kafka 2.5 develops event integration capabilities.
• Data Catalog, MDM, and Data Quality help maintain corporate information governance.

Documented procedures, responsibility allocation, and verified recovery from failures play a special role.

The main principle of Business Continuity for corporate data is that the enterprise must maintain the availability of critical information and the ability to perform necessary business operations regardless of the employees’ physical location, while preserving the proper quality, security, and governance of information processes.