1. Overview (Project Background)

This project was initiated to improve data quality degradation and management complexity caused by legacy table structures used in the dashboard-based analysis environment.
Previously, different Fact tables and user-related data were fragmented by dashboard, limiting data utilization scope and consistency.

Accordingly, legacy tables were reorganized into a Base → Middle → Aggregated structure from a DWH perspective,
and the goal was to secure both data quality and reusability through integrated user data management, alternative table configuration for user_ft/cohort, and monitoring pipeline establishment.

2. Solution (Resolution Approach)

Improvement work was performed focusing on data structure standardization and expansion of utilization scope.

• Fact Table Structure Improvement
  Organized Fact tables that were separated by dashboard and redesigned them into a common Middle and Aggregated table structure

• Expansion of Data Utilization Scope
  Expanded the range of analyzable data through adjustment of population criteria and addition of columns

• Azar Web Data Integration
  Integrated existing Legacy tables into Base and Middle table structures

• User Data Correction Work
  Corrected missing and inconsistent data in users and azar_user_dm tables and corrected column meanings

3. Result (Achievements)

Through data structure improvement, consistency and usability of the analysis environment have been greatly enhanced.

• Fact Structure Integration
  Integrated Fact tables that were separated by each dashboard into common Middle and Aggregated tables

• azar_du_match_ft Improvement
  Expanded data scope to enable data used only for specific dashboards to be utilized across all dashboards

• azar_dt_user_ft Expansion
  Added Session, order, match, inventory, and login info data, and enhanced analysis utilization through new columns

• Web Event Log Integration
  Designed event logs individually used in multiple web dashboards to be queryable from a single Middle table

• Legacy Logic Reimplementation and Integration
  Reimplemented existing azar_cohort_user_fact_daily logic based on Base tables,
  and integrated calculated metrics into existing Middle tables (azar_dt_user_ft, azar_dt_user_history_ft, azar_user_dm)

• User Data Consistency Improvement

  • Corrected missing deletion_timestamp data in users table

  • Improved app_type logic that could not distinguish cheero data

  • Separated reg_country_cd column to match its actual meaning and added new registration country code column

Expected Effects

The following effects can be expected from this improvement.

• Standardization of data structure and naming conventions

• Strengthened data lineage management system

• Systematic management and reusability of analysis deliverables

• Improved change management process and collaboration efficiency

• Foundation for introducing anomalous data pre-processing process

Through this, we have secured a foundation to more stably expand the efficiency and results of future data quality improvement work.