---
title: "Multi-Row Test Results Tables"
slug: "results-multi-row-tests"
updated: 2026-06-16T21:41:24Z
published: 2026-06-16T21:41:24Z
canonical: "docs.validatar.com/results-multi-row-tests"
---

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

# Multi-Row Test Results Tables

## Overview

Multi-row test results are stored across four related tables that work together to represent the full result set. Unlike single-value tests (which store everything in one table), multi-row tests use a normalized "long format" structure that separates column definitions, row-level results, key values, and comparison values into separate tables.

This design supports tests with any number of columns and rows without requiring a fixed schema. The trade-off is that querying specific column values requires pivoting the data.

## The Four Tables

```
TEST_EXECUTION_TABLE_COLUMNS     — defines which columns exist for an execution
        │
        ▼
TEST_EXECUTION_TABLE_RESULTS     — one row per result row (pass/fail per row)
        │
        ├──── TEST_EXECUTION_TABLE_RESULT_KEYS    — key column values (long format)
        │
        └──── TEST_EXECUTION_TABLE_RESULT_VALUES  — value column comparisons (long format)
```

## TEST_EXECUTION_TABLE_COLUMNS

Defines the column structure for a test execution — what columns exist, their roles, and aggregate pass/fail counts.

| Column | Type | Description |
| --- | --- | --- |
| TEST_EXECUTION_ID | NUMBER | Execution ID |
| TEST_ID | NUMBER | Test ID |
| TEST_VERSION_ID | NUMBER | Test version ID |
| COLUMN_SEQUENCE | NUMBER | Column position (used to join to KEYS and VALUES tables) |
| TEST_SET_COLUMN_NAME | VARCHAR | Column name from the test (source) dataset |
| TEST_SET_COLUMN_SEQUENCE | NUMBER | Original sequence in the test dataset |
| TEST_SET_COLUMN_TYPE_ID | NUMBER | Column type code |
| TEST_SET_COLUMN_TYPE_NAME | VARCHAR | "Key" or "Value" |
| TEST_SET_COLUMN_ROLE_ID | NUMBER | Column role code |
| TEST_SET_COLUMN_ROLE_NAME | VARCHAR | Column role (e.g., "Key", "Source Value", "Target Value") |
| CONTROL_SET_COLUMN_NAME | VARCHAR | Corresponding column name in the control dataset |
| CONTROL_SET_COLUMN_SEQUENCE | NUMBER | Sequence in the control dataset |
| CONTROL_SET_COLUMN_TYPE_ID | NUMBER | Control column type code |
| CONTROL_SET_COLUMN_TYPE_NAME | VARCHAR | Control column type name |
| CONTROL_SET_COLUMN_ROLE_ID | NUMBER | Control column role code |
| CONTROL_SET_COLUMN_ROLE_NAME | VARCHAR | Control column role name |
| PASSED_COUNT | NUMBER | Rows that passed for this column |
| FAILED_COUNT | NUMBER | Rows that failed for this column |
| ERROR_COUNT | NUMBER | Rows that errored for this column |

## TEST_EXECUTION_TABLE_RESULTS

One row per result row in the test execution. Contains the overall evaluation for each row.

| Column | Type | Description |
| --- | --- | --- |
| TEST_EXECUTION_ID | NUMBER | Execution ID |
| TEST_ID | NUMBER | Test ID |
| TEST_VERSION_ID | NUMBER | Test version ID |
| ROW_INDEX | NUMBER | Row position (1-based) |
| COVERAGE_FLAG | NUMBER | 3 = Matched, 1 = Unmatched Test Only, 2 = Unmatched Control Only |
| EVALUATION_RESULT_ID | NUMBER | 24 = Passed, 25 = Failed, 26 = Error |
| EVALUATION_RESULT_NAME | VARCHAR | "PASSED", "FAILED", or "ERROR" |
| PASSED_VALUE_COUNT | NUMBER | Number of value columns that passed for this row |
| FAILED_VALUE_COUNT | NUMBER | Number of value columns that failed |
| ERROR_VALUE_COUNT | NUMBER | Number of value columns that errored |
| KEY_HASH | VARCHAR | Hash of the combined key values for this row |

