Investigate strict uniqueness for fct_vehicle_locations.key

[chrisyamas]

While building fct_tides_vehicle_locations (PR #5216), I leaned on the documented "almost unique" guarantee on fct_vehicle_locations.key and added a defensive QUALIFY ROW_NUMBER per microbatch. Residual cross-batch duplicates land at 0.0089% (8,538 of 96M rows over a 24-day window).

That fits the upstream unique_proportion at_least 0.999 test, but it isn't strict TIDES unique: true for the corresponding location_ping_id field. So the TIDES output inherits the same near-uniqueness rather than guaranteed uniqueness.

Worth a separate conversation about whether to tighten the upstream key composition (e.g., adding dt or microbatch boundary, or regenerating via farm_fingerprint over the natural-key tuple) so downstream consumers can rely on strict uniqueness without each adding their own dedup layer.

Filing this so we can discuss separately from the TIDES PR scope.

[lauriemerrell]

I am a little of two minds about this -- I take the point about guarantees for downstream consumers, but IIRC the looseness here was established for a combination of performance (we don't want to guarantee that we're actually testing with full coverage all the time because of the cost of running tests on all this data) and because we want to err on the side of allowing data availability for various analytics use cases.... Part of me thinks that downstream consumers figuring out what grain of uniqueness matters for their use case is appropriate.

[chrisyamas]

yeah that lands. agreed that downstream consumers handling their own uniqueness needs is the right framing here, and the testing-cost / data-availability tradeoff makes the looseness on the upstream the right call.

closing.