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
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, 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.
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
|Notebook Entry||Author||Review Status||Project||Created At|
|etr_7O2twKtE||Rosalind Franklin||IN_PROGRESS||SCFA Platform||2020-04-06T21:45:54.716093|
|etr_XnmA7Fh5||Rosalind Franklin||IN_PROGRESS||SCFA Platform||2020-04-06T21:46:13.181137|
|etr_gGyzeVhn||James Watson||IN_PROGRESS||SCFA Platform||2020-04-15T22:44:15.662984|
|etr_OqHUmK2D||Rosalind Franklin||IN_PROGRESS||SCFA Platform||2020-04-15T23:37:33.465126|
|etr_4qgVERkt||Francis Crick||IN_PROGRESS||SCFA Platform||2020-03-20T22:25:33.428003|
|etr_zpHeAFc2||James Watson||IN_PROGRESS||SCFA Platform||2020-04-15T22:44:24.523667|
Updated 2 days ago