Multi-Tables
Learn how Benchling warehouse multi-tables represent fields that can store multiple values. This page explains how multi-tables are generated, how they relate to the original schema tables, and how to query them effectively.
Overview
Multi-tables are specialized warehouse tables that Benchling automatically creates when your schemas contain fields configured to accept multiple values. These tables store the individual values from multi-select fields in a structured format that enables flexible querying and analysis.
Important: Multi-value field data appears in both the original per-schema table and the multi-table. The data is duplicated to the multi-table to provide additional querying flexibility. It is not moved or removed from the original table.
When Multi-Tables Are Created
Multi-tables are only created for the following schema types when they contain multi-select fields:
- Request Task Schemas
- Request Schemas
- Result Schemas
- Run Schemas
Other schema types (such as Entity Schemas, Workflow Task Schemas, etc.) can have multi-select fields configured but multi-tables are not created for them in the warehouse.
Identifying Multi-Tables
Multi-tables are easy to identify by their naming convention:
Naming Pattern: <schema_name>_multi$raw (raw table) and <schema_name>_multi (view)
Example:
- Schema system name:
protein_analysis - Standard table:
protein_analysis$raw - Multi-table:
protein_analysis_multi$raw
All tables ending in _multi or _multi$raw are multi-tables.
Multi-Table Structure
Primary Key
Unlike standard warehouse tables that use id as the primary key, multi-tables use a composite primary key consisting of:
id: The identifier of the parent recordindex: A sequential number (starting from 0) representing the position of each value in the multi-select field
Schema
Each row in a multi-table represents one value from a multi-select field for a specific parent record.
Example Structure:
In this example, record prt_ABC123 has two sample types (Blood and Serum), while record prt_XYZ789 has one (Plasma).
Querying Multi-Tables
Basic Query
To retrieve all values for multi-select fields:
Joining with Parent Table
To combine data from the parent table with multi-select values:
Aggregating Multi-Select Values
To concatenate multiple values back into a single row:
Need Help?
If you have questions about multi-tables or need assistance querying your warehouse data, please contact Benchling Support or consult with your Benchling Implementation Manager.