How would you design a custom data layer that harmonizes planned spend with actual spend across digital, offline, and social channels, and supports daily and quarterly reporting?

To harmonize planned spend with actual spend across digital, offline, and social channels, you would start by defining a common dimensional model that captures the spend dimension (date, channel, campaign, product) and the fact tables for planned and actual spend. Each source system (e.g., Google Ads, Facebook, internal budgeting tool) would feed into a staging area where you map its native fields to the common schema. You would then use dbt to transform the raw data into the harmonized fact tables, applying business rules to reconcile discrepancies (e.g., rounding, currency conversion). For daily reporting, you would build incremental models that load only new or changed rows, and materialize them as views or tables that Power BI or Tableau can consume. For quarterly reports, you would pre‑aggregate the data into a separate fact table to reduce query latency. You would also implement data quality checks (e.g., spend variance thresholds) and alerting. Finally, you would expose the harmonized tables through a data catalog and schedule the pipeline with Airflow, ensuring that the daily and quarterly jobs run in the correct order and that the reports are refreshed automatically.