Logical Data Quality

Logical Data Quality answers a different question from Structural Data Quality: do the values and relationships in the mapped Input Layer data meet Tuva's expectations for analytically usable healthcare data? A Logical Data Quality test is one named expectation evaluated against records from one Input Layer Model. Examples include an invalid bill type code, a claim with no matching eligibility for its member, payer, plan, calendar month, and data_source, or a clinical record whose patient_id does not resolve to a record in patient for the same data_source.

How to Use Logical Data Quality

Build Logical Data Quality

Logical flag models also read Tuva Core terminology and provider-data assets. On a fresh installation, load the required Core seeds before building the Input Layer and running Structural Data Quality. Reload them when the asset version, seed schema, or loader contract changes. Routine code-only refreshes can reuse unchanged asset relations; the Core code and asset versions are configured independently.

Logical Data Quality assumes that the current Input Layer data is structurally ready. Complete the three build steps in How to Use Structural Data Quality, then query data_quality.structural. Continue only when every enabled Input Layer Model and data_source has pass for columns_exist, data_types_correct, table_populated, and primary_key_correct.

Run Logical Data Quality against the same current input data, dbt target, and domain-variable configuration used for that successful Structural Data Quality run:

dbt build --select tag:dq_logical

Once the current Input Layer Wrappers and required package assets exist, the dq_logical tag is the complete Logical Data Quality selector. It selects the internal models that calculate native-grain test results, the enabled public relations described below, and the dbt tests that validate the Logical pipeline. When failure keys are disabled, dbt excludes the optional logical_failure_keys relation. Set data_quality_enabled: true and the applicable domain variables before running the command. Do not add dbt ancestor selection to this command: rebuilding an Input Layer Wrapper after Structural Data Quality runs would invalidate the Structural results used as the Logical readiness check.

Trust the public relations described below only after this command completes successfully. A failed run can leave tables from an earlier successful run in place, and setting data_quality_enabled: false does not remove existing tables. Run builds serially when they use the same target schema. Tuva Core publishes current-state results; Tuva Data Quality Intelligence adds run provenance, refresh history, and remediation workflow.

Use the Data Quality Test Catalog

The Data Quality Test Catalog is generated from the explicit Logical Data Quality registry in the installed Tuva Core version. It is the complete list of built-in tests for that version. Use it to find a test's stable test_name, Input Layer Model, description, test type, and severity. The public logical_test_catalog relation described below contains the same metadata for the tests enabled in the current dbt run.

Understand Test Types

Every Logical Data Quality test has one test type, which identifies the kind of expectation it evaluates.

Test type	Meaning
missing	A required or analytically important value is null.
invalid	A value does not satisfy an accepted terminology, format, range, or other value constraint.
temporal	A date or timestamp is outside an accepted range or occurs in an invalid order relative to another date or timestamp.
referential	An identifier or combination of identifiers does not resolve to the expected record in a related Input Layer Model for the same data_source.
consistency	Values that should agree across fields or records conflict, or a value does not agree with the context in which it appears.

The test type describes what the test evaluates. It does not describe the impact of a failure.

Understand Severities

Every test also has one severity, which describes the expected impact when records fail that test.

Severity	Meaning
S1	The affected part of Tuva cannot run correctly.
S2	The affected analytics may be materially unreliable.
S3	The issue has a minor or secondary analytical impact.

Severity is independent of the number or percentage of records that fail. When reviewing results, start with S1 failures, then review S2 and S3 failures.

Date Type, Value Range, and Serialized Format

Tuva applies a complete, nonduplicative contract to all 72 Input Layer DATE and timestamp fields:

1. The warehouse column must have its declared native SQL DATE or timestamp type. Structural Data Quality validates this physical type.
2. Exactly one S2 Logical invalid test per temporal field validates its inclusive semantic range.
3. Textual dates use ISO 8601 YYYY-MM-DD; timestamps retain their time component and are cast to the declared native timestamp type. Once a value has its native type, Data Quality validates its value rather than applying a string-format regular expression.

