address_seen

Stores every address we've ever encountered from any source in the database.

Primary Key:

• address_id

Checks:

• address_seen_address1_upper_check - (address1 = upper(address1))
• address_seen_address2_upper_check - (address2 = upper(address2))
• address_seen_address_id_check - ((canonical_address_id IS NULL) = (canonical_primary_address_only_id IS NULL))
• address_seen_city_upper_check - (city = upper(city))
• address_seen_country_code_check - (country_code <> ''::text)
• address_seen_country_subdivision_upper_check - (country_subdivision = upper(country_subdivision))
• address_seen_postal_code_upper_check - (postal_code = upper(postal_code))
• address_seen_state_or_province_upper_check - (state_or_province = upper(state_or_province))

Foreign Keys:

• canonical_address_id => address_canonical.canonical_address_id
• canonical_primary_address_only_id => address_canonical.canonical_address_id
• country_code => npi_country_lookup.code

Indexes:

• address_seen_address1_address2_city_state_or_province_posta_idx (Unique) - address1, address2, city, state_or_province, postal_code, country_code, country_subdivision
• address_seen_address1_idx - address1
• address_seen_canonical_address_id_idx - canonical_address_id
• address_seen_canonical_primary_address_only_id_idx - canonical_primary_address_only_id
• address_seen_country_code_idx - country_code

Column	Data Type	Nullable	Indexed	Description
address_id	bigint	No	Yes	Snowflake ID for address.
canonical_address_id	bigint	Yes	Yes	Link to the canonical address record.
canonical_primary_address_only_id	bigint	Yes	Yes	Link to the canonical primary address record. Primary address is usually the address of the building without apartment numbers, floors, etc. This makes it possible to identify addresses within the same building.
is_invalid_address	boolean	Yes	No	Is the address invalid.
address1	text	No	Yes	Address 1 should contain the building or house number, predirectionals, street name/PO Box, postdirectionals, and street suffix.
address2	text	No	Yes	Address 2 is additional information to address 1 such as Floor 2 or Apartment 3.
city	text	No	Yes	City name
state_or_province	text	No	Yes	State or province name
postal_code	text	No	Yes	Postal code, the 5 or 9 digit zip code in the United States.
country_code	text	Yes	Yes	Two-digit country code such as US for United States or UK for United Kingdom.
country_subdivision	text	No	Yes	Administrative or political divisions within a country such as state, province, territory, or administrative region.
last_retrieved_date	date	Yes	No	-