Data Lake Delta Files

Delta Files are Socotra's purpose-built mechanism for replicating Data Lake data into your own infrastructure — the recommended path for production replication (see Reporting Overview).

Appropriately permissioned API clients can list and retrieve a series of incremental diff files via a pair of endpoints optimized for programmatic consumption.

Getting Started

Delta files are enabled on a business account level. Once enabled for a business account, delta files will be generated for all existing and future tenants and will be automatically available via API.

Delta files are not enabled by default. Contact your Socotra representative and request to have delta files enabled for your business account, providing the name of the business account and the delta file format that you prefer (sql or csv).

Overview

Consuming the Socotra Data Lake Delta file API involves a recursive, two-step process:

1. Get an index of available files for a given table.
2. Retrieve the necessary individual files.

The delta files are provided in sql or csv format, containing all requisite upsert statements (for sql) or updated records (for csv) to replicate table records in the correct format and order.

Delta files in csv format follow RFC:4180 formatting, with comma delimiters and standard quote escaping for fields containing special characters, and null values are represented as empty fields.

In order to ensure a complete and accurate replication, all delta files for a given table's latest schema version must be consumed, and in the order in which they are presented within the index.

While each delta file enumerated in the index array will include metadata related to the generation of that file's contents, it is not recommended to rely on that metadata to derive the correct order of consumption. The system handles and guarantees this via the ordering of the files in the index array.

Update Frequency

Delta files are generated at most once every two hours following Data Lake updates. If an update occurs within two hours of the previous delta file generation, the system will generate the next set once the interval has elapsed. This two-hour interval is configurable by environment. If no data changes were processed since the last set of files was generated, no new files are generated for that interval — the next set is generated as soon as the next change is processed.

Data changes are themselves processed usually within minutes, and for the majority of customers, at most 2 hours (see Data Lake Database Update Frequency for the same underlying processing step). Combining both steps, changes to underlying data typically appear in a delta file within two hours, but not more than four. Review each file's dataProcessedThroughTime (described below) to understand exactly which underlying data changes it reflects.

Data Availability

Files are generated in batches. For each batch, the system first selects a single dataProcessedThroughTime, then updates every table, with the exception of Moratoriums tables, through that same point in time.

Delta files do not become available as each table finishes processing. Instead, the system waits until processing has completed for all non-moratorium tables in the batch, and only then publishes the files. As a result, all non-moratorium tables published in the same batch share the same dataProcessedThroughTime.

Moratoriums are updated together as their own group and may complete later than the other tables. As a result, they should not be expected to share the same dataProcessedThroughTime as the other tables.

The dataProcessedThroughTime represents the latest time of operational data changes included in that batch for the table.

If retrieving data for all tables except moratoriums, pass the dataProcessedThroughTime in the request to ensure that data is consistent across all tables. This prevents a scenario where some tables have newer data than others if a new batch is published while you are still fetching files.

For example, passing lastFile along with dataProcessedThroughTime in the Fetch List of Delta Files API request returns all files generated since the last file and up through that processed time.

File Size

The maximum size of a delta file is 10,000 statements (for sql) or 10,000 rows (for csv).

Schema Versioning

Since the schema of any source Data Lake table may evolve over time, each table consumed via the Delta File API has a corresponding version number. The version number is a sequentially incrementing integer.

When a source table schema update occurs, a new schema version is automatically made available in the Delta File API, and all historical data is regenerated into the newest version. Historical versions will remain available, but updated delta files will not be generated for it.

For each table's schema version, files containing the requisite drop and create statements are also provided.

On first consumption of the Delta File API, the create statement will be needed. The drop and create files will be used in sequence when a new table schema version becomes available.

Pagination

The number of files available for a specific table and schema version may vary based on data volume and growth rate. The Fetch List of Delta Files API Response is limited to 100 Delta files per request. If more than 100 files exist, the consuming client must paginate through results.