Semantic profile	Fields	Inclusive range
Historical person and clinical history	Eligibility and patient birth/death; condition recorded/onset/resolved; immunization occurrence	1900-01-01 through the current date
Future-known coverage	Eligibility enrollment start and finite end dates	1900-01-01 through the current date plus 12 calendar months
Future-bookable care	Appointment start/end timestamps	2000-01-01 through the current date plus 12 calendar months
Transactional and operational activity	Medical and pharmacy claims (including file and procedure dates), encounters, medications, observations, procedures, lab results, and every ingest_datetime	2000-01-01 through the current date

The timestamp checks compare each timestamp's calendar-date portion. Range boundaries are evaluated from the warehouse current date when Logical Data Quality runs. The fixed 1900–2100 member-month spine is a separate processing boundary, not a universal data-validity rule.

Nullability is a separate field-level contract. Relative-date, consistency, and referential tests remain when they answer a different question: for example, enrollment start after end, condition onset after resolution, or a claim date with no matching eligibility span. An invalid, out-of-spine, or post-tuva_last_run claim date is not applicable to the eligibility-match test, so one bad date does not also produce a misleading coverage failure.

The canonical value for an open eligibility.enrollment_end_date is null. Tuva continues to normalize the backward-compatible 9999-12-31 ingestion alias to null downstream. Null and that alias are not applicable to the finite-end S2 Logical range check.

Public Logical Data Quality Relations

Tuva exposes exactly four stable Logical Data Quality relations. Three are built when Data Quality is enabled, and the failure-key relation is built only when its separate variable is enabled. A test's native grain is the unit counted by that test, such as a medical claim line or a person. A flag table is an internal Warehouse Table with one row per native-grain record and one result column per test that it calculates.

Public relation	Row grain	Fields
data_quality.logical_test_results	One enabled test and one data_source present in that test's flag table	data_source, input_table_name, test_name, display_name, description, grain, flag_table_name, flag_column_name, test_type, severity, total_row_count, tested_count, failed_count, passed_count, not_applicable_count
data_quality.logical_test_catalog	One enabled test	test_name, display_name, description, input_model_name, input_table_name, flag_model_name, flag_table_name, flag_column_name, grain, key_columns, test_type, severity
data_quality.logical_test_input_columns	One enabled test and one affected Input Layer field	test_name, input_table_name, input_column_name
data_quality.logical_failure_keys	One failed native-grain row and one test	data_source, input_table_name, test_name, grain, key_columns, key_values_format, key_values

These four relations are the stable Logical Data Quality integration contract. The flag tables described in How Logical Data Quality Works are version-specific investigation surfaces. Their names, schemas, and groupings may change between releases.

In the catalog, input_table_name identifies the connector-owned Input Layer Model, such as medical_claim. input_model_name identifies its Tuva Core Input Layer Wrapper, such as input_layer__medical_claim. flag_model_name is the dbt model that calculates tests for the same Input Layer Model and native grain. flag_table_name and flag_column_name identify its warehouse relation and the column that stores one test's result. key_columns lists, in order, the fields that identify a row at the test's native grain.

The following query returns every field in the enabled test catalog:

select
    test_name,
    display_name,
    description,
    input_model_name,
    input_table_name,
    flag_model_name,
    flag_table_name,
    flag_column_name,
    grain,
    key_columns,
    test_type,
    severity
from data_quality.logical_test_catalog
order by
    input_table_name,
    test_name;

Query Logical Data Quality Results

The following query returns active failures and calculates a failure rate from the stored counts:

select
    data_source,
    input_table_name,
    test_name,
    display_name,
    grain,
    test_type,
    severity,
    total_row_count,
    tested_count,
    failed_count,
    passed_count,
    not_applicable_count,
    case
        when tested_count = 0 then null
        else 1.0 * failed_count / tested_count
    end as failure_rate
from data_quality.logical_test_results
where failed_count > 0
order by
    severity,
    input_table_name,
    test_name,
    data_source;

failure_rate is calculated by the query; it is not stored in logical_test_results. Each count has the following exact meaning:

Count	Meaning
total_row_count	All rows in the flag table for the data_source, measured at the test's native grain.
tested_count	Rows for which the test applied, represented by flags equal to 0 or 1.
failed_count	Tested rows that failed, represented by flags equal to 1.
passed_count	Tested rows that passed, represented by flags equal to 0.
not_applicable_count	Rows to which the test did not apply, represented by null flags.

