How manishreddykovvuri/data-warehousing-assignment works
Project Type and Purpose
• This is a data‐warehousing project that implements an end‑to‑end ETL pipeline to consolidate HR, Finance, and Operations data into a star schema and expose KPI views for analytics.
• Key goals: raw data ingestion from Excel, cleaning/transformation in Python, staging in PostgreSQL, dimensional modeling (star schema), SCD Type 2 for employee history, audit & DQ logging, and KPI view layer.
File Structure
• 02_Extract_and_transform_raw_data/
• A2_hr_etl.py, B2_finance_etl.py, C2_ops_etl.py – domain‑specific extract/transform scripts
• ET_combined.py – orchestrates all three ETL flows in one run
• 03_load_into_fact_and_dim_tables/
• A3_load_dim_emp.sql – SCD2 load for dim_employee
• B3_load_fin_facts.sql, C3_load_dim_ops.sql – loads for other dims/facts
• TL_combine.sql – combined fact table loading
• 04_KPI/
• KPIs.sql – view definitions for headcount, expenses, downtime, etc.
• requirements.txt, Excel files (dirty source data)
Extract Insights from README
• Star schema with shared dims: employee, department, time, expense_type, process, location
• Fact tables: fact_employee (daily snapshot), fact_expenses, fact_downtime
• Staging layer in dw.staging_* tables
• Audit and data quality logs in dw.audit_log and dw.data_quality_log
• Hybrid ETL/ELT: Python cleans and stages, SQL performs dimensional loads and SCD2 logic
• Role‑based access controls applied at view layer (HR, Finance, Super User roles)