DEV Community

Dave Zavin
Dave Zavin

Posted on

How I turned Canada's messy open government data into 3 clean data products

#dataengineering #python #snowflake #opendata

Canadian government data is public. That's the good news. The bad news is that
"public" and "usable" are two very different things.

The same category of data lives in a dozen portals, in three different formats,
half of it only in French, with schemas that change per municipality. If you want
"licensed contractors in Canada" or "open government tenders," you don't get a table —
you get a scavenger hunt.

So I built the scavenger hunt once, cleaned everything up, and published three free
data products on Snowflake Marketplace (also on Apify as API/no-code actors):

  • Licensed Contractors — Quebec (RBQ) + Ontario (HCRA), ~60,500 records
  • Building Permits — with contractor names + project values, ~16,500 records
  • Government Tenders — federal (CanadaBuys) + Québec (SEAO), ~10,000 open bids

Here's how it's built, quirks and all.

Step 1: Sources and licences (do it legally)

Everything is built on official open data with permissive licences. This matters —
you can't resell scraped data you don't have the right to redistribute.

  • RBQ (Quebec contractor licences) — a bulk CSV shipped in a ~10 MB zip, refreshed daily, CC-BY. ~924k raw rows collapse to ~54k unique active holders.
  • HCRA (Ontario home builders) — no bulk file, but an internal JSON API: obd.hcraontario.ca/api/builders?builderName=<term> (an empty term 500s, so you iterate). Details come from /api/buildersummary?id=<ACCT>.
  • CanadaBuys (federal tenders) — a ~100 MB CSV on CloudFront, refreshed daily for open tenders, every 2 hours for new ones.
  • SEAO (Québec public sector) — weekly OCDS/CKAN files from the Données Québec catalogue. You have to resolve the newest weekly files programmatically.
  • Building permits — a different API per city: Toronto (CKAN), Vancouver (Opendatasoft), Calgary/Edmonton (Socrata), Montréal (CKAN CSV, in French).

Lesson one: there is no such thing as "the Canada open-data API." Every source is a
one-off.

Step 2: The scraping quirks that cost me the most time

A few things that aren't in any tutorial:

  • SEAO weekly files — the catalogue lists many resources; you want the latest N weekly OCDS files and have to sort/resolve them by date, then handle the OCDS release structure, not a flat row.
  • Socrata lag — Calgary's permit data can trail reality by ~6 weeks. Don't promise "real-time" on data that isn't.
  • French-first fields — SEAO titles are in French. If you filter by keyword you need French terms ("informatique," "construction") or you silently drop Québec rows.
  • Encoding — the classic Windows gotcha. When you shell out to a scraper and capture its output, Python defaults to cp1252 and dies on the first emoji/UTF-8 byte: 
# This blows up on Windows with UnicodeDecodeError: 'charmap' codec...
subprocess.run(cmd, capture_output=True, text=True)

# This doesn't:
subprocess.run(cmd, capture_output=True, text=True,
               encoding="utf-8", errors="replace")
Enter fullscreen mode Exit fullscreen mode

Step 3: Normalize to one clean schema

Each source gets mapped into a single, boring, predictable schema. For contractors:
legal_name, province, licence_body, licence_number, city, postal, status. Then a
normalized name_key for dedupe across RBQ + HCRA:

import re, unicodedata

def namekey(s):
    if not s:
        return ""
    s = unicodedata.normalize("NFKD", str(s)).encode("ascii", "ignore").decode().lower()
    s = re.sub(r"[^a-z0-9 ]", " ", s)
    return " ".join(s.split())

# dedupe on (name_key, province) after concatenating sources
df = df[df["name_key"] != ""].drop_duplicates(["name_key", "province"])
Enter fullscreen mode Exit fullscreen mode

The one rule I never break: no personal contact info for private individuals.
The normalizer simply doesn't carry personal emails/phones into the output, and every
product links back to the official registry so anyone can verify the source record.

Step 4: Load into Snowflake without breaking the listing

This is the part that surprised me. Once a table is published on Snowflake
Marketplace, you can't just DROP and recreate it — that detaches the share. You want
to replace the rows but keep the table object. So the weekly refresh does
TRUNCATE + PUT a Parquet file to the table stage + COPY INTO with
MATCH_BY_COLUMN_NAME:

cur.execute(f"PUT 'file://{parquet}' @%{table} AUTO_COMPRESS=FALSE OVERWRITE=TRUE")
cur.execute(f"TRUNCATE TABLE {table}")
cur.execute(
    f"COPY INTO {table} FROM @%{table}/{parquet.name} "
    f"FILE_FORMAT=(TYPE=PARQUET) MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE "
    f"ON_ERROR=ABORT_STATEMENT")
Enter fullscreen mode Exit fullscreen mode

Two more things that saved me:

  • Key-pair auth, so the scheduled job authenticates without MFA prompts. Generate an RSA key, ALTER USER ... SET RSA_PUBLIC_KEY=..., and pass the private key to the connector.
  • An empty-guard: if a rebuild comes back with 0 rows (a scraper failed), abort the load. Never let a broken run truncate a live product to empty. 
if len(product_a) == 0 or len(product_b) == 0:
    raise SystemExit("a product came back EMPTY - aborting load")
Enter fullscreen mode Exit fullscreen mode

Step 5: Ship it — free, cross-cloud

All three are free listings on Snowflake Marketplace with cross-cloud
auto-fulfillment enabled, so a consumer in another region gets the data replicated
on demand instead of me pre-copying it everywhere. The whole refresh runs weekly from
a single scheduled task.

What's next

Ontario building permits, BC contractor licences, and a few more provinces are on the
list. If you work with Canadian market data — outbound to contractors, construction
analytics, or bidding on public tenders — I'd love to know which dataset or province
to add next.

Links:

All data built on official open sources (Open Government Licence – Canada,
RBQ/SEAO CC-BY), with a link to the official registry on every record.

Top comments (1)

Collapse
 
renolu profile image
Reno Lu •

Turning open government data into something usable is mostly a maintenance bet. The scrape is a weekend; what kills these projects is a province quietly renaming a column two months later. Curious whether you added schema checks that fail loudly before the product goes stale.