Skip to main content

Product Requirements Document

Mission​

We support multiple ingestion strategies as part of our acquisition phase. We want to bubble these up to the user interface so that users can easily configure new ingestion strategies.

Background​

Because of client need, our data team implemented two new load strategies to ingest data. Currently this is only accessible from StateDB configurations, but we’d like users to be able to select these from the user interface. This brings our total number of load strategies to 2, and merge strategies to 5. On top of these, we support type 1 and type 2 history retention options.

Load Strategies​

  1. Full Load: This is the default load strategy. This strategy does a complete refresh of the target table. The entire source data set is read from the source and used to update the target table. Any records in the target that are not in the source are deleted. Any records in the source that are not matched get appended. This strategy is most useful for tables that have no watermark columns to use for an incremental extraction.
  2. Incremental Load: This strategy loads an incremental dataset to the target table. A watermark column is used during extraction to only fetch records that are new relative to the last extraction.

Merge Strategies​

This would be part of Advanced Options flagging.

  1. Auto: System picks the correct merge strategy. Need to think about this.
  2. Full Merge:
    1. This is the default merge strategy strategy for full loads.
    2. This strategy uses row hashes and set differences to determine which records are new or updated/removed. No candidate key is needed. Requirements: none.
  3. Incremental Merge:
    1. This is the default merge strategy strategy for incremental loads.
    2. This strategy loads the dataset to the target table. Within the ingestion, the watermark column is used to identify the newest version of each candidate key(s), and the older versions are dropped. The data is then loaded, using the candidate key as the merge column(s). When there is no match from the source, the new record is insert to the target. When there is a match, the watermark value is newer, and the rowhash columns differ, the target record is updated.
    3. Requirements: a watermark and at least one candidate key.
  4. Direct/Unsafe Incremental Merge:
    1. This strategy is identical to the incremental merge strategy, except it does not utilize the watermark column during the ingestion step. This means that the de-deduplication of candidate keys is not executed and incoming records are always assumed to be newer than the existing data. Just like Incremental Merging, this then will use the candidate key as the merge column(s). This strategy is useful when one is confident that data will never be duplicated and any new data is always desired.
    2. Requirements: at least one candidate key.
  5. Append Merge:
    1. This strategy only checks the rowhash values. If any source rowhash is not in the target, it is appended to the target table. This strategy is useful when there is a watermark value to do an incremental extraction, but there is no candidate key to merge on. Many log tables are good candidates for this strategy.
    2. Requirements: none.+

History Options​

This would be part of Advanced Options flagging.

  1. History (Type 2): This is the default option. Type 2 slowly changing dimensions (SCD) maintain the history of the raw data that has entered the table. Any time a record is deleted, the record is marked as no longer current and the recordenddate is marked for the current time. Note that updates in these tables are really executed as delete the current record and then insert the new record. This option comes with a view layer which tracks the most current version of the table.
  2. Overwrite (Type 1) - This variation of Type 1 slowly changing dimensions (SCD) does not maintain the history of the raw data. Any time a record is deleted, the record is removed from the table. Updates are executed as updates. This strategy is useful when latency requirements are strict. Note that since all Empower tables use Delta format, the history is still present in the delta version history, but in a different format from the type 2 history table. This is an experimental variation. A tooltip should specify that this is experimental! This is slightly different than how Type 1 is done in Model Builder FYI, hence the name differentiation

Notes​

There are a few common and interesting combinations to mention:

Incremental Load and Incremental Merge: This is the ideal world, since we have watermark columns to refrain from overextracting old data while only upserting new data in bronze. Unfortunately not everything has a watermark column to do this with.

Incremental Load and Unsafe Incremental Merge : Assumes no duplicate keys, a little bit faster than Safe Incremental

Full Load and Full Merge: Fully wipes out target columns to get a full table refresh in Bronze. While capturing all data changes including deletions (which the other merge strategies will not do), this is not nearly as efficient as Change Data Capture strategies, which are not supported yet. (soon!)

Full Load and Incremental/Direct Merge: Basically performs the equivalent of a full load without processing record deletion. Useful if you did not want to/could not track data deletion.

