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

{% 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)
{% 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.

### Metadata Tables

| Table             | Purpose                                                                       |
| ----------------- | ----------------------------------------------------------------------------- |
| `DATA_DICTIONARY` | Column descriptions for all tables — 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 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

{% tabs %}
{% tab title="Snowflake" %}

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

{% endtab %}

{% tab title="Databricks" %}

```sql
SELECT table_name, column_name, data_type, column_description
FROM canada_provider_registry_dwv.data_dictionary
ORDER BY table_name, ordinal_position;
```

{% endtab %}
{% endtabs %}

### Find Specialists by City

{% tabs %}
{% tab title="Snowflake" %}

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

{% endtab %}

{% tab title="Databricks" %}

```sql
SELECT provider_id, first_name, last_name, city,
       province, specialty, phone
FROM canada_provider_registry_dwv.providers
WHERE UPPER(city) = 'TORONTO'
  AND specialty ILIKE '%cardiol%'
ORDER BY last_name, first_name
LIMIT 25;
```

{% endtab %}
{% endtabs %}

### Physician Counts by Province

{% tabs %}
{% tab title="Snowflake" %}

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

{% endtab %}

{% tab title="Databricks" %}

```sql
SELECT province, COUNT(*) AS physician_count
FROM canada_provider_registry_dwv.providers
GROUP BY province
ORDER BY physician_count DESC;
```

{% endtab %}
{% endtabs %}

### Search by Name

{% tabs %}
{% tab title="Snowflake" %}

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

{% endtab %}

{% tab title="Databricks" %}

```sql
SELECT provider_id, first_name, last_name, city, province,
       specialty, phone
FROM canada_provider_registry_dwv.providers
WHERE last_name ILIKE 'Smith%'
ORDER BY province, city
LIMIT 25;
```

{% endtab %}
{% endtabs %}

### Specialty Distribution by Province

{% tabs %}
{% tab title="Snowflake" %}

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

{% endtab %}

{% tab title="Databricks" %}

```sql
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 canada_provider_registry_dwv.providers
WHERE specialty IS NOT NULL
  AND registration_status = 'Active'
GROUP BY specialty
ORDER BY total_providers DESC
LIMIT 30;
```

{% endtab %}
{% endtabs %}

### Territory Planning — Physicians per City

{% tabs %}
{% tab title="Snowflake" %}

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

{% endtab %}

{% tab title="Databricks" %}

```sql
SELECT
    city,
    province,
    province_code,
    COUNT(*) AS physician_count,
    COUNT(DISTINCT specialty) AS specialty_count
FROM canada_provider_registry_dwv.providers
WHERE registration_status = 'Active'
  AND city IS NOT NULL
GROUP BY city, province, province_code
ORDER BY physician_count DESC
LIMIT 50;
```

{% endtab %}
{% endtabs %}

### Workforce Gap Analysis

{% tabs %}
{% tab title="Snowflake" %}

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

{% endtab %}

{% tab title="Databricks" %}

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

{% endtab %}
{% endtabs %}

{% 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) — 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 %}