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

Column	Data Type	Nullable	Indexed	Description
address_id	bigint	No	Yes	Snowflake ID for address. Generated through expression:
canonical_address_id	bigint	Yes	Yes	Link to the canonical address record. Generated through expression:
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. Generated through expression:
is_invalid_address	boolean	Yes	No	Is the address invalid. Generated through expression:
address1	text	No	Yes	Address 1 should contain the building or house number, predirectionals, street name/PO Box, postdirectionals, and street suffix. Generated through expression:
address2	text	No	Yes	Address 2 is additional information to address 1 such as Floor 2 or Apartment 3. Generated through expression:
city	text	No	Yes	City name Generated through expression:
state_or_province	text	No	Yes	State or province name Generated through expression:
postal_code	text	No	Yes	Postal code, the 5 or 9 digit zip code in the United States. Generated through expression:
country_code	text	Yes	Yes	Two-digit country code such as US for United States or UK for United Kingdom. Generated through expression:
country_subdivision	text	No	Yes	Administrative or political divisions within a country such as state, province, territory, or administrative region. Generated through expression:
last_retrieved_date	date	Yes	No	- Generated through expression: