Aki for AWS Community Builders

Posted on Jul 21

Connecting Amazon S3 Tables with Snowflake

#aws #snowflake #dataengineering

Original Japanese article: Amazon S3 TablesとSnowflakeを連携する

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

In a previous article, I introduced how to connect an Iceberg table built on a general purpose S3 bucket with Snowflake.

AWS Snowflake Lakehouse: 2 Practical Apache Iceberg Integration Patterns

This time, as a follow up, I'm going to read and write an Iceberg table on S3 Tables from Snowflake, since I've had more chances to use S3 Tables recently.

There were a few points that tripped me up along the way, so I put this together along with some personal reflections. Hope it's useful!

Test Environment

I'm reusing the same resources as in the previous article.

Verifying How IAM and Lake Formation Behave for the Glue REST Catalog and S3 Tables

Table bucket: penguin-rest-test
Namespace: analytics
Table: daily_sales (two columns, sales_date and amount)
Glue integration: enabled (mounted as s3tablescatalog/penguin-rest-test)

Read the account ID as 123456789012 throughout.

Reading S3 Tables Data from Snowflake

Creating the IAM Role

I'll create a role that lets Snowflake access S3 Tables through the Glue Iceberg REST endpoint.

I'm reusing the IAM actions from the previous article that were required for access through the Glue endpoint (s3tables:*, lakeformation:GetDataAccess, and the glue:GetCatalog family), and defining them as the policy for the catalog integration role.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3TablesAccess",
            "Effect": "Allow",
            "Action": [
                "s3tables:ListTableBuckets",
                "s3tables:GetTableBucket",
                "s3tables:GetNamespace",
                "s3tables:ListNamespaces",
                "s3tables:GetTable",
                "s3tables:ListTables",
                "s3tables:GetTableMetadataLocation",
                "s3tables:UpdateTableMetadataLocation",
                "s3tables:GetTableData",
                "s3tables:PutTableData"
            ],
            "Resource": "*"
        },
        {
            "Sid": "LakeFormationAccess",
            "Effect": "Allow",
            "Action": "lakeformation:GetDataAccess",
            "Resource": "*"
        },
        {
            "Sid": "GlueS3TablesCatalogAccess",
            "Effect": "Allow",
            "Action": [
                "glue:GetCatalog",
                "glue:GetCatalogs",
                "glue:GetPartitions",
                "glue:GetPartition",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "glue:UpdateTable"
            ],
            "Resource": [
                "arn:aws:glue:*:123456789012:catalog",
                "arn:aws:glue:*:123456789012:catalog/*",
                "arn:aws:glue:*:123456789012:database/*",
                "arn:aws:glue:*:123456789012:table/*/*"
            ]
        }
    ]
}

Alongside the read actions (GetTable, GetTableData, and so on), I've also included the write actions s3tables:PutTableData and glue:UpdateTable up front. Granting write permissions here means writes will also be possible once the integration is set up.

Creating the CATALOG INTEGRATION

On the Snowflake side, I'll create a catalog integration that connects to the Iceberg REST Catalog (AWS's Glue implementation).

CREATE OR REPLACE CATALOG INTEGRATION penguin_catalog_integration
  CATALOG_SOURCE = ICEBERG_REST
  TABLE_FORMAT = ICEBERG
  CATALOG_NAMESPACE = 'analytics'
  REST_CONFIG = (
    CATALOG_URI = 'https://glue.ap-northeast-1.amazonaws.com/iceberg'
    CATALOG_API_TYPE = AWS_GLUE
    CATALOG_NAME = '123456789012:s3tablescatalog/penguin-rest-test'
    ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS
  )
  REST_AUTHENTICATION = (
    TYPE = SIGV4
    SIGV4_IAM_ROLE = 'arn:aws:iam::123456789012:role/Snowflake_Catalog_Integration'
    SIGV4_SIGNING_REGION = 'ap-northeast-1'
  )
  ENABLED = TRUE;

You can see that CATALOG_URI is the exact same Glue Iceberg REST endpoint I was hitting directly in the previous article.

CATALOG_NAME takes the nested catalog's prefix notation (account ID:s3tablescatalog/bucket name). This mixes colon and slash separators, matching the same --catalog-id format I confirmed against Glue's native API in the previous article.

Since I've specified ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS, Snowflake operates by receiving vended credentials issued from the Glue endpoint.

DESC CATALOG INTEGRATION

Once you create the catalog integration, Snowflake issues an IAM user ARN and an external ID for it to assume on the AWS side.

DESC CATALOG INTEGRATION penguin_catalog_integration;

Two properties, API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID, come back in the result. Keep these on hand.

