Loading Houski property data into Snowflake

Alex Wilkinson

CEO of Houski

2025-03-17

Most data teams already have a Snowflake warehouse. What they do not have is a clean, structured feed of 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 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 it holds across 16 countries, Canada and the United States among them. Each row is one home with its own address, so each flat in a building is a row of its own. 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. The pre-built datasets export as comma-separated values (CSV) or JSON, and a custom bulk delivery can arrive as Arrow or Parquet files in an S3 bucket your team reads 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, so 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.

The full extract across every country is more than 220 million rows, so most teams scope the delivery to the countries, provinces or states they work in. Ask for the size of your cut when you scope it.

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_sale_price = src.estimate_sale_price,
  tgt.estimate_rent_monthly = src.estimate_rent_monthly,
  tgt.assessment_value = src.assessment_value,
  tgt.assessment_year = src.assessment_year,
  tgt.for_sale = src.for_sale,
  tgt.list_price = src.list_price
WHEN NOT MATCHED THEN INSERT *;

ALTER TASK refresh_houski_properties RESUME;

That gives you a Snowflake table that mirrors Houski's view of the 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/search',
        params={
            'api_key': api_key,
            'query': address,
            'max_results': 1,
            'select': 'property_id,address,property_type,construction_year,interior_sq_m,estimate_list_price',
        },
        timeout=10,
    )
    return r.json()
$$;

The search endpoint takes the free-text address exactly as the loan system stores it and returns the best matching property with the fields you select.

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

Every call is billed for the data it returns, 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": "518 Rochester Terrace NW",
      "assessment_value": 1300000,
      "bathroom_full": 2,
      "bedroom": 3,
      "construction_year": 2023,
      "estimate_list_price": 1494309,
      "estimate_rent_monthly": 4310,
      "interior_sq_m": 169.45374,
      "latitude": 51.11072,
      "longitude": -114.2748,
      "property_id": "103f92f6cf7bbdd9",
      "property_type": "Land"
    },
    {
      "address": "36 Crimson Ridge Heights NW",
      "assessment_value": 194000,
      "bathroom_full": 2,
      "bedroom": 3,
      "construction_year": 2024,
      "estimate_list_price": 702800,
      "estimate_rent_monthly": 2718,
      "interior_sq_m": 169.45374,
      "latitude": 51.107574,
      "longitude": -114.25946,
      "property_id": "104fb85a9c002e5d",
      "property_type": "Commercial"
    },
    {
      "address": "54 Rowmont Link NW",
      "assessment_value": 853500,
      "bathroom_full": 2,
      "bedroom": 3,
      "construction_year": 2023,
      "estimate_list_price": 820503,
      "estimate_rent_monthly": 2852,
      "interior_sq_m": 169.45374,
      "latitude": 51.106037,
      "longitude": -114.275,
      "property_id": "1055f97323e53f25",
      "property_type": "House"
    },
    {
      "address": "499 Rochester Terrace NW",
      "assessment_value": 888000,
      "bathroom_full": 2,
      "bedroom": 3,
      "construction_year": 2023,
      "estimate_list_price": 931980,
      "estimate_rent_monthly": 2934,
      "interior_sq_m": 169.45374,
      "latitude": 51.111366,
      "longitude": -114.27512,
      "property_id": "1106094a42c3757d",
      "property_type": "Land"
    },
    {
      "address": "304 30 Rochester View NW",
      "assessment_value": 105000,
      "bathroom_full": 2,
      "bedroom": 2,
      "construction_year": 1800,
      "estimate_list_price": 600847,
      "estimate_rent_monthly": 2324,
      "interior_sq_m": 121.70197,
      "latitude": 51.109074,
      "longitude": -114.26745,
      "property_id": "1198b011e799062c",
      "property_type": "Townhouse"
    },
    {
      "address": "95 Royston He NW",
      "assessment_value": 918500,
      "bathroom_full": 2,
      "bedroom": 3,
      "construction_year": 2023,
      "estimate_list_price": 1005831,
      "estimate_rent_monthly": 2906,
      "interior_sq_m": 169.45374,
      "latitude": 51.107533,
      "longitude": -114.272934,
      "property_id": "11aa161875771a70",
      "property_type": "Land"
    }
  ],
  "error": "",
  "pagination": {
    "current_page": 1,
    "has_next_page": true,
    "has_previous_page": false,
    "page_total": 110113
  },
  "price_quote": false,
  "result_total": 660677,
  "time_ms": 26,
  "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_sale_price,
  estimate_rent_monthly,
  demographic_income_median_pre_tax,
  demographic_municipal_population,
  score_walkability,
  score_transit,
  for_sale,
  list_price
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 by scope 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

• Collecting and cleaning hundreds of data sources, each in its own format
• Keeping every one of those connections working as publishers change their files
• Standardizing addresses across languages, countries 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.