Skip to main content

rp.alias

Maps each identifier or name a source system uses for a service back to one rp_id.

One row per string that refers to a relying party. The same service is called different things in different systems, and this table maps each of those strings back to a single rp_id.

Use it to join tables that have no shared service identifier, such as ibm_verify_events_transformed.events_app_login_counts.application_id and the rp_name breakdown in the Google Analytics funnel tables.

Keywords: Relying party, RP, alias, application ID, application name, crosswalk, lookup, join

Provenance

Maintained by hand in the same CanadaLogin Relying Parties Google Sheet as rp.service, read every weekday by the same Lambda and written as a single Parquet file. Not partitioned, overwritten in place, current state only.

  • Updated: Weekdays, at 06:00 ET
  • Retention: While CanadaLogin exists
  • Steward: EDCP Data and Research Team
  • Contact: Colin Douglas
  • Location: s3://canadalogin-reporting-transformed/rp/alias/

Fields

Field Name Type Description
alias varchar The string that refers to the service, exactly as the source system writes it.
rp_id bigint The relying party this alias refers to. Joins to rp.service.rp_id.
source varchar Where the alias was first encountered, for example IBM Verify Application ID, IBM Verify Application Name, Google Analytics, or Call Centre Topics. Record keeping only, see the note below.
fetched_at timestamp(3) When the Lambda last read the source sheet, in UTC.

Notes

Join on alias alone. Do not filter on source. source records where the name was first seen, not which system the name belongs to. Filtering by it before joining will silently drop matches for any alias that turns up in both systems.

Join IBM Verify applications on the application ID. Every production IBM Verify application has two rows: one with its numeric application_id as the alias, and one with its name (source is IBM Verify Application ID and IBM Verify Application Name). Join application_id (or the raw tables’ applicationid) to alias. An ID does not change when a partner renames the application, so the join keeps working. A name does change, and a renamed application would stop matching its name row.

One service, several aliases. A service has as many rows here as it has strings. For example, MyCGC is MyCGC in Google Analytics but has one ID row (and one name row) for each of its two IBM Verify applications, MyCGC - CGC - .NET - Prod and MyCGC - CGC - Power Pages - Prod. Joining a fact table through this lookup is a many-to-one join, so aggregate after the join, not before.

Not every string in the analytics tables is an alias. The rp_name breakdown in the Google Analytics funnel tables also contains RESERVED_TOTAL (the all-services rollup), (not set), and the empty string. None of these are services, and an inner join drops them. If you are reconciling totals, exclude RESERVED_TOTAL explicitly rather than relying on the join to remove it.

Aliases are exact strings, not patterns. They are matched literally, with no trimming or case folding. A new IBM Verify application will not resolve until its ID is added to the source sheet by hand.