Creating Data Lake Table

Create Data Lake Tables with Amazon S3 Tables

Overview

Amazon S3 Tables provides managed Iceberg tables with automatic compaction and snapshot management. Queryable via Athena and Iceberg-compatible engines.

Common Tasks

You MUST use AWS MCP server tools when connected, they provide command validation, sandboxed execution, and audit logging. Fall back to AWS CLI if MCP unavailable.

Decision Guide

Before creating, You MUST check what exists:

You MUST run aws glue get-tables --database-name <NAME> when user mentions a database.

Creation paths:

• Existing data in S3: Create empty table (Steps 1-8), then use ingesting-into-data-lake skill.
• Glue ETL pipeline: Read references/table-creation-glue-etl.md first, then Steps 1-6.
• Lake Formation access control: Search AWS docs for "S3 Tables integration with Lake Formation".

1. Verify Dependencies

Constraints:

• You MUST check whether AWS MCP server tools or AWS CLI are available and inform user if missing
• You MUST confirm target AWS region and verify credentials with aws sts get-caller-identity

2. Understand the Schema

• Explicit schema: Validate Iceberg types.
• Loose description: Ask columns, types, grain. Propose and confirm.
• Existing S3 data: Infer schema from file headers only. Create empty table first, then use ingesting-into-data-lake skill.

Constraints:

• You MUST read references/best-practices.md for Iceberg type mapping, partitions, and naming.
• You MUST ask for all required parameters upfront: table name, columns, types, partition strategy. For schema evolution, see references/athena-ddl-path.md.
• You MUST use all lowercase names -- Glue rejects mixed case with GENERIC_INTERNAL_ERROR. Namespace and table names MUST NOT contain hyphens.
• You SHOULD suggest partition columns based on access patterns.

3. Create Table Bucket

Names: 3-63 chars, lowercase, numbers, hyphens.

aws s3tables create-table-bucket --name <BUCKET_NAME> --region <REGION>

Capture table-bucket-arn. Encryption (SSE-S3 default, SSE-KMS) and storage class (STANDARD, INTELLIGENT_TIERING) set at creation. See references/best-practices.md.

Constraints:

• You MUST check existing buckets with aws s3tables list-table-buckets and ask user to select or create new.
• If using SSE-KMS, KMS key policy MUST allow S3 Tables maintenance service principal to read data. Search AWS docs for "S3 Tables KMS key policy" for required policy.
• If bucket creation fails, see references/best-practices.md for common errors.

4. Create Namespace

aws s3tables create-namespace --table-bucket-arn <ARN> --namespace <NAMESPACE>

Constraints:

• You MUST list existing namespaces first and suggest reusing if relevant
• You MUST use lowercase names with no hyphens

5. Create Glue Data Catalog Integration

Check if s3tablescatalog exists (create once per region per account):

aws glue get-catalog --catalog-id s3tablescatalog

If not found, create (requires glue:CreateCatalog, glue:passConnection):

aws glue create-catalog --name "s3tablescatalog" --catalog-input '{
  "FederatedCatalog": {
    "Identifier": "arn:aws:s3tables:<REGION>:<ACCOUNT_ID>:bucket/*",
    "ConnectionName": "aws:s3tables"
  },
  "CreateDatabaseDefaultPermissions": [{"Principal": {"DataLakePrincipalIdentifier": "IAM_ALLOWED_PRINCIPALS"}, "Permissions": ["ALL"]}],
  "CreateTableDefaultPermissions": [{"Principal": {"DataLakePrincipalIdentifier": "IAM_ALLOWED_PRINCIPALS"}, "Permissions": ["ALL"]}],
  "AllowFullTableExternalDataAccess": "True"
}'

Verify with aws glue get-catalogs --parent-catalog-id s3tablescatalog.

6. Configure Access Control

S3 Tables uses s3tables:* IAM namespace (not s3:*).

Querying principal permissions (bucket policy):

• s3tables:GetTableBucket, s3tables:GetNamespace, s3tables:GetTable, s3tables:GetTableMetadataLocation, s3tables:GetTableData

Querying principal permissions (IAM policy):

