Update S3 Paths in ingest_config_master1 Based on Location and Source System

Introduction: Ingestion pipeline relies on the "ingest_config_master" table to determine source file locations. However, the existing s3_vendor_path and s3_location_path values need to be dynamically updated based on specific conditions related to location and source_system. This update ensures that files are correctly read from and written to the appropriate S3 paths, preventing incorrect data ingestion and processing errors.

 

Requirements: Read the "purgo_playground.ingest_config_master1" table. Update the ingest_config_master1 table by apply conditional replacements for S3 paths based on the following rules and other records remain unchanged.

 

1. If location is "Global":

#* Update s3_vendor_path to "s3://vendor/global"

#* Update s3_landing_path to "s3://landing/global"

1. If source_system is "SystemA":

#* Update s3_vendor_path to "s3://vendor/systema_US"

#* Update s3_landing_path to "s3://landing/systema_US"

 

Final Output: Show the result.

 

Prerequisite:

 

1. Drop the table ingest_config_master1_clone if exist.
2. Create replica of ingest_config_master1 table in ingest_config_master1_clone table and perform the requirement in the replica table.

 

Unity Catalog: “purgo_playground.ingest_config_master1“