> For the complete documentation index, see [llms.txt](https://docs.dataplex-consulting.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.dataplex-consulting.com/data-catalog/canada-provider-registry-dataset.md).

# Canadian Provider Registry

### About the Dataset

The **Canadian Provider Registry Dataset** is a unified **physician directory** for **Ontario, Quebec, and Alberta**, Canada's three largest provinces. It gives you one queryable schema over **\~93,000 physicians** (\~75% of all Canadian physicians), sourced from the official provincial regulatory colleges (**CPSO, CMQ, CPSA**) and refreshed **weekly** so your data always reflects current licensure status. It is built for provider lookup, territory planning, and physician workforce analysis.

{% hint style="info" %}
**Get Full Access** | [Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRSX3V/dataplex-consulting-data-products-canadian-provider-registry) | [Free Trial](https://trial.dataplex-consulting.com/?platform=snowflake)
{% endhint %}

### 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.

### Canadian Provider Registry Dataset vs. the free provincial registries

Each province's regulatory college offers a free public licence lookup: CPSO (Ontario), CMQ (Quebec), and CPSA (Alberta). Those are the right tool for verifying one physician. They are not built for analytics across provinces:

| If you need to…                               | Free provincial college lookups             | Canadian Provider Registry Dataset       |
| --------------------------------------------- | ------------------------------------------- | ---------------------------------------- |
| Verify one physician's licence status         | ✅ Free, authoritative, real-time            | Not the use case; go to the college      |
| Query all three provinces in one schema       | ❌ Three separate sites, three formats       | ✅ One normalized `PROVIDERS` table       |
| Join providers to your own data at scale      | ❌ No bulk export; manual, per-record        | ✅ In-warehouse SQL, no rate limits       |
| Analyze physician workforce supply            | ❌ Manual collection and cleanup             | ✅ Ready-to-query, normalized specialties |
| Rely on a consistent, weekly-refreshed schema | ❌ Varies by college, changes without notice | ✅ Stable schema, weekly refresh          |

### Metadata Tables

| Table             | Purpose                                                                        |
| ----------------- | ------------------------------------------------------------------------------ |
| `DATA_DICTIONARY` | Column descriptions for all tables, a queryable reference for schema discovery |

## Entity Relationship Diagram

![Canadian Provider Registry ERD](https://813439891-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDeimGtBflXKQn786VLvj%2Fuploads%2Fgit-blob-e6b3f0e71f1ef181bfbf1d9977db1690c48bcd2d%2Fentity-relationship.png?alt=media)

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 Snowflake. Queries use schema-only references, since the database is already set by the share context:

| Platform      | Schema | Example         |
| ------------- | ------ | --------------- |
| **Snowflake** | `DWV`  | `DWV.PROVIDERS` |

### Discover Available Data

```sql
-- 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;
```

### Find Specialists by City

```sql
-- 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;
```

### Physician Counts by Province

```sql
-- 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;
```

### Search by Name

```sql
-- 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;
```

### Specialty Distribution by Province

```sql
-- 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;
```

### Territory Planning: Physicians per City

```sql
-- 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;
```

### Workforce Gap Analysis

```sql
-- 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;
```

### Frequently Asked Questions

**Which provinces and how many physicians are included?** Ontario (\~53,000), Quebec (\~27,000), and Alberta (\~13,000): about 93,000 physicians in total, roughly 75% of all Canadian physicians. These are Canada's three largest provinces by physician count.

**Where does the data come from?** The official provincial regulatory colleges: the College of Physicians and Surgeons of Ontario (CPSO), the Collège des médecins du Québec (CMQ), and the College of Physicians & Surgeons of Alberta (CPSA). Each college's public register is normalized into one consistent `PROVIDERS` schema.

**How often is it updated?** Weekly (Mondays), so registration status and newly licensed physicians stay current with no manual refresh on your side.

**How are physicians identified across provinces?** Each row has a `provider_id` of province code + registration number (e.g., `ON-12345`, `QC-67890`), so providers are unambiguous even though each college numbers its registrants independently.

**Does it include specialty and registration status?** Yes. Specialties are normalized to standard English names (Family Medicine, Psychiatry, etc.) and registration status is tracked (Active, Inactive, Suspended, Deceased).

{% hint style="success" %}
**Ready to access Canadian Provider Registry data?**
{% endhint %}

| Platform      | Action                                                                                                                                        |
| ------------- | --------------------------------------------------------------------------------------------------------------------------------------------- |
| **Snowflake** | [Get on Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRSX3V/dataplex-consulting-data-products-canadian-provider-registry) |

{% hint style="success" %}
[Start 14-Day Free Trial](https://trial.dataplex-consulting.com/?platform=snowflake): 1,500 rows per province (4,500 total) to explore the schema and validate data quality.

Questions? [Contact our team](mailto:support@dataplex-consulting.com) for a walkthrough.
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.dataplex-consulting.com/data-catalog/canada-provider-registry-dataset.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.