ICEBERG_BAD_DATA with Firehose Iceberg table destination

Question

We have been trying Firehose for Iceberg Tables. The source is Kinesis stream attached to DynamoDB tables with some Lambda processing in between.

Table has been successfully filled by Firehose, but when we tried to run `MERGE INTO` using the source table as target target table, then the DML failed with `ICEBERG_BAD_DATA: Multiple entries with same key: 2147483645=_pos and 2147483645=$row_id.pos`.

I tried to isolate the problem, so I copied existing table into new one using CTAS and executed the same DML on it, which worked without any problem.

It seems like either we are not using Firehose properly or it damages the Iceberg table somehow.

Is this a documented thing or have we been using it in improper way?

The DML for merge is

```
MERGE INTO db.tab AS t
USING db.tab AS s
ON s.pk = t.pk AND s.sk = t.sk
WHEN MATCHED
    THEN UPDATE SET _record_changed_at = s._record_changed_at || '_test';
```

The DDL for CTAS is

```
CREATE TABLE db.tab WITH (
    table_type = 'ICEBERG',
    format = 'PARQUET',
    location = 's3://bucket-export-${aws_account_id}-${aws_region_name}/data/test/test2',
    is_external = false,
    partitioning = ARRAY['account_id'],
    vacuum_max_snapshot_age_seconds = 604800,
    vacuum_min_snapshots_to_keep = 5
                                                    )
AS SELECT
       pk, sk, account_id
-- more columns go here
       FROM db.orig_tab;
```

The original table was crated with this DDL

```
CREATE TABLE IF NOT EXISTS awsdatacatalog.db.orig_tab(
    pk string COMMENT 'Partition Key: PK',
    sk string COMMENT 'Sort Key: SK',
    account_id string COMMENT 'Customer account ID, <% map path=AccountId %>'
   -- more columns
)
COMMENT 'Mirror of DynamoDB Table'
PARTITIONED BY (account_id)
LOCATION 's3://data-export-${aws_account_id}-${aws_region_name}/data/glue/iceberg/db/orig_tab'
TBLPROPERTIES (
    'table_type' = 'ICEBERG',
    'format' = 'parquet'
)
;
```

re:Post Agent · Answer

The issue you're experiencing with the ICEBERG_BAD_DATA error when running a MERGE INTO operation on a table filled by Amazon Data Firehose is not a documented behavior. It appears that there might be an issue with how the data is being written to the Iceberg table by Firehose.

The error message suggests that there are duplicate entries for the hidden metadata columns _pos and $row_id.pos, which are used internally by Iceberg for managing row positions. This duplication shouldn't occur under normal circumstances.

Since the CTAS operation and subsequent MERGE INTO worked without issues, it's likely that the problem lies in how Firehose is writing data to the Iceberg table. Here are a few things to consider:

1. Ensure that you have properly configured the Firehose delivery stream for Iceberg tables. Pay special attention to the DestinationTableConfiguration, particularly the UniqueKeys setting if you're performing updates or deletes.

2. Check if you have specified the correct buffering hints and processing configurations for your Firehose stream. Improper buffering or processing might lead to unexpected behavior.

3. Verify that the schema of your source data (from Kinesis and DynamoDB) matches the schema of your Iceberg table. Any mismatches could potentially cause issues.

4. If you're using a Lambda function for transformation between your source and Firehose, ensure that it's not introducing any duplicates or unexpected data.

5. Review your Firehose error logs and CloudWatch metrics to see if there are any indications of issues during data delivery.

6. Consider reaching out to AWS support, as this behavior seems unusual and might be a bug in the Firehose Iceberg integration.

To resolve the issue, you might want to try recreating the table and redelivering the data through Firehose with careful attention to the configuration. If the problem persists, using the CTAS method you've discovered as a workaround could be a temporary solution while you investigate further with AWS support.  
**Sources**  
[IcebergDestinationUpdate - Amazon Data Firehose](https://docs.aws.amazon.com/firehose/latest/APIReference/API_IcebergDestinationUpdate.html)  
[IcebergDestinationDescription - Amazon Data Firehose](https://docs.aws.amazon.com/firehose/latest/APIReference/API_IcebergDestinationDescription.html)  
[Amazon Data Firehose delivers data streams into Apache Iceberg format tables in Amazon S3 - AWS](https://aws.amazon.com/about-aws/whats-new/2024/10/amazon-data-firehose-data-streams-iceberg-format-tables-s3/)  
[DestinationTableConfiguration - Amazon Data Firehose](https://docs.aws.amazon.com/firehose/latest/APIReference/API_DestinationTableConfiguration.html)  
[Route incoming records to different Iceberg tables - Amazon Data Firehose](https://docs.aws.amazon.com/firehose/latest/dev/apache-iceberg-format-input-record-different.html)

ICEBERG_BAD_DATA with Firehose Iceberg table destination

Add your answer

Relevant content