• glue:GetCatalog, glue:GetDatabase, glue:GetTable

You MUST scope to correct ARN patterns. You MUST read references/access-control.md for exact resource ARNs.

Constraints:

• You MUST ask user for querying principal ARN
• You MUST NOT grant broader permissions than necessary
• You MUST NOT create IAM roles automatically, verify existing and guide user

7. Create the Table

Default: S3 Tables API:

aws s3tables create-table \
  --table-bucket-arn <ARN> \
  --namespace <NAMESPACE> \
  --name <TABLE_NAME> \
  --format ICEBERG \
  --metadata '<METADATA_JSON>'

Metadata JSON MUST nest under "iceberg" key:

{"iceberg":{"schema":{"fields":[
  {"name":"order_date","type":"date","required":true},
  {"name":"customer_id","type":"string","required":true},
  {"name":"amount","type":"double","required":false}
]},
"partitionSpec":{"fields":[
  {"sourceId":1,"fieldId":1000,"transform":"month","name":"order_date_month"}
]}}}

Constraints:

• partitionSpec.sourceId MUST reference a valid schema field ID
• For schema evolution after creation, use Athena DDL. See references/athena-ddl-path.md
• You MUST use schemaV2 for complex types (list, map, struct) with explicit field IDs. See references/best-practices.md.
• You SHOULD search AWS docs for "IcebergPartitionField S3 Tables" for supported partition transforms

8. Verify and Confirm

You MUST verify with aws s3tables get-table and confirm queryability with DESCRIBE <table_name> via Athena using --query-execution-context '{"Catalog":"s3tablescatalog/<BUCKET_NAME>","Database":"<NAMESPACE>"}'. Do NOT put catalog in SQL. Present summary: bucket ARN, namespace, table, schema, partitions.

Troubleshooting

Additional Resources

• access-control.md -- IAM permissions, ARN patterns, permission errors
• best-practices.md -- Iceberg types, partitions, naming, common errors
• athena-ddl-path.md -- Athena DDL, schema evolution
• table-creation-glue-etl.md -- Spark DDL via Glue ETL
• Loading data: ingesting-into-data-lake skill

S3 Tables Access Control

You MUST use least-privilege permissions when configuring access to S3 Tables.

Bucket Policy (s3tables actions)

Actions: s3tables:GetTableBucket, s3tables:GetNamespace, s3tables:GetTable, s3tables:GetTableMetadataLocation, s3tables:GetTableData

Resources:

• arn:aws:s3tables:{region}:{account_id}:bucket/{bucket_name}
• arn:aws:s3tables:{region}:{account_id}:bucket/{bucket_name}/table/*

Set with aws s3tables put-table-bucket-policy --table-bucket-arn <ARN> --resource-policy '<POLICY_JSON>'.

IAM Policy (glue actions)

Actions: glue:GetCatalog, glue:GetDatabase, glue:GetTable

Resources (all three actions on each):

• arn:aws:glue:{region}:{account_id}:catalog (root -- required for federated catalog resolution)
• arn:aws:glue:{region}:{account_id}:catalog/s3tablescatalog
• arn:aws:glue:{region}:{account_id}:catalog/s3tablescatalog/*
• arn:aws:glue:{region}:{account_id}:database/s3tablescatalog/*/*
• arn:aws:glue:{region}:{account_id}:table/s3tablescatalog/*/*/*

SSE-KMS

If the table bucket uses SSE-KMS, the querying principal also needs kms:Decrypt and kms:GenerateDataKey on the KMS key.

Glue ETL Service Role

See table-creation-glue-etl.md for the Glue job service role permissions.

Additional Resources

For latest IAM guidance, search AWS docs for "S3 Tables identity-based policies IAM", "S3 Tables access management", and "S3 Tables Glue catalog prerequisites".

Creating Tables via Athena DDL

Alternative to the S3 Tables API. Use when the user specifically wants SQL DDL or needs schema evolution via ALTER TABLE after creation.

Prerequisites

