> ## 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.

# Getting started with data lakes

> A hands-on introduction to querying, accelerating, and writing back data in open table formats with ClickHouse.

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

<Info>
  **TL;DR**

  A hands-on walkthrough of querying data lake tables, accelerating them with MergeTree, and writing results back to Iceberg. All steps use public datasets and work on both Cloud and OSS.
</Info>

Screenshots in this guide are from the [ClickHouse Cloud](https://console.clickhouse.cloud) SQL console. All queries work on both Cloud and self-managed deployments.

<Steps>
  <Step>
    <h2 id="query-directly">
      Query Iceberg data directly
    </h2>

    The fastest way to start is with the [`icebergS3()`](/reference/functions/table-functions/iceberg) table function — point it at an Iceberg table in S3 and query immediately, no setup required.

    Inspect the schema:

    ```sql theme={null}
    DESCRIBE icebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')
    ```

    Run a query:

    ```sql theme={null}
    SELECT
        url,
        count() AS cnt
    FROM icebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')
    GROUP BY url
    ORDER BY cnt DESC
    LIMIT 5
    ```

    <Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/datalake/iceberg-query-direct.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=af8c2ac0f7acf772dac43fa8666750b5" alt="Iceberg Query" width="3836" height="1744" data-path="images/datalake/iceberg-query-direct.png" />

    ClickHouse reads the Iceberg metadata directly from S3 and infers the schema automatically. The same approach works for [`deltaLake()`](/reference/functions/table-functions/deltalake), [`hudi()`](/reference/functions/table-functions/hudi), and [`paimon()`](/reference/functions/table-functions/paimon).

    **Learn more:** [Querying open table formats directly](/guides/use-cases/data-warehousing/getting-started/querying-directly) covers all four formats, cluster variants for distributed reads, and storage backend options (S3, Azure, HDFS, local).
  </Step>

  <Step>
    <h2 id="table-engine">
      Create a persistent table engine
    </h2>

    For repeated access, create a table using the Iceberg table engine so you don't need to pass the path every time. The data stays in S3 — no data is duplicated:

    ```sql theme={null}
    CREATE TABLE hits_iceberg
        ENGINE = IcebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')
    ```

    Now query it like any ClickHouse table:

    ```sql theme={null}
    SELECT
        url,
        count() AS cnt
    FROM hits_iceberg
    GROUP BY url
    ORDER BY cnt DESC
    LIMIT 5
    ```

    <Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/datalake/iceberg-query-engine.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=4a7c28bcdfac29816d72895acf80a7cf" alt="Iceberg Query" width="3836" height="1744" data-path="images/datalake/iceberg-query-engine.png" />

    The table engine supports data caching, metadata caching, schema evolution, and time travel. See the [Querying directly](/guides/use-cases/data-warehousing/getting-started/querying-directly) guide for details on table engine features and the [support matrix](/guides/use-cases/data-warehousing/support-matrix) for a full feature comparison.
  </Step>

  <Step>
    <h2 id="connect-catalog">
      Connect to a catalog
    </h2>

    Most organizations manage Iceberg tables through a data catalog to centralize the table metadata and data discovery. ClickHouse supports connecting to your catalog using the [`DataLakeCatalog`](/reference/engines/database-engines/datalake) database engine, exposing all catalog tables as a ClickHouse database. This is the more scalable path so as new Iceberg tables are created, they are always accessible in ClickHouse without additional work.

    Here's an example connecting to [AWS Glue](/guides/use-cases/data-warehousing/glue-catalog):

    ```sql theme={null}
    CREATE DATABASE my_lake
    ENGINE = DataLakeCatalog
    SETTINGS
        catalog_type = 'glue',
        region = '<your-region>',
        aws_access_key_id = '<your-access-key>',
        aws_secret_access_key = '<your-secret-key>'
    ```

    Each catalog type requires its own connection settings — see the [Catalogs guides](/guides/use-cases/data-warehousing/reference/index) for the full list of supported catalogs and their configuration options.

    Browse tables and query:

    ```sql theme={null}
    SHOW TABLES FROM my_lake;
    ```

    ```sql theme={null}
    SELECT count(*) FROM my_lake.`<database>.<table>`
    ```

    <Note>
      Backticks are required around `<database>.<table>` because ClickHouse doesn't natively support more than one namespace.
    </Note>

    **Learn more:** [Connecting to a data catalog](/guides/use-cases/data-warehousing/getting-started/connecting-catalogs) walks through a full Unity Catalog setup with Delta and Iceberg examples.
  </Step>

  <Step>
    <h2 id="issue-query">
      Issue a query
    </h2>

    Regardless of which method you used above — table function, table engine, or catalog — the same ClickHouse SQL works across all of them:

    ```sql theme={null}
    -- Table function
    SELECT url, count() AS cnt
    FROM icebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')
    GROUP BY url ORDER BY cnt DESC LIMIT 5

    -- Table engine
    SELECT url, count() AS cnt
    FROM hits_iceberg
    GROUP BY url ORDER BY cnt DESC LIMIT 5

    -- Catalog
    SELECT url, count() AS cnt
    FROM my_lake.`<database>.<table>`
    GROUP BY url ORDER BY cnt DESC LIMIT 5
    ```

    The query syntax is identical — only the `FROM` clause changes. All ClickHouse SQL functions, joins, and aggregations work the same way regardless of the data source.
  </Step>

  <Step>
    <h2 id="load-data">
      Load a subset into ClickHouse
    </h2>

    Querying Iceberg directly is convenient, but performance is bounded by network throughput and the file layout. For analytical workloads, load data into a native MergeTree table.

    First, run a filtered query over the Iceberg table to get a baseline:

    ```sql theme={null}
    SELECT
        url,
        count() AS cnt
    FROM hits_iceberg
    WHERE counterid = 38
    GROUP BY url
    ORDER BY cnt DESC
    LIMIT 5
    ```

    This query scans the full dataset in S3 since Iceberg has no awareness of the `counterid` filter — expect it to take several seconds.

    <Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/datalake/iceberg-query.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=6e331ff4fd08e1fd3095d7f185993e90" alt="Iceberg Query" width="3836" height="1744" data-path="images/datalake/iceberg-query.png" />

    Now create a MergeTree table and load the data:

    ```sql theme={null}
    CREATE TABLE hits_clickhouse
    (
        url String,
        eventtime DateTime,
        counterid UInt32
    )
    ENGINE = MergeTree()
    ORDER BY (counterid, eventtime);
    ```

    ```sql theme={null}
    INSERT INTO hits_clickhouse
    SELECT url, eventtime, counterid
    FROM hits_iceberg
    ```

    Re-run the same query against the MergeTree table:

    ```sql theme={null}
    SELECT
        url,
        count() AS cnt
    FROM hits_clickhouse
    WHERE counterid = 38
    GROUP BY url
    ORDER BY cnt DESC
    LIMIT 5
    ```

    <Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/datalake/clickhouse-query.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=d511cd9cfd368d2a652b8d994b7010c2" alt="ClickHouse Query" width="3836" height="1744" data-path="images/datalake/clickhouse-query.png" />

    Because `counterid` is the first column in the `ORDER BY` key, ClickHouse's sparse primary index skips directly to the relevant granules — only reading the rows for `counterid = 38` instead of scanning all 100 million rows. The result is a dramatic speedup.

    The [accelerating analytics](/guides/use-cases/data-warehousing/getting-started/accelerating-analytics) guide takes this further with `LowCardinality` types, full-text indices, and optimized ordering keys, demonstrating a **\~40x improvement** on a 283 million row dataset.

    **Learn more:** [Accelerating analytics with MergeTree](/guides/use-cases/data-warehousing/getting-started/accelerating-analytics) covers schema optimization, full-text indexing, and a complete before/after performance comparison.
  </Step>

  <Step>
    <h2 id="write-back">
      Write back to Iceberg
    </h2>

    ClickHouse can also write data back to Iceberg tables, enabling reverse ETL workflows — publishing aggregated results or subsets for consumption by other tools (Spark, Trino, DuckDB, etc.).

    Create an Iceberg table for output:

    ```sql theme={null}
    CREATE TABLE output_iceberg
    (
        url String,
        cnt UInt64
    )
    ENGINE = IcebergS3('https://your-bucket.s3.amazonaws.com/output/', 'access_key', 'secret_key')
    ```

    Write aggregated results:

    ```sql theme={null}
    SET allow_experimental_insert_into_iceberg = 1;

    INSERT INTO output_iceberg
    SELECT
        url,
        count() AS cnt
    FROM hits_clickhouse
    GROUP BY url
    ORDER BY cnt DESC
    ```

    The resulting Iceberg table is readable by any Iceberg-compatible engine.

    **Learn more:** [Writing data to open table formats](/guides/use-cases/data-warehousing/getting-started/writing-data) covers writing raw data and aggregated results using the UK Price Paid dataset, including schema considerations when mapping ClickHouse types to Iceberg.
  </Step>
</Steps>

<h2 id="next-steps">
  Next steps
</h2>

Now that you've seen the full workflow, dive deeper into each area:

* [Querying directly](/guides/use-cases/data-warehousing/getting-started/querying-directly) — All four formats, cluster variants, table engines, caching
* [Connecting to catalogs](/guides/use-cases/data-warehousing/getting-started/connecting-catalogs) — Full Unity Catalog walkthrough with Delta and Iceberg
* [Accelerating analytics](/guides/use-cases/data-warehousing/getting-started/accelerating-analytics) — Schema optimization, indexing, \~40x speedup demo
* [Writing to data lakes](/guides/use-cases/data-warehousing/getting-started/writing-data) — Raw writes, aggregated writes, type mapping
* [Support matrix](/guides/use-cases/data-warehousing/support-matrix) — Feature comparison across formats and storage backends