The counts always satisfy these two relationships:

total_row_count = tested_count + not_applicable_count
tested_count = failed_count + passed_count

Counts remain at the test's native grain. For example, counts produced by a medical-claim-line test represent claim lines, not claims. Do not add or average counts or failure rates from different grains.

Query Affected Input Layer Fields

Use logical_test_input_columns to find the Input Layer fields associated with a test. An affected Input Layer field is a field whose value a test failure calls into question and that a user may need to correct. It does not include a field used only to determine whether the test applies. Each row names a real field in the Input Layer contract; Tuva does not use a wildcard or infer the field from test_name.

select
    test_name,
    input_table_name,
    input_column_name
from data_quality.logical_test_input_columns
where test_name =
    'medical_claim__paid_date_before_claim_end_date'
order by input_column_name;

Investigate Failed Records

By default, the result tables contain counts rather than record identifiers. To publish failed native-grain keys, enable the optional failure-key table and rebuild Logical Data Quality from the same current flag tables:

dbt build --select tag:dq_logical \
  --vars '{"enable_data_quality_failure_keys": true}'

Then query the failed keys for a specific test:

select
    data_source,
    input_table_name,
    test_name,
    grain,
    key_columns,
    key_values_format,
    key_values
from data_quality.logical_failure_keys
where data_source = 'example_claims'
  and test_name =
    'medical_claim__paid_date_before_claim_end_date'
order by key_values;

key_columns lists the native-grain key fields in order. key_values_format identifies how the values are encoded. In percent_escaped_v1, key_values contains one component for each field in key_columns, in the same order, separated by |. N represents a null value. V begins a non-null value, including an empty string. Within a non-null value, % is encoded as %25 and | as %7C. For example:

key_columns      = claim_id,claim_line_number,data_source
key_values       = Vclaim%7C100%25|V1|Vexample_claims
decoded values   = claim|100%, 1, example_claims

To decode the format, split on |, interpret N as null, remove the leading V from every other component, replace %7C with |, and then replace %25 with %. Decoding in that order preserves original text that contains a literal sequence such as %7C.

The table can be large and can contain sensitive identifiers, so apply the same access controls used for the underlying Input Layer data. If the variable is later disabled, an older logical_failure_keys table may remain in the warehouse; trust it only when the current successful run enabled it.

You can also inspect a version-specific flag table and join its native-grain keys to the Warehouse Table or View produced by the corresponding Input Layer Wrapper. For example:

select source_rows.*
from input_layer.input_layer__medical_claim as source_rows
inner join data_quality.medical_claim_line_flags as flags
    on source_rows.claim_id = flags.claim_id
   and source_rows.claim_line_number = flags.claim_line_number
   and source_rows.data_source = flags.data_source
where flags.data_source = 'example_claims'
  and flags.paid_date_before_claim_end_date = 1;

If tuva_schema_prefix is configured, replace data_quality with <tuva_schema_prefix>_data_quality and input_layer with <tuva_schema_prefix>_input_layer. After finding the cause, correct the connector or source data, rebuild the Input Layer Models and Wrappers, rerun Structural Data Quality, and rerun Logical Data Quality.

How Logical Data Quality Works

data_quality.logical_test_results is produced by a dbt pipeline that evaluates enabled Logical Data Quality tests against Warehouse Tables or Views produced by the Input Layer Wrappers.

Before describing the pipeline, we define the terms used in this section:

• A native grain is the unit represented by one row in a flag table, such as one medical claim line, one medical claim, one eligibility span, or one person.
• Key columns are the fields that identify one row at the native grain within a data_source.
• A flag model is a Tuva Core dbt model that calculates one or more Logical Data Quality tests for one Input Layer Model at one native grain.
• A flag table is the Warehouse Table created by a flag model.
• A flag is the integer or null value that represents one test's result for one row in a flag table.
• An eligibility-derived member month is one calendar month covered by an eligibility span for one exact person_id, member_id, payer, plan, and data_source combination.
• A test is applicable to a flag-table row when the test has enough relevant information and the row satisfies any condition that limits which records the test evaluates.
• Source-specific means Tuva calculates and reports results separately for each data_source.

