# Join Guarantees in Stream-Table Join

**URL:** <https://forum.confluent.io/t/join-guarantees-in-stream-table-join/1378>\
**Category:** ksqlDB\
**Created:** [1 April 2021 11:31 UTC](https://forum.confluent.io/t/join-guarantees-in-stream-table-join/1378 "2021-04-01T11:31:30Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sahil-malhotra](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/sahil-malhotra/32/679_2.png) [@sahil-malhotra](https://forum.confluent.io/u/sahil-malhotra)\
**Post date:** [1 April 2021 11:31 UTC](https://forum.confluent.io/t/join-guarantees-in-stream-table-join/1378/1 "2021-04-01T11:31:31Z")

</div>

I have the following tables in postgres:

1. `clients` : Columns - client\_id, tenant\_id
2. `divisions` : Columns - division\_id, client\_id
3. `contacts` : Columns - contact\_id, division\_id

I need a Kafka topic with denormalized data joining the above tables. With ksqlDB and using Kafka-connect (Postgres CDC):

1. I created a `clients` stream and started materializing it into the ksql Table `clients_table`
2. Then I created a `divisions` stream, joined it with the `clients_table` and materialized it again into a `divisions_table` (which now has the tenant\_id info from `clients_table` )
3. And created the final `contacts` stream and joined it with `divisions_table` and loaded it into Elasticsearch (again using Kafka-connect)

The above seems to satisfy my use-case, but:  
When the queries in the Postgres tables are run in 10-100 millisecond gaps, I do not get the enriched data.  
I am setting `KSQL_KSQL_STREAMS_MAX_TASK_IDLE_MS` to 2,500 milliseconds, only after setting the above to 25,000 ms, I see the data enriched properly. But it has slowed down the process as well.

If I only consider joining the `divisions` stream with `client_table`, I could easily get away with a 2,500ms setting, but for another layer of materialization (i.e with `contacts`, it requires more than 25,000ms to guarantee joins.  
Is there a way to improve this?

---

<div class="post-metadata">

**Author:** ![sahil-malhotra](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/sahil-malhotra/32/679_2.png) [@sahil-malhotra](https://forum.confluent.io/u/sahil-malhotra)\
**Post date:** [1 April 2021 14:40 UTC](https://forum.confluent.io/t/join-guarantees-in-stream-table-join/1378/2 "2021-04-01T14:40:21Z")

</div>

**UPDATE** :

Could make it work this way, with the same 2,500 idle ms.:

```
CREATE STREAM enriched_division_contacts AS
SELECT * FROM "contacts"
INNER JOIN
"divisions_table" on "contacts".division_id = "divisions_table"."division_id"
INNER JOIN "clients_table" on "divisions_table"."client_id" = "clients_table"."client_id" ;

```

Thanks [@Aneel](https://confluentcommunity.slack.com/team/UDAMF2S12) for the help.

Slack Thread: [Slack](https://confluentcommunity.slack.com/archives/C6UJNMY67/p1617270568266500)

---

<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:** [8 April 2021 14:40 UTC](https://forum.confluent.io/t/join-guarantees-in-stream-table-join/1378/3 "2021-04-08T14:40:34Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.