I am contemplating four types of environments (i.e., tech, development, certification, and production), each contains one to dozens of Azure SQL Data Warehouse databases. Each database will contain from a few to several dozen schemas and from hundreds to thousands of tables, views, procedures. Schemas will be added over time. Tables/Views would be added and modified (e.g., column addition) over time.
I want to introduce all change first to one database called "tech". I would then have an automated process that promotes those changes to all the databases in higher environments (based on stakeholder approval). The ADW databases, at least for production, will be deployed in the available Azure regions.
To date, I have been keeping all my deployment scripts in a source code repository and rerunning the scripts (on Linux using sqlcmd) for each ADW database on each environment.
I haven't found the functionality I would use in other MPP databases that I have supported that allow such automation. Teradata has "SHOW TABLE|VIEW|MACRO;" commands that allow one to dump specific DDL. Greenplum has a pg_dump command that backs up DDL.
In Azure SQL Data Warehouse, how could the "version of" DDL be determined? In Azure SQL Data Warehouse, how can specific DDL to be unloaded from one database and applied to another?