Knowledge MCP Service

# Business Rules — TransferCustomerData

Source: `Customer_*.csv` (encoding UTF-8)

## Overview
- Job: TransferCustomerData — read customer CSV(s), transform fields, validate, upsert into `STG_CUSTOMER`.
- Reader must support CSV; files matched by `/box/input/Customer_*.csv`.

## Field Mapping

- Source column `CustID` -> Target column `customer_id`
  - Type: String
  - Usage: Primary key for upsert keys.

- Source column `FullName` -> Target column `customer_name`
  - Transform: Trim whitespace
  - Validation: Not null / not empty
  - Error Message: "Name cannot be empty"

- Source column `BirthDate` -> Target column `dob`
  - Transform: Parse date from input format `yyyyMMdd` and output as `yyyy-MM-dd` (ISO date string)
  - If parse fails -> mark record invalid with message `Invalid BirthDate format, expected yyyyMMdd`.

- Source column `Balance` -> Target column `is_vip`
  - Type: Decimal -> derive integer flag
  - Business rule: if `Balance > 10000` then `is_vip = 1` else `is_vip = 0`
  - If `Balance` missing or non-numeric -> treat as `0` but record a validation note `Balance not numeric, defaulted to 0` (decide whether to treat as error based on downstream requirements).

## Validation Rules (ItemProcessor responsibilities)

- `customer_name` must be non-null and non-empty after trimming.
  - On failure: set `Error_Detail` = `Name cannot be empty` and route record to error CSV.

- `dob` must be a valid date parsed from `yyyyMMdd`.
  - On failure: append `Invalid BirthDate format, expected yyyyMMdd` to `Error_Detail`.

- `CustID` must be present (used as upsert key).
  - On missing: append `CustID is missing` to `Error_Detail` and mark invalid.

- `Balance` should be numeric. If not numeric, processor may coerce to 0 and append `Balance not numeric, defaulted to 0` to `Error_Detail` (policy choice: non-fatal warning vs error). Follow project standard: collect errors but do not throw to stop the job.

## Processor Output (Wrapper)
- For each input record, the `ItemProcessor` must return a wrapper object containing:
  1. `originalData` — raw input fields
  2. `entity` — validated/normalized target fields (`customer_id`, `customer_name`, `dob`, `is_vip`, plus any audit fields)
  3. `errorDetail` — aggregated error string (empty/null if valid)

## Writer Behavior
- If `errorDetail` is empty: route to MyBatis writer that calls a T-SQL Stored Procedure to perform an upsert into `STG_CUSTOMER` using TVP when possible.
- If `errorDetail` is non-empty: route to `FlatFileItemWriter` producing `[original_filename]_error_[timestamp].csv` containing all original columns plus `Error_Detail` column.

## Job Post-Execution File Movement
- If job COMPLETED and no error records: move source file to `/success`.
- If job FAILED or any error records exist: move source file and the generated error CSV to `/error`.

## Edge Cases & Notes
- Timezone/locale: date parsing assumes input is in local timezone; store `dob` as date-only (yyyy-MM-dd).
- Numeric decimal separators: expect dot `.`; if comma present, normalize before parsing if policy allows.
- Upsert semantics: use `CustID` -> `customer_id` as key; stored procedure should implement efficient set-based upsert via TVP.

## Example Error Messages
- `Name cannot be empty`
- `Invalid BirthDate format, expected yyyyMMdd`
- `CustID is missing`
- `Balance not numeric, defaulted to 0`

---
Generated by Lead Analyst from `/Users/dung/Documents/workspace/agents-elt/asteria/Asteria_Mock_Flow.xml` per project instructions in `.github/`.