DLT || Python || Aggregate Functions recomputing all the records

[shivam-singh]

Hi all, 

I am building a realtime dashboard using Databricks Delta Live Tables Pipeline and using the following steps : - 

Bronze Table : Using the autoloader functionality provided by databricks, its incrementally ingesting new files records into a bronze table.
Silver Table : Using the read_stream function provided in spark for structured streaming, we are creating the silver table by filtering the records and selecting few fields from the bronze table that are required.
Gold Table : Using the read function provided in spark for reading complete record, we are creating the gold table, which is the materialized view and also using aggregate function (SUM), and group by clause to create it.


Problem : 
Bronze and silver table are doing incremental ingestion, however incase of gold table, the entire record in the table is getting recomputed everytime a new record is received in the silver table.

What I want to ensure is that for the particular group by clause only updates should be performed and rest of the records are locked and dont require any update. 

I have also tried using streaming table instead of materialized view for gold as well, in this case also the entire records are getting recomputed.
Any help would be appreciated.

[Kaniz]

Hi @shivam-singh, 

• One option is to use change data capture (CDC) in Databricks Delta Live Tables Pipeline to update ta.... CDC lets you capture and apply changes from streaming sources like Kafka, S3, or Delta Lake tables. You can configure CDC settings such as how often to trigger an update, how long to keep the old data, and how to handle conflicts. You can also use CDC with other features of Databricks Delta Live Tables, such as Auto Loader and st....
• Another option is to use a long-running Databricks Job instead of a triggered Databricks Workflow fo.... A long-running job will keep a persistent Spark context active, allowing you to continuously execute the necessary data transformations and merge tasks. You can define a continuous query inside your job to handle the required data transformations and merging. Continuous questions are structured streaming queries designed to run continuously, writing the outp....

I hope this helps you with your project. You can check out the links I provided if you need more information. Have a great day! 😊