AWWA G400 Audit Field Mapping for the UN Schema

An AWWA G400 asset-management audit asks a utility to produce a specific set of figures — inventory by material, condition distribution, criticality, and remaining service life — and every one of those figures is only unambiguous if each audit field resolves to exactly one attribute in the system of record. When the crosswalk between the audit template and the geodatabase lives in an analyst’s head, two engineers report the same field from two different attributes, an abandoned segment is counted in one submission and dropped from the next, and a condition grade is read from a legacy column that was superseded three migrations ago. The audit field mapping documented here removes that ambiguity by binding each G400 field to a named Utility Network feature-class attribute, its coded-value domain, and its authoritative source system. It is the lookup the report generator reads at runtime in generating AWWA G400 compliance reports with Python, and it operationalizes the binding discipline established across regulatory compliance reporting automation within the wider practice of asset lifecycle and maintenance automation. Treat it as the contract that makes a G400 export reproducible.

Environment Prerequisites

Before adopting the mapping against production data, confirm the schema it references actually exists in your geodatabase and matches these names and domains:

• ArcGIS Pro 3.2+ with a Standard or Advanced license so the utility-network attribute rules and coded-value domains referenced below are enforced at the data layer.
• Python 3.11 in an isolated conda environment with geopandas>=1.0 and pandas>=2.0 pinned, so the validation helper below behaves identically across engineers.
• The water-main and service-line feature classes carrying the attributes named in the table — attribute names vary between UN data models, so reconcile the left-hand mapping column against your own schema before trusting an export.
• Coded-value domains loaded for MATERIAL, LIFECYCLESTATUS, CONDITIONGRADE, CRITICALITY, and LININGTYPE, with the permitted values matching the domain column below.
• A reconciled, read-only snapshot to validate against, never an actively edited version, so the attribute coverage you measure reflects committed data.

Schema-Aware Validation Protocol — Run Before the Export

A mapping is only trustworthy if the schema underneath it conforms. Work this ordered checklist before relying on the crosswalk for a submission; the earliest item is the most frequent culprit.

1. Confirm every mapped attribute exists. A G400 field bound to an attribute that a migration renamed produces a silently empty column, not an error. Check the presence of each attribute in the table against the live feature class before exporting.
2. Verify domain conformance for each coded field. Distinct values in MATERIAL, LIFECYCLESTATUS, CONDITIONGRADE, and CRITICALITY must be subsets of the domains below. A trailing space or a legacy code shifts a subtotal without raising an error.
3. Distinguish computed fields from stored fields. Some G400 fields — remaining service life, installation decade — are derived, not stored. Confirm the derivation reads the stored source attribute named in the notes column rather than a stale cached copy.
4. Check non-null coverage on non-nullable audit fields. Material, install date, and lifecycle status must be fully populated for reportable assets; quantify nulls before the export rather than discovering the gap in the audit.
5. Pin the source system per field. Where a field can be sourced from either the GIS register or the CMMS, the table’s source column is authoritative. A condition grade pulled from the CMMS when the mapping specifies the register produces a defensible-looking but non-reproducible figure.

G400 Audit Field to UN Schema Crosswalk

The table below is the reference mapping. Each row binds one AWWA G400 audit field to the Utility Network attribute that supplies it, the coded-value domain or unit that constrains it, the authoritative source system, and the notes an engineer needs to apply it correctly. Read the source column as binding: a field is sourced from exactly one system, and where two systems hold the value, the named one wins.

Two conventions keep the crosswalk unambiguous. Derived fields never carry a stored attribute, because a cached copy of remaining service life or installation decade goes stale the moment the clock advances; the mapping points them at their stored sources so they are recomputed every run. And the source column is exclusive — a field held in both the register and the CMMS is read from the one named here — so the same G400 field resolves identically regardless of which engineer runs the export. The small diagram below shows the four attributes that carry the most reporting weight and how each resolves from a single source through its domain.

The single-row helper below validates a GeoDataFrame against the mapping: it confirms each required attribute is present, checks coded-value conformance for the domain-constrained fields, and returns a structured report the export pipeline can gate on. Keeping the mapping as data — a dictionary of attribute to permitted-value set — means a schema change is a one-line edit, not a code rewrite.

import geopandas as gpd
from dataclasses import dataclass, field

