Benchling

Warehouse Tables

Objects from the main Benchling database are periodically synced to the warehouse to make them available for queries. It may take time (usually a few minutes, up to 24 hours) for data to appear in the warehouse.

If you need additional help for the Benchling Insights application please visit https://help.benchling.com/en/collections/2259232-insights

AND/OR

If you need more detail about each Benchling table then please navigate to https://docs.benchling.com/docs/warehouse-columns-rows-descriptions

Tables exposed in the warehouse are shown in the image below. Because of its size, it's best to download it and open it separately, or open it in a new tab, where you can zoom in and see the part you'd like. In some cases, we have a table per user-defined schema in Benchling. In these cases, an (Example) is added to the table's name. The tables you see in your Benchling warehouse would be named after the schemas you have configured.

All of the tables in the below image are Postgres views, backed by a table of the same name followed by $raw. Generally speaking, the views are "cleaned up" versions of the raw data (archived items are hidden, etc). We recommend trying to stick with the cleaned up views when possible, but you can query the $raw tables if needed.

$raw tables also include various "other" columns like pkey and others starting with "". Those columns are used for Benchling to track changes to the Warehouse and generally shouldn't be used in any query. The "_pkey" column also does not represent the primary key for a particular table. All tables will have an initial "id" column which represents that particular table's primary key.

Sample Queries

You can query for data in Benchling by combining tables together. For example to show all "in-progress" entries you can combine the entry, entry_auditor, user, and project tables. See below:

SELECT a.id as "Notebook Entry", 
        c.name as "Author", 
        coalesce(a.review_status, 'IN_PROGRESS') as "Review Status", 
        d.name as "Project",
        a.created_at as "Created At"
FROM entry$raw a
LEFT JOIN entry_auditor$raw b on a.id = b.entry_id
LEFT JOIN user$raw c on c.id = a.creator_id
LEFT JOIN project$raw d on a.source_id = d.id

Query Output:

Notebook EntryAuthorReview StatusProjectCreated At
etr_7O2twKtERosalind FranklinIN_PROGRESSSCFA Platform2020-04-06T21:45:54.716093
etr_XnmA7Fh5Rosalind FranklinIN_PROGRESSSCFA Platform2020-04-06T21:46:13.181137
etr_gGyzeVhnJames WatsonIN_PROGRESSSCFA Platform2020-04-15T22:44:15.662984
etr_OqHUmK2DRosalind FranklinIN_PROGRESSSCFA Platform2020-04-15T23:37:33.465126
etr_4qgVERktFrancis CrickIN_PROGRESSSCFA Platform2020-03-20T22:25:33.428003
etr_zpHeAFc2James WatsonIN_PROGRESSSCFA Platform2020-04-15T22:44:24.523667

Updated 11 days ago

Warehouse Tables


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.