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).
2. The DDL statements used to create the tables.

Modifying the Testing DWH Data

You can use duckdb to modify the testing data warehouse dataset.

Here's an example script:

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:

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. These files inform xngin-cli create-testing-dwh when creating the tables.