automate-dv icon indicating copy to clipboard operation
automate-dv copied to clipboard

Initial Additional columns feature

Open DVAlexHiggs opened this issue 1 year ago • 0 comments

This is a bit of a bigger commit than I intended as it should have been three separate pull requests. This PR consists of three main things:

Additional columns parameter for every table macro

A new parameter src_additional_columns will allow users to add arbitrary columns to their output tables (Hubs, Links, Satellites, all other table macros) This will provide the means to include more audit and metadata columns if the user chooses to, as well as things such as putting the business/natural keys into Links if a user so chooses.

Users should still be careful to be sensible with this and follow the standards, it just allows more flexibility. The additional columns are non-invasive and will not have any affect on any logic, as they are merely added to the resulting final table and never used in any prior selects, joins, aggregates etc. This should make it a safe way to just add arbitrary columns to a table without unnecessarily wrapping table macro calls in CTEs and adding them later as suggested in various places by myself previously.

The implementation of this feature for PITs and Bridges is not quite there yet, as there needs to be some additional logic to handle if we get columns from the Stage, the Hubs, Links, or the Satellites that the tables are built on top of, currently the macros assume the columns are in the source model, but this is not guaranteed.

Minor Fixture re-factor

I've re-worked the way fixtures work very slightly to reduce the amount of duplicated metadata going on behind the scenes. We still have a long way to go, but this is the first step towards a cleaner back-end and metadata management system.

PITs and Bridges re-factor

PIT and Bridge SQL was very hard to read when compiled, and the Jinja had a huge amount of repetition, suitable for moving into macros. We need to make some improvements to both of these macros in the long-term anyway, so this is the first phase to making them more modular and easier to maintain. For example, I've moved the AS_OF date handling to its own macro, which both PITs and Bridges now use.

DVAlexHiggs avatar Jul 19 '22 18:07 DVAlexHiggs