• Glue catalog (s3tablescatalog) MUST be registered (see Step 5 in SKILL.md)
• Athena workgroup MUST use engine version 3 (required for Iceberg support)
• Output S3 bucket MUST exist in the same region as the table bucket for Athena query results. If Athena has never been used in this region, the user MUST first configure a query result location in the Athena workgroup settings or via --result-configuration on each query.

CREATE TABLE

The catalog reference goes in --query-execution-context, NOT in the SQL statement. Use <database>.<table> format in SQL:

CREATE TABLE <namespace>.<table_name> (
  <column_definitions>
)
PARTITIONED BY (<partition_columns>)
TBLPROPERTIES ('table_type' = 'ICEBERG')

CRITICAL: Do NOT include a LOCATION clause. S3 Tables manages storage automatically. This differs from regular Athena external tables.

CRITICAL: Do NOT put the catalog name in the SQL. Athena cannot parse s3tablescatalog/<bucket> as a catalog identifier in DDL. It goes in the execution context only.

Execute via Athena

aws athena start-query-execution \
  --query-string "<DDL>" \
  --query-execution-context '{"Catalog": "s3tablescatalog/<BUCKET_NAME>", "Database": "<NAMESPACE>"}' \
  --work-group "<WORKGROUP>" \
  --result-configuration '{"OutputLocation": "s3://<RESULTS_BUCKET>/output/"}'

Check status with aws athena get-query-execution --query-execution-id <ID>.

The results bucket MUST be in the same region as the table bucket.

Querying

Use the same execution context pattern for SELECT queries:

aws athena start-query-execution \
  --query-string "SELECT * FROM <namespace>.<table_name> LIMIT 10" \
  --query-execution-context '{"Catalog": "s3tablescatalog/<BUCKET_NAME>", "Database": "<NAMESPACE>"}' \
  --work-group "<WORKGROUP>" \
  --result-configuration '{"OutputLocation": "s3://<RESULTS_BUCKET>/output/"}'

Constraints

• All table and column names MUST be lowercase
• You MUST NOT include a LOCATION clause
• You MUST NOT put catalog name in the SQL -- use execution context
• Output S3 bucket MUST be in the same region
• The querying principal needs athena:StartQueryExecution, athena:GetQueryExecution, athena:GetQueryResults plus S3 access to the results bucket. Also requires S3 Tables and Glue permissions — see access-control.md.

Schema Evolution

ALTER TABLE uses the same --query-execution-context pattern:

aws athena start-query-execution \
  --query-string "ALTER TABLE <namespace>.<table_name> ADD COLUMNS (<col> <type>)" \
  --query-execution-context '{"Catalog": "s3tablescatalog/<BUCKET_NAME>", "Database": "<NAMESPACE>"}' \
  --work-group "<WORKGROUP>" \
  --result-configuration '{"OutputLocation": "s3://<RESULTS_BUCKET>/output/"}'

Supported operations: ALTER TABLE ADD COLUMNS, ALTER TABLE DROP COLUMN. WARNING: schema changes affect all future queries. You MUST confirm with the user before executing.

Alternative: Schema evolution is also supported via the S3 Tables Iceberg REST API or the S3 Tables Catalog for Apache Iceberg (open-source). Search AWS docs for "S3 Tables Catalog for Apache Iceberg" for setup.

Additional Resources

For latest Athena DDL syntax, search AWS docs for "Creating Iceberg tables in Athena" and "Supported data types for Iceberg tables in Athena".

S3 Tables Best Practices

Iceberg Type Mapping

For the full list of supported Iceberg data types and their mappings to query engine types, search AWS docs for "Supported data types for Iceberg tables in Athena". Complex types (list, map, struct) require schemaV2 instead of schema in API metadata. Search AWS docs for "IcebergSchemaV2 S3 Tables" for the full spec. Example with nested struct:

{"iceberg":{"schemaV2":{"type":"struct","fields":[
  {"id":1,"name":"device_id","required":true,"type":"string"},
  {"id":2,"name":"location","required":false,"type":{
    "type":"struct","fields":[
      {"id":3,"name":"latitude","required":true,"type":"double"},
      {"id":4,"name":"longitude","required":true,"type":"double"}
    ]}}
]}}}

Key: top-level must have "type":"struct", all fields need explicit "id", nested struct uses "type":{"type":"struct","fields":[...]}.

