Purpose: Update DATA_EXPORTER package to support renamed column in CT_ODS.A_LOAD_HISTORY
Type: Pre-Hook Deployment (Required before MARS-826)
Target Schema: CT_MRDS
Database Objects: DATA_EXPORTER package body

Background

Issue

The CT_ODS.A_LOAD_HISTORY table has column A_ETL_LOAD_SET_KEY (PRIMARY KEY), but the DATA_EXPORTER package had hardcoded references to the old column name A_WORKFLOW_HISTORY_KEY, causing JOIN failures.

Root Cause

Column name mismatch discovered during MARS-826 testing:

Database DDL: A_ETL_LOAD_SET_KEY (correct, as per source definition)
DATA_EXPORTER package: L.A_WORKFLOW_HISTORY_KEY (incorrect, outdated reference)
Impact: All export procedures failed with ORA-00904: "L"."A_WORKFLOW_HISTORY_KEY": invalid identifier

Solution

Updated DATA_EXPORTER.pkb to use correct column name A_ETL_LOAD_SET_KEY in all JOIN operations with CT_ODS.A_LOAD_HISTORY.

Changes Made

Package Version Update

Previous Version: 2.1.0
New Version: 2.1.1
Change Type: PATCH (Bug Fix)
Build Date: 2025-12-04 13:10:00

Modified Files

DATA_EXPORTER.pkg (Package Specification)
- Updated PACKAGE_VERSION: '2.1.0' → '2.1.1'
- Updated PACKAGE_BUILD_DATE: '2025-12-04 13:10:00'
- Updated VERSION_HISTORY with v2.1.1 entry
DATA_EXPORTER.pkb (Package Body)
- Updated 4 JOIN clauses in dynamic SQL
- Updated example in documentation comment

Specific Changes

Location 1: Line ~326 (EXPORT_TABLE_DATA_BY_DATE - partition query)

-- BEFORE:
WHERE T.' || ... || ' = L.A_WORKFLOW_HISTORY_KEY

-- AFTER:
WHERE T.' || ... || ' = L.A_ETL_LOAD_SET_KEY

Location 2: Line ~340 (EXPORT_TABLE_DATA_BY_DATE - export query)

-- BEFORE:
WHERE T.' || ... || ' = L.A_WORKFLOW_HISTORY_KEY

-- AFTER:
WHERE T.' || ... || ' = L.A_ETL_LOAD_SET_KEY

Location 3: Line ~613 (EXPORT_TABLE_DATA_TO_CSV_BY_DATE - partition query)

-- BEFORE:
WHERE T.' || ... || ' = L.A_WORKFLOW_HISTORY_KEY

-- AFTER:
WHERE T.' || ... || ' = L.A_ETL_LOAD_SET_KEY

Location 4: Line ~629 (EXPORT_TABLE_DATA_TO_CSV_BY_DATE - export query)

-- BEFORE:
WHERE T.' || ... || ' = L.A_WORKFLOW_HISTORY_KEY

-- AFTER:
WHERE T.' || ... || ' = L.A_ETL_LOAD_SET_KEY

Location 5: Line ~398 (Documentation example)

-- BEFORE:
pKeyColumnName  => 'A_WORKFLOW_HISTORY_KEY',

-- AFTER:
pKeyColumnName  => 'A_ETL_LOAD_SET_KEY_FK',

Deployment

Prerequisites

Database connection: CT_MRDS schema
Required privileges: ALTER PACKAGE on CT_MRDS.DATA_EXPORTER
Estimated time: 1-2 minutes

Installation Steps

The master installation script performs these operations in sequence:

Deploy Package Specification - Install updated DATA_EXPORTER.pkg from new_version/ (v2.1.1)
Deploy Package Body - Install updated DATA_EXPORTER.pkb from new_version/ with column reference fixes
Track Version - Record package version using universal tracking script (Standard)
Verify Status - Check all tracked packages for untracked changes using universal verification script (Standard)

Version Folders:

current_version/ - Contains DATA_EXPORTER v2.1.0 (backup from MARS-846 for rollback)
new_version/ - Contains DATA_EXPORTER v2.1.1 (updated with A_ETL_LOAD_SET_KEY fixes)

Execution

Connect to database as CT_MRDS user
Execute install_mars826_prehook.sql
Verify package compilation status
Test export functionality

Verification

-- Check package compilation status
SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_name = 'DATA_EXPORTER' 
  AND object_type = 'PACKAGE BODY';

-- Verify package version
SELECT CT_MRDS.DATA_EXPORTER.GET_VERSION() FROM DUAL;

