4 ms·

I like the "type=history" mode which can auto-build a slowly changing dimension ("SCD type 2") for you; more CDC solutions should do that: https://streamer.kuva

by gregw2 2y ago

I like the "type=history" mode which can auto-build a slowly changing dimension ("SCD type 2") for you; more CDC solutions should do that: https://streamer.kuvasz.io/streaming-modes/ https://streamer.kuvasz.io/streaming-modes/ That said, their implementation is kinda poor since it allows overlapping dates for queries when a row gets updated multiple times per day. When you SQL join to that kind of SCD2 by a given date you can easily get duplicates. This can be avoided by A) updating old rows to end-date yesterday rather than today, and B) if a row begins and ends on the same day, the start date or end date can be NULL or a hardcoded ancient or far-future end-date, such as having the record from "2023-01-01 to 2023-01-01" instead be "2023-01-01 to 0001-01-01". Those rows won't show up in joins, but the change remains visible/auditable and you do get the last row available for every given date (and only one such row.)

kuvasz-io 2y ago
The date fields are actually timestamps having microsecond accuracy. Maybe this was not clear in the docs. 1 | 12 | 1 | r1 | | 1900-01-01 00:00:00+00 | 2025-01-07 21:15:49.233384+00 | f 7 | 12 | 2 | r2 | | 1900-01-01 00:00:00+00 | 2025-01-07 21:15:49.233384+00 | f 13 | 12 | 1 | x1 | | 2025-01-07 21:15:49.233384+00 | 9999-01-01 00:00:00+00 | f
- gregw2 2y ago
  Ah yeah, makes sense. The docs are misleading. 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?

by gregw2 2y ago

kuvasz-io 2y ago

The date fields are actually timestamps having microsecond accuracy. Maybe this was not clear in the docs. 1 | 12 | 1 | r1 | | 1900-01-01 00:00:00+00 | 2025-01-07 21:15:49.233384+00 | f 7 | 12 | 2 | r2 | | 1900-01-01 00:00:00+00 | 2025-01-07 21:15:49.233384+00 | f 13 | 12 | 1 | x1 | | 2025-01-07 21:15:49.233384+00 | 9999-01-01 00:00:00+00 | f

gregw2 2y ago
Ah yeah, makes sense. The docs are misleading. 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?