Setting Up the Trust Policy

On the trust policy of the role specified in SIGV4_IAM_ROLE (Snowflake_Catalog_Integration), allow the Snowflake side IAM user obtained from DESC CATALOG INTEGRATION above, along with its external ID.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::999999999999:user/xxxx1000-s"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": "SFCRole=2_(masked)"
                }
            }
        }
    ]
}

Principal is an IAM user that belongs to an AWS account managed by Snowflake. Attaching the sts:ExternalId condition prevents AssumeRole calls from anything other than this specific catalog integration on Snowflake's side. At this point, Snowflake can assume the role and use the IAM side permissions.

Note

The IAM policy body and the trust policy are two separate things. Even if you've included s3tables:PutTableData in the IAM policy, if the trust policy isn't set up correctly, the role can't be assumed and the catalog integration won't work. Check both.

Registering the Lake Formation Data Location

Separately from the trust policy, you also need to register this table bucket as a data lake location on the Lake Formation side.

This is the same idea as register-resource --with-federation that I covered in a previous article. Without this step, Lake Formation can't perform permission evaluation or issue vended credentials for this resource in the first place.

Registration requires an IAM role (a data access role) that can actually reach the target S3 location. This time I created a role named penguin-lakeformation-role.

The permission policy grants metadata reads via Glue, plus full access to S3 Tables tables and namespaces (read/write, along with Create/Delete/Rename operations).

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "glue:GetCatalog",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "glue:GetPartitions"
            ],
            "Resource": "*"
        },
        {
            "Sid": "ListBuckets",
            "Effect": "Allow",
            "Action": ["s3tables:ListTableBuckets"],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3tables:GetTableBucket",
                "s3tables:GetNamespace",
                "s3tables:ListNamespaces",
                "s3tables:GetTable",
                "s3tables:ListTables",
                "s3tables:GetTableMetadataLocation",
                "s3tables:UpdateTableMetadataLocation",
                "s3tables:GetTableData",
                "s3tables:PutTableData",
                "s3tables:CreateTable",
                "s3tables:DeleteTable",
                "s3tables:RenameTable",
                "s3tables:CreateNamespace",
                "s3tables:DeleteNamespace"
            ],
            "Resource": [
                "arn:aws:s3tables:ap-northeast-1:123456789012:bucket/*"
            ]
        }
    ]
}

The trust policy allows AssumeRole from lakeformation.amazonaws.com, but it turned out I needed to allow not just sts:AssumeRole but also sts:SetSourceIdentity and sts:SetContext alongside it. I've also restricted calls to my own account with the aws:SourceAccount condition.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "lakeformation.amazonaws.com"
            },
            "Action": [
                "sts:AssumeRole",
                "sts:SetSourceIdentity",
                "sts:SetContext"
            ],
            "Condition": {
                "StringEquals": {
                    "aws:SourceAccount": "123456789012"
                }
            }
        }
    ]
}

With this role specified, register the table bucket as a federated Lake Formation resource.

aws lakeformation register-resource \
  --resource-arn "arn:aws:s3tables:ap-northeast-1:123456789012:bucket/*" \
  --with-federation \
  --with-privileged-access \
  --role-arn arn:aws:iam::123456789012:role/penguin-lakeformation-role \
  --region ap-northeast-1

