https://community.databricks.com/t5/data-engineering/window-functions-in-change-data-feed/m-p/48426#M28304 <DIV><SPAN>Hello!</SPAN></DIV><DIV><P>I am currently exploring the possibility of implementing incremental changes in our company's ETL pipeline and looking into Change Data Feed option. There are a couple of challenges I'm uncertain about.</P><P>For instance, we have a piece of logic like this:</P></DIV><P>&nbsp;</P><LI-CODE lang="markup">lag(is_available, 1, date '1970-01-01') over (partition by store, product_id order by is_available) as was_available_yesterday</LI-CODE><P>&nbsp;</P><P><BR />Another case involves calculating the most popular quantity sold:</P><P>&nbsp;</P><LI-CODE lang="markup">first_value(quantity) over (partition by sales_date, store, product order by count(*) desc) as quantity_mode</LI-CODE><P>&nbsp;</P><P>In both cases, I need to reference historical data. What would be the best approach to handle such scenarios? Should I create a separate table to store values for each unique combination of store and product, or should I join with a history table? I'm concerned about performance in the second case, as scanning the entire history table could be costly, especially considering that I have several fields that rely on historical context.</P> Thu, 05 Oct 2023 17:01:11 GMT mike_engineer 2023-10-05T17:01:11Z https://community.databricks.com/t5/data-engineering/window-functions-in-change-data-feed/m-p/48426#M28304 <DIV><SPAN>Hello!</SPAN></DIV><DIV><P>I am currently exploring the possibility of implementing incremental changes in our company's ETL pipeline and looking into Change Data Feed option. There are a couple of challenges I'm uncertain about.</P><P>For instance, we have a piece of logic like this:</P></DIV><P>&nbsp;</P><LI-CODE lang="markup">lag(is_available, 1, date '1970-01-01') over (partition by store, product_id order by is_available) as was_available_yesterday</LI-CODE><P>&nbsp;</P><P><BR />Another case involves calculating the most popular quantity sold:</P><P>&nbsp;</P><LI-CODE lang="markup">first_value(quantity) over (partition by sales_date, store, product order by count(*) desc) as quantity_mode</LI-CODE><P>&nbsp;</P><P>In both cases, I need to reference historical data. What would be the best approach to handle such scenarios? Should I create a separate table to store values for each unique combination of store and product, or should I join with a history table? I'm concerned about performance in the second case, as scanning the entire history table could be costly, especially considering that I have several fields that rely on historical context.</P> Thu, 05 Oct 2023 17:01:11 GMT https://community.databricks.com/t5/data-engineering/window-functions-in-change-data-feed/m-p/48426#M28304 mike_engineer 2023-10-05T17:01:11Z