1. How Tuva Defines Logical Data Quality Tests

Tuva Core keeps every built-in Logical Data Quality test in one explicit registry. For each test, the registry declares:

• the stable test_name, display name, and description;
• one test type and one severity; and
• every affected Input Layer field: each field whose value a failure calls into question, excluding fields used only to determine applicability.

The registry also groups tests that share one flag model. Each group declares:

• the connector-owned Input Layer Model and its Tuva Core Input Layer Wrapper;
• the flag model and flag-table name;
• the native grain and ordered key columns; and
• the tests implemented by that flag model.

During dbt compilation, dq_logical_test_manifest() joins the group-level facts to each explicit test definition, associates the test with its flag column, and creates one complete manifest row per test. Tuva does not infer a display name, description, test type, severity, or affected field from the spelling of test_name. The manifest supplies the definitions used to generate aggregate result SQL, validate flag-table outputs, publish the public metadata relations, and build the Data Quality Test Catalog. The flag models still contain the SQL expressions that decide whether each native-grain row passes, fails, or is not applicable; the registry supplies the explicit contract for those expressions and their outputs.

Before generating model SQL, Tuva validates the registry. Among other requirements, each test must appear exactly once, each test and group must refer to one another, each test type and severity must be allowed, each group must include data_source in its declared keys, and each affected field must exist in the corresponding Input Layer contract. Invalid definitions stop dbt with a compilation error instead of silently receiving inferred metadata.

2. How Tuva Determines Which Tests to Evaluate

The dq_enabled_input_layer_model_names() macro determines which Input Layer Wrappers are enabled by the connector's domain variables. Claims enable eligibility, medical_claim, and pharmacy_claim. Clinical enables the clinical Input Layer Models. Provider attribution is added only when claims and provider attribution are both enabled.

The dq_enabled_logical_test_manifest() macro retains only test definitions whose Input Layer Wrapper is enabled. As a result, Tuva does not build flag tables or publish test metadata for disabled Input Layer Models.

3. How Tuva Builds Flag Tables at the Native Grain

Tuva builds one flag model for each combination of Input Layer Model and native grain represented in the enabled manifest. Tests share a flag model only when they evaluate the same Input Layer Model at the same native grain. This is why, for example, medical-claim-line tests and medical-claim tests use separate flag tables even though both begin with medical_claim data.

A flag model reads the Warehouse Table or View produced by its Input Layer Wrapper. When a test evaluates a relationship or terminology value, the flag model may also read a related Wrapper output, a terminology table, or provider reference data. The resulting flag table contains only the ordered key columns for its native grain, including mandatory data_source, and one flag column per registered test in that flag model. It does not copy other source fields into the Data Quality schema.

Every calculation over Input Layer records remains within one data_source. Joins between Input Layer Models match data_source; grouped calculations group by it; and window calculations partition by it. Tuva therefore does not compare, combine, or count Input Layer records from different sources. Shared terminology and provider-data tables are reference datasets, not Input Layer records, so their lookups are not partitioned by data_source.

For example, the medical-claim eligibility test runs at medical-claim-line grain. It derives year_month from the first populated date in this order: claim_line_start_date, claim_start_date, admission_date. It then requires an eligibility-derived member month with the same person_id, member_id, payer, plan, data_source, and derived year_month. The pharmacy-claim eligibility test also runs at line grain and requires the same exact key, using paid_date to derive year_month.

Both eligibility tests mirror member-month construction: the derived claim month must be within the supported 190001 through 210012 month spine and must not be later than the calendar month containing the date portion of tuva_last_run. A claim date outside those month boundaries cannot be reported as matching an eligibility span that produces no member-month row.

The installed flag-table inventory can be queried without relying on a static list in the documentation:

select
    input_table_name,
    flag_table_name,
    grain,
    key_columns,
    count(*) as test_count
from data_quality.logical_test_catalog
group by
    input_table_name,
    flag_table_name,
    grain,
    key_columns
order by
    input_table_name,
    flag_table_name;

4. How Tuva Represents Each Record's Test Result

