Warehouse Tables (old)

❗️

Page Deprecated

Please navigate to the new page at new page at: https://docs.benchling.com/docs/warehouse-tables-v2

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 seconds, sometimes a few minutes) for data to appear in the warehouse.

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.

The following standard Benchling tables are synced:

Requests, request tasks, runs, and results are synced to their own tables. The name of the table is derived from the schema name. For example, a run schema called Flow Cytometry Run will have a table called flow_cytometry_run. The table columns are based on the fields of the schema.

Fields

You can query for fields on entities by joining the registry_entity and field tables. For example, to show all resistance field values, you might query:

SELECT
registry_entity.id, registry_entity.name, field.field_name AS field_name, field.value
FROM registry_entity
JOIN field ON registry_entity.id = field.registry_entity_id
JOIN schema_field ON field.schema_field_id = schema_field.id
WHERE field.field_name = 'Resistance';

      id      |   name    | field_name | value
--------------+-----------+------------+--------
 seq_diETgMr3 | backBN001 | Resistance | AmpR
 seq_vFYFPvDQ | backBn002 | Resistance | Kan
 seq_6RUIueD4 | backBN003 | Resistance | AmpR
(3 rows)

You can also query for all fields attached to a single entity:

SELECT
registry_entity.id, registry_entity.name, schema_field.name AS field_name, field.value
FROM registry_entity
JOIN field ON registry_entity.id = field.registry_entity_id
JOIN schema_field ON field.schema_field_id = schema_field.id
WHERE registry_entity.id = 'bfi_YdzRyDA6';


      id      |   name   |  field_name   |    value
--------------+----------+---------------+-------------
 bfi_YdzRyDA6 | AMAT1132 | Transgene     | bfi_q11Mnlbg
 bfi_YdzRyDA6 | AMAT1132 | Analytes      | Acetic acid
 (2 rows)

Each row in the field table represents the value of one schematized object's field, for example the value of the "Resistance" field of seq_diETgMr3 in the table above is a single row in this table.

The field table has the following columns:

Registered Entities

Currently, only registered entities are synced. This may change in the future.