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_entity for all registered entities, or schema specific registry tables ie.
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
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.
request_fulfillment tracks the fulfillment actions against a request.
request_assignee tracks the user set as the assignee for request.
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.
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