# Creating many-to-many tables from dbt

**URL:** <https://discourse.getdbt.com/t/creating-many-to-many-tables-from-dbt/4131>\
**Category:** Archive\
**Created:** [April 2, 2022, 4:12pm UTC](https://discourse.getdbt.com/t/creating-many-to-many-tables-from-dbt/4131 "2022-04-02T16:12:46Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![vlourme](https://avatars.discourse-cdn.com/v4/letter/v/c4cdca/32.png) [@vlourme](https://discourse.getdbt.com/u/vlourme)\
**Post date:** [April 2, 2022, 4:12pm UTC](https://discourse.getdbt.com/t/creating-many-to-many-tables-from-dbt/4131/1 "2022-04-02T16:12:46Z")

</div>

Hello everybody 🙂

We are starting using dbt in my company (rewriting all our data workflow, migrating from MongoDB to Postgres, etc.). We are querying billing data, everything is in a single, very large raw table, that actually contains around 100M rows, and slowly growing more and more.

With dbt, our goal is to split this large table into multiple smaller and regrouped tables to make our queries faster for the end-user. But we are facing an issue with arrays unnesting and many-to-many relations, I’ll explain with examples.

First, here is an example of our raw table:

 ![Raw table](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/7/76de01a329c3cf7340581e598a084e852511c64b.png)

As you can see, there are tags in a `varchar(255)[]` array. We want to make two tables:

- tags: which will contain every unique tag, distinct and split in `name` and `value` columns.
- tags\_source: which is a pivot between the raw table ID and the tag ID.

Actually, as I am writing this, it actually does work and we found a way to optimize this query, taking now around 500 seconds to process on 70M rows. We did two iterations before getting this result:

- The first attempt was to make the `tags` table and then a second query was doing the pivot using the `@>` operator (array contains), this was really slow, the `@>` operator is not good for handling millions of rows. **It took more than an hour to process.**
- The second and actual attempt was to make an `ephemeral` materialization that `unnest` tags, add the original raw ID in the same row and also do an MD5 of the original unnested value. Then, we can launch two parallel queries:
  - One that will create the `tags` table by doing a distinct on the MD5 generated before, preventing any duplicates.
  - The second does a pivot by grouping on the MD5 and the `source_id`.

Using the second method, these two tables now take **around 500s to create, in parallel** , which is much better.  
However, my real questions are:

- Can I make this query even faster?
- Is there a better, more “dbt” convenient approach?

---

<div class="post-metadata">

**Author:** ![ted.conbeer](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ted.conbeer/32/42_2.png) [@ted.conbeer](https://discourse.getdbt.com/u/ted.conbeer)\
**Post date:** [April 4, 2022, 3:40pm UTC](https://discourse.getdbt.com/t/creating-many-to-many-tables-from-dbt/4131/2 "2022-04-04T15:40:58Z")

</div>

Hi there!

What warehouse are you using (sounds like Postgres)? Your warehouse likely has some support for parsing JSON, which will be much faster than treating it as text. I’d create a “staging” table downstream from this raw table that casts `tags` to json. See the Postgres docs on manipulating JSON: [PostgreSQL: Documentation: 14: 9.16. JSON Functions and Operators](https://www.postgresql.org/docs/current/functions-json.html) (other warehouses have something similar).