A systematic review of automation in modern data warehouse design: from ER and dimensional models to data vault and medallion architectures

The rapid growth in data volume, complexity, and heterogeneity has intensified the need for automated and scalable data design patterns and architectures supporting integration, standardization, and advanced analytics. This paper investigates how far current techniques go toward automating modern data design patterns, from entity relationship models through Data Vault modeling to medallion style lakehouse architectures. We conduct a systematic review of methods that support automation across key stages of data warehouse development, including the identification of primary and foreign keys as part of broader modeling automation, transformation of entity relationship models into Data Vault structures, metadata-driven ETL/ELT generation, and orchestration of layered pipelines aligned with Bronze, Silver, and Gold medallion tiers. The review covers studies published between 2012 and 2025 in IEEE Xplore, Springer, Elsevier, MDPI, and other non-peer-reviewed sources. From an initial set of 83,022 articles, a multi stage screening and selection process yields 51 primary studies for detailed analysis. The results show that rule based techniques and, more recently, large language models can reliably automate parts of the design and implementation process when source systems follow sound modeling practices and expose consistent metadata. However, current approaches struggle in environments with weakly structured sources, frequent schema evolution, and fragmented or low quality metadata, leaving substantial portions of the end to end life cycle manual. We summarize prevailing trends, quantify coverage of different automation targets, and outline research opportunities toward integrated automation frameworks for Data Vault and Data Warehouse life cycle management that also embrace medallion based data platforms.