> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Writing data to open table formats > Write data from ClickHouse back to Iceberg tables in object storage for long-term storage and downstream consumption. In the previous guides, you queried open table formats in place and loaded data into MergeTree for fast analytics. In many architectures, data also needs to flow in the other direction - from ClickHouse back into open table formats. Two common scenarios drive this: * **Offloading to long-term storage** - Data arrives in ClickHouse as a real-time analytics layer, powering dashboards and operational reporting. Once the data ages beyond its real-time window, it can be written out to Iceberg in object storage for durable, cost-effective retention in an interoperable format. * **Reverse ETL** - Transformations, aggregations, and enrichment performed inside ClickHouse produce derived datasets that downstream tools and other teams need to consume. Writing these results to Iceberg tables makes them available across the broader data ecosystem. In both cases, `INSERT INTO SELECT` lets you move data from ClickHouse tables into Iceberg tables stored in object storage. Writing to open table formats is currently supported for **Iceberg tables only**. Partial support for Delta Lake tables is under development. Tables must not be managed by a catalog.

Prepare a source dataset

For this guide, we'll use the [UK Price Paid](/get-started/sample-datasets/uk-price-paid) dataset - a public record of every residential property transaction in England and Wales.

Create and populate a MergeTree table

```sql theme={null} CREATE DATABASE uk; CREATE TABLE uk.uk_price_paid ( price UInt32, date Date, postcode1 LowCardinality(String), postcode2 LowCardinality(String), type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0), is_new UInt8, duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0), addr1 String, addr2 String, street LowCardinality(String), locality LowCardinality(String), town LowCardinality(String), district LowCardinality(String), county LowCardinality(String) ) ENGINE = MergeTree ORDER BY (postcode1, postcode2, addr1, addr2); ``` Populate the table directly from the public CSV source: ```sql theme={null} INSERT INTO uk.uk_price_paid SELECT toUInt32(price_string) AS price, parseDateTimeBestEffortUS(time) AS date, splitByChar(' ', postcode)[1] AS postcode1, splitByChar(' ', postcode)[2] AS postcode2, transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type, b = 'Y' AS is_new, transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration, addr1, addr2, street, locality, town, district, county FROM url( 'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv', 'CSV', 'uuid_string String, price_string String, time String, postcode String, a String, b String, c String, addr1 String, addr2 String, street String, locality String, town String, district String, county String, d String, e String' ) SETTINGS max_http_get_redirects=10; ``` ```response theme={null} 30906560 rows in set. Elapsed: 59.852 sec. Processed 30.91 million rows, 5.41 GB (516.39 thousand rows/s., 90.40 MB/s.) Peak memory usage: 485.15 MiB. ```

Write data to an Iceberg table

Create the Iceberg table

To write data into Iceberg, create a table using the [`IcebergS3` table engine](/reference/engines/table-engines/integrations/iceberg). Note that the schema must be simplified compared to the MergeTree source. ClickHouse supports a richer type system than Iceberg and the underlying Parquet files - types such as `Enum`, `LowCardinality`, and `UInt8` are not supported in Iceberg and must be mapped to compatible types. ```sql theme={null} CREATE TABLE uk.uk_iceberg ( price UInt32, date Date, postcode1 String, postcode2 String, type UInt32, is_new UInt32, duration UInt32, addr1 String, addr2 String, street String, locality String, town String, district String, county String ) ENGINE = IcebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg_uk_price_paid/', '', '', '') ```

Insert a subset of data

Use `INSERT INTO SELECT` to write data from the MergeTree table into the Iceberg table. In this example, we write only London transactions: ```sql theme={null} SET allow_experimental_insert_into_iceberg = 1; INSERT INTO uk.uk_iceberg SELECT * FROM uk.uk_price_paid WHERE town = 'LONDON' ``` ```response theme={null} 2346741 rows in set. Elapsed: 1.419 sec. Processed 30.91 million rows, 153.43 MB (21.78 million rows/s., 108.15 MB/s.) Peak memory usage: 371.60 MiB. ```

Query the Iceberg table

The data is now stored as Iceberg in object storage and can be queried from ClickHouse - or any other tool that reads Iceberg: ```sql theme={null} SELECT locality, count() FROM uk.uk_iceberg WHERE locality != '' GROUP BY locality ORDER BY count() DESC LIMIT 10 ``` ```response theme={null} ┌─locality────┬─count()─┐ │ LONDON │ 896796 │ │ WALTHAMSTOW │ 8610 │ │ LEYTON │ 3525 │ │ CHINGFORD │ 3133 │ │ HORNSEY │ 2794 │ │ STREATHAM │ 2760 │ │ WOOD GREEN │ 2443 │ │ ACTON │ 2155 │ │ LEYTONSTONE │ 2102 │ │ EAST HAM │ 2085 │ └─────────────┴─────────┘ 10 rows in set. Elapsed: 0.329 sec. Processed 457.86 thousand rows, 2.62 MB (1.39 million rows/s., 7.95 MB/s.) Peak memory usage: 12.19 MiB. ```

Write aggregated results

Iceberg tables are not limited to storing raw rows. They can also hold the output of aggregations and transformations - the results of ETL processes performed inside ClickHouse. This is useful for publishing pre-computed summaries to a lakehouse for downstream consumption.

Create an Iceberg table for aggregates

```sql theme={null} CREATE TABLE uk.uk_avg_town ( price Float64, town String ) ENGINE = IcebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg_uk_avg_town/', '', '', '') ```

Insert aggregated data

Compute average property prices by town and write the results directly into Iceberg: ```sql theme={null} INSERT INTO uk.uk_avg_town SELECT avg(price) AS price, town FROM uk.uk_price_paid GROUP BY town ``` ```response theme={null} 1173 rows in set. Elapsed: 0.480 sec. Processed 30.91 million rows, 185.44 MB (64.34 million rows/s., 386.05 MB/s.) Peak memory usage: 4.18 MiB. ```

Query the aggregated table

Other tools - and other ClickHouse instances - can now read this pre-computed dataset: ```sql theme={null} SELECT town, price FROM uk.uk_avg_town ORDER BY price DESC LIMIT 10 ``` ```response theme={null} ┌─town───────────────┬──────────────price─┐ │ GATWICK │ 28232811.583333332 │ │ THORNHILL │ 985000 │ │ VIRGINIA WATER │ 984633.2938574939 │ │ CHALFONT ST GILES │ 863347.7280187573 │ │ COBHAM │ 775251.47313278 │ │ PURFLEET-ON-THAMES │ 772651.8 │ │ BEACONSFIELD │ 746052.9327405858 │ │ ESHER │ 686708.4969745865 │ │ KESTON │ 654541.1774842045 │ │ GERRARDS CROSS │ 639109.4084023251 │ └────────────────────┴────────────────────┘ 10 rows in set. Elapsed: 0.210 sec. ```