> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Using dynamic union tables

> Sometimes, you may have a lot of tables in a database, which actually relate to the same entity.

## Use case

Sometimes, you may have a lot of tables in a database, which actually relate
to the same entity.

For example, you can have “per client” tables with the same data, but related to
different customers: `elon_musk_table`, `john_doe_table`, `steve_jobs_table`,
etc. In this case, it would make sense to create a *single* [cube][ref-cubes]
for customers, which should be backed by a union table from all customers tables.

## Data modeling

You can use the [`sql` parameter][ref-cube-sql] to define a cube over an
arbitrary SQL query, e.g., a query that includes `UNION` or `UNION ALL`
operators:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: customers
      sql: |
        SELECT *, 'Einstein' AS name FROM einstein_data UNION ALL
        SELECT *, 'Pascal'   AS name FROM pascal_data   UNION ALL
        SELECT *, 'Newton'   AS name FROM newton_data
    
      measures:
        - name: count
          type: count
    
      dimensions:
        - name: name
          sql: name
          type: string





  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`customers`, {
    sql: `
      SELECT *, 'Einstein' AS name FROM einstein_data UNION ALL
      SELECT *, 'Pascal'   AS name FROM pascal_data   UNION ALL
      SELECT *, 'Newton'   AS name FROM newton_data
    `,
   
    measures: {
      count: {
        type: `count`
      }
    },
   
    dimensions: {
      name: {
        sql: `name`,
        type: `string`
      }
    }
  })
  ```
</CodeGroup>

However, it can be quite annoying to write the SQL to union all tables manually.
Luckily, you can use [dynamic data modeling][ref-dynamic-data-modeling] to
generate necessary SQL based on a list of tables:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  {%- set customer_tables = {
    "einstein_data": "Einstein",
    "pascal_data": "Pascal",
    "newton_data": "Newton"
  } -%}
   
  cubes:
    - name: customers
      sql: |
        {%- for table, name in customer_tables | items %}
        SELECT *, '{{ name | safe }}' AS name FROM {{ table | safe }}
        {% if not loop.last %}UNION ALL{% endif %}
        {% endfor %}
    
      measures:
        - name: count
          type: count
    
      dimensions:
        - name: name
          sql: name
          type: string
   
   
   
   
   
  ```

  ```javascript title="JavaScript" theme={"dark"}
  const customer_tables = [
    { table: "einstein_data", name: "Einstein" },
    { table: "pascal_data", name: "Pascal" },
    { table: "newton_data", name: "Newton" }
  ]

  cube(`customers`, {
    sql: customer_tables
      .map(entry => `SELECT *, '${entry.name}' AS name FROM ${entry.table}`)
      .join(` UNION ALL `),
   
    measures: {
      count: {
        type: `count`
      }
    },
   
    dimensions: {
      name: {
        sql: `name`,
        type: `string`
      }
    }
  })
  ```
</CodeGroup>

## Result

Querying `count` and `name` members of the dynamically defined `customers` cube
would result in the following generated SQL:

```sql theme={"dark"}
SELECT
  "customers".name "customers__name",
  count(*) "customers__count"
FROM
  (
    SELECT
      *,
      'Einstein' AS name
    FROM
      einstein_data
    UNION ALL
    SELECT
      *,
      'Pascal' AS name
    FROM
      pascal_data
    UNION ALL
    SELECT
      *,
      'Newton' AS name
    FROM
      newton_data
  ) AS "customers"
GROUP BY
  1
ORDER BY
  2 DESC
```

[ref-cubes]: /reference/data-modeling/cube

[ref-cube-sql]: /reference/data-modeling/cube#sql

[ref-dynamic-data-modeling]: /docs/data-modeling/dynamic