Compose FDW - Make Datamart validation not force a drop and recreate for every fact/dim structure change
Currently, when making any structure changes (E.g. add a column, drop a column) to a data mart fact or dimension, a subsequent validate of the changes results in dropping and recreating the entire data mart, even if only one object should have been modified. This causes a usability and data availability issue, particularly when you have a large data mart (300+) facts, because both the drop and create and reload operation of the entire mart could take many hours.
The idea is to potentially add more elaborate logic to the validation so that it can detect a minor change to one table and just operate on that table rather than the entire data mart.
Thanks for your input and thanks to others that commented. This is on the roadmap, but I would be interested in what you consider the most painful "minor change" that should be addressed first.
We just bought Compose for DW and somehow we missed this problem in the POC we did before committing. This is a big issue. Dropping and recreating the entire data mart forces changes to be done only during weekend maintenance windows. Worse, it encourages modelers to include everything "just in case." That is the opposite of agile development.
I realize some types of changes are "breaking changes" and I would expect to have to drop and recreate the affected tables to implement them. However, for non breaking changes that could be done with alters and update statements, the burn everything down and rebuild approach is unreasonable.
IMO, adding an attribute to a dimension would be a great place to start and would add a lot of value to the product. Next, I would tackle adding columns to fact tables and then adding new dimensions. Of course renaming attributes and non breaking data type changes e.g. varchar(10) to varchar(20) should be low hanging fruit and be included in the first iteration since they would literally only require a simple alter table statement to implement.
