sftp-package 1.0.0
A reusable SFTP client package for Python
Requires Python: >=3.7



## Hands-On Documentation: Airflow SPS Data Loader

### Interface Functionality
The interface is designed to detect differences in the input files compared to previously received files. It identifies the following changes:

1. **New Records**: Records that are received for the first time (labeled as "NEW").
2. **Unchanged Records**: Records that are received again without any changes (labeled as "UNCHANGED").
3. **Changed Records**: Records that are received again but with changes (labeled as "CHANGED").
4. **Deleted Records**: Records that were previously received but are no longer present in the input (labeled as "DELETED").
5. **Reactivated Records**: Records that were previously deleted but received again in the most recent files.

### SFTP Configuration for Testing

- **Server**: `ftp.uat.braskem.mytower.fr`
- **Username**: `braskem-tps`
- **Key File**: Attached

Files are structured as follows:

### File Types

1. **Type 1**: 
   - Example: `BRASKEM_RPS_BRIDGE_SANCTIONS_SCREENING_20240516_020001.csv`
   - Description: Extract of all entities except US. A full extract is provided daily.

2. **Type 2**: 
   - Example: `BRASKEM_RPS_BRIDGE_SANCTIONS_SCREENING_USMASTER_20240516_023000.csv`
   - Description: Extract of all US entities. A full extract is provided daily.

3. **Type 3**: 
   - Example: `BRASKEM_RPS_BRIDGE_SANCTIONS_SCREENING_ORDERS_20240519_233002.csv`
   - Description: Extract of entity orders from the last X days.

For data from each file type, a field in GTM needs to be populated to differentiate the data source and potentially map against different segments.

### File Loading Process
The interface validates and loads the CSV data through the following steps:

1. **File Name Recognition**: The file name must be recognized; otherwise, the file will be skipped.
2. **Column Name Validation**: The file column names must match the expected column names.
3. **Duplicate Handling**: If duplicates are found, only the first line is loaded, and the rest are skipped.
4. **Value Validation**: If there are missing or extra values in a row, at least the `Account Number`, `Country`, and `Full Name` must be present to load the row.

### Error Reporting
There are three types of errors reported in the error file:

1. **NOT ENOUGH COLUMN**: When there are fewer columns than expected.
2. **TOO MANY COLUMN**: When there are more columns than expected.
3. **DUPLICATE ID**: When duplicate IDs are found.

### Airflow SPS Data Loader

**Project Description**: This project orchestrates and processes files received from Braskem via an SFTP server.

#### Workflow
1. **Download Files**: Downloads files matching a specified pattern from the SFTP server.
2. **Process Files**:
   - Loads each file into the `raw_data` database table.
   - Updates a reference table (`raw_data_ref`) to track the status of each record.
   - Sends processed data to GTM.
3. **Move Files**: Moves successfully processed files to a "success" folder on the SFTP server and failed files to an "error" folder.

#### Dependencies
- **CronJob**: Scheduled to run every day at 09:00 UTC.
- **lib.sftp_helper**: Library for SFTP operations.
- **lib.data_loader.file_loader**: Library for CSV file loading and validation.
- **lib.data_loader.delta_ref**: Library for updating the reference table.
- **lib.data_sender.send_to_gtm**: Library for sending data to GTM.

### Database Structure

The database schema for this project is `sps_interface`, and the database server is `postgress-airflow`.

#### `raw_data` Table

The `raw_data` table stores the raw data loaded from the input files. Here are its main fields:

- **ROW_ID**: Primary key, auto-incremented.
- **TID**: Transaction ID.
- **Account_Number**: Account number.
- **Country**: Country.
- **Full_Name**: Full name.
- **City**: City.
- **District**: District.
- **Postal_Code**: Postal code.
- **State**: State.
- **Address_Line**: Address line.
- **Account_Type**: Account type.
- **Tax_ID**: Tax ID.
- **FILE_NAME**: Name of the file from which the record was loaded.
- **SOURCE**: Source of the data.
- **LOAD_DATE**: Date the data was loaded.
- **MD5_HASH**: MD5 hash of the record for change detection.

#### `raw_data_ref` Table

The `raw_data_ref` table tracks the status of each record and helps in identifying changes, new records, deletions, etc. Here are its main fields:

- **ROW_ID**: Primary key.
- **TID**: Transaction ID.
- **Account_Number**: Account number.
- **Country**: Country.
- **Full_Name**: Full name.
- **City**: City.
- **District**: District.
- **Postal_Code**: Postal code.
- **State**: State.
- **Address_Line**: Address line.
- **Account_Type**: Account type.
- **Tax_ID**: Tax ID.
- **FILE_NAME**: Name of the file from which the record was loaded.
- **SOURCE**: Source of the data.
- **LOAD_DATE**: Date the data was loaded.
- **MD5_HASH**: MD5 hash of the record for change detection.
- **STATUS**: Current status of the record (e.g., NEW, UNCHANGED, CHANGED, DELETED).
- **INTEGRATION_STATUS**: Status of the integration process.

### GTM Data Send API

To map and send data to the GTM API, the following methods and authentication process are used:

#### Data Mapping

The data received from the input files is mapped to the GTM API as follows:

- **New Records**: Use the `POST` method to send new records.
- **Changed Records**: Use the `PATCH` method to update existing records.

#### Authentication

The GTM API uses Keycloak SSO for authentication. Ensure you have the necessary credentials and tokens for accessing the API.

####  API Workflow

1. **Authentication**: Obtain an access token from Keycloak SSO.
2. **POST New Records**: For each new record detected, send a `POST` request to the GTM API.
3. **PATCH Changed Records**: For each changed record detected, send a `PATCH` request to the GTM API.