> For the complete documentation index, see [llms.txt](https://docs.brigge.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.brigge.ai/documentation/brigge-for-transformers/warehouses.md).

# Warehouses

## Overview

A data warehouse is a central store for data. You can define two types of warehouse: [schema](#schema-based-warehouses) and [simple](#simple-warehouses). Once defined, data is added (via pipelines, or manually) and can be retrieved or referenced in pipelines via:

* *Data Warehouse* nodes. Perform `select`, `insert`, `compare` and `delete` operations with a [schema-based warehouse](#schema-based-warehouses), at any point in a pipeline.
* *Map* nodes. Use the `data warehouse lookup` transformation to lookup/output fields from a schema-based or [simple warehouse](#simple-warehouses), at any point in a pipeline.

{% hint style="info" %}
If you need to reference warehouse data in a [custom script](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/custom-script-node.md), define pipeline steps to lookup/load the data first, then pass these payload into the *Custom Script* node.
{% endhint %}

This page focuses on creating and managing both types of warehouse. For more information on referencing warehouses in pipelines, see the following pages:

* [Data warehouse node](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md)
* [Data warehouse lookup transformation](/documentation/brigge-for-transformers/pipelines/data-transformations.md#data-warehouse-lookup)

## Accessing your warehouses

To view, edit, create, and manage data warehouses for your organisation, select `settings` ➥ `warehouses` from the navigation menu.

## Schema-based warehouses

A `schema`-based warehouse stores incoming pipeline data. The warehouse is configured with a schema that reflects the structure of payloads that are expected. 

When payloads hit a [data warehouse node](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md) that's configured to update this warehouse, they are added. This is useful if you need to reference or reload processed data later, for any reason.

### Working with schema-based warehouses

<details>

<summary>Using a schema-based warehouse in pipelines</summary>

Consider the scenario where you need to send data into two different systems (*Timesheets* and *PayrolI)* in a single pipeline, and each has different mapping/transform requirements.

By storing the incoming data in an early state, we can process it as needed for the first system, then reload the original data and process it for the second system:

<div align="left" data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FfM3FOt0LvAf0LDM0KChd%2FData%20warehouse%20-%20using%20a%20schema%20warehoue.svg?alt=media&amp;token=f8cb217a-9b87-4e4f-90dc-4909cf7c3630" alt=""><figcaption></figcaption></figure></div>

These pipeline steps are summarised below:

{% stepper %}
{% step %}
**Incoming data**

Incoming data is [split](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/split-node.md) into single-item payloads with [metadata](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/set-meta-node.md) applied - these steps are needed for both *Timesheets* and *Payroll* systems, so they are placed before anything is added to our warehouse.
{% endstep %}

{% step %}
**Insert to warehouse**

We add the split payloads to a warehouse. In this scenario, the first *Data Warehouse* node is configured to `insert`, with the `scope` set to `{{system.pipeline_run_id}}`, so each item goes into the warehouse with the current run id.
{% endstep %}

{% step %}
**Map & push**

Our split payloads continue to the map node, where fields are mapped and transformed as needed for the *Timesheets* system. Mapped output is sent into the *Timesheets* system.
{% endstep %}

{% step %}
**Retrieve data**

At this point, the shape of our data has changed and it's not suitable for sending in to the *Payroll* system. So, we use a second *Data Warehouse* node to retrieve our stored data.

The second *Data Warehouse* node is configured to `select`, with the `scope` set to `{{system.pipeline_run_id}}`. So, any warehouse items associated with the current run id are selected from the warehouse and reloaded into the pipeline for onward processing.

Using `{{system.pipeline_run_id}}` for scope always resolves to the *current* run id. So, applying it to two *Data Warehouse* nodes in the same pipeline will always resolve to the same (i.e. current) run id.

You might notice that our sample pipeline includes a [*Set Payload*](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/set-payload-node.md) node immediately before the second *Data Warehouse* node. This is because the previous [*Connector*](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/connector-node.md) step outputs multiple payloads, which we don't want to send into the *Data Warehouse* node. Adding a *Set Payload* node here (with any placeholder text) interrupts the flow of payloads, ready for the *Data Warehouse* step.
{% endstep %}

{% step %}
**New map & push**

The reloaded data is mapped and transformed as needed for the *Payroll* system. Mapped output is sent into the *Payroll* system.
{% endstep %}
{% endstepper %}

</details>

<details>

<summary>Creating a schema-based warehouse</summary>

To add a new schema-based warehouse, [open the *Warehouses* page](#accessing-your-warehouses), then click the `create data warehouse` button:

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FAXuT0SBY4sweucmalJWG%2Fcreate%20warehouse%201.png?alt=media&amp;token=9642423e-faa0-49b6-b397-9648bdab863d" alt=""><figcaption></figcaption></figure></div>

Now you can configure the warehouse:

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FCEW0lP1DzuJgkzzQVfoM%2FAdd%20data%20warehouse%20-%20schema.svg?alt=media&amp;token=5e5d522d-a5ed-43f6-bb0f-393298027e22" alt=""><figcaption></figcaption></figure></div>

Configuration options are summarised below:

{% stepper %}
{% step %}
**Warehouse type**

Select the `schema` type.
{% endstep %}

{% step %}
**Warehouse name**

Enter a name for the new warehouse - this is how the warehouse will be identified throughout the system.
{% endstep %}

{% step %}
**Schema**

Select a schema from the dropdown list. This schema should reflect the structure of data that you expect *Data Warehouse* nodes to receive when inserting data for this warehouse.

When choosing a schema, think about where the *Data Warehouse* node will be placed in the pipeline. For example, the schema for a multi-item payload won't be the same as the schema for a single-item payloads output from a [*Split*](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/split-node.md) node.
{% endstep %}

{% step %}
**Select an index field**

Navigate the schema structure and select an `index` field:

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FGzjhhlRRLY2fthGbfOfS%2Fcreate%20schema%20warehouse%20-%20index.png?alt=media&amp;token=09497156-ca8e-4ada-9b85-181bae24e8fc" alt=""><figcaption></figcaption></figure></div>

As the name suggests, the selected field is used to index the warehouse.

{% hint style="info" %}
When defining rules to determine which warehouse records to consider (e.g. for a [select](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md#select) operation), querying on an index field is more efficient than querying on a non-indexed field.

Index fields are also used to uniquely identify warehouse records. When a pipeline [updates](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md#insert) or [compares](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md#compare) data (or if you [insert data manually](#inserting-warehouse-content-manually) and allow updates), Brigge checks the index field to match records.
{% endhint %}

You must configure at least one index, but you can add more if needed:

<figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FpHZsPaurMYeFNG5hsSgM%2Fadd%20another%20index.png?alt=media&amp;token=cb12d87e-33f8-4784-a0c9-fe6fc64d5142" alt=""><figcaption></figcaption></figure>

This is useful if different pipelines look up the same warehouse data by different fields. For example, an employee warehouse might be queried by `employee_id` in one pipeline and by `email` in another.
{% endstep %}
{% endstepper %}

</details>

<details>

<summary>Querying &#x26; managing schema-based warehouse data</summary>

When viewing an existing schema-based warehouse, you can query, edit, and remove data via the *Query Content panel:*

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2Fn5lAt3l8kcsXIAHwt3cY%2FWarehouse%20query%20content.svg?alt=media&amp;token=c26bb278-589a-4d69-bd98-edae39800dbc" alt=""><figcaption></figcaption></figure></div>

These options are summarised below:

{% stepper %}
{% step %}
**Scope**

If the data in this warehouse was inserted with a `scope` (for example,an environment or pipeline run id) you can filter query results by this value. For example:

* Suppose data is inserted during pipeline runs where the [data warehouse node](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md#defining-data-warehouse-rules) is configured to insert payloads with a `{{system.environment}}` scope. To query the warehouse for data related to the *Production* environment, you would enter *Production* here.
* Suppose data is inserted during pipeline runs where the [data warehouse node](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md#defining-data-warehouse-rules) is configured to insert payloads with a `{{system.pipeline_run_id}}` scope. To query the warehouse for data related to run id 485, you would enter *485* here.

{% hint style="info" %}
The scope value is case sensitive - *production* is not the same as *Production*.
{% endhint %}

{% hint style="info" %}
Data is inserted (with or without a given `scope`) via the [data warehouse node](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md) or [manually](#inserting-warehouse-content-manually).
{% endhint %}
{% endstep %}

{% step %}
**Clause**

Use the rule builder to define rules that determine which subset of data to target. This is a standard rule builder that's used throughout Brigge - for more information, see the [Data warehouse node](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md#defining-data-warehouse-rules) page.
{% endstep %}

{% step %}
**Run query**

Click the `run query` button to execute the defined query. Any matched records are listed in the lower results panel. If you have defined a `scope`, query results are filtered by this value.
{% endstep %}

{% step %}
**Get all**

Click the `get all` button to either:

* Retrieve all records for the defined `scope`
* Retrieve all records in the warehouse (if no `scope` is defined)

{% hint style="info" %}
The `get all` option ignores any defined queries but it does not ignore the `scope` setting.
{% endhint %}
{% endstep %}

{% step %}
**Query results**

Query (and get all) results are listed in the lower panel. Use page numbers to step through.
{% endstep %}

{% step %}
**View & edit record**

Each retrieved record is associated with an `edit` option. Use this to open the record in a `data management` editor, where you can view full details. From here, you can make changes or delete the record:

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2Fbs9lGwZIS9tOxPcK17I8%2Fdata%20management%20editor.png?alt=media&amp;token=51804a7e-6981-4fc5-9699-651ac61c1110" alt=""><figcaption></figcaption></figure></div>

Click the `save` button to save changes immediately, or click the `delete` button to delete this item.
{% endstep %}

{% step %}
**Delete data**

Click the `delete data` button to delete ALL data currently displayed in the results panel.

{% hint style="info" %}
If you need to delete one or two records, you may prefer to edit each one and use the `delete` option from the `data management` editor.
{% endhint %}

{% hint style="danger" %}
Items are deleted from the warehouse permanently. Ensure that your query/scope filter has retrieved the expected results before proceeding.
{% endhint %}
{% endstep %}

{% step %}
**Delete warehouse**

Click the `delete` button to delete the warehouse entirely.

{% hint style="danger" %}
A data warehouse cannot be restored if deleted.
{% endhint %}

{% hint style="info" %}
You can't delete a warehouse if it's currently configured in pipelines.&#x20;

In this scenario, you'll see a warning which lists relevant pipelines. You should remove the warehouse from any [data warehouse](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md) nodes and/or [data warehouse lookup transformations](/documentation/brigge-for-transformers/pipelines/data-transformations.md#data-warehouse-lookup) in these pipelines and then try the `delete` action again.
{% endhint %}
{% endstep %}
{% endstepper %}

</details>

<details>

<summary>Inserting schema-based warehouse content manually</summary>

Warehouse content is typically inserted with data from pipeline runs, via the [data warehouse node (insert method)](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md#insert). However, when viewing an existing schema-based warehouse, you can use the *Insert Content* panel to add data manually.

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2Fxs51iHpQa0RV66lFguQ5%2Fwarehouse%20-%20insert%20content.svg?alt=media&amp;token=51b7fc9f-ced5-4d83-a017-496a8d37c210" alt=""><figcaption></figcaption></figure></div>

These options are summarised below:

{% stepper %}
{% step %}
**Scope**

Assigning a `scope` to inserted data is a way of categorising this data so it can be retrieved separately. For example, if data being added is associated with a particular environment, enter the environment name here.

{% hint style="info" %}
The scope value is case sensitive - *production* is not the same as *Production*.
{% endhint %}
{% endstep %}

{% step %}
**Allow updates**

If you are inserting data in a warehouse where content exists, select this option if you want to update existing records if a match is found in this content.

Matching is performed on the schema index field(s). For example, suppose you are adding data to a warehouse that was [created](#creating-a-schema-based-warehouse) with its `index` set to `timesheet_id`. When you insert content manually and choose to `allow updates`, the inserted data is checked for `timesheet_id` values. If a match is found in the warehouse, the warehouse record is updated; if no match is found, a new record is created.

{% hint style="info" %}
Inserted data must have the same schema structure as the warehouse - therefore the warehouse `index` field will always exist in the data being added.
{% endhint %}
{% endstep %}

{% step %}
**Select a data file (optional)**

If data to be inserted exists in file, you can upload it here. Files should be uploaded one at a time.

{% hint style="info" %}
Remember, uploaded files must contain data in the same schema structure as the warehouse.
{% endhint %}

File upload is one option or inserting content. If preferred, you paste content directly (see below).
{% endstep %}

{% step %}
**Paste content (optional)**

As an alternative to file uploads, you can paste content into the text area.

{% hint style="info" %}
Remember, pasted content must be in the same schema structure as the warehouse.
{% endhint %}
{% endstep %}
{% endstepper %}

</details>

## Simple warehouses

A `simple` warehouse is useful for any scenario where one set of static values needs to be consistently mapped to another - for example, country names to country codes, department names to cost centre IDs, product SKUs to display names, etc.

When [creating](#creating-a-schema-based-warehouse) a `simple` warehouse, you build a lookup table where each row pairs a `key` with a `value`. When a pipeline queries this warehouse, it matches incoming data against one column and returns the other (the lookup can work in either direction).

{% hint style="info" %}
Which column acts as the lookup field and which is returned is determined when you come to [reference this warehouse in a pipeline](#using-a-simple-warehouse-in-pipelines).
{% endhint %}

### Working with simple warehouses

<details>

<summary>Using a simple warehouse in pipelines</summary>

A simple warehouse can be referenced in any pipeline, using the [data warehouse lookup transformation](/documentation/brigge-for-transformers/pipelines/data-transformations.md#data-warehouse-lookup) in a [map](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/map-node.md) step. Here, you configure which warehouse to use, and the direction in which replacements are made.

Consider a scenario where incoming data includes a `country` field with long-form country names, but a target system requires short-form country codes. To handle this, we [create](#creating-a-schema-based-warehouse) a `simple` warehouse and add a `key` for every country that might be received, each with a `value` set to the corresponding country code:

<div align="left" data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FTkpOrOeU0ivQXrhijPyW%2Fsimple%20-%20country%20codes%20.png?alt=media&amp;token=f274a2f1-e32d-4ef7-9922-d9196b63a9a9" alt="" width="563"><figcaption></figcaption></figure></div>

When we go on to map the `country` field in a pipeline, we can apply a [data warehouse lookup transformation](/documentation/brigge-for-transformers/pipelines/data-transformations.md#data-warehouse-lookup) to replace `keys`  (countries) with corresponding `values` (country codes) - i.e.:

**Data warehouse lookup inputs**

<pre class="language-json"><code class="lang-json">{
  "full_name": "Priya Nair",
<strong>  "country": "India",
</strong>  "department": "Engineering"
}
</code></pre>

**Data warehouse lookup outputs**

<pre class="language-json"><code class="lang-json">{
  "full_name": "Priya Nair",
  "department": "Engineering",
<strong>  "country_code": "IN"
</strong>}
</code></pre>

But if the scenario was reversed, we could just as easily replace country codes with countries:

**Data warehouse lookup inputs**

<pre class="language-json"><code class="lang-json">{
  "full_name": "Priya Nair",
  "department": "Engineering",
<strong>  "country_code": "IN"
</strong>}
</code></pre>

**Data warehouse lookup outputs**

<pre class="language-json"><code class="lang-json">{
  "full_name": "Priya Nair",
<strong>  "country": "India",
</strong>  "department": "Engineering"
}
</code></pre>

{% hint style="info" %}
See [Data warehouse lookup transformation](/documentation/brigge-for-transformers/pipelines/data-transformations.md#data-warehouse-lookup) for information about configuring this transformation.
{% endhint %}

</details>

<details>

<summary>Creating a simple warehouse</summary>

To add a new schema-based warehouse, access the *Warehouses* page from the navigation menu, then click the `create data warehouse` button:

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FAXuT0SBY4sweucmalJWG%2Fcreate%20warehouse%201.png?alt=media&amp;token=9642423e-faa0-49b6-b397-9648bdab863d" alt=""><figcaption></figcaption></figure></div>

Now you can configure the warehouse:

<div data-with-frame="true"><figure><img src="https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FoZqJB3ht9M11oCbSspJt%2FData%20warehouse%20-%20configure%20simple.svg?alt=media&amp;token=c00163a6-7021-486e-98cf-60f6be4c9dbb" alt=""><figcaption></figcaption></figure></div>

Configuration options are summarised below:

{% stepper %}
{% step %}
**Warehouse type**

Select the `simple` type.
{% endstep %}

{% step %}
**Warehouse name**

Enter a name for the new warehouse - this is how the warehouse will be identified throughout the system.
{% endstep %}

{% step %}
**Scope**

You can define key/value pairs for different scopes. A common use case for this is where you need to store the same kind of data for multiple [environments](/documentation/brigge-for-transformers/environments.md), but keys and/or values are different between environments. You could create multiple warehouses (one per environment) however, having everything in a single warehouse is easier to maintain.

To do this, enter an environment name in the scope field - if a matching environment is found, the scope field is shaded and you can add required keys and values in the usual way.

Having saved rows against a scope, you'll see them in the main list (i.e. when no scope is selected), but can filter by scope when needed. For example:

![](https://2172380191-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F8IqdqwZowcxtFpZfLa6k%2Fuploads%2FL0NNO8YomzUzBRVoyWWC%2Fscoped%20simple%20warehouse.gif?alt=media\&token=0bb89907-37c9-4073-bb35-b00afa042da0)

{% hint style="warning" %}
The important difference is that these rows will only be referenced when a pipeline runs in this environment.
{% endhint %}

{% hint style="info" %}
If there's no requirement to apply warehouse values by scope, you can ignore this field.
{% endhint %}
{% endstep %}

{% step %}
**Define rows**

Add required keys and values. When referencing this warehouse in a [data warehouse lookup transformation](/documentation/brigge-for-transformers/pipelines/data-transformations.md#data-warehouse-lookup), you can specify the replacement direction (i.e. replace `keys` with `values` or `values` with `keys`).
{% endstep %}

{% step %}
**Add more rows**

Use the `plus` icon to add more rows as needed.
{% endstep %}
{% endstepper %}

</details>

## Related pages

* [Data warehouse node](/documentation/brigge-for-transformers/pipelines/pipeline-nodes/data-warehouse-node.md)
* [Data warehouse lookup transformation](/documentation/brigge-for-transformers/pipelines/data-transformations.md#data-warehouse-lookup)