/creating-data-lake-table

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