Create an ADR for Changing Watermark Functionality
Proposed Object Extract Watermark Design
Tables
| Table | Description | Notes |
|---|---|---|
| State.ObjectWatermark | Table that stores a watermark value for each object, this value is intended to be updated each time an extract is successfully run for each object. You can also reset the incremental loading range by altering this watermark value for an object. | New table, grain is one record per object Fields ( ObjectWatermarkId ,DatabaseId ,ObjectId ,LastWatermark ) |
| State.ObjectList | Table that stores attributes about Objects. | Grain is one record per object |
| State.ObjectExtract | Table that stores logs for each object extract. | Grain is one record per extract |
Stored procedures
| Stored Procedure | Description | Notes |
|---|---|---|
| State.GetObjectWatermark | This stored procedure takes parameters of an object and returns the current watermark value. | New stored procedure |
| State.UpdateObjectWatermark | This stored procedure takes parameters of an object and a new watermark value, then updates the watermark value for that object. | New stored procedure |
| State.GetObjectToCopy | This stored procedure takes parameters of an object and returns information about an object for the extraction process. | Grain is one record per extract |
| State.GetObjectListById | This stored procedure takes parameters for datasource and either load group or flow id and returns information about a list of objects to be extracted. | |
| Flow.GetObjectListById | This stored procedure takes parameters for datasource and either load group or flow id and returns information about a list of objects to be extracted. | |
| State.AddUpdateObjectExtractStatusGeneric | This stored procedure takes a bunch of parameters on metadata about the extraction that ran for an object, then updates an objectextract record. If successful extraction then it inserts a record into the ObjectIngest table. |
Flow Chart
The diagram above shows the process by which watermarks are managed, updated and retrieved as part of this proposed process.
Notes
- Will create all stored procedure changes as v2 with an api flag to toggle between new way of managing watermarks vs old way of managing watermarks
- As this feature is ready for release it will be defaulted to v2 ( where we have new watermark capability added)
- Need public documentation to added on Empower public docs for DA's to refer and understand how it will be used
- Delivery team wide notification to give a demo to entire delivery team in the Presales/Delivery Call
- Make sure this feature is not release until the documentation is available for QA and Delivery team.
Benefits
- This will allow for resetting incremental loads as far back as needed, also resetting for first time incremental load as needed
- This is also much faster for querying the latest watermark
Decisions
- Chose to put current watermarks in separate table rather than putting in ObjectList field because ObjectList is already bloated with fields and this current watermark field should not be included in config promo and should be different in every environment (This change will not affect config promo at all)