I’d like to treat the initial snapshot for a given source record differently in downstream queries in a performant fashion. I could certainly sub-select in my staging table on top of the snapshot table, but that seems inefficient.
Is there a way I can add either a sequence number or an initial boolean to the snapshots table itself and set it when the snapshot is created?
You could try setting a flag or something in a post-hook after your snapshot runs. We took the inefficient route of building all of our staging tables based on the max_date for each pkey
Note: @Jeff Green (CHG Healthcare) originally posted this reply in Slack. It might not have transferred perfectly.
Thanks - I think that’s the right solution. We’ll see how well computing row_number() in a post-event hook scales performance wise…I doubt I’ll notice it. But the hook feels like the idiomatic approach here, and I like that it’s self-contained within the snapshot configuration which should increase maintainability.