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.

TEST_EXECUTION_TABLE_RESULTS

One row per result row in the test execution. Contains the overall evaluation for each 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).

TEST_EXECUTION_TABLE_RESULT_VALUES

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

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

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