mattcasters opened a new issue, #8803: URL: https://github.com/apache/hop/issues/8803
### What needs to happen? ## Challenge While the Dimension Update/Lookup transform offers a lot of flexibility and convenience for building slowly changing dimensions of any type, including hybrid, it comes at the cost of doing individual lookups in the target dimension. Despite the available caching features this can be slow, especially when using certain types of database like the clustered kind, remote ones like Snowflake, and so on. That being said, you still don't want to compromise on the functional requirements of seeing all the changes that happened to a certain business topic like customers, products, and so on. ## The logic To achieve the goal we're going to read data from one or more sources all sorted by the same business key (field `business_key`) and their changed dates. These sources can include the latest version of the target SCD2 target table. We ask the source database(s) to sort in the Table Input transforms we'll be using to read. If the source is not a database sort it in the Hop pipeline. (Warning block: In all scenarios, make sure to consider sorting with the right collation.) You will want to add a constant value identifying the source record (field `record_source`) when reading from each source. What is also needed is that we call the changed date the same in all sources to make it easy to work with (field `last_changed_ts`). We'll then combine all sources in the pipeline with a Sorted Schema Merge transform: https://hop.apache.org/manual/latest/pipeline/transforms/sortedschemamerge.html The output will be a sparse matrix where only the changes of the sources (and this can be all records for an initial load) are placed on the rows. In other words, for every source only the fields pertaining to that row will be filled in. To copy the data from the previous records we can use the Repeat Fields transform : https://hop.apache.org/manual/latest/pipeline/transforms/repeatfields.html with the "Current when indicated" option. See the example on the page to see how this works. Now we have a single data stream which is sorted by fields `business_key`, `last_changed_ts`, and where every record is correct. We will still need a date range to be calculate for the validity of this record. We can do this with an Analytic Query transform: https://hop.apache.org/manual/latest/pipeline/transforms/analyticquery.html <img width="678" height="130" alt="Image" src="https://github.com/user-attachments/assets/8f3ed350-3361-458a-bdc7-c062dbbe29f3"; /> We'll now have certain `from_ts` and `to_ts` values that are null. We can fill them in (to your liking) with start and end dates with an If Null transform: <img width="643" height="132" alt="Image" src="https://github.com/user-attachments/assets/ce549f9b-9dc7-4dd1-9518-56a5a0eeaa87"; /> So far all these oparations are very fast, capable of handling hundreds of thousads of records per seconds, all while consuming next to no memory since at most the amount of changes for a single business key is considered. It is possible to have an issue where multiple sources deliver duplicates, for example if you're not reading all attributes from a DV satellite. Then you'll get different records for identical field values. You can "compress" the data stream then with a Group By transform. Since the data stream is still sorted correctly we can group over all fields except `from_ts`, `to_ts`, `record_source`, `changed_date_ts`. The aggregation looks like this: <img width="652" height="158" alt="Image" src="https://github.com/user-attachments/assets/1bbeacf8-4c49-47ae-b6ad-2bff386ea240"; /> This too will be fast and in memory since unlike a relational database the transform only looks at a previous row to consider if it's still handling the same group to do the aggregation. For an initial load you can simply bulk load the data into your target database with optimal performance. For an incremental load your challenge is to correct the `to_ts` field at the very end of the pipeline with an Update transform. If that is slow and your database supports it, save these records (filter them out) to a separate file and bulk load them as well. ### Issue Priority Priority: 2 ### Issue Component Component: Database, Component: Documentation, Component: Pipelines, Component: Transforms -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
