Denormalizing Data

We can perform this denormalization using an `INSERT INTO SELECT` with an `OUTER JOIN` query:

```
INSERT INTO posts_with_links
SELECT
    posts.*,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
    SELECT
         PostId,
         groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts.Id = postlinks.PostId

0 rows in set. Elapsed: 155.372 sec. Processed 66.37 million rows, 76.33 GB (427.18 thousand rows/s., 491.25 MB/s.)
Peak memory usage: 6.98 GiB.
```

> Note the timing here. We've managed to denormalize 66m rows in around 2mins. As we'll see later, this is an operation we can schedule.

Note the use of the `groupArray` functions to collapse the `PostLinks` down into an array for each `PostId`, prior to joining. This array is then filtered into two sublists: `LinkedPosts` and `DuplicatePosts`, which also exclude any empty results from the outer join.

We can select some rows to see our new denormalized structure:

```
SELECT LinkedPosts, DuplicatePosts
FROM posts_with_links
WHERE (length(LinkedPosts) > 2) AND (length(DuplicatePosts) > 0)
LIMIT 1
FORMAT Vertical

Row 1:
──────
LinkedPosts:    [('2017-04-11 11:53:09.583',3404508),('2017-04-11 11:49:07.680',3922739),('2017-04-11 11:48:33.353',33058004)]
DuplicatePosts: [('2017-04-11 12:18:37.260',3922739),('2017-04-11 12:18:37.260',33058004)]
```

## Orchestrating and scheduling denormalization[​](#orchestrating-and-scheduling-denormalization "Direct link to Orchestrating and scheduling denormalization")

### Batch[​](#batch "Direct link to Batch")

Exploiting denormalization requires a transformation process in which it can be performed and orchestrated.

We have shown above how ClickHouse can be used to perform this transformation once data has been loaded through an `INSERT INTO SELECT`. This is appropriate for periodic batch transformations.

Users have several options for orchestrating this in ClickHouse, assuming a periodic batch load process is acceptable:

- **[Refreshable Materialized Views](/docs/materialized-view/refreshable-materialized-view)** - Refreshable materialized views can be used to periodically schedule a query with the results sent to a target table. On query execution, the view ensures the target table is atomically updated. This provides a ClickHouse native means of scheduling this work.
- **External tooling** - Utilizing tools such as [dbt](https://www.getdbt.com/) and [Airflow](https://airflow.apache.org/) to periodically schedule the transformation. The [ClickHouse integration for dbt](/docs/integrations/dbt) ensures this is performed atomically with a new version of the target table created and then atomically swapped with the version receiving queries (via the [EXCHANGE](/docs/sql-reference/statements/exchange) command).

### Streaming[​](#streaming "Direct link to Streaming")

You may alternatively wish to perform this outside of ClickHouse, prior to insertion, using streaming technologies such as [Apache Flink](/docs/integrations/apache-flink). Alternatively, incremental [materialized views](/docs/guides/developer/cascading-materialized-views) can be used to perform this process as data is inserted.

- [Comparing Normalized vs. Denormalized Schemas](#comparing-normalized-vs-denormalized-schemas)- [When to use denormalization](#when-to-use-denormalization)- [Avoid denormalization on frequently updated data](#avoid-denormalization-on-frequently-updated-data)- [Practical cases for denormalization](#practical-cases-for-denormalization)
        - [Posts and Votes](#posts-and-votes)- [Users and Badges](#users-and-badges)- [Posts and PostLinks](#posts-and-postlinks)- [Simple statistic example](#simple-statistic-example)- [Exploiting complex types for one-to-many relationships](#exploiting-complex-types-for-one-to-many-relationships)- [Orchestrating and scheduling denormalization](#orchestrating-and-scheduling-denormalization)
          - [Batch](#batch)- [Streaming](#streaming)

Was this page helpful?