Default choices when ambiguous:

• IDs: use long (safer than int for growth)
• Text: use string (no need to specify length in Iceberg)
• Timestamps: use timestamp unless timezone awareness is needed, then timestamptz
• Money: use int or long storing cents/smallest unit to avoid floating-point errors. Use decimal(p,s) only when fractional amounts are required.

Partition Strategy

Choose partitions based on query access patterns, not data structure.

Time-series (events, logs, metrics):

• High/medium-volume (≥100K rows/day): PARTITIONED BY (event_date) with identity transform
• Low-volume (<100K rows/day): partition by month transform

Multi-tenant: PARTITIONED BY (tenant_id), add date if high volume per tenant.

No clear pattern: Start without partitions. Iceberg supports adding partitions later without rewriting data.

Partition guidelines:

• Use columns with low cardinality (10-10,000 unique values) frequently in WHERE clauses
• Limit to 2-3 partition levels
• Do NOT partition by high-cardinality columns (user_id, transaction_id)
• Aim for partition sizes of 100MB-1GB

Naming Conventions

All names MUST be lowercase (Glue Data Catalog requirement).

• Table bucket: lowercase, numbers, hyphens. 3-63 chars. Name by team/domain (e.g., analytics-tables, marketing-data)
• Namespace: lowercase, underscores. Name by data stage or domain (e.g., raw_events, processed, analytics)
• Table: lowercase, underscores. Name by entity (e.g., customer_orders, click_events)
• Columns: lowercase, snake_case. Descriptive names, avoid abbreviations.

Schema Design

• Use descriptive names that won't need renaming
• Avoid packing JSON strings into single columns -- use Iceberg struct/map/array types
• For schema evolution, see athena-ddl-path.md.

Storage Class

Default is STANDARD. For tables with infrequently accessed historical data, set Intelligent Tiering at bucket creation:

aws s3tables create-table-bucket --name <NAME> --region <REGION> \
  --storage-class-configuration '{"storageClass":"INTELLIGENT_TIERING"}'

Bucket default can be changed with aws s3tables put-table-bucket-storage-class (applies to new tables only). Per-table storage class is set at creation via create-table --storage-class-configuration and cannot be changed after.

Common Errors

Creating S3 Tables with Spark DDL in Glue ETL

Use when building Glue ETL pipelines that create and write to S3 Tables. Tables created via the S3 Tables API (aws s3tables create-table --metadata) are also readable by Spark.

Critical Requirements

• Glue 5.1 or higher is required (Spark 3.5.6, Iceberg 1.10.0). Do NOT use Glue 4.0.
• `--datalake-formats iceberg` MUST be set as a job argument
• Table bucket and namespace MUST exist before running the job

Static Config Gotcha (Most Common Failure)

In Glue 5.x, catalog configs are static and MUST go in --conf job arguments. Using spark.conf.set() throws:

AnalysisException: Cannot modify the value of a static config: spark.sql.extensions

Rule: All spark.sql.catalog.* configuration goes in --conf, never in the PySpark script.

