2
Accepted answer
Sentinel simplifies join logic since you skip the OR IS NULL, and it plays nicer with some BI tools. NULL is more semantically honest though.
Designing dim_customer SCD2 in Snowflake. Team is split on valid_to IS NULL for the current row versus a '9999-12-31' sentinel.
Downstream dbt models and BI tools mix both patterns already. What are the real pros and cons in production pipelines?
Accepted answer
Sentinel simplifies join logic since you skip the OR IS NULL, and it plays nicer with some BI tools. NULL is more semantically honest though.
Pick one and enforce it in the dbt contract. We use NULL, then a macro coalesces to far-future only at the presentation layer.
We saw the same issue, fixing the partition filter dropped runtime 60%.
Document the grain decision, most BI bugs turn out to be grain bugs.
Sign in to reply.