Canadian Provider Registry

About the Dataset

The Canadian Provider Registry is a unified, queryable directory of ~93,000 physicians across Ontario, Quebec, and Alberta — Canada's three largest provinces covering 75% of all Canadian physicians. It provides a consistent schema for provider lookup, territory planning, and workforce analysis. Updated weekly so your data always reflects current licensure status.

Get Full Access | Snowflake Marketplace | Free Trial

Quick Access

Tables: PROVIDERS, DATA_DICTIONARY Sources: 3 official provincial physician registries Update Frequency: Weekly (Mondays) Geography: Ontario, Quebec, Alberta (~75% of Canadian physicians)

Overview

The Canadian Provider Registry combines three provincial physician registries into a single queryable table:

• Ontario — ~53,000 physicians

• Quebec — ~27,000 physicians

• Alberta — ~13,000 physicians

All three provinces are normalized into a common PROVIDERS schema with consistent column names, uppercase cities, and standardized registration status values.

Metadata Tables

Table	Purpose
DATA_DICTIONARY	Column descriptions for all tables — queryable reference for schema discovery

Entity Relationship Diagram

Canadian Provider Registry ERD

The PROVIDERS table contains all physicians across three provinces. Each row is uniquely identified by provider_id (province code + registration number).

Data Tables

PROVIDERS

Unified directory of ~93,000 Canadian physicians across Ontario, Quebec, and Alberta with normalized specialties and consistent schema.

Key Features:

• ~93,000 rows (one per physician across 3 provinces)

• provider_id = province code + registration number (e.g., ON-12345, QC-67890)

• Normalized specialty names in standard English (Family Medicine, Psychiatry, etc.)

• Registration status tracking (Active, Inactive, Suspended, Deceased)

• Weekly refresh

Column Reference

Column	Type	Description
provider_id	VARCHAR	Unique provider identifier: province code + registration number (e.g., ON-12345, QC-67890). Primary key.
province	VARCHAR	Full province name: Ontario, Alberta, or Quebec
registration_number	VARCHAR	Province-specific registration number. Unique identifier for Ontario and Quebec; derived hash for Alberta.
first_name	VARCHAR	Provider first name
last_name	VARCHAR	Provider last name
city	VARCHAR	Practice city (uppercase)
province_code	VARCHAR	Two-letter province abbreviation: ON, AB, QC. Use for JOINs to other Canadian datasets.
postal_code	VARCHAR	Postal code. Available for Ontario (~85%) and Quebec (~95%). Not available for Alberta.
phone	VARCHAR	Phone number. Available for Ontario and Quebec; partial coverage for Alberta.
fax	VARCHAR	Fax number. Available for Ontario and Quebec; not available for Alberta.
address	VARCHAR	Street address of primary practice location. Available for Ontario (~85%) and Quebec (~95%). Not available for Alberta.
specialty_raw	VARCHAR	Raw specialty string as reported by the province. Format varies by province.
specialty	VARCHAR	Primary medical specialty normalized to standard English (e.g., Family Medicine, Psychiatry, Cardiology). Available for Ontario (~76%), Alberta (~99%), and Quebec (~95%).
registration_status	VARCHAR	Registration status: Active, Inactive, Suspended, or Deceased. Coverage varies by province.
created_at	TIMESTAMP	When the source data was loaded into the warehouse
updated_at	TIMESTAMP	When dbt last rebuilt this table

Data Coverage by Province

Field	Ontario	Quebec	Alberta
Rows	~53,000	~27,000	~13,000
Registration number	Unique ID	Unique ID	Derived hash
City	~85%	~99%	100%
Postal code	~85%	~95%	Not available
Phone	~85%	~95%	Partial
Address	~85%	~95%	Not available
Specialty	~76%	~95%	~99%
Registration status	100%	~100%	All assumed Active

Data Quality

Standardization

• Unified schema: Three different provincial formats normalized into a single table with consistent column names

• Specialty normalization: Province-specific specialty formats mapped to standard English names (e.g., Family Medicine, Psychiatry, Cardiology)

• City standardization: All city names uppercased for consistent filtering

• Registration status: Mapped to standard values (Active, Inactive, Suspended, Deceased) across provinces

