SFTP Integrations

SFTP (Secure File Transfer Protocol) is a network protocol used to securely transfer files over a network. This protocol uses encryption techniques to protect the confidentiality and integrity of the data during the transfer.

Execution of SFTP

The way this protocol is executed in the data integration process is as follows:

  1. The file is detected and placed into the processing queue, with the client identifier appended.

  2. When the file reaches its turn for processing, all lines are read and the format is validated.

  3. Records containing invalid values (incorrect identifiers, wrong date formats, negative values, etc.) are filtered out, and any unprocessable lines are ignored. For all remaining values processed as valid:

    • The current records are deleted.

    • The new records are inserted.

In general, the process does not stop due to the number of validation errors, but only for issues that may endanger the system, such as timeouts (database overload) or insertion errors. This saving process is asynchronous, meaning that multiple values are processed at the same time.

Measures integration

Once the SFTP directory for the client has been created, and after they have received all the necessary connection information (credentials, host, etc.), they can upload files at the agreed frequency. The client then places the CSV files in their SFTP directory so they can be processed as described in the previous section.

According to the default configuration, the following headers must be specified:

  • STORE: external service identifier.

  • DATE: date, by default in yyyyMMdd format.

  • TIME: local time, by default in HHmm format.

  • ZONE: external product or section identifier.

  • The different types of measures configured in the business: the name must exactly match the one configured in Orquest.

Below is an example:

STORE DATE TIME ZONE measure1 measure2 measure3

serviceId

20241124

0000

productId

0.77

2

1.5

serviceId

20241124

0015

productId

6.66

1

6.4

serviceId

20241124

0030

productId

7.93

7

1

serviceId

20241124

0045

productId

5.87

10

1.4

serviceId

20241124

0100

productId

8.09

6

8.7

The format of these columns may vary depending on the business configuration. For example, a different date format could be configured: yyyy-MM-dd.

Thus, the files containing real measures are securely sent to Orquest’s SFTP server, from where they are processed to integrate the data into the system.

Protocol for uploading historical data

To avoid monopolizing the processing queue, the recommended flow for uploading files with historical data is 100 files every 30 minutes. However, it is essential to consult the Orquest team before performing this type of upload, as the protocol may vary depending on the volume of data that needs to be handled.

If these recommendations are not followed and inappropriate use of the tool is detected, the Orquest team may temporarily ban the user to ensure the proper functioning of the system.

Considerations

  • Only the data for the types of measures present in the file are updated. Therefore, if there were previously loaded data for measure types that are not included in the file, those data will remain unchanged in the system.

  • The correct processing of a file does not imply the insertion of data into the system: if all lines contain errors and are ignored, the file is processed successfully, but there are no valid data to insert.

  • File processing time will depend on the number of elements in the queue: it will take longer if it coincides with another upload or if many files are loaded at the same time.

  • There are different SFTP templates for each business: they are generated based on the parameters configured by the business.

  • It is recommended not to send files with the same name, in order to better monitor data processing.

Common errors

Below are some of the most common errors in file processing via SFTP.

SFTP Error Description

encoding_possibly_wrong

The processor detects that the file is not encoded as UTF-8.

invalid_date_format

Error in date and time format.

product_not_exist

The product identifier does not match any product configured in Orquest.

product_for_service_not_exist

The service or product identifier does not match any configuration defined in Orquest.

invalid_value

The file contains invalid values, such as negative values.

demandtype_not_exist

The type of measure does not exist in the business.