Delta Lake

​

Setting up AWS S3 Delta Lake

​

Prerequisites

• By default, S3 authentication uses role-based access. You will need the trust policy prepopulated with our identifier to grant access. It should look similar to the following JSON object with a proper service account identifier:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "sts:AssumeRoleWithWebIdentity"
      ],
      "Principal": {
        "Federated": "accounts.google.com"
      },
      "Condition": {
        "StringEquals": {
          "accounts.google.com:oaud": "<some_organization_identifier>",
          "accounts.google.com:sub": "<some_service_account_identifier>"
        }
      }
    }
  ]
}

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::BUCKET_NAME"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:DeleteObject"
      ],
      "Resource": "arn:aws:s3:::BUCKET_NAME/*"
    }
  ]
}

​

Setting up Google Cloud Delta Lake

​

Prerequisites

• By default, GCS authentication uses role-based access. You will need our service account name available to grant access. It should look like some-name@some-project.iam.gserviceaccount.com.

1

Create a service account

1. In the GCP console, navigate to the IAM & Admin menu, click into the Service Accounts tab, and click Create service account at the top of the menu.
2. In the first step, name the service account that will be used to transfer data into Cloud Storage and click Create and Continue. Click Continue in the following optional step without assigning any roles.
3. In the Grant users access to this service account step, within the Service account users role field, enter the provided Service account (see prerequisite) and click Done.
4. Once successfully created, search for the created service account in the service accounts list, click the Service account name to view the details, and make a note of the email (note: this is a different email than the service’s service account).
5. Grant access using one of the following authentication methods:

• Service account impersonation (recommended)
• Service account key

Select the permissions tab, find the provided principal name (Service account from the prerequisite), click the Edit principal button (pencil icon), click Add another role, select the Service Account Token Creator role, and click Save.

Role based authentication is the preferred authentication mode for GCS based on GCP recommendations, however, providing a service account key to directly log-in to the created service account is an alternative authentication method that can be used if preferred.

1. Back in the Service accounts menu, click the Actions dropdown next to the newly created service account and click Manage keys.

2. Click Add key and then Create new key.

3. Select the JSON Key type and click Create and make note of the key that is generated.

2

Create destination GCS bucket

1. Navigate to the Cloud Storage page.
2. Click Create.
3. Enter a bucket name, choose a region. Note: at the Choose how to control access to objects step, we recommend selecting Enforce public access prevention on this bucket.

4. After choosing your preferences for the remaining steps, click Create.
5. On the Bucket details page for the bucket you created, select the Permissions tab, and click Grant access.
6. Grant access to the principal (Service Account) you created in Step 1 (Note: this is the service account you created, not the service account from the prerequisite), and assign the Roles: Storage Legacy Bucket Writer, Storage Legacy Bucket Reader, Storage Legacy Object Reader. Click Save.

3

Add your destination

Use the following details to complete the connection setup: bucket name, your chosen folder name for the data, and your Service account email.

​

Setting up Azure Delta Lake

1

Create Azure storage account

1. In the Azure portal, navigate to the Storage accounts service and click + Create.
2. In the “Basics” tab of the “Create a storage account” form, fill in the required details.
3. In the “Advanced” settings, under “Security” make sure Enable storage account key access is turned on. You may turn off (deselect) “Allow enabling public access on containers”. Under “Data Lake Storage Gen2”, select Enable hierarchical namespace.

4. In the “Networking” settings, you may limit “Network access” to either Enable public access from all networks or Enable public access from selected virtual networks and IP addresses. If the latter is selected, be sure to add the service’s static IP to the address range of the chosen virtual network. All other settings can use the default selections.
5. In the “Data protection” settings, you must turn off Enable soft delete for blobs, Enable soft delete for containers, and Enable soft delete for file shares.

6. Once the remaining options have been configured to your preference, click Create.

2

Create container and access token

1. In the Azure portal, navigate to the Storage accounts service and click on the account that was created in the previous step.
2. In the navigation pane, under “Data storage”, click Containers. Click + Container, choose a name for the container, and click Create.
3. In the navigation pane, under “Security + networking”, click Access keys.
4. Make a note of the Key that is generated for either key1 or key2.

3

Add your destination

Use the following details to complete the connection setup: storage account name, container name, your chosen folder name for the data, and your Access key.

​

Understanding Delta Lake configuration options

​

Protocol compatibility summary

​

retention_window_days

​

column_mapping_mode

​

deletion_vectors_disabled

• Deletion Vectors enable the merge-on-read approach, where modifications (like deletes) are applied by marking rows as deleted without rewriting the underlying Parquet files.
• The traditional copy-on-write approach rewrites entire files for each change, which can be slower for small modifications.

• Enable deletion vectors (i.e., set deletion_vectors_disabled to false) to leverage merge-on-read performance benefits.
• Disable deletion vectors if you must support a lower minimum reader version.

​

change_data_feed_disabled

• Keep change data feed enabled (i.e., set change_data_feed_disabled to false) by default.
• Disable change data feed only if you need to support a lower minimum reader version.

​

FAQ

DESCRIBE HISTORY table_name LIMIT 1;

​

Mounting AWS S3 Delta Lake to Athena

1. In the AWS console, navigate to the Athena query editor.
2. Choose the same region as your configured bucket.
3. Execute the following SQL:

