# 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](/files/1WOnmgNy6zN0x4v1HHmj)

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 %}


---

# Agent Instructions: 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:

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

The question should be specific, self-contained, and written in natural language.
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.