# Eliminating all but last CDC record for a topic within 2 sec window

**URL:** <https://forum.confluent.io/t/eliminating-all-but-last-cdc-record-for-a-topic-within-2-sec-window/10033>\
**Category:** ksqlDB\
**Created:** [5 February 2024 11:18 UTC](https://forum.confluent.io/t/eliminating-all-but-last-cdc-record-for-a-topic-within-2-sec-window/10033 "2024-02-05T11:18:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tim.jones.001](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/tim.jones.001/32/3575_2.png) [@tim.jones.001](https://forum.confluent.io/u/tim.jones.001)\
**Post date:** [5 February 2024 11:18 UTC](https://forum.confluent.io/t/eliminating-all-but-last-cdc-record-for-a-topic-within-2-sec-window/10033/1 "2024-02-05T11:18:44Z")

</div>

We have a legacy source SQL Server DB that produces multiple CDC records for each table update due to post-insert/update triggers.

We are using debezium to capture the SQL Server transactions and can see how we could filter all but the first within a couple of seconds in a SMT, but not the last.

Rather than processing each record, we would like to generate a filtered topic with only the last record of the series, on which to do further processing.

We can see how we can do this manually by reading the topic with its duplicates, and caching records for a second and only producing a filtered record to another topic containing the last value within the 2 second window, but can’t help thinking that ksqlDB was created to do such things more easily.

Can anyone point us in the right direction please?

---

<div class="post-metadata">

**Author:** ![dtroiano](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/dtroiano/32/1961_2.png) [@dtroiano](https://forum.confluent.io/u/dtroiano)\
**Post date:** [5 February 2024 16:07 UTC](https://forum.confluent.io/t/eliminating-all-but-last-cdc-record-for-a-topic-within-2-sec-window/10033/2 "2024-02-05T16:07:24Z")

</div>

You could use a tumbling window with `EMIT FINAL` to accomplish this. E.g., if you have a stream with deduplication key column `id`, and another column that might vary called `val`, then you can get the last `val` per `id` over 2-second windows like this:

```auto
SELECT id,
    LATEST_BY_OFFSET(val),
    WINDOWSTART,
    WINDOWEND
FROM test
WINDOW TUMBLING (SIZE 2 SECONDS, GRACE PERIOD 0 SECOND)
GROUP BY id
EMIT FINAL;

```

Note that data has to keep flowing in order for windows to close (see [here](https://forum.confluent.io/t/ksqldb-table-with-tumbling-window-and-emit-final/6910/2)).

Flink SQL’s [window deduplication](https://nightlies.apache.org/flink/flink-docs-stable/docs/dev/table/sql/queries/window-deduplication/) is also a good fit for this. By ordering by timestamp `DESC` and returning the first row, you get the last row per window, e.g.:

```auto
SELECT id, val FROM (
  SELECT id,
    val,
    window_start,
    window_end,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC) AS rownum
  FROM TABLE(TUMBLE(TABLE test, DESCRIPTOR(ts), INTERVAL '2' SECONDS))
) WHERE rownum = 1;

```

---

<div class="post-metadata">

**Author:** ![tim.jones.001](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/tim.jones.001/32/3575_2.png) [@tim.jones.001](https://forum.confluent.io/u/tim.jones.001)\
**Post date:** [19 February 2024 22:10 UTC](https://forum.confluent.io/t/eliminating-all-but-last-cdc-record-for-a-topic-within-2-sec-window/10033/3 "2024-02-19T22:10:09Z")

</div>

Thanks very much for your reply; I’ll give it a try 👍🏼

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [6 March 2024 11:19 UTC](https://forum.confluent.io/t/eliminating-all-but-last-cdc-record-for-a-topic-within-2-sec-window/10033/4 "2024-03-06T11:19:21Z")

</div>

Closed on 6 March 2024 11:19 UTC

This topic was automatically closed after 30 days. New replies are no longer allowed.