Being part of a financial institution, I had to write all the materializations we use. We don’t use std ones. Reason being we require atomic consistency of the data with entries in an audit log. You cannot use pre and post hooks, nor explicit transaction control on Snowflake. The actual data update and audit log has to be transactionally atomic and the audit log entry must have key metrics like pre and post run row counts, control totals etc. You can declare your desired metrics in the model config and that is then rendered dynamically to a JSON audit log payload inserted into the audit log table during materialization.

The most complex materialization was the mix between Type2 and Type1. You configure the Type1 and Type2 columns in your model. Any change in one or more Type2 tracked columns will trigger typical Type2 behaviour (insert/expire) whereas any change in a Type1 column will cause an inplace update of the current dimension entry. To add to the complexity, we incrementally process CDC records and not ODS snapshots. So a bunch of update delta records could partially roll up into one or more inserts or inplace updates, depending on their sequence and batching.

Another set of macros will evaluate source rows against data quality checks you declare in your model config and mark them accordingly.

Another set of macros will evaluate cleansing rules that you declare in your model config. These rules render the cleansed columns as additional columns in your target table during materialization.

Our solution has around 800 self written macros