Loading Canadian property data into Snowflake with Houski
Most data teams already have a Snowflake warehouse. What they do not have is a clean, structured feed of Canadian property data sitting next to their internal tables.
This is a step-by-step guide for data engineers who want to land Houski's full Canadian property dataset into Snowflake, with two paths depending on freshness needs: bulk file delivery for the whole stock, and a live API path for the deltas.
What Houski delivers
Houski maintains a unified record for every property in Canada. Each row has identification fields, structural attributes, assessment history, listing history, demographic context drawn from public, government, and crowdsourced data sources, and estimates from our valuation models. Bulk data is delivered as Arrow or Parquet files in an S3 bucket your team can read with Snowflake's external stage. For on-demand queries, the same fields are reachable through the properties endpoint.
You generally want both. Bulk for the cold storage history and heavy joins. Live API for everything that moved in the last 24 hours.
Step 1: Get the bulk dataset
Bulk delivery is a custom arrangement our data team sets up with you - ask us to scope one. As an example of what we can deliver: an S3 prefix your team reads from, synced on whatever cadence you need (daily, weekly, monthly), partitioned by province with a manifest at the root. Parquet is the default for Snowflake teams because the external table syntax is straightforward.
A typical Canadian properties extract is roughly 19 million rows and around 18 to 22 GB compressed across all provinces. Single-province extracts (Ontario, Quebec, British Columbia, Alberta) sit between 2 and 5 GB.
Step 2: Configure the Snowflake external stage
CREATE OR REPLACE STORAGE INTEGRATION houski_integration
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::YOUR_ACCOUNT:role/houski-snowflake-reader'
STORAGE_ALLOWED_LOCATIONS = ('s3://houski-customer-yourteam/');
CREATE OR REPLACE FILE FORMAT houski_parquet
TYPE = PARQUET;
CREATE OR REPLACE STAGE houski_stage
STORAGE_INTEGRATION = houski_integration
URL = 's3://houski-customer-yourteam/properties/'
FILE_FORMAT = houski_parquet;
Once that runs, you can list files and preview a row count without copying anything yet.
LIST @houski_stage; SELECT COUNT(*) FROM @houski_stage/province=ON/;
Step 3: Land the data
The simplest landing is a single wide table that mirrors the schema we publish in our dataset manifest. Snowflake handles schema inference for Parquet, so the initial table is one statement:
CREATE OR REPLACE TABLE raw.houski_properties
USING TEMPLATE (
SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(
INFER_SCHEMA(
LOCATION => '@houski_stage/province=ON/',
FILE_FORMAT => 'houski_parquet'
)
)
);
COPY INTO raw.houski_properties
FROM @houski_stage
FILE_FORMAT = (FORMAT_NAME = houski_parquet)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
For most teams the wide raw table feeds a smaller curated mart with the 30 to 50 columns analysts actually use. Keep the wide raw table around for the columns you do not predict you will need.
Step 4: Schedule incremental refreshes
As part of a custom dataset arrangement, we can deliver a daily delta partition alongside the full snapshot. The delta carries only properties touched in the last 24 hours (new listings, new assessments, new permit filings, automated valuation model (AVM) refreshes). Wire it up with a Snowflake task:
CREATE OR REPLACE TASK refresh_houski_properties WAREHOUSE = compute_wh SCHEDULE = 'USING CRON 0 9 * * * America/Edmonton' AS MERGE INTO raw.houski_properties tgt USING ( SELECT * FROM @houski_stage/delta/dt=CURRENT_DATE()/ ) src ON tgt.property_id = src.property_id WHEN MATCHED THEN UPDATE SET tgt.estimate_list_price = src.estimate_list_price, tgt.estimate_list_price_date = src.estimate_list_price_date, tgt.estimate_rent_monthly = src.estimate_rent_monthly, tgt.estimate_rent_monthly_date = src.estimate_rent_monthly_date, tgt.assessment_value = src.assessment_value, tgt.assessment_year = src.assessment_year, tgt.for_sale = src.for_sale, tgt.for_sale_list_date = src.for_sale_list_date WHEN NOT MATCHED THEN INSERT *; ALTER TASK refresh_houski_properties RESUME;
That gives you a Snowflake table that mirrors Houski's view of the Canadian property market with under 24 hours of lag.
Step 5: The live API path for true real-time
For use cases where 24 hours is too much lag (active acquisition pipelines, fraud detection, mortgage origination), supplement the bulk feed with direct calls to the properties endpoint. Snowflake's external access integration plus a Python user-defined function (UDF) lets a query in your warehouse call Houski directly. You wire up a network rule and a secret, then expose the call as a function:
CREATE OR REPLACE NETWORK RULE houski_api_rule
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('api.houski.ca:443');
CREATE OR REPLACE SECRET houski_api_key
TYPE = GENERIC_STRING
SECRET_STRING = 'YOUR_HOUSKI_API_KEY';
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION houski_access
ALLOWED_NETWORK_RULES = (houski_api_rule)
ALLOWED_AUTHENTICATION_SECRETS = (houski_api_key)
ENABLED = TRUE;
CREATE OR REPLACE FUNCTION houski_property(address VARCHAR)
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.11
HANDLER = 'fetch'
EXTERNAL_ACCESS_INTEGRATIONS = (houski_access)
SECRETS = ('api_key' = houski_api_key)
PACKAGES = ('requests')
AS $$
import _snowflake
import requests
def fetch(address):
api_key = _snowflake.get_generic_secret_string('api_key')
r = requests.get(
'https://api.houski.ca/properties',
params={'api_key': api_key, 'address': address, 'results_per_page': 1},
timeout=10,
)
return r.json()
$$;
Then any query inside Snowflake can pull a fresh row:
SELECT l.application_id, houski_property(l.subject_address) AS property FROM staging.loan_applications l WHERE l.received_at > DATEADD(hour, -1, CURRENT_TIMESTAMP);
Calls go against your API quota, so most teams batch lookups and cache results back into a Snowflake table for 24 hours.
For the analyst-facing version, data engineers often loop through customer addresses one at a time to backfill or enrich a list before Snowflake ingestion. The underlying call per address looks like the block below, which is a live call against the real API, the request code and the JSON response are generated every time this page is rendered:
const houski_data = async (): Promise<PropertiesResponse> => {
// You must copy the PropertiesResponse type declarations from the
// Houski API documentation to strongly type the response
const url = new URL('https://api.houski.ca/properties');
url.searchParams.set('api_key', 'YOUR_API_KEY');
url.searchParams.set('city', 'calgary');
url.searchParams.set('country_abbreviation', 'ca');
url.searchParams.set('province_abbreviation', 'ab');
url.searchParams.set('select', 'property_id,latitude,longitude,property_type,interior_sq_m,bedroom,bathroom_full,construction_year,assessment_value,estimate_list_price,estimate_rent_monthly');
const response = await fetch(url);
const data = await response.json();
return data;
}
(async () => {
let data: PropertiesResponse = await houski_data();
// Log the response
console.log(data);
})();
{
"cache_hit": false,
"cost_cents": 4.5,
"data": [
{
"address": "31 Hawkside Park NW",
"assessment_value": 648500,
"bathroom_full": 3,
"bedroom": 3,
"construction_year": 1988,
"estimate_list_price": 668241,
"estimate_rent_monthly": 2587,
"interior_sq_m": 126.34708404541016,
"latitude": 51.12946319580078,
"longitude": -114.17919921875,
"property_id": "10000f97f5cb7b9f",
"property_type": "Duplex"
},
{
"address": "6 1744 7 Street SW",
"bathroom_full": 2,
"bedroom": 3,
"construction_year": 2006,
"estimate_list_price": 660525,
"estimate_rent_monthly": 3259,
"interior_sq_m": 125.882568359375,
"latitude": 51.03611755371094,
"longitude": -114.0792007446289,
"property_id": "10004f7afe0c1946",
"property_type": "House"
},
{
"address": "384 Copperpond Landng SE",
"bathroom_full": 2,
"bedroom": 3,
"construction_year": 2006,
"estimate_list_price": 389896,
"estimate_rent_monthly": 2571,
"interior_sq_m": 125.882568359375,
"latitude": 50.925743103027344,
"longitude": -113.92975616455078,
"property_id": "10007f9761f49940",
"property_type": "House"
},
{
"address": "239 Dalhurst Way NW",
"assessment_value": 1110000,
"bathroom_full": 2,
"bedroom": 3,
"construction_year": 1971,
"estimate_list_price": 1102567,
"estimate_rent_monthly": 3270,
"interior_sq_m": 108.46339416503906,
"latitude": 51.110740661621094,
"longitude": -114.1513900756836,
"property_id": "100086f6bc064d3f",
"property_type": "House"
},
{
"address": "52 Cedargrove Way SW",
"assessment_value": 662000,
"bathroom_full": 1,
"bedroom": 3,
"construction_year": 1984,
"estimate_list_price": 635789,
"estimate_rent_monthly": 2561,
"interior_sq_m": 136.4734344482422,
"latitude": 50.95145034790039,
"longitude": -114.12571716308594,
"property_id": "1000c277cd905d3b",
"property_type": "House"
},
{
"address": "28 Sundown Gr SE",
"assessment_value": 692500,
"bathroom_full": 3,
"bedroom": 3,
"construction_year": 1988,
"estimate_list_price": 735563,
"estimate_rent_monthly": 3173,
"interior_sq_m": 172.61241149902344,
"latitude": 50.899044036865234,
"longitude": -114.04895782470705,
"property_id": "1001109ab2aebbc0",
"property_type": "House"
}
],
"error": "",
"pagination": {
"current_page": 1,
"has_next_page": true,
"has_previous_page": false,
"page_total": 113490
},
"price_quote": false,
"result_total": 680936,
"time_ms": 84,
"ui_info": {
"city": "Calgary",
"city_id": "6ec95b53075d062c",
"city_link": "ca/ab/calgary",
"city_slug": "calgary",
"country": "Canada",
"country_abbreviation": "CA",
"country_abbreviation_id": "9ace2b6431b7f1be",
"country_abbreviation_link": "ca",
"country_slug": "canada",
"province": "Alberta",
"province_abbreviation": "AB",
"province_abbreviation_id": "aae1f05a0f89d2c7",
"province_abbreviation_link": "ca/ab",
"province_slug": "alberta"
}
}
Pipe the output to a JSONL file and PUT it into a Snowflake stage. Done.
Step 6: Modeling on top
Once the raw table is landed, the standard dbt-style modeling stack works without modification. We see customers building these marts most often:
- dim_properties: one row per property with the curated 30 to 50 attributes
- fct_listings: one row per historical listing event with price and days on market
- fct_assessments: one row per assessment year per property
- dim_demographics: area-level demographic rollups by community (already in the wide table)
- agg_market_health: city or community level rollups updated daily
A dim_properties view:
CREATE OR REPLACE VIEW marts.dim_properties AS SELECT property_id, address, city, province_abbreviation, country_abbreviation, latitude, longitude, property_type, construction_year, interior_sq_m, land_area_sq_m, bedroom, bathroom_full, bathroom_half, assessment_value, assessment_year, estimate_list_price, estimate_list_price_date, estimate_rent_monthly, estimate_rent_monthly_date, demographic_income_median_pre_tax, demographic_municipal_population, score_walkability, score_transit, for_sale, for_sale_list_date FROM raw.houski_properties;
All the score_* fields in Houski are oriented so that higher is better, on a 0 to 10 scale. A score_walkability of 9 means more walkable than a score of 3. Same goes for transit, flood safety, fire safety, and the other risk scores.
Step 7: Adding the geocoding and AVM endpoints
For workflows that need radius search or batch valuation, the geocoding endpoint and predict endpoint are the same shape: GET request, JSON response, callable from a Snowflake external function or a Lambda you invoke via task.
A common pattern is a Snowflake task that runs nightly, picks every loan in the portfolio with a loan-to-value (LTV) ratio that has drifted more than 5 percent since last refresh, and runs them through /predict to backfill an updated valuation column.
Cost and licensing
Bulk dataset delivery is priced per province and refresh cadence. The live API is metered by the rows and fields returned, with a $99 USD monthly minimum. Most teams that already have Snowflake combine the two: bulk for the long tail of historical analysis, API for the active workflows that need same-day numbers.
The Houski datasets page has current pricing, and the full data dictionary lives in the field documentation. Sample files are available so your data team can validate schema fit before signing anything.
What you skip by buying instead of building
- Scraping listing portals (legally fraught, technically annoying)
- Maintaining 13 provincial and territorial assessment authority connections
- Standardizing addresses across French, English, and abbreviation variants
- Reconciling property type vocabularies across cities
- Building and maintaining an AVM
That is roughly two full-time data engineers worth of work, ongoing.
If you want to scope a Snowflake load with us, start at api-documentation/quick-start for the live endpoints, or reach out for a dataset sample. Most teams have data flowing into Snowflake within a week of the first conversation.