CREATE EXTERNAL TABLE IF NOT EXISTS schema.table_name.=
   LOCATION 's3://bucket-name/<configured_path>/<table_name>'
   TBLPROPERTIES ('table_type' = DELTA);

​

Mounting GCS Delta Lake to BigQuery

1. In the Google Cloud console, navigate to the BigQuery Console.
2. Click the + Add Data button at the top left of the console.
3. Select Google Cloud Storage as a Data Source
4. Select GCS: (Manual) BigLake External & External Tables: BigQuery option
5. Set the file format to Delta, then write provide the path expression '<bucket-name>/<configured_path>/<table_name>'
6. Choose a Dataset in the same region as the bucket and a table name.
7. Choose Table Type External Table.
8. Create your external table and query data to test.

​

Mounting AWS S3 Delta Lake to ClickHouse

1. Open a Clickhouse SQL session.
2. Execute the following SQL:

CREATE TABLE schema.table_name AS
ENGINE = DeltaLake(
   's3://bucket-name/<configured_path>/<table_name>',
   '<AWS_ACCESS_ID>',
   '<AWS_SECRET_KEY>'
);

​

Mounting AWS S3 Delta Lake to DuckDB/MotherDuck

1. Install the DuckDB Delta extension.
2. Open a DuckDB SQL session.
3. Execute the following SQL:

CREATE VIEW schema.table_name AS
SELECT
   *
FROM deltaLake(
   's3://bucket-name/<configured_path>/<table_name>',
   '<AWS_ACCESS_ID>',
   '<AWS_SECRET_KEY>'
);

​

Mounting S3 Delta Lake to Databricks (Unity)

1. Ensure your Databricks session has read access to the configured bucket.
2. Open a Databricks SQL session.
3. Execute the following SQL:

CREATE VIEW schema.table_name AS
SELECT
   *
FROM delta.`s3a://bucket-name/metastore/<configured_path>/<table_name>`
);

​

Mounting S3 Delta Lake to Redshift

1. On the AWS Glue console, choose Crawlers in the navigation pane.
2. Choose Create crawler.
3. For Name, enter whatever, and choose “Next”.
4. For Data source configuration, choose “Not yet”.
5. For Data source, choose Add a data source.
6. For Data source, select Delta Lake.
7. For Include delta lake table paths, enter s3://bucket-name/<configured_path>/<table_name>
8. Select Enable write manifest, then choose Add a Delta Lake data source. Choose Next.
9. For IAM role, either select an existing role or create one with permissions to Glue and the bucket
10. For Target database, choose Add database, then Create a database page is shown.
11. For Name, enter whatever, then choose Create database. Then come back to the previous page. For Target database, click the reload button, and select the created database.
12. For Frequency under Crawler schedule, choose double the frequency data is delivered. For example for data arriving on hourly minute frequency, choose 30 minutes.
13. Review your configuration, and choose Create crawler. You can trigger the crawler to run manually via the AWS Glue console, or through the SDK or AWS CLI using the StartCrawl API. You could also schedule a trigger via the AWS Glue console.
14. Wait for the crawler to complete.
15. Navigate to redshift and inspect the awsdatacatalog to find the newly created table

​

Reading S3 Delta Lake with PySpark

1. In your PySpark code, run the following to instantiate a dataframe backed by your delta table:

df = spark.read.format("delta").load("s3://bucket-name/<configured_path>/<table_name>")

​

Mounting S3 Delta Lake to Snowflake

1. Open a Snowflake client.
2. Execute the following SQL, choose your frequency based on desired data freshness:

CREATE OR REPLACE CATALOG INTEGRATION delta_catalog_name
  CATALOG_SOURCE = OBJECT_STORE
  TABLE_FORMAT = DELTA
  ENABLED = TRUE;

CREATE OR REPLACE EXTERNAL VOLUME ext_volume_name
  STORAGE_LOCATIONS = (
    (
      NAME = 'some_name'
      STORAGE_PROVIDER = S3
      ...
    )
  )
  ALLOW_WRITES = FALSE;


CREATE ICEBERG TABLE schema.table_name
  CATALOG = delta_catalog_name
  EXTERNAL_VOLUME = ext_volume_name
  BASE_LOCATION = '<configured_path>/<table_name>';

CREATE OR REPLACE TASK task_name
  SCHEDULE = 'USING CRON 0/5 * * * * America/Los_Angeles' -- Runs every 5 minutes, change cron to match desired freshness
  ...
AS
   ALTER ICEBERG TABLE schema.table_name REFRESH;

​

Mounting Delta Lake to Microsoft Fabric & Microsoft OneLake

1. Navigate to the Microsoft Fabric
2. Navigate to your lakehouse, if you do not have one, create one.
3. In your lakehouse, select the ellipses (…) next to Tables and then select “New shortcut”.

4. In the New shortcut screen, select your bucket provider. Normally this will be Azure Data Lake Storage Gen2 tile.
5. Connect to your bucket and select the path of the delta lake table '<configured_path>'/<table_name>'

6. After creating the shortcut, the shortcut should appears as a Delta table under Tables. It may appear as “Unidentified”, this is a UI bug in Azure.
7. To confirm the table is correctly mounted, select “SQL Analytics endpoint” from the drop down to the left of the “share” button, in the top right of the console
8. The shortcut should correctly appear as a Delta table under Tables.
9. [optional] Click the “New semantic model” button at the top of the page to setup the mounted table for use in Microsoft products like PowerBI.