-- Test export with new column reference
BEGIN
    CT_MRDS.DATA_EXPORTER.EXPORT_TABLE_DATA_BY_DATE(
        pSchemaName => 'OU_LM',
        pTableName => 'ADHOC_ADJ_HEADER',
        pKeyColumnName => 'A_ETL_LOAD_SET_KEY_FK',
        pBucketArea => 'ARCHIVE',
        pFolderName => 'TEST_EXPORT'
    );
END;
/

Rollback

Execute rollback_mars826_prehook.sql to restore previous package version.

Dependencies

Required by MARS-826

CRITICAL: This package must be deployed BEFORE MARS-826 execution. MARS-826 export scripts depend on this updated DATA_EXPORTER package.

Database Objects

Table: CT_ODS.A_LOAD_HISTORY (must have column A_ETL_LOAD_SET_KEY)
Package: CT_MRDS.ENV_MANAGER (for error handling and logging)
Package: CT_MRDS.FILE_MANAGER (for bucket URI resolution)

Configuration Changes

Bucket Configuration

Note: During testing, discovered bucket configuration issue:

Original: ArchiveBucketName = 'archive' (bucket did not exist)
Fixed: ArchiveBucketName = 'history' (correct bucket name)

This configuration fix is NOT part of this MARS package but was applied directly to database:

UPDATE CT_MRDS.A_FILE_MANAGER_CONFIG 
SET CONFIG_VARIABLE_VALUE = 'history' 
WHERE CONFIG_VARIABLE = 'ArchiveBucketName';
COMMIT;

Testing Results

Test Case 1: Single Table Export

Table: OU_LM.ADHOC_ADJ_HEADER
Result: ✅ SUCCESS
Output: 2 Parquet files created with Hive-style partitioning

LM_ADHOC_ADJUSTMENTS_HEADER/PARTITION_YEAR=2025/PARTITION_MONTH=08/202508_1_*.parquet (1,433 bytes)
LM_ADHOC_ADJUSTMENTS_HEADER/PARTITION_YEAR=2025/PARTITION_MONTH=09/202509_1_*.parquet (1,432 bytes)

Test Case 2: Package Compilation

Status: ✅ VALID
Compilation Time: < 1 second
Errors: None

Impact Analysis

Affected Procedures

EXPORT_TABLE_DATA_BY_DATE - Main export with Parquet partitioning
EXPORT_TABLE_DATA_TO_CSV_BY_DATE - CSV export with date filtering

Tables Using A_ETL_LOAD_SET_KEY_FK

All tables in MARS-826 export scope (19 tables):

OU_LM: 17 tables (ADHOC_ADJ_, BALANCESHEET_, CSM_ADJ_, FORECAST_, QR_ADJ_, STANDING_FACILITY, TTS_*)
OU_MRR: 2 tables (IND_CURRENT_ACCOUNT, IND_OVERNIGHT_DEPOSITS)

Backward Compatibility

⚠️ BREAKING CHANGE: This update is NOT backward compatible with databases where A_LOAD_HISTORY still uses A_WORKFLOW_HISTORY_KEY column name.

Files Included

README.md - This documentation file
.gitignore - Git exclusions (confluence/, log/, test/, mock_data/)
install_mars826_prehook.sql - Master installation script with SPOOL logging (5 steps)
rollback_mars826_prehook.sql - Master rollback script (2 steps)
00_MARS_826_PREHOOK_install_DATA_EXPORTER_SPEC.sql - Deploy updated package specification
01_MARS_826_PREHOOK_install_DATA_EXPORTER_BODY.sql - Deploy updated package body
02_MARS_826_PREHOOK_verify_package.sql - Verify package compilation and test export
track_package_versions.sql - Universal version tracking script (Standard)
verify_packages_version.sql - Universal package verification script (Standard)
91_MARS_826_PREHOOK_rollback_DATA_EXPORTER_BODY.sql - Restore previous package body
92_MARS_826_PREHOOK_rollback_DATA_EXPORTER_SPEC.sql - Restore previous package specification
current_version/ - Backup of DATA_EXPORTER v2.1.0 (from MARS-846)
new_version/ - Updated DATA_EXPORTER v2.1.1 (with A_ETL_LOAD_SET_KEY fixes)

Version History

v2.1.1 (2025-12-04): Fixed JOIN column reference A_WORKFLOW_HISTORY_KEY → A_ETL_LOAD_SET_KEY
v2.1.0 (2025-10-22): Added version tracking and PARTITION_YEAR/PARTITION_MONTH support
v2.0.0 (2025-10-01): Separated export functionality from FILE_MANAGER package

MARS-826: Export CSDB historical data to HIST bucket (requires this pre-hook)

Author

Created by: Grzegorz Michalski Date: 2025-12-04
Schema: CT_MRDS

README.md

MARS-826-PREHOOK: DATA_EXPORTER Package Update - Column Rename Support

Overview