Data Modeling Extensions: Federated Views Across Years

This page is the authoritative reference for the extensions to the Dorieh data-modeling DSL. The extensions add directives for building federated views over collections of tables that describe the same data but differ in column names and types from year to year. The core DSL — domains, tables, columns, create statements, validation — is documented in Data Modeling for Dorieh Data Platform; this page covers only the additional directives. Extensions described here are used by The mcr_combine_tables Module.

Combining multiple sources and optional columns

Source can be an array of columns rather than one column.

The following block will define a column named ssa3. The tool will look for columns named either cnty_cd, or bene_county_cd, or ssa_county to map to the new ssa3 column. If none of these three columns is found, a new column will be created and filled with NULL values.

Without optional: true, if an appropriate source column is not found, an exception will be raised.

- ssa3:
    optional: true
    description: Social Security Administration (SSA) three digit code for county
    reference: https://www.nber.org/research/data/ssa-federal-information-processing-series-fips-state-and-county-crosswalk
    source:
      - cnty_cd
      - bene_county_cd
      - ssa_county

The description and reference keys are documentation metadata: they are included in the generated data dictionary but do not cause any data to be fetched. The NBER page cited above documents the SSA-to-FIPS crosswalk that gives these county codes their meaning; the crosswalk itself is loaded into the database by the separate dorieh.platform.crosswalks.ssa2fips utility (see the “Linking with nomenclature” section of Data Modeling for Dorieh Data Platform).

Exclude

The exclude key removes specific tables from a federated view even when their names match the union’s inclusion patterns.

The following example creates a view by combining all tables matching either cms.mbsf_ab* or cms.mcr_bene_* pattern, but excluding the table named mbsf_ab_2015:

ps:
  create:
    type: view
    from:
      - cms.mbsf_ab*
      - cms.mcr_bene_*
    exclude:
      - mbsf_ab_2015

Cast

It is possible to define custom casts from one type to another. When tables to be combined into a single view have columns containing corresponding data but of different types, it is possible to cast all of them to the same type.

In the following example:

- dob:
    type: date
    cast:
      "character varying": "public.parse_date({column_name})"
      numeric: "to_date(to_char({column_name}, '00000000'), 'YYYYMMDD')"
      "*": "{column_name}::DATE"

If a source column is of type DATE, it will be left as is
If the source column is of numeric type, the code
```
to_date(to_char({column_name}, '00000000'), 'YYYYMMDD')
```
will be used to transform the source value
If the source column has type character varying, then the function public.parse_date will be called to transform the value
For all other types a simple PostgreSQL cast will be attempted

Note

The wildcard key * must be quoted ("*" or '*') to be valid YAML: an unquoted asterisk starts a YAML alias and fails to parse. Likewise, values beginning with {, such as "{column_name}::DATE", must be quoted because an unquoted brace starts a YAML flow mapping. Inside the cast expression, {column_name} is substituted with the name of the actual source column.

If the type of a source column differs from the declared target type and the cast mapping contains neither an entry for the source type nor a "*" entry, the tool raises an error stating that the cast is not defined.

Validating consistency of data across tables

There is no dedicated “consistency validation” directive in the DSL. Validating that data is consistent across the combined tables is achieved by composing features documented on this page and in the core reference:

the candidate source lists and casts described above harmonize columns that differ in name and type across years into single, uniformly typed columns of the federated view;
identifier columns and the {identifiers} token let a grouped view count records that disagree on the attributes that define an entity’s identity (see the discrepancies column of the _beneficiaries view in medicare.yaml);
disambiguation rules (defined in The Dorieh approach) computed in grouped views surface conflicting values instead of silently dropping them: for example, in medicare.yaml the beneficiary’s dob is defined as MIN(dob) while dob_latest is non-null only when the source records disagree, and the derived consistent_dob flag classifies each beneficiary as CONSISTENT, AMBIGUOUS or MISSING;
the invalid.records machinery of the core DSL journals records that fail primary key, referential integrity or duplicate checks into an audit table (see the “Invalid Record” section of Data Modeling for Dorieh Data Platform).

The qc_* views of medicare.yaml show all of these techniques working together over a federated view.