File Format
Delimiters
All files contain the following delimiters:
Column: pipe ( | )
Row: carriage return and line feed
Header Row
The first row in all files is a header row. This contains the name of each column, separated by a column delimiter.
Metadata Columns
Change Bit
The first column in all incremental feed files is the Change Bit. This denotes whether the row record is:
A newly created record: change bit C
An updated record: change bit U
A deleted record: change bit D
When a row record has the change bit D, all other columns, except the Primary Key ID, for the record will be null.
The Change Bit is only present in incremental feed files. It does not appear in full feed files.
Deleted records do not appear in full files. If you intend to load from both our full and incremental files, it is recommended to process any outstanding incremental files before processing the full files. This will ensure any deletions are applied first.
Record Updated Timestamp
The second column in all incremental feed files is the Record Updated Timestamp. This indicates the datetime when the row record was last updated.
The format is yyyy-mm-ddThh:mm:ss.mss+0000, where mss is a three-digit millisecond and +0000 represents the UTC time zone. Below is an example:
2023-02-08T16:52:52.062+0000
The Record Updated Timestamp is only present in incremental feed files. It does not appear in full feed files.
Data Types
In addition to the above metadata fields, the data fields within a feed file can be one of the following types.
Boolean
All boolean values are represented as a string literal true or false.
Dates
All dates in the feed files are represented as ISO date strings, in the format yyyy-mm-dd as shown below:
2023-01-01
Dates can be partial, with values to the right of yyyy omitted.
Year only: yyyy – (e.g. 2000)
Year and month: yyyy-mm (e.g. 2000-01)
Strings
We recommend storing string fields with a varchar(max) or text type in SQL Server 2022 and varchar with no length specified, or text type in PostgreSQL.