Orchestrating Data Loads with Apache Airflow

Apache Airflow / ETL / Data Engineering / Workflow Orchestration

Why a standard task scheduler is insufficient for an enterprise DWH

Enterprise analytical systems rely on complex processes for information acquisition, transformation, and loading.

For example, a Data Warehouse daily receives financial operations from ERP, customer records from CRM, MDM reference data, and other information sets.

To prepare management reports, a defined sequence of operations must be performed.

First, the system receives the initial data. After that, it checks their structure, normalizes formats, matches corporate identifiers, and loads the results into analytical tables.

Only after the successful completion of these procedures can the corresponding reports be updated.

Traditionally, operating system schedulers, SQL Server Agent, specialized ETL platforms, and custom scripts are used for automation.

For small tasks, this may be sufficient.

However, as the number of integration processes grows, complex dependencies arise.

For example, sales loading must be completed after updating the product catalog.

A financial report cannot be generated before currency conversions are completed.

The demand forecasting model must use only verified historical data.

If one procedure fails, other tasks should not continue to run uncontrollably.

In addition, the enterprise must be able to determine exactly what was executed, what dataset was used, and what results were obtained.

Workflow orchestration systems are used to solve such problems.

One of the promising open-source tools is Apache Airflow — a platform that allows you to programmatically describe, schedule, and monitor the execution of interdependent tasks.

The main advantage of Airflow lies not in executing a specific SQL query or ETL transformation, but in organizing the entire process of corporate information processing.

Apache Airflow 1.9.0: Technology Status as of June 2018

Apache Airflow originated as an internal Airbnb project and was subsequently open-sourced.

In 2016, the project joined the Apache Incubator.

On January 3, 2018, Apache Airflow 1.9.0 was released.

Therefore, as of June 28, 2018, this version is the current stable release of the 1.9 branch.

The next version, 1.10.0, will be released on August 27, 2018.

For a historical project, it is important to consider these limitations.

For example, the modern TaskFlow API or the @task decorator syntax, which appeared much later, cannot be used in the description of Airflow 1.9.

Similarly, in June 2018, Airflow was still in the Apache Incubator.

It would only receive Top-Level Project status in 2019.

Key Features

Airflow allows workflows to be described using Python.

For this purpose, a Directed Acyclic Graph (DAG) is used.

Each DAG contains a set of tasks and dependencies between them.

For example, one task retrieves files from ERP, a second checks their structure, and a third loads the data into DWH.

Airflow controls the execution order and provides relevant information through its web interface.

However, the platform should not be considered a universal replacement for all ETL systems.

It manages execution, while the transformations themselves can be performed using SQL, Spark, Python, or specialized integration platforms.

Directed Acyclic Graph: Formalization of the Information Process

DAG is a directed acyclic graph.

It describes tasks and their execution sequence.

• For example, a corporate sales loading process might include the following operations:
• ERP Data Ingestion
• Data Profiling
• Data Quality Rules
• Loading Verified Data
• Exception Logging
• Building Analytical Tables
• Summary Control and DWH Readiness

Conceptual process. Parallel exception logging and load continuation require separately defined dependencies and execution rules.

Importantly, the DAG does not describe an arbitrary process with unlimited loops.

Separate task runs, retries, or corresponding external mechanisms are used for re-execution.

This distinguishes Airflow from BPM, where long-running processes involving people, waiting, and alternative routes can be modeled.

For an enterprise DWH, this model is particularly useful, as most scheduled loads have a clear sequence.

Main Airflow Components

For industrial operation, it is necessary to understand the platform architecture.

Scheduler

The Scheduler determines which tasks should be run according to the schedule and dependencies.

For example, a daily load process can be triggered after a defined interval has elapsed.

It is important to correctly consider the time semantics of Airflow 1.9.

In the traditional model, a periodic DAG usually runs after its corresponding period has completed, and the execution_date represents the logical processing period, not necessarily the actual start time.

This is important for correct historical data loading.

Webserver

The web interface allows viewing DAGs, statuses, and corresponding logs.

For example, an administrator can set which task has failed.

Metadata Database

Airflow uses its own metadata database to store task states, DAG runs, and other service characteristics.

For industrial use, it is necessary to organize reliable operation of this database.

At the same time, it is not designed for storing large corporate datasets that are processed by ETL.

Executor

Executor defines the task execution method. For example, in Airflow 1.9, SequentialExecutor, LocalExecutor, and CeleryExecutor can be used.

For certain distributed environments, CeleryExecutor allows tasks to be executed on multiple worker nodes.

