How do you design and implement modern data warehouse architectures?

Modern data warehouse architecture handles structured and semi-structured data at scale for analytics. Architecture evolution: traditional EDW (monolithic, schema-on-write, ETL-heavy) to modern approaches: cloud data warehouse (Snowflake, BigQuery, Redshift), data lakehouse (Databricks, combining data lake flexibility with warehouse reliability), and data mesh (domain-oriented, decentralized data ownership). Cloud data warehouse implementation: define source systems and data flows, implement ELT (Extract-Load-Transform) instead of traditional ETL (load raw data first, transform in the warehouse using SQL), use a medallion architecture (bronze/raw layer stores data as-is from sources, silver/cleansed layer applies data quality rules and standardization, gold/business layer provides curated datasets for specific use cases). Schema design: star schema for analytics (fact tables containing measures, dimension tables containing attributes), implement slowly changing dimensions (SCD Type 2 for historical tracking), and use wide denormalized tables for simple query patterns. Data modeling: dimensional modeling (Kimball methodology) for bottom-up design organized around business processes, or data vault (hub-satellite-link) for enterprise-wide flexible modeling that accommodates change. Implementation tools: dbt for SQL-based transformation (version-controlled, tested, documented), Apache Airflow or Prefect for pipeline orchestration, and Great Expectations for data quality testing. Performance: implement partitioning (by date for time-series data), clustering/sort keys for common query patterns, materialized views for complex aggregations, and implement query governance (cost limits, timeout policies). Governance: implement data catalog (DataHub, Atlan, or built-in catalog) for data discovery, data lineage tracking for impact analysis, access control with column-level security for PII, and data quality monitoring dashboards.