JobCargaIndicesDiario

Daily Bloomberg Indices Load to Geneva

Date	27/01/2026
Author	Leandro Crespo
Version	1.0

Process Definition

It is a batch integration process that extracts exchange rate index information EUR/CLP and USD/CLP from a Bloomberg CSV file stored on an SMB server, queries the recording date from the Geneva database, inserts the records into a daily indices table in the Cerberus database, and generates an output CSV file in Geneva format for subsequent processing.

Scope

The process includes:

• Reading a Bloomberg CSV file with fixed name (dwBBG-BCentralindices.csv) from SMB server.
• Bloomberg format validation (START-OF-DATA / END-OF-DATA markers and discard ZIP/XLSX files).
• Query to Geneva database via JDBC to obtain the calculated recording date (previous business day, considering weekends).
• Parsing of Bloomberg file and extraction of specific instruments:
  • CLFXEEUR Index → EUR/CLP
  • CLFXDOOB Index → USD/CLP
• Data transformation: date conversion (format MM/dd/yyyy → yyyy-MM-dd) and mapping of instruments to investment codes (EUR, USD).
• Conditional insertion into INDICESDIARIOS table of Cerberus database (only if the record for that date and investment does not exist).
• Generation of output CSV file in Geneva format (without header, semicolon delimiter, fixed fields).
• Deposit of output file in specific folder on SMB server.
• Email notification of execution result.

Inputs

• SMB File: dwBBG-BCentralindices.csv located in {{SMB_IN_PATH}} on server {{SMB_HOST}}.
• Expected format: Bloomberg CSV with pipe-delimited structure (|) and START-OF-DATA / END-OF-DATA markers.
• Target instruments:
  • CLFXEEUR Index (EUR/CLP)
  • CLFXDOOB Index (USD/CLP)
• Geneva Database (JDBC): Query of recording date with previous business day logic.

Outputs

• INDICESDIARIOS Table (Cerberus Database):

  • Fields: date, Investment, Nav
  • Conditional insertion: only if no previous record exists for the date + Investment combination

• Geneva CSV File: IndiceDiario_<timestamp>.csv

  • Location: {{SMB_OUT_PATH}}
  • Format: CSV without header, semicolon delimiter (;)
  • Fields: LoaderType;LoaderAction;Investment;FechaCarga;ListCharge;Denomination;Price
  • Fixed values:
    • LoaderType: Price
    • LoaderAction: InsertUpdate
    • ListCharge: BL_DL_USDOBS_CLOSE
    • Denomination: CLP
  • Content: EUR and USD records with their respective prices

• Notification: Email via SMTP API with execution summary (success or error).

Process Flow in Sensedia

The flow is structured in the following stages:

1. Trigger: Quartz Scheduler executes the process Monday through Friday at 06:40 AM.

2. SMB Read: Connection to SMB server and reading of dwBBG-BCentralindices.csv file using pollEnrich with 20-second timeout.

3. File Validations:

   • Verification of existence (if not found, marks error, notifies and stops).
   • Conversion to UTF-8 String (handling of bytes, InputStream or String).
   • Validation of non-empty content.
   • File signature validation (discards ZIP/XLSX with “PK” signature).
   • Bloomberg marker validation (START-OF-DATA / END-OF-DATA).
   • 120-character preview for debugging.

4. Geneva Query - Recording Date:

   • Execution of SQL query that calculates the previous business day (if Monday, goes back 3 days; otherwise, goes back 1 day).
   • Output format: yyyy-MM-ddT00:00:00.

5. Timestamp Generation: Creation of timestamp for output file name (format yyyyMMdd_HHmmss).

6. Bloomberg Parsing:

   • Extraction of lines between START-OF-DATA and END-OF-DATA markers.
   • Split by pipe delimiter (|).
   • Extraction of fields: Instrument (position 0), Date (position 3), Price (position 4).
   • Date conversion from MM/dd/yyyy format to yyyy-MM-dd.

7. Data Enrichment:

   • Filtering of specific instruments: CLFXEEUR Index and CLFXDOOB Index.
   • Mapping to investment codes: EUR and USD respectively.
   • Assignment of Nav field with price value.

8. Insertion into Cerberus Database:

   • Generation of conditional SQL INSERT statements (with IF NOT EXISTS).
   • Execution via split (one statement per record) via JDBC ConexionCerberus2.
   • Value normalization: single quote escape, comma to period conversion in numbers.

9. Geneva CSV Generation:

   • Construction of CSV lines without header.
   • Format: LoaderType;LoaderAction;Investment;FechaCarga;ListCharge;Denomination;Price
   • Use of recording date obtained from Geneva.

10. SMB Write: Conversion of CSV to UTF-8 bytes and deposit in {{SMB_OUT_PATH}}.

11. Notification: Sending email with execution result (includes number of parsed lines and EUR/USD records processed).

12. REST Response: Generation of JSON with process status (without control characters).

Sensedia Configuration

• SMB Connector: Requires file server access configuration:
  • SMB_HOST, SMB_USERNAME, SMB_PASSWORD, SMB_DIR_NAME
  • SMB_IN_PATH (input folder)
  • SMB_OUT_PATH (output folder)
