topic Re: Multiple streaming sources to the same delta table in Data Engineering

Multiple streaming sources to the same delta table

hari — Wed, 01 Jun 2022 10:48:45 GMT

Is it possible to have two streaming sources doing Merge into the same delta table with each source setting a different set of fields?

We are trying to create a single table which will be used by the service layer for queries. The table can be populated from multiple source tables each contributing a set of fields to the sink schema.

Sample query for the scenario:

MERGE INTO sink
   USING source_a
   ON source_a.id = sink.id
   WHEN MATCHED THEN
     UPDATE SET
       id = source_a.id,
       summary = source_a.summary,
       description = source_a.description,
       customerId = source_a.customerId
   WHEN NOT MATCHED
  THEN INSERT (
    id,
    summary,
    description,
    customerId
  )
  VALUES (
    source_a.id,
    source_a.summary,
    source_a.description,
    source_a.customerId
  )

MERGE INTO sink
   USING source_b
   ON source_b.customer.id = sink.customerId
   WHEN MATCHED THEN
     UPDATE SET
       customerId = source_b.customerId,
       customerName = source_b.customerName,
       customerIndustry = source_b.customerIndustry

I am new to streaming and was wondering whether there is some way to implement this using streaming queries. Currently seeing out of order issues when merging the data to the sink, But is there something which can be done to go around the problem.

Re: Multiple streaming sources to the same delta table

Zachary_Higgins — Wed, 01 Jun 2022 21:43:32 GMT

Not sure about your partitioning schema, but be careful, as you can run into issues with concurrent updates to the same partition. Concurrency control | Databricks on AWS. In this case, I usually set the trigger to run once, running each merge update synchronously, and putting the job on a schedule.

What kind of "out of order" issues are you seeing?

Re: Multiple streaming sources to the same delta table

hari — Thu, 02 Jun 2022 10:57:10 GMT

Hi @Zachary Higgins

Thanks for the reply

Currently, we are also using Trigger.once so that we can handle the merge stream dependencies properly. But was wondering whether we can scale our pipeline to be streaming by changing the Trigger duration in the future

Thanks for the heads up regarding Concurrency, I was assuming a Pessimistic concurrency control where delta would handle concurrent writes to the same partition without exceptions or data corruption.

I don't think we can support full streaming pipelines if there can be concurrency issues

Re: Multiple streaming sources to the same delta table

Shourya — Fri, 20 Jan 2023 18:17:41 GMT

Hi @Kaniz Fatma

I'm looking for a similar use case where we have MULTIPLE SOURCE TABLES IN BRONZE/Silver and would need to push data into the same TARGET TABLE. We have adopted a Data Vault as our data Model for Silver. If you could provide some documentation or URL that would be great.