Design a payroll payouts data pipeline (star schema + transformation)

Context Multiplier pays salaries to members under its payroll; payouts are recorded in a raw ingestion file with inconsistent date formats, nested JSON-like amount strings (Salary, Tax, Bonus components) and mixed currencies. Build a pipeline to transform this raw data into a clean, query-able format for analytics.

Part 1 - Architecture & Modeling Design a star schema (or appropriate model) for the data, and describe an ingestion strategy for a daily-arriving file (handling duplicates, partitioning, late/again-delivered files, etc.).

Part 2 - Implementation Using Python/DataFrame (pandas/Spark/Polars) or SQL/ELT (assuming a raw staging table of TEXT columns), transform the raw data and answer: total amount disbursed per currency in a given month; average salary per currency by department; and flatten the amounts column so salary, tax and bonus become distinct columns/rows.

Deep dives / trade-offs

• Handling schema drift and dirty data (bad dates, mixed currencies, nested strings).
• Idempotent daily ingestion, partitioning, and incremental vs full loads.
• Choice of transformation engine (pandas vs Spark) by data volume.