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.

Some important considerations when using $raw tables:

• $raw tables also include various "other" columns like _pkey and others starting with "_"; these columns exist for internal use, and shouldn't be used in any query.
• The source_id column exists for internal use, and should not be used in any query.
• The stability and semantics of these columns do not fall under our Stability guidelines, and may change without warning.
• The "_pkey" column 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, principal (includes Benchling User and App accounts), 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 principal$raw c on c.id = a.creator_id
LEFT JOIN project$raw d on a.source_id = d.id

Query Output: