Optimizing Workflow in Data Warehouse Construction: A Task‑Instance Layered Approach

01 Data Warehouse Construction

Data warehouses, first defined by William Inmon in 1991, are subject‑oriented, integrated, stable, and time‑variant collections of data that support analytical processing rather than merely storing raw data.

OLAP (On‑line Analytical Processing) is the core technology for data‑warehouse analysis, providing multidimensional operations such as drill‑down, roll‑up, slice, dice, and pivot.

Dimensional modeling, introduced by Kimball, maps multidimensional models to relational tables, separating dimension tables (descriptive attributes) from fact tables (measurable metrics).

2. Main Work of Data Warehouse Construction

The architecture consists of three core components: the raw data layer, the data warehouse layer, and front‑end applications (BI, search, recommendation, etc.).

Data is organized into six logical layers: STG (raw data landing), ODS (operational data store), DWD (detail layer), DWS (service layer), ADS (application data layer), and DIM (dimension layer). Each layer adds cleaning, integration, aggregation, or enrichment to serve downstream analytics.

Workflow in Data Warehouse Construction

Workflow originated from production and office automation, defining a series of tasks or roles that are executed and monitored by a workflow management system (WfMS). The WfMC defines workflow as an automatically executable business process that moves documents, information, or tasks between participants.

In data‑warehouse projects, workflow nodes represent data‑processing tasks and directed edges indicate data flow between tasks, matching the definition of workflow.

1. Workflow Overview

Workflow management systems such as Azkaban, Oozie, and Airflow are commonly used, but they have limitations in multi‑developer collaboration, complex dependency configuration, and incremental data repair.

2. Proposed Optimization

The paper proposes abstracting workflow nodes into two layers: tasks (static definitions) and instances (concrete executions). Users only configure a task’s period and dependency attributes; the system automatically generates instance DAGs.

Task Layer

Each task contains processing logic (Shell, Hive SQL, Spark, etc.), a scheduling period (day, hour, week, month), and dependency attributes (inter‑task and self‑dependency). The period determines how many instances are created per time unit, while dependencies define execution order.

Instance Layer

Based on task attributes, the system creates instances at the appropriate times and builds a DAG where an instance can run only when all its parent instances have completed and its scheduled time has arrived.

Generation Rules

1) Instances are first generated according to the task’s period (daily, hourly, etc.). 2) Dependencies are then applied to connect instances: identical‑count hour‑hour dependencies match instances by order; mismatched counts use the nearest earlier parent instance; hour‑day and day‑hour dependencies follow similar nearest‑or‑all‑instance strategies; self‑dependency links an instance to its previous execution.

4. Optimization Benefits

Task‑level configuration eliminates the need to modify the whole workflow when adding or changing a node.

Period and dependency attributes automatically generate complex instance relationships, reducing manual dependency configuration.

The model supports building sub‑workflows rooted at any task, facilitating historical data repair and incremental processing.

Conclusion

By separating workflow nodes into tasks and instances and using period/dependency attributes, data‑warehouse workflow management becomes more flexible, collaborative, and capable of handling diverse scheduling scenarios, while still supporting data quality monitoring, lineage tracking, and performance optimization.