Full Load and Append Merge: Grab all data available and append any new hashes to target. Useful for log dumps!

Incremental Load and Full Merge: This is really weird and should be heavily scrutinized. I think the user should* be allowed to do this, but it should raise eyebrows.

Targeted Personas​

Who is the primary beneficiary of this initiative? Why? Are there secondary beneficiaries?

  • Dario Data Professional: who will be going in to set this up in the UI based on project requirements
  • Debbie Delivery Engineer: who will be doing the same ^

Current Behavior​

Currently we support two Load Strategies:

  1. Full Merge
  2. Incremental Merge

This PRD is to support the addition of merge strategies and SCD variations during data acquisition.

Outcomes / Goals​

  • This will be a single-version release, simultaneous support in StateDB as well as the UI.

Support for Load Strategy, Merge Strategy, and SCD Option manipulation from the UI. Merge Strategy and SCD Option manipulation will only be available to users with Advanced Options toggled on.

User Stories​

Modifying a load strategy​

As a data engineer, I want to change the load strategy from full merge to incremental merge from the metadata catalog page.

Use cases​

Advanced Options Off​

  1. Defining Load Strategy
    1. Primary Actor: Data engineer/architect
    2. Description: DA's will need to configure and manipulate load strategies from the UI inside the metadata catalog (where they do so today). This will be expanded to include two new strategies to select from.
    3. Preconditions: There is already a metadata catalog to manipulate (data source has metadata extracted (v1.14+) and acquisition flow has been created (v2.0+)) Advanced options is flagged off.
    4. Postconditions: Any object's load strategy can be modified away from default (full merge)
    5. Flow:
      1. User navigates to the flow metadata catalog.
      2. Every object in the catalog has a load strategy defined.
      3. User can modify the load/merge strategy for any object to be any one of the two regular strategies:
        1. Full - full (default)
        2. Incremental - Incremental
      4. Outcome: metadata catalog captures changes and this is reflected in the acquisition of the data going forward.

Advanced Options On​

  1. Defining Load Strategy, Merge Strategy, and SCD Variation
    1. Primary Actor: Data engineer/architect
    2. Description: DA's will need to configure and manipulate load strategies from the UI inside the metadata catalog (where they do so today). This will be expanded to include two new strategies to select from.
    3. Preconditions: There is already a metadata catalog to manipulate (data source has metadata extracted (v1.14+) and acquisition flow has been created (v2.0+)). Advanced options is flagged on.
    4. Postconditions: Any object's strategy combination can be modified to be one of any acceptable combinations
    5. Flow:
      1. User navigates to the flow metadata catalog.
      2. Every object in the catalog has a load strategy defined.
      3. User can modify the combination strategy to be any one of the following:
        1. full - full (Default)
        2. full - append
        3. incremental - incremental
        4. incremental - (direct) unsafe incremental
        5. incremental - append
      4. User can modify the strategy variation for any object to be one of two:
        1. Type 2: this is the default.
        2. Raw: this will have a tooltip specifying it is experimental.
      5. Outcome: metadata catalog captures changes and this is reflected in the acquisition of the data going forward.

Requirements​

Functional​

  • List of functional description of the feature

Non-functional requirements​

  • Simplicity, we don’t want to confuse the user.
  • Scalability, we may have NEW ingestion, merge, and SCD Option strategies in the future! We want to support those too.
  • We dont want to build too many guardrails for the user, but we do want a way to explain these if they have questions from the UI, perhaps using toolips or wiki links to help guide.

Out of Scope​

  • Anything not acquisition related.

Dependencies & Constraints​

  • Today, there is a boolean column in ObjectList "IsIncremental" in StateDB's ObjectList. So, just supports True/False for Incremental/Full. We want to create enums for Merge and SCD options. If possible, we can do the same for load strategies.
  • This will have to be synced to any changes for Flows, as we want to support that initiative going forward.

Assumptions​

  • N/A

Risks​

  • N/A

Timeline and Milestones​

Phase I​

  • Release 1.21
    • Feature complete

Questions?​