Ok, thanks for the reply.
I've landed a different solution. Even though Terraform could be a neat option, I would prefer to primarily use it for IaC-type operations.
What I've done (as opposed to having DDL in -many different- notebooks) is to have a single DDL notebook. This notebook read queries from a Control Table in our lake (this can also reside in a dedicated DB to easily cover multiple DBX workspaces).
DDLs are registered in this table and marked as 'pending' if to be executed for the next release. As part of the release process, this is then executed and then marked as non-pending again. this ensures statements are only executed once, for example, an alter statement. While at the same time having your DDL history.
Here is the code. Hope it can help someone else:
%sql
### Inserting Create Table statement for Calendar Dimension
INSERT INTO
Control.DDL (
SchemaName,
TableName,
QueryString,
QueryOrder,
PendingIndicator,
LastExecutedDateTime,
DLCreatedDateTime
)
VALUES(
"gold",
"DimCalendar",
"""CREATE TABLE Gold.DimCalendar (
DLKey BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 0 INCREMENT BY 1),
CalendarDateID DATE NOT NULL,
DayShortName STRING NOT NULL,
DayLongName STRING NOT NULL,
WeekDayNumber INTEGER NOT NULL,
MonthShortName STRING NOT NULL,
MonthLongName STRING NOT NULL,
MonthDayNumber INTEGER NOT NULL,
QuarterNumber INTEGER NOT NULL,
QuarterShortName STRING NOT NULL,
QuarterLongName STRING NOT NULL,
YearShort INTEGER NOT NULL,
YearLong INTEGER NOT NULL,
YearDayNumber INTEGER NOT NULL,
YearWeekISONumber INTEGER NOT NULL,
YearMonthNumber INTEGER NOT NULL,
OrdinalDayNumber INTEGER NOT NULL,
OrdinalCalendarYearNumber INTEGER NOT NULL,
DLCreatedDateTime TIMESTAMP NOT NULL,
DLUpdatedDateTime TIMESTAMP NOT NULL
) USING DELTA LOCATION '/mnt/ContainerNameGold/DimCalendar'""" ,
0,
1,
NULL,
Current_Timestamp
)
%python
### this is the logic to execute the pending DDLs
ExecutionDF = spark.sql("SELECT * FROM Control.DDL WHERE PendingIndicator IS TRUE ORDER BY SchemaName, TableName, QueryOrder")
ExecutionList = ExecutionDF.collect()
for i in ExecutionList:
print(i["QueryString"])
spark.sql(i["QueryString"])
spark.sql(f"UPDATE Control.DDL SET PendingIndicator = 0, QueryOrder = 0, LastExecutedDateTime = CURRENT_TIMESTAMP WHERE ID = {i['ID']}")
%sql
---Creating the DDL Control table:
CREATE TABLE IF NOT EXISTS Control.DDL (
ID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
SchemaName STRING NOT NULL,
TableName STRING NOT NULL,
QueryString STRING NOT NULL,
QueryOrder INT,
PendingIndicator BOOLEAN NOT NULL,
LastExecutedDateTime TIMESTAMP,
DLCreatedDateTime TIMESTAMP
) USING DELTA LOCATION '/mnt/ContainerName/Control/DDL'