To ensure complete indexing, use the lastFile parameter in the Fetch Delta Files to continue retrieving additional files beyond the initial response.

Client Example

A sample client implementation, illustrating how to consume the API programmatically, is available upon request.

Data Replication

Replicating Data Lake data via Delta Files follows the same general pattern regardless of your destination, and is deliberately tool-agnostic — it works with whatever cloud storage, warehouse, lake, or destination you already use.

One common pattern for exporting delta files is as follows:

1. Files are exported from the Delta Files API and landed into your own staging area.
2. Files are ingested from that staging area into staging tables in your destination.
3. Staging tables are merged/upserted into your final Data Lake tables.

The platform generates files on a two-hour cadence, but you control how frequently you process them; for example, you may set up a daily process to extract and ingest files.

Example: Databricks or Snowflake

If replicating files into Databricks or Snowflake, Databricks' Autoloader or Snowflake's Snowpipe can pick up files from your cloud storage staging area automatically and load them into staging tables, ready to be merged/upserted into final Data Lake tables.

Delta Files APIs

Fetch Delta Files API

Clients can retrieve an index of the available files for a particular Data Lake table using the Fetch List of Delta Files endpoint.

Sample DeltaFilesGetRequest

{
	// required
	"tenantLocator": "b6f8aa30-b978-4934-bef3-627XXXXXXXXX",
	"transformationTable": "DataLakeInvoices",

	// optional
	"deltaFileType": "csv",
	"version": 0,
	"dataProcessedThroughTime": "1734542240",

	// optional, mutually exclusive
	// "startTime":1734542240221,
	"lastFile": "DataLakeInvoices/version_0/2025/March/b6f8aa30-b978-4934-bef3-627b0e6edd88_DataLakeInvoices_1451606100_1741713134734.csv"
}

Sample DeltaFilesGetResponse

{
	"version": 0,
	"createTableFile": "DataLakeInvoices/version_0/createTable.sql",
	"dropTableFile": "DataLakeInvoices/version_0/dropTable.sql",
	"s3Bucket": "socotra-kernel-develop-dm-delta",
	"deltaFiles": [
		{
			"deltaFileType": "csv",
			"fileName": "DataLakeInvoices/version_0/2025/March/b6f8aa30-b978-4934-bef3-627b0e6edd88_DataLakeInvoices_1451606100_1741713134934.csv",
			"jobStartTime": 1451606100,
			"jobEndTime": 1741582882,
			"generationTime": 1741713134934,
			"recordCount": 1000,
			"md5HashSum": "a1b2c3d4e5f67890abcdef1234567890"
		},
		{
			"deltaFileType": "csv",
			"fileName": "DataLakeInvoices/version_0/2025/March/b6f8aa30-b978-4934-bef3-627b0e6edd88_DataLakeInvoices_1451606100_1741713135163.csv",
			"jobStartTime": 1451606100,
			"jobEndTime": 1741582882,
			"generationTime": 1741713135163,
			"recordCount": 1000,
			"md5HashSum": "8d4a2f9c1e7b3d5a0f6c8e2b4a9d1f7c"
		},
		{
			"deltaFileType": "csv",
			"fileName": "DataLakeInvoices/version_0/2025/March/b6f8aa30-b978-4934-bef3-627b0e6edd88_DataLakeInvoices_1451606100_1741713135490.csv",
			"jobStartTime": 1451606100,
			"jobEndTime": 1741582882,
			"generationTime": 1741713135490,
			"recordCount": 198,
			"md5HashSum": "e3b0c44298fc1c149afbf4c8996fb924"
		}
	]
}

Fetch Delta File API

Clients can download each individual Delta File using the Fetch Specific Delta File endpoint. The response will be a streamed file StreamingResponseBody<string>.

See Also

• Reporting Overview
• Data Lake Data Model
• Data Lake Database
• Data Lake Delta File API Guide