The tricky thing with DateTime SCD2s vs Date-only SCD2s is that DateTime SCD2s work for identifying what was true for a given click/transaction/ingest time, but doesn't work for identifying what was the final truth associated for a given "business date" such as an "invoice date". That tends to take an ETL or SQL window functions complexity/performance hit. But with streams/CDC, DateTime SCD2s are the easy+clean thing to implement.
Do you use the DateTime the message is received on the target system, or some DateTime from the originating journal/WAL-log?
The tricky thing with DateTime SCD2s vs Date-only SCD2s is that DateTime SCD2s work for identifying what was true for a given click/transaction/ingest time, but doesn't work for identifying what was the final truth associated for a given "business date" such as an "invoice date". That tends to take an ETL or SQL window functions complexity/performance hit. But with streams/CDC, DateTime SCD2s are the easy+clean thing to implement.
Do you use the DateTime the message is received on the target system, or some DateTime from the originating journal/WAL-log?