Rule: Catalog and database names containing hyphens MUST be backtick-escaped in Spark SQL (e.g., ` my-catalog.my-db.my_table ). Without backticks, Spark returns INVALID_IDENTIFIER`.

Access Methods

REST Endpoint (simplest)

spark.sql.catalog.<name>=org.apache.iceberg.spark.SparkCatalog
spark.sql.catalog.<name>.type=rest
spark.sql.catalog.<name>.uri=https://s3tables.<region>.amazonaws.com/iceberg
spark.sql.catalog.<name>.warehouse=<table_bucket_arn>
spark.sql.catalog.<name>.rest.sigv4-enabled=true
spark.sql.catalog.<name>.rest.signing-name=s3tables
spark.sql.catalog.<name>.rest.signing-region=<region>
spark.sql.catalog.<name>.io-impl=org.apache.iceberg.aws.s3.S3FileIO

Analytics Integration (for Athena/Redshift access)

spark.sql.catalog.<name>=org.apache.iceberg.spark.SparkCatalog
spark.sql.catalog.<name>.catalog-impl=org.apache.iceberg.aws.glue.GlueCatalog
spark.sql.catalog.<name>.glue.id=<account_id>:s3tablescatalog/<table_bucket_name>
spark.sql.catalog.<name>.warehouse=<table_bucket_arn>

The warehouse parameter is required — without it Spark fails with "Cannot derive default warehouse location".

--conf Format Rules

The --conf argument is a single string with space-separated --conf key=value pairs:

"--conf": "spark.sql.catalog.s3tables=org.apache.iceberg.spark.SparkCatalog --conf spark.sql.catalog.s3tables.type=rest --conf ..."

First key-value has no --conf prefix. Use --cli-input-json file://config.json to avoid shell escaping.

Glue Job Config Example (REST Endpoint)

job-config.json:

{
    "Name": "my-etl-job",
    "Role": "arn:aws:iam::<ACCOUNT>:role/<GLUE_ROLE>",
    "Command": {
        "Name": "glueetl",
        "ScriptLocation": "s3://<BUCKET>/scripts/my_etl.py",
        "PythonVersion": "3"
    },
    "DefaultArguments": {
        "--datalake-formats": "iceberg",
        "--conf": "spark.sql.catalog.s3tables=org.apache.iceberg.spark.SparkCatalog --conf spark.sql.catalog.s3tables.type=rest --conf spark.sql.catalog.s3tables.uri=https://s3tables.<REGION>.amazonaws.com/iceberg --conf spark.sql.catalog.s3tables.warehouse=<TABLE_BUCKET_ARN> --conf spark.sql.catalog.s3tables.rest.sigv4-enabled=true --conf spark.sql.catalog.s3tables.rest.signing-name=s3tables --conf spark.sql.catalog.s3tables.rest.signing-region=<REGION> --conf spark.sql.catalog.s3tables.io-impl=org.apache.iceberg.aws.s3.S3FileIO",
        "--catalog_name": "s3tables",
        "--namespace": "<NAMESPACE>",
        "--table_name": "<TABLE_NAME>"
    },
    "GlueVersion": "5.1",
    "NumberOfWorkers": 2,
    "WorkerType": "G.1X"
}

For Analytics Integration, replace the --conf value with: spark.sql.catalog.s3tablescatalog=org.apache.iceberg.spark.SparkCatalog --conf spark.sql.catalog.s3tablescatalog.catalog-impl=org.apache.iceberg.aws.glue.GlueCatalog --conf spark.sql.catalog.s3tablescatalog.glue.id=<ACCOUNT>:s3tablescatalog/<BUCKET_NAME> --conf spark.sql.catalog.s3tablescatalog.warehouse=<TABLE_BUCKET_ARN>

PySpark Script

Catalog config is in --conf, so the script is clean:

import sys
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ['JOB_NAME', 'catalog_name', 'namespace', 'table_name'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# Do NOT call spark.conf.set() for catalog config in Glue 5.x
# catalog_name must match spark.sql.catalog.<name> from --conf

spark.sql(f"""
CREATE TABLE IF NOT EXISTS {args['catalog_name']}.{args['namespace']}.{args['table_name']} (
  col1 STRING,
  col2 INT
)
USING iceberg
PARTITIONED BY (col1)
""")
# No LOCATION clause -- S3 Tables manages storage

job.commit()

IAM Requirements

The Glue service role needs: AWSGlueServiceRole plus s3tables:GetTableBucket, s3tables:GetNamespace, s3tables:ListNamespaces, s3tables:CreateTable, s3tables:GetTable, s3tables:ListTables, s3tables:UpdateTableMetadataLocation, s3tables:GetTableMetadataLocation, s3tables:GetTableData, s3tables:PutTableData, and glue:GetCatalog, glue:GetDatabase, glue:GetTable, glue:passConnection. Table bucket, namespace, and Glue catalog MUST be created before the Glue job runs (Steps 3-5 in SKILL.md). For exact resource ARN scoping, see access-control.md.

Troubleshooting

Additional Resources

For latest Glue ETL guidance, search AWS docs for "Running ETL jobs on Amazon S3 tables with AWS Glue".