## TEST_EXECUTION_TABLE_RESULT_KEYS

Stores the actual key column values for each result row in long format (one row per key column per result row).

| Column | Type | Description |
| --- | --- | --- |
| TEST_EXECUTION_ID | NUMBER | Execution ID |
| ROW_INDEX | NUMBER | Row position — join to TABLE_RESULTS |
| COLUMN_SEQUENCE | NUMBER | Which column this value belongs to — join to TABLE_COLUMNS |
| RESULT_KEY | VARCHAR | The actual key value (stored as string regardless of original type) |
| OVERFLOW_FLAG | NUMBER | 0 = normal, 1 = value truncated |

## TEST_EXECUTION_TABLE_RESULT_VALUES

Stores the comparison values for each value column per result row in long format.

| Column | Type | Description |
| --- | --- | --- |
| TEST_EXECUTION_ID | NUMBER | Execution ID |
| ROW_INDEX | NUMBER | Row position — join to TABLE_RESULTS |
| COLUMN_SEQUENCE | NUMBER | Which column — join to TABLE_COLUMNS |
| NUMERIC_SOURCE_VALUE | DECIMAL(27,8) | Numeric value from source |
| NUMERIC_TARGET_VALUE | DECIMAL(27,8) | Numeric value from target |
| ACTUAL_DIFFERENCE | DECIMAL(27,8) | Source minus target |
| ABSOLUTE_DIFFERENCE | DECIMAL(27,8) | Absolute difference |
| PERCENT_DIFFERENCE | DECIMAL(18,9) | Percentage difference |
| EVALUATION_RESULT_ID | NUMBER | 24 = Passed, 25 = Failed, 26 = Error |
| EVALUATION_RESULT_NAME | VARCHAR | "PASSED", "FAILED", or "ERROR" |
| STRING_SOURCE_VALUE | VARCHAR | String value from source |
| STRING_TARGET_VALUE | VARCHAR | String value from target |
| DATE_SOURCE_VALUE | VARCHAR | Date value from source |
| DATE_TARGET_VALUE | VARCHAR | Date value from target |
| OVERFLOW_FLAG | NUMBER | 0 = normal, 1 = truncated |

## Join Pattern

All four tables join on `TEST_EXECUTION_ID`. Within an execution:

- `TABLE_RESULTS` + `TABLE_RESULT_KEYS` join on `ROW_INDEX`
- `TABLE_RESULTS` + `TABLE_RESULT_VALUES` join on `ROW_INDEX`
- `TABLE_COLUMNS` defines what `COLUMN_SEQUENCE` means in KEYS and VALUES

```
-- Full join pattern
SELECT
    cols.TEST_SET_COLUMN_NAME,
    cols.TEST_SET_COLUMN_TYPE_NAME,
    res.ROW_INDEX,
    res.EVALUATION_RESULT_NAME AS ROW_RESULT,
    keys.RESULT_KEY,
    vals.NUMERIC_SOURCE_VALUE,
    vals.NUMERIC_TARGET_VALUE,
    vals.EVALUATION_RESULT_NAME AS VALUE_RESULT
FROM RESULTS.TEST_EXECUTION_TABLE_COLUMNS cols
LEFT JOIN RESULTS.TEST_EXECUTION_TABLE_RESULTS res
    ON res.TEST_EXECUTION_ID = cols.TEST_EXECUTION_ID
LEFT JOIN RESULTS.TEST_EXECUTION_TABLE_RESULT_KEYS keys
    ON keys.TEST_EXECUTION_ID = cols.TEST_EXECUTION_ID
    AND keys.ROW_INDEX = res.ROW_INDEX
    AND keys.COLUMN_SEQUENCE = cols.COLUMN_SEQUENCE
LEFT JOIN RESULTS.TEST_EXECUTION_TABLE_RESULT_VALUES vals
    ON vals.TEST_EXECUTION_ID = cols.TEST_EXECUTION_ID
    AND vals.ROW_INDEX = res.ROW_INDEX
    AND vals.COLUMN_SEQUENCE = cols.COLUMN_SEQUENCE
WHERE cols.TEST_EXECUTION_ID = 3913134;
```

