uspSetDeltaExportTimePeriod

The purpose of this stored procedure is to override the value in column DeltaExportAssertionTimeFrom and DeltaExportAssertionTimeBefore in view MetadataZone.MetadataForExecuteDataLogisticLoadProcessStep which uses a module as load window at execution time in data logistics.

Overview

uspSetDeltaExportTimePeriod provides precise control over delta export time periods for modules within the ExMeX Framework Core. This procedure enables administrators to set specific time boundaries for data extraction, supporting both single modules and batch-wide operations.

Use Cases

The motivation for this stored procedure includes various scenarios:

• Initial Load Configuration: Set a timestamp to perform an module initial load from a source to one or many target objects
• Data Recovery: Set a timestamp to perform an module reload of a defined time period from a source to target objects in case of errors or other issues
• Source Reliability Handling: Set different timestamps for an module to ensure extraction of slightly different time periods from unreliable sources
• Load Window Management: Define precise time boundaries for delta processing with both start and end timestamps

Applicability

This stored procedure is applied whenever the timestamp value in table MetadataForExecuteDataLogisticLoadProcessStep, column DeltaExportAssertionTimeFrom, needs to be replaced with a different timestamp for a Lmodule provided by the ExMeX Framework Core.

Syntax

EXEC MetadataZone.uspSetDeltaExportTimePeriod
     @BatchLabel                     = N'DailyCustomerLoad'
    ,@LoadProcessStepId              = 1234 -- ModuleId
    ,@DeltaExportAssertionTimeFrom   = '2025-01-01 00:00:00'
    ,@DeltaExportAssertionTimeBefore = '2025-01-01 23:59:59'

Parameters

Required Parameters

Parameter	Data Type	Description
@BatchLabel	NVARCHAR(256)	Label (name) of the batch as defined in MetadataZone.LoadBatch
@DeltaExportAssertionTimeFrom	DATETIME2	New timestamp for column DeltaExportAssertionTimeFrom - defines the start of the extraction period

Optional Parameters

Parameter	Data Type	Default	Description
@LoadProcessStepId	BIGINT	NULL	Unique identifier for a specific module. If NULL, affects all module in the batch
@DeltaExportAssertionTimeBefore	DATETIME2	NULL	End timestamp for the extraction period. If NULL, uses SYSUTCDATETIME()

Return Values

The procedure returns an integer status code:

• 0: Successful execution

Functionality

Data Processing Flow

1. Target Identification: The procedure identifies target modules based on the provided @BatchLabel and optional @LoadProcessStepId
2. Conflict Resolution: Existing override records for the same LoadBatchId/ProcessStepId combinations are automatically deleted
3. Override Creation: New override records are inserted into MetadataZoneStage.ProcessStepDeltaExportOverride
4. Framework Integration: The ExMeX Framework Core uses this override data via stored procedure uspProcessStepVapourTrail

Scope Control

• Single module: When @LoadProcessStepId is specified, only that specific module is affected
• Batch-wide: When @LoadProcessStepId is NULL, all modules within the specified batch are affected

Usage Examples

Example 1: Override Timestamp for Single module

-- Set specific time period for one module
EXEC MetadataZone.uspSetDeltaExportTimePeriod
     @BatchLabel                     = N'Customer Data Hub (Core)'
    ,@LoadProcessStepId              = 1256
    ,@DeltaExportAssertionTimeFrom   = '2024-01-01 00:00:00'
    ,@DeltaExportAssertionTimeBefore = '2024-01-31 23:59:59'
;

Example 2: Override Timestamp for All module in Batch

-- Set time period for entire batch (all modules)
EXEC MetadataZone.uspSetDeltaExportTimePeriod
     @BatchLabel                   = N'DailyCustomerLoad'
    ,@LoadProcessStepId            = NULL  -- Affects all module in batch
    ,@DeltaExportAssertionTimeFrom = '2024-01-01 06:00:00'
;

Example 3: Initial Load Configuration

-- Configure initial load from beginning of time
EXEC MetadataZone.uspSetDeltaExportTimePeriod
     @BatchLabel                     = N'Product Master Initial Load'
    ,@LoadProcessStepId              = NULL
    ,@DeltaExportAssertionTimeFrom   = '1900-01-01 00:00:00'
    ,@DeltaExportAssertionTimeBefore = NULL -- Replaced with SYSUTCDATETIME()
;

Example 4: Data Recovery Scenario

-- Reload specific time period after data corruption
EXEC MetadataZone.uspSetDeltaExportTimePeriod
     @BatchLabel                     = N'Sales Transaction Recovery'
    ,@LoadProcessStepId              = 2048
    ,@DeltaExportAssertionTimeFrom   = '2024-09-08 14:00:00'
    ,@DeltaExportAssertionTimeBefore = '2024-09-08 18:00:00'
;

Key Features

Automatic Conflict Management

The procedure automatically handles conflicts by removing existing override records before inserting new ones, ensuring clean state management.

Flexible Time Boundaries

With the introduction of @DeltaExportAssertionTimeBefore, you can now define precise extraction windows instead of relying on system timestamps.

Batch and Individual Control

Supports both granular (single module) and broad (entire batch) scope control through the @LoadProcessStepId parameter.

Integration with Load Windows

The procedure works seamlessly with the ExMeX Framework's load window functionality to prevent incorrect behavior during delta processing.

Dependencies

This stored procedure interacts with the following ExMeX Framework objects:

• Input Tables:

  • MetadataZone.LoadBatch - For batch label resolution
  • MetadataZone.LoadBatchProcessStep - For module identification within batches

• Target Table:

  • MetadataZoneStage.ProcessStepDeltaExportOverride - Stores the override configuration

• Integration:

  • uspProcessStepVapourTrail - Consumes the override data during execution
  • MetadataForExecuteDataLogisticLoadProcessStep - Target view affected by overrides

Considerations and Consequences

Important Limitations

• Batch-level Override: Override of timestamps at the batch level in MetadataForExecuteDataLogisticBatch is not possible through this procedure
• Load Window Interaction: When using load windows, ensure @DeltaExportAssertionTimeBefore is properly set to avoid incorrect time boundary calculations

Data Persistence

The procedure inserts data into ProcessStepDeltaExportOverride table, which persists until explicitly removed or overwritten by subsequent procedure calls.

Framework Integration

Override data is automatically consumed by the ExMeX Framework Core during module execution, requiring no additional configuration steps.

Version History

• Version 1.0.3 (2024-09-04)
  • Updated: Feature 0000701 - Duplicate load windows for modules
  • Added: @DeltaExportAssertionTimeBefore parameter with NULL default
  • Improved: Load window handling to prevent incorrect behavior
  • Changed: Default end timestamp behavior from high-end-timestamp to SYSUTCDATETIME()

Best Practices

1. Time Zone Consistency: Always use consistent time zones across your timestamps
2. Load Window Boundaries: When defining extraction periods, ensure @DeltaExportAssertionTimeBefore is set appropriately for load window operations
3. Testing: Test override configurations in non-production environments first
4. Documentation: Document the business reasons for timestamp overrides for audit purposes
5. Monitoring: Monitor the ProcessStepDeltaExportOverride table for active overrides

Copyright

© 2017 - 2025 Dirk Lerner | TEDAMOH GmbH