However, this requires additional infrastructure and correct configuration of the message broker and execution results.

Python as a Process Description Language

One of the features of Airflow is the use of Python to describe DAGs.

This allows workflows to be stored in a version control system and apply standard development practices. For example, an enterprise can create a DAG for daily sales loading.

Here is a simplified example using the API available in Airflow 1.9.

from datetime import datetime, timedeltafrom airflow import DAGfrom airflow.operators.bash_operator import BashOperatordefault_args = {    'owner': 'data_management',    'depends_on_past': False,    'start_date': datetime(2018, 6, 1),    'retries': 2,    'retry_delay': timedelta(minutes=5)}dag = DAG(    'daily_sales_etl',    default_args=default_args,    schedule_interval='0 2 * * *',    catchup=False)extract = BashOperator(    task_id='extract_erp',    bash_command='/opt/etl/extract_sales.sh',    dag=dag)validate = BashOperator(    task_id='validate_sales',    bash_command='/opt/etl/validate_sales.sh',    dag=dag)load = BashOperator(    task_id='load_dwh',    bash_command='/opt/etl/load_sales.sh',    dag=dag

In this example, Airflow defines the execution order. It launches separate external scripts.

However, Airflow does not automatically know if the corporate revenue was calculated correctly.

This should be checked by appropriate Data Quality procedures.

It is also important to understand that catchup=False affects the creation of historical scheduled runs, but does not replace incremental processing rules within the ETL.

For production use, data control boundaries, recovery mechanisms, and re-run verification are required.

Airflow and Traditional ETL Platforms

For large enterprises, an important question is whether Airflow can replace Informatica PowerCenter, IBM InfoSphere DataStage, or Microsoft SSIS?

There is no universal answer.

Airflow primarily manages execution and dependencies.

Specialized ETL platforms may include their own transformation, visual design, source access, and quality control mechanisms. For example, an enterprise might continue to use SSIS to extract data from SQL Server.

Airflow launches the relevant packages and controls the overall sequence. Another process might run Spark for processing large Hadoop datasets.

A third might use SQL procedures in the DWH.

In such an architecture, Airflow acts as the orchestrator.

It is important not to create parallel independent schedulers without proper coordination. For example, if SSIS independently launches a package at 2 AM, and Airflow launches it again, duplicate processing is possible.

Therefore, the enterprise needs to define a unified procedure for launching critical processes.

Data Quality as a Prerequisite for DAG Completion

One of the biggest advantages of orchestration is the ability to formalize quality control points. For example, after receiving data from an ERP system, profiling is performed.

If critical fields are missing, the process must not publish incorrect results.

Structure Control

Checks for the presence of necessary attributes and valid data types.

Quantity Control

The number of received records is established.

If it significantly differs from the expected, a warning or blocking is generated depending on the rules.

MDM Control

Checks the mapping of client and product codes.

Sum Control

For financial datasets, control totals reconciliation is performed.

Duplicate Control

It is necessary to determine whether re-uploading has created duplicate business transactions.

Exception Handling

If some records are incorrect, it is possible to separate them into a special verification zone. However, the threshold for acceptable errors should be determined by the business owner.

For example, for an optional product description, certain deviations may be allowed. For critical financial transactions, even a single rejected record may require halting publication.

Therefore, Airflow must apply approved rules, not arbitrary technical thresholds.

Error Handling and Retries

In a distributed enterprise environment, individual tasks may complete with an error. For example, the ERP is temporarily unavailable.

Or the network connection to the DWH is interrupted. Airflow allows configuring retries for execution.

However, rerunning is not always safe.

Idempotency

If an ETL process adds records to the target table, rerunning it should not create duplicates.

For this, unique business keys, batch control tables, and appropriate transactional mechanisms can be used.

Batch ID

A load ID can be defined for each batch.

For example:

PROCESS: DAILY_SALES
BATCH:   2018-06-27
SOURCE:  ERP_MAIN
STATUS:  VALIDATED

This allows to establish whether the corresponding processing has already been performed.

Boundary Control

For incremental ETL, it is necessary to store the position of the last successful load. For example, a timestamp or a corresponding CDC offset.

However, changing the control position before all necessary operations are successfully completed is dangerous. In case of failure, a part of the records may be missed.

Period Reprocessing

For historical corrections, the company must be able to re-formulate a defined period. However, it should be noted that not all source systems retain a complete change history.

Therefore, reproducibility depends not only on Airflow but also on the data architecture.

Airflow and ELT in Cloud Analytical Platforms

In recent years, ELT has become increasingly popular – an approach where a significant portion of transformations is performed after loading information into the analytical environment. Amazon Redshift, Google BigQuery, and Azure SQL Data Warehouse can be used for this purpose.

Airflow can coordinate these operations.

• For example, a company uploads files to Amazon S3.
• After that, it initiates a bulk load into Redshift.
• Next, it performs SQL transformations and summary checks.

For BigQuery, appropriate integration operators and APIs can be used. However, it is necessary to consider the support for specific operators in Airflow 1.9 and corresponding dependency versions.

Not all capabilities of modern provider packages were available in June 2018.

Advantages

Airflow allows combining operations of different systems in a common workflow.

Risks

Cloud SQL computations can incur additional costs. Therefore, it is important to control processing volumes and the number of retries.

For example, a misconfigured DAG can repeatedly run a large BigQuery query. This can increase costs even without a technical error.

Therefore, economic control must be part of orchestration.

Airflow and Apache Spark

For large datasets, distributed computing with Apache Spark can be used. Airflow can coordinate the execution of relevant tasks through supported integration mechanisms.

• For example, an enterprise has a Data Lake on HDFS.
• Web service logs are ingested into it daily.
• Spark performs normalization and aggregation.
• After that, the prepared results are loaded into the DWH.
• Airflow controls the order:

1. Retrieve information.
2. Check file availability.
3. Run Spark job.
4. Verify result.
5. Load prepared data.
6. Update analytical metrics.

It is important to differentiate functions. Spark performs distributed computations.

Airflow manages their execution and dependencies. For streaming systems with continuous processing, Airflow is not necessarily the appropriate mechanism for managing every individual event.

For example, Apache Kafka and Spark Structured Streaming can continuously process a stream. Airflow is more appropriate for related batch jobs, checks, and corresponding scheduled operations.

Data Lineage and Operational Metadata

For corporate DWHs, it is important to know which sources formed the result.

Airflow allows describing dependencies between tasks, but this does not mean automatically obtaining complete Data Lineage at the field level.

For example, a DAG shows that transform_sales is executed after extract_erp.

However, it does not necessarily explain how the net_amount field is transformed into corporate revenue.

This requires documentation of SQL, ETL processes, and relevant metadata.

Apache Atlas 1.0

Apache Atlas 1.0 was released on June 2, 2018.

It develops a graph model of relationships and metadata management tools.

For enterprises using Hadoop and Spark, this can be a useful component for describing datasets.

However, the standard Airflow 1.9 should not be described as a platform with universal built-in automatic Lineage for all DBMS.

Separate integration and documentation are required to transfer relevant metadata to Atlas or another catalog.

Operational History

For each critical run, it is advisable to store:

• process ID and the period being processed;
• initial data sources;
• software code version;
• number of records received and loaded;
• quality check results;
• completion status;
• relevant checksums.

This allows to reproduce the circumstances of the analytical dataset formation.

Airflow Information Security after GDPR

For relevant enterprises, the start of GDPR application creates an additional need to assess the protection of personal data in all components of the information architecture.

Airflow can run ETL processes that work with personal information.

Therefore, service accounts and access to logs must be controlled.

Connection Credentials

Airflow supports connection parameter storage mechanisms.

However, the enterprise must correctly configure the protection of the service metadata database and relevant secrets.

Minimum Privileges

If a DAG only reads specific ERP tables, it should not unnecessarily have administrative access to the entire DBMS.

Journaling

It is necessary to avoid logging passwords, tokens, and full personal data sets into technical logs.

Web Interface

For Airflow 1.9, it is particularly important to correctly configure authentication and network access restrictions for the web interface.

The modern security model of Airflow 2.x or 3.x cannot be ported to this version as if it were available by default.

Access to Worker Nodes

A worker can execute code with access to corporate DBMS.

Therefore, it is necessary to protect the operating system, configurations, and corresponding credentials.

Practical Scenario: Modernizing Nightly ETL in a Holding Company

Let’s consider a hypothetical holding company that uses three ERP systems, a centralized CRM, MDM, and a corporate DWH.

Previously, information loads were launched through several independent schedulers.

As a result, situations arose where the DWH began generating reports before the master data update was completed.

Stage 1. Inventory

The team identifies all existing ETL processes and their dependencies.

Stage 2. Information Model

The Data Architect documents which sources are needed for each analytical dataset.

Stage 3. DAG

A corresponding task graph is formed for the scheduled process.

Stage 4. Master Data Loading

First, the necessary corporate MDM data is updated.

Stage 5. Operation Retrieval

New and changed records are retrieved from ERP.

Stage 6. Data Profiling

Volumes, formats, and critical characteristics are checked.

Stage 7. Data Quality

Code matching and business rule compliance checks are performed.

Stage 8. ELT

Relevant SQL transformations are executed in the analytical environment.

Stage 9. Reconciliation

Quantitative indicators and financial checksums are compared.

Stage 10. Publication

Only after successful completion of checks, the information sets become available for relevant BI reports.

Stage 11. Monitoring

Airflow monitors statuses, durations, and retries.

Stage 12. Error Handling

Critical exceptions are logged in the appropriate corporate process.

For example, reference data errors can be forwarded to the Data Steward via BPM.

As a result, the enterprise receives not just a new scheduler, but a more manageable process for generating corporate analytics.

Capacity Planning for Airflow

For productive operation, it is necessary to assess the load on the platform itself.

For example, if an enterprise has hundreds of DAGs and thousands of daily tasks, the scheduler, executor, and metadata database must be configured correctly.

Parallelism

Not all tasks should run simultaneously.

For example, running ten heavy SQL transformations in parallel can overload the DWH.

Therefore, appropriate mechanisms for limiting concurrent execution must be used.

Pools

Airflow supports Pools, which allow limiting the number of tasks that concurrently use a specific resource.

For example, a small number of concurrent connections can be defined for ERP.

This reduces the risk of ETL impacting regular business operations.

Queues

Appropriate task queues can be used for distributed execution.

SLA

Airflow supports mechanisms for controlling task completion deadlines.

However, the corresponding SLA configuration should not be considered an automatic guarantee of timely execution.

It helps to identify violations of the established deadline.

Operational Database

It is necessary to monitor the growth of operational metadata and related resources.

Otherwise, the orchestration platform itself may become a bottleneck.

Airflow, BPM, and RPA: Correct Role Separation

In an enterprise environment, Airflow, BPM, and RPA can be used simultaneously.

However, these tools have different purposes.

Airflow manages technical data processing pipelines and their dependencies.

BPM ensures the execution of business processes, approvals, and collaboration with responsible employees.

RPA automates defined repetitive actions within enterprise applications.

For example, an enterprise receives an incorrect product record.

Airflow detects violations during ETL.

BPM creates a task for the Data Steward.

If necessary, RPA can perform the corresponding operation in a legacy application that lacks a supported API.

After the fix is approved, Airflow can reprocess the corresponding dataset.

Thus, the technologies can complement each other.

However, duplicate process logic should not be created simultaneously on multiple platforms.

For example, the decision to approve a new counterparty should have a single defined owner and a corresponding procedure.

Corporate Data Orchestration KPIs

Airflow’s effectiveness must be evaluated by measurable results.

However, the success should not be evaluated solely by the technical status of the DAG.

For example, a process may complete without errors but load an incomplete financial dataset.

Therefore, for critical data, the result must include quality control and relevant business reconciliations.

Intecracy Group Competencies in ETL/ELT Orchestration

Implementing Apache Airflow in an enterprise environment requires a combination of several technological areas. IQusion IT operates in the areas of system integration, electronic document management, and BPM. Relevant competencies are important for ETL interaction with application systems, organization of business exceptions, and approval procedures.

Softline IT has competencies in server, network infrastructure, and storage systems. They provide the foundation for the reliable operation of Airflow, worker nodes, metadata databases, and relevant information resources.

The choice of orchestration architecture should be determined by the complexity of information processes and the enterprise’s requirements for data availability, quality, and timeliness.

Practical Implementation Guidelines

Apache Airflow is a promising tool for managing complex enterprise data processing workflows.

It allows describing ETL/ELT tasks using Python, defining dependencies, monitoring execution, and organizing retries after technical failures.

Version Airflow 1.9.0, available since January 2018, already provides the core orchestration capabilities for enterprise information environments.

In conjunction with SQL, Spark, Hadoop, and traditional ETL platforms, Airflow can provide centralized management of complex information flows.

However, its use does not eliminate the need for Data Quality, MDM, Data Lineage, and controlled integration.

Idempotency of processes, correct handling of historical periods, security of service accounts, and independent verification of business results are of particular importance.

The main value of Apache Airflow lies not simply in the automatic execution of SQL scripts, but in creating a manageable and reproducible sequence for processing enterprise information, where each stage has defined dependencies, quality control, and verifiable results.