## Pivoting Results into a Wide Format

Because key and value data is stored in long format (one row per column per result row), you typically need to pivot it into a human-readable wide format. The pivot pattern uses conditional aggregation:

### Pivot Key Columns

```
-- First, identify the column sequences for your execution
SELECT COLUMN_SEQUENCE, TEST_SET_COLUMN_NAME, TEST_SET_COLUMN_TYPE_NAME
FROM RESULTS.TEST_EXECUTION_TABLE_COLUMNS
WHERE TEST_EXECUTION_ID = 3913134
ORDER BY COLUMN_SEQUENCE;

-- Then pivot the keys
SELECT
    ROW_INDEX,
    MAX(CASE WHEN COLUMN_SEQUENCE = 1 THEN RESULT_KEY END) AS KEY_COL_1,
    MAX(CASE WHEN COLUMN_SEQUENCE = 2 THEN RESULT_KEY END) AS KEY_COL_2,
    MAX(CASE WHEN COLUMN_SEQUENCE = 3 THEN RESULT_KEY END) AS KEY_COL_3
FROM RESULTS.TEST_EXECUTION_TABLE_RESULT_KEYS
WHERE TEST_EXECUTION_ID = 3913134
GROUP BY ROW_INDEX
ORDER BY ROW_INDEX;
```

### Pivot Value Columns with Source/Target

```
SELECT
    ROW_INDEX,
    MAX(CASE WHEN COLUMN_SEQUENCE = 4 THEN NUMERIC_SOURCE_VALUE END) AS COL4_SOURCE,
    MAX(CASE WHEN COLUMN_SEQUENCE = 4 THEN NUMERIC_TARGET_VALUE END) AS COL4_TARGET,
    MAX(CASE WHEN COLUMN_SEQUENCE = 4 THEN EVALUATION_RESULT_NAME END) AS COL4_RESULT,
    MAX(CASE WHEN COLUMN_SEQUENCE = 5 THEN NUMERIC_SOURCE_VALUE END) AS COL5_SOURCE,
    MAX(CASE WHEN COLUMN_SEQUENCE = 5 THEN NUMERIC_TARGET_VALUE END) AS COL5_TARGET,
    MAX(CASE WHEN COLUMN_SEQUENCE = 5 THEN EVALUATION_RESULT_NAME END) AS COL5_RESULT
FROM RESULTS.TEST_EXECUTION_TABLE_RESULT_VALUES
WHERE TEST_EXECUTION_ID = 3913134
GROUP BY ROW_INDEX
ORDER BY ROW_INDEX;
```

### Complete Pivot with Keys and Values Joined

```
WITH pivoted_keys AS (
    SELECT
        ROW_INDEX,
        MAX(CASE WHEN COLUMN_SEQUENCE = 1 THEN RESULT_KEY END) AS ORDERKEY,
        MAX(CASE WHEN COLUMN_SEQUENCE = 2 THEN RESULT_KEY END) AS PARTKEY
    FROM RESULTS.TEST_EXECUTION_TABLE_RESULT_KEYS
    WHERE TEST_EXECUTION_ID = 3913134
    GROUP BY ROW_INDEX
),
pivoted_values AS (
    SELECT
        ROW_INDEX,
        MAX(CASE WHEN COLUMN_SEQUENCE = 3 THEN NUMERIC_SOURCE_VALUE END) AS AMOUNT_SOURCE,
        MAX(CASE WHEN COLUMN_SEQUENCE = 3 THEN NUMERIC_TARGET_VALUE END) AS AMOUNT_TARGET,
        MAX(CASE WHEN COLUMN_SEQUENCE = 3 THEN EVALUATION_RESULT_NAME END) AS AMOUNT_RESULT
    FROM RESULTS.TEST_EXECUTION_TABLE_RESULT_VALUES
    WHERE TEST_EXECUTION_ID = 3913134
    GROUP BY ROW_INDEX
)
SELECT
    k.ORDERKEY,
    k.PARTKEY,
    v.AMOUNT_SOURCE,
    v.AMOUNT_TARGET,
    v.AMOUNT_RESULT,
    r.EVALUATION_RESULT_NAME AS ROW_RESULT
FROM pivoted_keys k
JOIN pivoted_values v ON v.ROW_INDEX = k.ROW_INDEX
JOIN RESULTS.TEST_EXECUTION_TABLE_RESULTS r
    ON r.TEST_EXECUTION_ID = 3913134
    AND r.ROW_INDEX = k.ROW_INDEX
ORDER BY k.ROW_INDEX;
```

