Building a Data Warehouse for the Data Processing Layer

Why a Data Warehouse Is Needed

Early data services suffered from intermediate data loss, scattered data sources (MySQL, MongoDB, Elasticsearch), and the need for every team member to understand business logic, which wasted time on basic data processing.

Proposed Two‑Phase Solution

The new system works in two parts: (1) abstract a data model based on analysis requirements, and (2) extract data from each source, clean and process it, then store it for downstream use.

Storage Selection

Factors considered were data volume (under 500 GB for the first year), need for SQL‑like operations, and the team’s Python‑centric stack. Options included MySQL, Oracle, and Hive; MySQL with the MyISAM engine was chosen because Hadoop’s overhead was unnecessary and MyISAM offers better read‑performance for the read‑heavy warehouse.

MyISAM vs. InnoDB: MyISAM lacks foreign‑key constraints, transactions, and row‑level locking, but these features are not required for a read‑many/write‑few warehouse, making MyISAM faster.

Data Modeling

Dimensional modeling (Kimball) was preferred over third‑normal‑form modeling to simplify analysis. The model uses fact tables and dimension tables, with a star schema chosen for better query performance despite higher redundancy.

Key concepts: dimension tables describe attributes; fact tables store measurable events.

Star schema vs. snowflake schema: star schema directly links all dimensions to the fact table, while snowflake normalizes dimensions.

Example Dimensional Model

For a chain‑restaurant recruiting product, six dimensions were defined (time, region, brand, store, position, channel) and metrics such as applicant count, hire count, and rejection count were captured at hourly granularity.

ETL Process

Incremental updates are handled by a temp table tracking last_update_time and etl_name. Each run extracts data changed since the last update, processes it, and inserts new rows; successful runs update the temp table.

Task Flow Management with Airflow

Crontab proved insufficient for growing tasks due to poor visibility, lack of retries, and unmanaged dependencies. Airflow was adopted because it is Python‑based, supports cron‑like schedules, visualizes DAGs, and offers retry and notification mechanisms. Airflow concepts include DAG (directed acyclic graph), Task, and Operator (e.g., BashOperator, PythonOperator). Writing an Airflow DAG involves defining DAG properties, task properties, and task dependencies.

Conclusion

The current data warehouse, built on MySQL/MyISAM, dimensional modeling, Python‑driven ETL, and Airflow, meets present needs but will require further evolution as data volume and business complexity grow.