• Geneva JDBC Connector: ConexionGeneva with access for recording date query.
• Cerberus JDBC Connector: ConexionCerberus2 with write access to INDICESDIARIOS table.
• Notifications: NOTIFY_TO_EMAIL, PATRIA_NOTIFY_ENDPOINT.
• Input file: Fixed name dwBBG-BCentralindices.csv (hardcoded in flow).

Scheduling and Frequency

• Active configuration: Monday through Friday at 3:40 AM

Notifications

• OK: Email is sent with success summary indicating:
  • Execution ID
  • Name of processed file
  • Number of parsed lines from Bloomberg file
  • Number of EUR/USD records processed
• NOT OK: Email is sent with subject “ERROR in JobCargaIndicesDiario Processing” including:
  • Execution ID
  • File name
  • Specific error detail (file not found, invalid format, Geneva query failure, JDBC insertion failure, etc.)
• Attachments: No attachments are included in notifications.

Logs and Monitoring

The following events are logged:

• Execution start with exchangeId and requested file name.
• Attempt to read SMB file (full path, share and search pattern).
• pollEnrich result (file name, SMB path, body class).
• Format validations (file signature, 120-character preview, marker presence).
• FechaGrabar value obtained from Geneva.
• Number of parsed lines from Bloomberg file.
• Number of records after EUR/USD enrichment.
• Confirmation of output file write on SMB with full name.
• Notification start with process status.
• Captured errors with full stacktrace.
• Notification service response.

Failure Behavior

The flow uses doTry-doCatch blocks at critical stages:

• File not found: Marks status as “ERROR”, logs detail with full path, notifies and stops.
• Empty file: Marks status as “ERROR”, notifies indicating the file was read but is empty, and stops.
• Invalid format (ZIP/XLSX with “PK” signature): Marks error, notifies with specific detail and stops.
• No Bloomberg markers: Marks error, notifies indicating START-OF-DATA/END-OF-DATA not found and stops.
• SMB read failure: Captures exception, logs stacktrace, notifies with error message and stops.
• General processing failure: Captures exception in main block, marks status as “ERROR”, logs stacktrace and continues to notification.
• No EUR/USD data: If after enrichment there are no records, logs informative message but does not mark as error (continues without generating output file).
• JDBC insertion: Uses conditional IF NOT EXISTS statements to avoid duplicates; if an individual insertion fails, the split allows the others to continue.

Recovery Process

• Automatic recovery: As a daily scheduled process, the next execution will attempt to process again.
• Manual recovery: If the error is due to data or connectivity:
  1. Bloomberg file: Verify availability and format of dwBBG-BCentralindices.csv in SMB_IN_PATH. Validate that it contains CLFXEEUR Index and CLFXDOOB Index instruments.
  2. Geneva connectivity: Verify JDBC access to ConexionGeneva.
  3. Cerberus connectivity: Verify JDBC access to ConexionCerberus2 and write permissions on INDICESDIARIOS table.
  4. Duplicate data: The IF NOT EXISTS logic prevents duplicates; if reprocessing is required, the table records for the specific date must be manually deleted.
  5. Logs: Review detailed logs to identify the specific failure stage.
  6. Manual execution: Invoke the REST endpoint of the process if reprocessing is necessary.
• No automatic retry: The flow does not implement retries; each execution is independent.
• Partial idempotence: JDBC insertions are conditional (no duplicates), but the output file is regenerated with a new timestamp on each execution.

Contacts

• Technical Support: Sensedia Integrations / Middleware Team.
• Business Owner: Financial Operations / Indices and Currency Management Area.
• Data Provider: Bloomberg (source of input file).
• Geneva Administrator: Team responsible for Geneva database.
• Cerberus Administrator: Team responsible for Cerberus database and INDICESDIARIOS table.
• Target Systems: Teams responsible for consuming the output Geneva file.

Publishing Checklist

• Validate SMB server connectivity and access credentials from production environment.
• Verify existence and read permissions in the {{SMB_IN_PATH}} folder.
• Verify existence and write permissions in the {{SMB_OUT_PATH}} folder.
• Confirm that dwBBG-BCentralindices.csv file is deposited daily before 06:40 AM.
• Validate input file format (must contain Bloomberg markers and CLFXEEUR Index and CLFXDOOB Index instruments).
• Validate JDBC connectivity to ConexionGeneva from production environment.
• Validate JDBC connectivity to ConexionCerberus2 from production environment.
• Verify existence of INDICESDIARIOS table in Cerberus database with expected structure (fields: date, Investment, Nav).
• Validate insertion permissions on INDICESDIARIOS table.
• Configure notification environment variables (NOTIFY_TO_EMAIL, PATRIA_NOTIFY_ENDPOINT) with production values.
• Validate that the notification endpoint is accessible and functional.
• Adjust Cron schedule if necessary according to server timezone and data availability.
• Perform end-to-end test in staging environment with real file and updated databases.
• Document the recording date calculation logic (previous business day) for support teams.
• Validate that the IF NOT EXISTS logic works correctly to prevent duplicates.
• Establish monitoring alerts for consecutive failed executions.
• Document the mapping of Bloomberg instruments to investment codes (CLFXEEUR → EUR, CLFXDOOB → USD).
• Validate the format of the output Geneva file with consuming teams.
• Verify that the pollEnrich timeout (20 seconds) is sufficient for file reading in production.

Detailed diagram of the flow in Sensedia: