---
title: "Update the testing data warehouse"
---

> Documentation Index
> Fetch the complete documentation index at: https://docs.evidential.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Update the testing data warehouse

## What is the testing DWH?

The testing DWH is a sample datasource we use for unit tests and for manually verification of functionality. We store
the dataset in a compressed CSV form in GitHub, and various tools load this dataset into various databases.

There are two important pieces:

1. The data itself (in the .csv.zst file).
1. The DDL statements used to create the tables.

## Modifying the Testing DWH Data

You can use [duckdb](https://duckdb.org/) to modify the testing data warehouse dataset.

Here's an example script:

```sql
CREATE TABLE testing_dwh AS
SELECT *
FROM 'src/xngin/apiserver/testdata/testing_dwh.csv.zst';

DESCRIBE testing_dwh;

ALTER TABLE testing_dwh
    ADD sample_date DATE
        DEFAULT NOW() + CAST((90 * random()) AS integer) * INTERVAL '1 days';
ALTER TABLE testing_dwh
    ADD sample_timestamp DATETIME
        DEFAULT DATETRUNC('second',
                          NOW() - CAST((525600 * random()) as integer) * interval '1 minute'
                              + CAST ((60*random()) as integer) * interval '1 second');
ALTER TABLE testing_dwh
    ADD timestamp_with_tz TIMESTAMPTZ
        DEFAULT DATETRUNC('second',
                          NOW() - CAST((525600 * random()) as integer) * interval '1 minute'
                              + CAST ((60*random()) as integer) * interval '1 second');

COPY testing_dwh (
    gender,
    ethnicity,
    first_name,
    last_name,
    id,
    income,
    potential_0,
    potential_1,
    is_recruited,
    is_registered,
    is_onboarded,
    is_engaged,
    is_retained,
    baseline_income,
    current_income,
    uuid_filter,
    sample_date,
    sample_timestamp,
    timestamp_with_tz)
    TO 'src/xngin/apiserver/testdata/testing_dwh.csv.zst';
```

You can recompress the file at a greater compression level:

```shell
ls -lh src/xngin/apiserver/testdata/testing_dwh.csv.zst
zstd -d -c src/xngin/apiserver/testdata/testing_dwh.csv.zst | zstd -9 > /tmp/smaller.tgz
mv /tmp/smaller.tgz src/xngin/apiserver/testdata/testing_dwh.csv.zst
ls -lh src/xngin/apiserver/testdata/testing_dwh.csv.zst
```

## Wide Testing DWH

The wide testing DWH (`wide_dwh.csv.zst`) is a separate, generated dataset with 500 columns and 1,000 rows. It is
used to exercise UI behavior with tables that have many columns. Its CSV and database-specific DDL files are in
`src/xngin/apiserver/testdata/`; the dataset and DDL are generated by `tools/generate_wide_dwh.py`. The regular
testing DWH remains the main sample dataset described above.

## Modifying the DDL

Different databases have different syntax for table creation. Variants may use different type names or support different
types. To handle these gracefully, we also keep distinct DDL per testing backend.

You can find them in [the \*.ddl files](https://github.com/agency-fund/evidential-be/blob/main/src/xngin/apiserver/testdata). These files inform
`xngin-cli create-testing-dwh` when creating the tables.

Source: https://docs.evidential.dev/develop/guides/update-the-testing-warehouse/index.mdx