Specifying a wildcard (bucket/*) for --resource-arn also lets you skip having to re-register each table bucket individually.

Note: if you want more flexible control, such as registering table buckets individually with different settings per bucket, you'll need to specify each one separately.

If this location registration is missing, permission evaluation won't work correctly at actual access time, even if the IAM policy, trust policy, and Lake Formation grants are all set up correctly. By the way, S3 Tables location registration can't be done from the console, so command line operation is required here.

Note: I really wish this could be registered and edited from the console...

Lake Formation Grants (Three Tiers: Catalog, Database, Table)

Grant permissions to the Snowflake_Catalog_Integration role at each of the three tiers, Catalog, Database, and Table.

# Catalog tier
aws lakeformation grant-permissions \
  --principal DataLakePrincipalIdentifier=arn:aws:iam::123456789012:role/Snowflake_Catalog_Integration \
  --resource '{"Catalog":{"Id":"123456789012:s3tablescatalog/penguin-rest-test"}}' \
  --permissions "ALL DESCRIBE" \
  --permissions-with-grant-option "ALL DESCRIBE" \
  --region ap-northeast-1

# Database tier
aws lakeformation grant-permissions \
  --principal DataLakePrincipalIdentifier=arn:aws:iam::123456789012:role/Snowflake_Catalog_Integration \
  --resource '{"Database":{"CatalogId":"123456789012:s3tablescatalog/penguin-rest-test","Name":"analytics"}}' \
  --permissions "ALL CREATE_TABLE DESCRIBE" \
  --permissions-with-grant-option ALL CREATE_TABLE DESCRIBE \
  --region ap-northeast-1

# Table tier
aws lakeformation grant-permissions \
  --principal DataLakePrincipalIdentifier=arn:aws:iam::123456789012:role/Snowflake_Catalog_Integration \
  --resource '{"Table":{"CatalogId":"123456789012:s3tablescatalog/penguin-rest-test","DatabaseName":"analytics","Name":"daily_sales"}}' \
  --permissions "ALL" \
  --permissions-with-grant-option ALL \
  --region ap-northeast-1

CREATE OR REPLACE ICEBERG TABLE

With the catalog integration ready, register the Iceberg table individually on the Snowflake side.

CREATE OR REPLACE ICEBERG TABLE GOLD.GOLD.s3tables_table
  CATALOG = 'penguin_catalog_integration'
  CATALOG_NAMESPACE  = 'analytics'
  CATALOG_TABLE_NAME = 'daily_sales'
  AUTO_REFRESH       = TRUE
;

CATALOG_NAMESPACE and CATALOG_TABLE_NAME explicitly specify the namespace and table name on the S3 Tables side. This is the key point of this approach: you have to run this statement once for every single table you register. When a new table shows up on the S3 Tables side, it doesn't automatically appear on the Snowflake side.

Verification

I'll insert data into the Iceberg table from the Athena side and check whether it shows up from Snowflake.

-- Athena side
INSERT INTO "analytics"."daily_sales" (sales_date, amount)
VALUES
    (CAST('2025-01-01' AS DATE), CAST(10000 AS BIGINT)),
    (CAST('2025-01-02' AS DATE), CAST(15000 AS BIGINT)),
    (CAST('2025-01-03' AS DATE), CAST(12000 AS BIGINT)),
    (CAST('2025-01-04' AS DATE), CAST(18000 AS BIGINT)),
    (CAST('2025-01-05' AS DATE), CAST(20000 AS BIGINT));

After refreshing the table on the Snowflake side and running a SELECT, the data I'd inserted from Athena showed up without any issues!

ALTER ICEBERG TABLE GOLD.GOLD.s3tables_table REFRESH;
SELECT * FROM GOLD.GOLD.s3tables_table;

Using a Catalog-Linked Database

With the CREATE ICEBERG TABLE approach, I had to register tables one at a time by hand. Using a Catalog-Linked Database (CLD) lets you link an entire namespace (database) at once instead.

CREATE OR REPLACE DATABASE LINKED_DB
  LINKED_CATALOG = (
    CATALOG = 'penguin_catalog_integration'
  );

The moment I created it, daily_sales, which already existed under the analytics namespace, showed up in the table list automatically, with no DDL needed.

Verification (Auto Detection)

To confirm that auto detection actually works, let's create a new table from the AWS side.

-- Athena side
CREATE TABLE `analytics`.daily_sales2 (
  sale_date date,
  product_category string,
  sales_amount double)
PARTITIONED BY (month(sale_date))
TBLPROPERTIES ('table_type' = 'iceberg')

After the CREATE, checking Snowsight showed the newly created table had been added automatically!

Unlike the CREATE ICEBERG TABLE approach, there's no need to write DDL for each table. I confirmed that changes on the AWS side are reflected directly on the Snowflake side.

Writing from Snowflake

Up to this point, data had only flowed one way, from AWS to Snowflake. Let's also check whether data inserted from the Snowflake side shows up on the AWS side (Athena).

The Individually Added Table

First, let's write to the table that was added individually with CREATE ICEBERG TABLE.

INSERT INTO GOLD.GOLD.S3TABLES_TABLE (SALES_DATE, AMOUNT)
VALUES
    ('2026-07-16', 10000),
    ('2026-07-16', 15000),
    ('2026-07-16', 12000);

Let's check on the AWS side (Athena).

SELECT * FROM "analytics"."daily_sales" ORDER BY sales_date DESC limit 10;

The data I inserted from Snowflake showed up on the Athena side too, no problem! Even for an individually added table, reads and writes work in both directions.

Catalog-Linked Database

Next, let's write to daily_sales2, the table that was auto detected through the Catalog-Linked Database.

INSERT INTO LINKED_DB.analytics.daily_sales2 (sale_date, product_category, sales_amount)
VALUES
    ('2024-01-01', 'Electronics', 1500.00),
    ('2024-01-01', 'Clothing', 800.50),
    ('2024-01-02', 'Electronics', 2100.75),
    ('2024-01-02', 'Clothing', 950.25),
    ('2024-01-03', 'Food', 1200.00);

Let's check on the AWS side (Athena).

SELECT * FROM "analytics"."daily_sales2" limit 10;

This one wrote through without any issues too!

CREATE OR REPLACE ICEBERG TABLE vs. Catalog-Linked Database: Which to Use

Having actually tried both, here's how I'd sum up the comparison.

Approach	Table Registration	Auto Detection	Good Fit For
CATALOG INTEGRATION + CREATE ICEBERG TABLE	Manual, one at a time	None	Validation/PoC work, or when you only want to expose a small, specific set of tables
CATALOG INTEGRATION + Catalog-Linked Database	Automatic	Yes (tables on the Glue side show up automatically)	Production use where tables on the Glue/S3 Tables side keep getting added or changed

The CREATE ICEBERG TABLE approach lets you explicitly control which tables get exposed to Snowflake, but it comes with the cost of DDL maintenance every time a table is added. CLD removes that syncing overhead, but since essentially all tables under a namespace become visible, you'll want to design your permissions more carefully if you need to control the scope of what's exposed.

I confirmed this time that both approaches support bidirectional reads and writes, from Snowflake to the AWS side and from the AWS side to Snowflake.

What About a General Purpose Bucket?

Everything up to this point assumed S3 Tables (a table bucket). Reading and writing an Iceberg table on a general purpose S3 bucket from Snowflake comes with a slightly different set of constraints.

Combining this with the External Volume setup I touched on previously, here's how things currently stand.

Configuration	Read	Write
CATALOG INTEGRATION (`CATALOG_SOURCE = GLUE`) + External Volume	Yes	No
CATALOG INTEGRATION (`CATALOG_SOURCE = ICEBERG_REST`) + External Volume	Yes	Yes
CATALOG INTEGRATION (`CATALOG_SOURCE = ICEBERG_REST`) + Vended Credentials	Yes	Yes
CATALOG INTEGRATION + Catalog-Linked Database (REST)	Yes	Yes

Only the combination of CATALOG_SOURCE = GLUE (the legacy approach that uses the Glue Data Catalog API directly, rather than going through Iceberg REST) with External Volume ends up read only. Snowflake's own documentation explicitly states that tables created this way are treated as read only, as an "Externally Managed Iceberg Table." If you want to be able to write, it looks like choosing CATALOG_SOURCE = ICEBERG_REST is a prerequisite.

The S3 Tables configuration I tested this time (CATALOG_SOURCE = ICEBERG_REST with Vended Credentials) falls into the row that supports both reads and writes. That said, actually getting writes working required all three layers of permissions I've laid out here (IAM, Lake Formation, and the S3 Tables bucket policy) to be in place, and that was the real takeaway from this round of testing.

Conclusion

In this article, I actually built out reading and writing an Iceberg table on S3 Tables from Snowflake's Catalog Integration, and worked through the permissions issues I ran into along the way.

To summarize:

To access S3 Tables from Snowflake through the Glue Iceberg REST endpoint, you need all of the following in place: the IAM policy, the trust policy, the Lake Formation data location registration (register-resource with --with-federation --with-privileged-access and a dedicated data access role), the Lake Formation grants across the three tiers (Catalog, Database, and Table), and the S3 Tables table bucket policy.
The data access role's trust policy needs to allow not just sts:AssumeRole but also sts:SetSourceIdentity and sts:SetContext.
S3 Tables location registration can't be done from the console, and requires command line operations.
CREATE ICEBERG TABLE registers tables one at a time by hand, while Catalog-Linked Database auto detects at the namespace level. The underlying permission model is the same either way, the difference is purely in the registration effort.
I confirmed that both approaches support bidirectional reads and writes, from AWS to Snowflake and from Snowflake to AWS.
For an Iceberg table on a general purpose S3 bucket, only the CATALOG_SOURCE = GLUE plus External Volume combination is read only. Getting writes to work requires CATALOG_SOURCE = ICEBERG_REST.

Error messages around permissions, things like "Insufficient permissions" or "Access Denied," don't tell you on their own which layer, IAM, Lake Formation, or a resource policy, is actually blocking things. Untangling that meant tracing the actual events in CloudTrail one by one.

Lake Formation is also an important service for maintaining governance when integrating with Snowflake. It's a bit of a quirky service the first time you work with it, so I'd recommend reading through the documentation before diving in.

Note: I work with it fairly often myself, and I still trip up on it every single time...

I hope this article is useful to anyone considering connecting S3 Tables and Snowflake.

DEV Community