An end-to-end analytics project examining Canada's July 2026 labour market using the Statistics Canada Labour Force Survey Public Use Microdata File (PUMF). The project uses Snowflake SQL for data validation, transformation and analytical modelling, followed by Power BI for interactive reporting and visualization.
Open the live Power BI dashboard
The public report contains two pages: Labour Market Overview and Workforce Demographics.
This project was developed to answer the following questions:
- What were Canada's principal labour-market indicators in July 2026?
- How did unemployment rates differ by province?
- How did employment outcomes vary by education level and gender?
- Which industries had the highest estimated employment?
- How did average hourly earnings differ across industries and genders?
- Snowflake: cloud data storage, validation, transformation and analytical views
- SQL: CTEs, CASE expressions, conditional aggregation, window functions, weighted calculations and reusable views
- Power BI: data modelling, interactive visuals, report design and public publishing
- Statistics Canada: July 2026 Labour Force Survey Public Use Microdata File
flowchart LR
A["Statistics Canada CSV"] --> B["RAW schema"]
B --> C["CLEAN schema"]
C --> D["ANALYTICS views"]
D --> E["Power BI report"]
The original CSV was loaded into RAW.LABOUR_FORCE_SURVEY_JULY_2026, preserving the source variables for validation and traceability.
CLEAN.LABOUR_FORCE_SURVEY_JULY_2026 adds analysis-ready fields, including:
- Survey month
- Province name
- Gender label
- Age group
- Education level
- Labour-force status
- Industry name
- Hourly earnings
Numeric category codes were translated into readable business labels with SQL CASE expressions.
Five reusable views were created for Power BI:
| View | Purpose |
|---|---|
VW_NATIONAL_LABOUR_MARKET_KPIS |
National employment, unemployment, participation and earnings KPIs |
VW_PROVINCIAL_LABOUR_MARKET_KPIS |
Provincial labour-market rates and population estimates |
VW_LABOUR_FORCE_BY_PROVINCE_STATUS |
Labour-force status distribution within each province |
VW_OUTCOMES_BY_EDUCATION_GENDER |
Employment, unemployment and earnings by education and gender |
VW_EMPLOYMENT_BY_INDUSTRY_GENDER |
Estimated employment, earnings and rankings by industry and gender |
The raw data was validated before transformation:
- 113,603 total records
- Survey period confirmed as July 2026
- 113,603 unique, non-null record identifiers
- 0 duplicate record identifiers
- 0 nulls across the core province, age, gender, education and labour-force-status fields
- Clean-table row count reconciled to the raw-table row count
- Decoded core dimensions checked for unexpected
Unknownvalues
| KPI | Result |
|---|---|
| Employment rate | 61.51% |
| Unemployment rate | 6.68% |
| Participation rate | 65.91% |
| Estimated employment | 21.41 million |
| Average hourly earnings | $37.17 |
Additional findings from the dashboard include:
- Newfoundland and Labrador had the highest calculated provincial unemployment rate at 8.1%, followed by Ontario at 7.3% and Alberta at 7.1%.
- Employment rates generally increased with educational attainment for both women and men.
- Utilities, mining, professional services and finance were among the higher-paying industries in the PUMF analysis.
- Health care and social assistance had the largest estimated employment and a workforce weighted strongly toward women.
- Several industries displayed noticeable gender differences in average hourly earnings and employment composition.
The model contains five purpose-built analytical views. A one-to-many relationship connects provincial KPIs to provincial labour-force status using PROVINCE_NAME. The industry visuals share a common analytical view and interact directly with each other.
Canadian-Labour-Market-Analytics/
├── Dashboard/ # Power BI PBIX file
├── Images/ # Dashboard and data-model screenshots
├── SQL/
│ ├── 01_database_and_schema_setup.sql
│ ├── 02_raw_data_validation.sql
│ ├── 03_create_clean_table.sql
│ └── 04_create_analytical_views.sql
└── README.md
- Database and schema creation
- Data-quality and reconciliation queries
CASEexpressions for categorical decoding- Common table expressions (CTEs)
- Conditional aggregation with
IFF - Survey-weighted population and earnings calculations
- Window functions for percentages and employment rankings
- Division-by-zero protection with
NULLIF - Reusable analytical views for business intelligence reporting
- Statistics Canada — Labour Force Survey: Public Use Microdata File
- Statistics Canada — Labour Force Survey, July 2026
The results in this project were independently calculated from the public-use microdata file for portfolio and educational purposes. PUMF anonymization, rounding and the project's calculation methodology can cause results to differ from Statistics Canada's official published estimates. Statistics Canada is the source of the underlying data but has not endorsed this project or its analysis.
Trivan Mitchell
Aspiring Data Analyst | Toronto, Ontario
GitHub Profile