How do I join data from different Benchling Applications?

  • Updated

To help you get started with Insights we compiled some joins you will use to combine the data in Benchling Data Warehouse across the different applications.

Registry & Inventory

Let’s say you would like to see where registered entities are located in your inventory.

Tables to consider:
Registry Tables: registry_entity for all registered entities, or schema specific registry tables ie. plasmid
Inventory Tables: container_content and container

Entities in your registry will have a unique identifier in the “id” column. To link them to the container level information join “id” on container_contents “entity_id” column. However, to get the container name as well as container location in plate or box schema you will need to join container_content to container table.

Example Query:
SELECT, b.container_id, c.concentration_si, c.plate_id
FROM benchlingtx.plasmid a
JOIN benchlingtx.container_content b ON
LEFT JOIN benchlingtx.container c ON

NOTE: The Benchling Warehouse container table will contain all container schemas configured in Inventory Application. (ie. Wells, Tubes, Vials, etc) and plate_id will reference all plate schemas. If your container is placed in a box you will use the box_id column instead.

Requests & Notebook

Let’s say you would like to see all Benchling Requests and any associated Notebook entries created for Fulfillment of those requests.

Tables to consider:
Requests Tables: Request tables are named after the Request Schema configured. ie. protein_request . request_fulfillment tracks the fulfillment actions against a request. request_assignee tracks the user set as the assignee for request.
Notebook Tables: entry

Requests schemas level information lives in the custom request table ie protein_request. Request assignee and fulfillment information is tracked in separate tables which you can join the “request_id” column to the “id” column of the custom Requests table. Finally to get notebook metadata you will join the ‘entry_id’ column of the fulfillment table to the “id” column of the entry table.

Example Query:
SELECT, b.entry_id, c.modified_at::date, b.status, d.user_id AS assignee
FROM protein_request a
LEFT JOIN request_fulfillment b ON
LEFT JOIN entry c ON
LEFT JOIN request_assignee d ON

Was this article helpful?

Have more questions? Submit a request