9.6 Combining sources

Never join sources using labels alone without checking definitions. Geographic names, age bands, fiscal years, service categories, and identifiers may differ. Preserve original fields and document every mapping.

Before a join, ask:

• Do the two sources describe the same population?
• Are time periods aligned?
• Are counts measured at the same frequency?
• Are categories mutually compatible?
• Are geographic boundaries from the same version?
• Are identifiers unique at the intended grain?
• What proportion of records fail to match?

After the join, verify row counts, duplicate keys, unmatched records, totals, and whether the join changed the unit of analysis.

Worked Example: Selecting Population Data for NVRW

NVRW wants population denominators for its four service areas. One source provides current municipal populations, while another provides projections for larger planning regions. Neither aligns perfectly with the organization’s postal-area service boundaries.

The team documents three options: use municipalities and accept incomplete alignment, aggregate NVRW activity to planning regions, or construct a postal-area crosswalk using a compatible census geography. It selects the planning-region approach for long-term projections because the boundaries and projected populations are available consistently. The loss of local detail is reported as a limitation.