Data Lake Database

Data Lake Database is Socotra's hosted MariaDB instance — a relational representation of your book of business, made available for you to query directly with SQL.

This page covers enabling it, connecting to it, and replicating it into your own data infrastructure. For the data model itself — entity relationships, API mapping, data extensions — see Data Lake Data Model.

For a full reference of all Data Lake tables, see the Data Lake Table Reference.

Getting Started

Prerequisites

Socotra-provided credentials and connection details
IP registration with Socotra to allow connections from your IP
A MariaDB-compatible client, such as MySQL Workbench or DBeaver

Enablement

Data Lake is not enabled by default. Contact your Socotra representative to request onboarding details.

Once Data Lake is enabled for your business account, all tenants will automatically have their data flow through. However, in many cases this does not mean that your credentials will have access to data for these tenants.

Credentials have access provisioned on a per-schema level. For the default per-tenant schema option, credentials granted access to all tenants in the business account at the time of enablement will not automatically be granted access to schemas for future tenants added to the business account; this access must be explicitly requested. For the per-business account schema option, any credential with access to that schema will by definition have access to all tenants' data in the business account, without any additional provisioning required.

Schema Isolation Options

Data Lake is enabled at the Business Account level and by default will replicate data for all test and production tenants in the account.

Users may elect one of the following schema options upon enablement:

Per-Tenant Schema (default) - A separate schema is created for each tenant. While the schemas are identical, their data is unique to each tenant. Querying across tenants is supported via UNION statements.
Business Account Schema - A single, consolidated schema contains data for all tenants across the business account.

A tenant identification field (tenant_locator) on each table will enable users to distinguish the source of each record, regardless of which schema isolation option is selected.

Changing the selected schema isolation option after enablement will require a period of data inaccessibility.

For details on the data schema itself — table relationships, API entity mapping, data extensions — see the Data Lake Data Model guide. For conventions like locators, primary keys, and timestamps, see Schema Conventions in the Table Reference.

Update Frequency

Data Lake is continuously loaded with new data from the corresponding tenant, usually within minutes and for the majority of customers, at most 2 hours.

In scenarios involving extremely large volumes or complex data structures, such as bulk ingestion of data via Migration APIs or other large scale use cases, data loads may exceed 2 hours.

Connecting

Any MariaDB-compatible client works, including popular analytical tools:

Data Lake runs MariaDB version 11.4.x. You can check the specific version at any time with the SELECT VERSION(); query.

Data Replication

While Delta Files is the recommended, purpose-built path for replicating Data Lake data into your own infrastructure, you can also replicate directly from Data Lake using a data integration (ELT) tool such as Fivetran.

Data Lake does not expose binary logs (binlogs). Tools connecting directly to it must rely on alternative methods to detect changes — such as scanning the database or polling based on the datalake_updated_timestamp column — which can add load or complexity depending on the approach.

If replicating via Fivetran specifically, use its Teleport Sync method, which detects changes using a checksum-based approach, rather than its standard binlog-based CDC method.

Any other tool or method that can connect via provided ODBC/JDBC credentials, provide static IPs for whitelisting, and doesn't require binlog access may also be used to replicate via Data Lake.

Sample Queries

Converting Timestamps to a Local Time Zone

Data Lake datetimes are stored as MariaDB datetime(6), in the format YYYY-MM-DD HH:MM:SS, with the (6) indicating precision down to microseconds. For example: 2024-11-30 12:34:56.123456.

All datetimes are expressed in Coordinated Universal Time (UTC). Use the MariaDB CONVERT_TZ function to convert to a local time zone; for example, to list all policies effective on or after midnight Jan 1st 2024 PST:

SELECT * FROM `data_lake_my_tenant_locator`.`policies`
WHERE start_time_utc < CONVERT_TZ('2024-01-00 12:00:00', 'America/Los_Angeles', 'UTC');

Refer to the following MariaDB documentation for details.

Date and Time Data Types: https://mariadb.com/docs/skysql-dbaas/ref/xpand/functions/CONVERT_TZ/
Date and Time Functions: https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types