• Name parsing: All names parsed into separate first_name and last_name columns

Known Limitations

• Alberta registration numbers: Alberta does not publish unique physician registration numbers. provider_id uses a derived identifier as a surrogate key.

• Null cities: Some records — particularly Inactive and Deceased physicians — have NULL city, address, and phone fields. This reflects providers whose practice information is not publicly available.

• Specialty coverage: Not all providers have a specialty mapped. Providers without a listed specialty appear as NULL.

Getting Started

Platform Schema Reference

This dataset is available on both Snowflake and Databricks. Queries use schema-only references — the database is already set by the share or catalog context:

Platform	Schema	Example
Snowflake	DWV	DWV.PROVIDERS
Databricks	canada_provider_registry_dwv	canada_provider_registry_dwv.providers

Discover Available Data

Snowflake

-- Browse the data dictionary to understand available columns
SELECT table_name, column_name, data_type, column_description
FROM DWV.DATA_DICTIONARY
ORDER BY table_name, ordinal_position;

Databricks

Find Specialists by City

Snowflake

-- Search for cardiologists in Toronto
SELECT provider_id, first_name, last_name, city,
       province, specialty, phone
FROM DWV.PROVIDERS
WHERE UPPER(city) = 'TORONTO'
  AND specialty ILIKE '%cardiol%'
ORDER BY last_name, first_name
LIMIT 25;

Databricks

Physician Counts by Province

Snowflake

-- See how many physicians are in each province
SELECT province, COUNT(*) AS physician_count
FROM DWV.PROVIDERS
GROUP BY province
ORDER BY physician_count DESC;

Databricks

Search by Name

Snowflake

-- Find a physician by last name across all provinces
SELECT provider_id, first_name, last_name, city, province,
       specialty, phone
FROM DWV.PROVIDERS
WHERE last_name ILIKE 'Smith%'
ORDER BY province, city
LIMIT 25;

Databricks

Specialty Distribution by Province

Snowflake

-- Compare specialist availability across provinces
-- Identifies which specialties are concentrated vs distributed
SELECT
    specialty,
    COUNT(*) AS total_providers,
    COUNT(CASE WHEN province = 'Ontario' THEN 1 END) AS ontario,
    COUNT(CASE WHEN province = 'Quebec' THEN 1 END) AS quebec,
    COUNT(CASE WHEN province = 'Alberta' THEN 1 END) AS alberta
FROM DWV.PROVIDERS
WHERE specialty IS NOT NULL
  AND registration_status = 'Active'
GROUP BY specialty
ORDER BY total_providers DESC
LIMIT 30;

Databricks

Territory Planning — Physicians per City

Snowflake

-- Top cities by active physician count for sales territory planning
SELECT
    city,
    province,
    province_code,
    COUNT(*) AS physician_count,
    COUNT(DISTINCT specialty) AS specialty_count
FROM DWV.PROVIDERS
WHERE registration_status = 'Active'
  AND city IS NOT NULL
GROUP BY city, province, province_code
ORDER BY physician_count DESC
LIMIT 50;

Databricks

Workforce Gap Analysis

Snowflake

-- Cities with few active physicians relative to specialty coverage
-- Potential underserved areas for workforce planning
SELECT
    city,
    province,
    COUNT(*) AS active_physicians,
    COUNT(CASE WHEN specialty = 'Family Medicine' THEN 1 END) AS family_medicine,
    COUNT(CASE WHEN specialty = 'Psychiatry' THEN 1 END) AS psychiatry,
    COUNT(CASE WHEN specialty = 'Internal Medicine' THEN 1 END) AS internal_medicine
FROM DWV.PROVIDERS
WHERE registration_status = 'Active'
  AND city IS NOT NULL
GROUP BY city, province
HAVING COUNT(*) >= 10
ORDER BY active_physicians ASC
LIMIT 50;

Databricks

Ready to access Canadian Provider Registry data?

Platform	Action
Snowflake	Get on Marketplace

Start 14-Day Free Trial — 1,500 rows per province (4,500 total) to explore the schema and validate data quality.

Questions? Contact our team for a walkthrough.