Each flag uses the same three-value contract:

Flag	Meaning	Included in tested_count?
1	The test applied and the flag-table row failed.	Yes
0	The test applied and the flag-table row passed.	Yes
null	The test did not apply to the flag-table row.	No

The dq_logical_int_flag_sql() macro implements this contract from two SQL expressions: one determines whether the test applies, and the other determines whether an applicable row fails. Applicability is therefore part of each test's SQL logic; it is not inferred during aggregation.

For example, a bill type test may apply to institutional claim lines and not to professional claim lines. An institutional line with a missing bill type has a flag of 1, an institutional line with a bill type has a flag of 0, and a professional line has a null flag.

Terminology tests likewise apply only when Tuva has the corresponding reference dictionary. For example, a clinical source-code test can validate a recognized standard code system and return null for a source-system-specific code-system name. A standard code system is also not applicable when Tuva Core does not include a complete reference dictionary for that field, such as an HCPCS field that can contain CPT codes or the RxNorm and ATC medication fields. A null flag in these cases means Tuva did not evaluate the code against a complete dictionary; it does not mean the code passed a terminology check.

Logical not applicable and Structural not evaluated have different meanings. Logical not applicable describes a particular record that is outside a test's defined applicability. Structural not evaluated describes a check that could not run because a prerequisite failed.

5. How Tuva Validates Flag Tables and Result Counts

Tuva validates both the registered contract and the materialized relations. The dbt tests verify that:

• each flag table contains exactly its registered key and flag columns, with no missing or extra source-context columns;
• every registered flag column uses an integer-family warehouse type;
• each flag table has no duplicate rows for its declared key columns;
• every affected-field mapping names a field in the enabled Input Layer contract;
• total_row_count equals tested_count + not_applicable_count and tested_count equals failed_count + passed_count for every result row;
• every public Logical relation has exactly its documented fields;
• when failure keys are enabled, their row counts equal failed_count for each test and data_source; and
• tag:dq_logical includes every Data Quality model required by a selected Logical model or validation test.

The two count identities also detect non-null flag values outside 0 and 1. Such a value is neither tested nor not applicable, so the first identity cannot hold.

The flag-table grain test verifies key uniqueness; it does not independently verify that every key value is non-null. The required Structural Data Quality workflow verifies primary-key completeness and uniqueness before Logical Data Quality results are trusted.

6. How Tuva Aggregates Results by data_source

The Logical aggregation models group the enabled test definitions by flag model and publish the combined counts in data_quality.logical_test_results. For each flag model, Tuva groups the flag-table rows by data_source and calculates the five counts for every flag in one grouped query. Each count is a 64-bit integer so large source populations and downstream sums do not overflow 32-bit integer limits. Tuva then combines those compact source-level counts with the corresponding test definitions to produce one result row per test and data_source.

This design references each enabled flag table once while aggregating Logical results, rather than once for every test in that flag table. The result values remain identical because every count is calculated from the same tri-state flags.

If a flag table contains rows for a data_source but every flag for one test is null, Tuva publishes the test row with tested_count = 0 and not_applicable_count = total_row_count. If a flag table contains no rows for a data_source, it cannot produce a Logical result row for that source. In that case, Structural Data Quality has already reported that the relevant Input Layer Model is not populated, so the Logical results are not trusted.

7. How Tuva Publishes Metadata and Failure Evidence

Tuva creates the four public Logical relations through three related pipeline branches:

1. logical_test_catalog and logical_test_input_columns are generated from the enabled compile-time manifest. They do not scan the flag tables.
2. logical_test_results depends on every enabled flag table and contains the source-specific aggregate counts.
3. When enabled, logical_failure_keys reads the flag tables and emits the encoded native-grain keys for rows whose flag equals 1.

The optional failure-key branch does not derive its rows from the aggregate result table because that table no longer contains individual record keys. Its public percent_escaped_v1 encoding preserves key order, nulls, empty strings, percent signs, and separators without adding source fields or full healthcare records to the relation.

Native-grain flag tables remain queryable for investigation in the installed Tuva Core version, but dbt marks them as protected: they are package implementation relations, not stable public interfaces. Their names and schemas can change with the installed version.