[PR] Issue #8803 : Document high-volume insert-heavy SCD2 loads (hop)

["mattcasters (via GitHub)" <gi...@apache.org>]

mattcasters opened a new pull request, #8807:
URL: https://github.com/apache/hop/pull/8807

   Documents the high-volume, insert-heavy SCD2 pattern from issue #8803 as a how-to guide.
   
   The page walks through sorted sources, a `record_source` constant, Sorted schema merge, Repeat fields with *Current when indicated*, an Analytic Query validity range, If null sentinels, and an optional Group By collapse. Screenshots from the issue are included, along with the customer 360 initial-load pipeline and the option to expose the current SCD2 row as an SCD1 view for facts.
   
   The guide is linked from the how-to index, the manual navigation, Dimension lookup/update, Sorted schema merge, Repeat fields, Joins and lookups, Change Data Capture, and the getting-started next steps.
   
   fixes #8803
   
   ------------------------
   
   Thank you for your contribution! Follow this checklist to help us incorporate your contribution quickly and easily:
   - [ ] Run `mvn clean install apache-rat:check` to make sure basic checks pass. A more thorough check will be performed on your pull request automatically.
   - [x] If you have a group of commits related to the same change, please squash your commits into one and force push your branch using `git rebase -i`.
   - [x] Mention the appropriate issue in your description (for example: `addresses #123`), if applicable.
   
   To make clear that you license your contribution under the [Apache License Version 2.0, January 2004](http://www.apache.org/licenses/LICENSE-2.0)
   you have to acknowledge this by using the following check-box.
   
   - [x] I hereby declare this contribution to be licensed under the [Apache License Version 2.0, January 2004](http://www.apache.org/licenses/LICENSE-2.0)
   - [ ] In any other case, please file an [Apache Individual Contributor License Agreement](https://www.apache.org/licenses/icla.pdf).
   


-- 
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: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [PR] Issue #8803 : Document high-volume insert-heavy SCD2 loads (hop)

["bamaer (via GitHub)" <gi...@apache.org>]

bamaer commented on PR #8807:
URL: https://github.com/apache/hop/pull/8807#issuecomment-6066930323

   Docs-only, so none of this can break anything. I reviewed the text but didn't build the site.
   
   **Fix before merge**
   
   1. **Should `from_ts` be the row's own `changed_date_ts` rather than Lag 1?** (`high-volume-scd2.adoc`, "Calculate the validity range"). With Lag, each version starts when the version before it started, so neighbouring ranges overlap, as the page itself says. That causes two problems further down the guide:
      - The fact join "range contains the fact timestamp" matches two SCD2 rows for any fact older than the latest change.
      - On an incremental load the stored current row is the first row of its key, so its recomputed `from_ts` becomes `1900-01-01`. The Update on `business_key` + `from_ts` then misses that row, or closes the key's first version instead.
   
      Using `from_ts = changed_date_ts` and `to_ts` = Lead 1 gives ranges that don't overlap and fixes both. The example tables and the Analytic Query screenshot would need to change with it.
   
   2. **An incremental load inserts unchanged current rows again** ("Incremental load"). The stored current row is fed back in as a source. For a key with no new change, or only changes that the Group By collapses into it, that row comes out with `to_ts = 9999-12-31`, and "Bulk load every row that still ends at `9999-12-31`" inserts a duplicate. Suggested fix: only insert rows that don't come from the target source. Filter on `record_source`, and only read target rows for business keys that appear in the delta.
   


-- 
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: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [PR] Issue #8803 : Document high-volume insert-heavy SCD2 loads (hop)

["bamaer (via GitHub)" <gi...@apache.org>]

bamaer merged PR #8807:
URL: https://github.com/apache/hop/pull/8807


-- 
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: issues-unsubscribe@hop.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org