## Example Queries

### Find All Failed Rows for an Execution

```
SELECT
    r.ROW_INDEX,
    r.EVALUATION_RESULT_NAME,
    r.FAILED_VALUE_COUNT,
    r.COVERAGE_FLAG
FROM RESULTS.TEST_EXECUTION_TABLE_RESULTS r
WHERE r.TEST_EXECUTION_ID = 3913134
    AND r.EVALUATION_RESULT_NAME = 'FAILED'
ORDER BY r.ROW_INDEX;
```

### Unmatched Rows (Rows in Source but Not Target)

```
SELECT
    r.ROW_INDEX,
    k.COLUMN_SEQUENCE,
    c.TEST_SET_COLUMN_NAME,
    k.RESULT_KEY
FROM RESULTS.TEST_EXECUTION_TABLE_RESULTS r
JOIN RESULTS.TEST_EXECUTION_TABLE_RESULT_KEYS k
    ON k.TEST_EXECUTION_ID = r.TEST_EXECUTION_ID
    AND k.ROW_INDEX = r.ROW_INDEX
JOIN RESULTS.TEST_EXECUTION_TABLE_COLUMNS c
    ON c.TEST_EXECUTION_ID = r.TEST_EXECUTION_ID
    AND c.COLUMN_SEQUENCE = k.COLUMN_SEQUENCE
WHERE r.TEST_EXECUTION_ID = 3913134
    AND r.COVERAGE_FLAG = 1  -- Test-only (unmatched in source)
ORDER BY r.ROW_INDEX, k.COLUMN_SEQUENCE;
```

### Row Count Summary by Execution

```
SELECT
    te.TEST_NAME,
    te.DATE_COMPLETED,
    COUNT(*) AS TOTAL_ROWS,
    SUM(CASE WHEN r.EVALUATION_RESULT_NAME = 'PASSED' THEN 1 ELSE 0 END) AS PASSED_ROWS,
    SUM(CASE WHEN r.EVALUATION_RESULT_NAME = 'FAILED' THEN 1 ELSE 0 END) AS FAILED_ROWS,
    SUM(CASE WHEN r.COVERAGE_FLAG = 1 THEN 1 ELSE 0 END) AS UNMATCHED_SOURCE,
    SUM(CASE WHEN r.COVERAGE_FLAG = 2 THEN 1 ELSE 0 END) AS UNMATCHED_TARGET
FROM REPOSITORY.VW_DATASET_TEST_EXECUTIONS te
JOIN RESULTS.TEST_EXECUTION_TABLE_RESULTS r
    ON r.TEST_EXECUTION_ID = te.TEST_EXECUTION_ID
WHERE te.LATEST_TEST_EXECUTION_INDEX = 1
    AND te.TEST_NAME = 'Value Comparison - LINEITEM'
GROUP BY te.TEST_NAME, te.DATE_COMPLETED;
```

## Coverage Flag Reference

| COVERAGE_FLAG | Meaning |
| --- | --- |
| 1 | Unmatched — row exists only in the test (source) dataset |
| 2 | Unmatched — row exists only in the control (target) dataset |
| 3 | Matched — row exists in both datasets and was compared |

## Databricks Notes

- The pivot pattern using `MAX(CASE WHEN ... END)` is identical in Databricks SQL
- Databricks may have different internal metadata columns in place of `SF_DATE_CREATED` and `SF_IMPORT_FILENAME`
- For very large result sets, consider filtering on `COVERAGE_FLAG` or specific `COLUMN_SEQUENCE` values before pivoting to improve performance