Deduplicate Enghouse `ticket_results`

[lauriemerrell]

User story / feature request

Please describe your need, outlining the key users, the feature being requested, and the goal that that the feature will facilitate. For example: As a [user or stakeholder type], I want [software feature] so that [some business value]

As a Payments data user, I want clean, de-duplicated rides data for agencies using Enghouse as their payment processor. Currently, duplicate records are appearing because there are entries that appear twice in the ticket_results table with two different clearing_id records, one with timestamps and one without.

Acceptance Criteria

Please enter something that can be verified to show that this user story is satisfied. For example: I can join table X with table Y. or Column A appears in table Z in Metabase.

Deduplicate these rows, presumably we want to keep the ones with timestamps populated

Notes

Please enter any additional information that will facilitate the completion of this ticket. For example: Are there any constraints not mentioned above? Are there any alternatives you have considered?

[stevenschrayer]

@lauriemerrell - taking a look at this, it looks like stg_enghouse__ticket_results.sql already has an existing deduplication step but it takes the content hash including the dtm column which I think is why they're not being de-duped.

Would the preference be to do one of:

1. update existing deduplication: omit the specific dtm column(s) from the content hash generation, order by dtm col so that not-null is first, keep first record
2. create an intermediate model and update refs(), basically doing above
3. mystery

I don't have strong feelings any way I'm not just sure what the preferred norm is