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 a.id, b.container_id, c.concentration_si, c.plate_id
FROM benchlingtx.plasmid a
JOIN benchlingtx.container_content b ON a.id=b.entity_id
LEFT JOIN benchlingtx.container c ON b.container_id=c.id

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 a.id, b.entry_id, c.modified_at::date, b.status, d.user_id AS assignee
FROM protein_request a
LEFT JOIN request_fulfillment b ON a.id=b.request_id
LEFT JOIN entry c ON b.entry_id=c.id
LEFT JOIN request_assignee d ON d.request_id=a.id

Did this answer your question?