Hey! I'm looking for a way a way to transform a ta...
# troubleshooting
f
Hey! I'm looking for a way a way to transform a table, but filter out "spurious" updates (i.e. for a given PK, no column value structurally changed). I need to support updates & tombstones as well. Say for example, you have a source table "security_prices", sourced in via upsert-kafka. Then imagine you're doing a transformation like "CREATE TEMPORARY VIEW transformed As SELECT CASE WHEN Price > 123 THEN 1 ELSE 0 As indicator FROM Securities" or such. You can imagine, the input (security prices) move a whole lot, but the results of this transformation will very rarely structurally change. Problem is, by default Flink doesn't de-dupe that, and in my real world cases these then go into various joins etc. that end up completely overloaded because of all those spurious updates. Is there a way to prevent that? So far I could think of A) Creating a view with LAG(indicator,1) and filtering only if LAG(indicator,1)<>indicator. Problem is, I'm running into "StreamOverPhysicalAggregate doesn't support consuming update and delete changes" with that B) I could create an intermediate kafka topic, sink the transformation into there, and source it back from there - that way, the ChangelogNormalize operator should filter out these non-updates. Seems like a fairly big hassle for what I'd hope is a somewhat common problem though C) Wondering if I should attempt minibatching, and enable minibatch compaction.. only thing is, I'm not sure if compaction would remove entries that just have a -U +U with identical values? Is there any nice and easy-ish way to achieve this?
p
Hey there 👋
by default Flink doesn't de-dupe that
With this do you mean a tumbling dedup? Some questions that pop on my side: • Do you necessarily need to resolve this with FlinkSQL? ◦ Did you thing about a custom connector to ensure you get only those changes? • Did you think about resolving it one layer before and then streaming only meaningful changes for you? I mean, a specific SoT for this usecase.
b
- sounds like something indeed. If not a topic you could think in a custom SQL connector perhaps.