# Domain-constrained fields from the crosswalk; extend as the schema evolves.
G400_DOMAINS: dict[str, set] = {
    "MATERIAL": {"DI", "CI", "PVC", "HDPE", "STEEL", "AC", "OTHER"},
    "LIFECYCLESTATUS": {"IN_SERVICE", "ABANDONED", "RETIRED", "PROPOSED"},
    "CONDITIONGRADE": {1, 2, 3, 4, 5},
    "CRITICALITY": {"LOW", "MEDIUM", "HIGH", "CRITICAL"},
}
G400_REQUIRED = ["ASSETGROUP", "MATERIAL", "DIAMETER_MM", "LENGTH_M",
                 "INSTALLDATE", "LIFECYCLESTATUS", "CONDITIONGRADE",
                 "CRITICALITY", "PRESSUREZONE"]


@dataclass
class MappingCheck:
    ok: bool = True
    missing_fields: list[str] = field(default_factory=list)
    domain_violations: dict[str, list] = field(default_factory=dict)


def validate_against_mapping(gdf: gpd.GeoDataFrame) -> MappingCheck:
    """Validate a GeoDataFrame against the G400-to-UN crosswalk.

    Checks that every required attribute is present and that each
    domain-constrained field holds only permitted coded values.
    Returns a structured MappingCheck; ok is False on any failure.
    """
    result = MappingCheck()
    result.missing_fields = [c for c in G400_REQUIRED if c not in gdf.columns]
    if result.missing_fields:
        result.ok = False

    for column, permitted in G400_DOMAINS.items():
        if column not in gdf.columns:
            continue
        offenders = sorted(set(gdf[column].dropna().unique()) - permitted)
        if offenders:
            result.domain_violations[column] = offenders
            result.ok = False
    return result


if __name__ == "__main__":
    sample = gpd.GeoDataFrame({
        "ASSETGROUP": ["Water Main"], "MATERIAL": ["CI"], "DIAMETER_MM": [150],
        "LENGTH_M": [42.7], "INSTALLDATE": ["1962-04-01"],
        "LIFECYCLESTATUS": ["IN_SERVICE"], "CONDITIONGRADE": [3],
        "CRITICALITY": ["HIGH"], "PRESSUREZONE": ["Z1"],
    })
    check = validate_against_mapping(sample)
    print("ok:", check.ok, "| violations:", check.domain_violations)

Production Deployment Pattern

A crosswalk is only durable if it is enforced rather than trusted. Promote the mapping into the reporting workflow as follows:

1. Version-control the mapping as data. Keep G400_DOMAINS and G400_REQUIRED in a checked-in module or a configuration file, tagged with a bindings version, so every report records exactly which crosswalk produced it and a schema change is reviewable in a diff.
2. Wire the validator as a build gate. Run validate_against_mapping on the extracted register in CI on every dataset commit and on the nightly sync, and fail the build when a required attribute is missing or a domain is violated, so a schema drift is caught before it reaches a submission.
3. Reconcile the mapping after every schema migration. When a UN data-model upgrade renames or retypes an attribute, update the crosswalk in the same change set, so the mapping never lags the schema it describes.
4. Publish the crosswalk to the CMMS. Share the attribute-to-field bindings with the work-management system so a condition grade or criticality resolves identically in GIS, the G400 report, and the maintenance backlog.
5. Stamp the bindings version into the audit record. Record the crosswalk version alongside the snapshot and report hashes so a reviewer can regenerate a figure against the exact mapping that produced it.

Conclusion

This crosswalk turns an AWWA G400 audit from a set of ambiguous field names into a set of exact bindings, each resolving to one Utility Network attribute, one domain, and one authoritative source. Enforcing it with the validation helper keeps every G400 figure reproducible, catches schema drift before it corrupts a submission, and gives a reviewer the provenance to regenerate any number on demand. The natural next step is to extend the same crosswalk pattern to the EPA service-line inventory, so a single versioned mapping governs both water submissions. Adopt the mapping as a version-controlled contract, not a convention, so every engineer’s export resolves each field the same way.

Related

• Up to the parent topic: Regulatory Compliance Reporting Automation
• Up to the section: Asset Lifecycle & Maintenance Automation
• Generating AWWA G400 Compliance Reports with Python — the report pipeline that reads this mapping at runtime.
• Lifecycle State Machines for Utility Assets — the source of the lifecycle-status domain this crosswalk enforces.

For authoritative reference, consult the AWWA standards program and the GeoPandas documentation.