Linear regression: house prices
Fit a numeric baseline and understand feature scaling, prediction units, and evaluation metrics.
The practical problem and model
An analytics team wants a baseline estimate of a listing's price from its size, bedroom and bathroom counts, and age. A linear model adds a weighted contribution from each feature to an intercept.
This is a useful starting point when you want a transparent numeric baseline. It does not capture every interaction, neighborhood effect, or market change. The eight listings are invented teaching data, not a valuation dataset.
The key lesson is units: training expresses price in units of $100,000, floor area in thousands of square feet, and age in decades. Every prediction must use those same transformations.
Before you run
Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.
Studio: Tutorial: Linear Regression — House Prices. Download the complete SQL.
Step 1: Create eight listings
The fixture stores human-readable units. Scaling happens explicitly in the training and scoring queries.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.housing;
CREATE OR REPLACE TABLE tutorial.housing.listings (
listing_id BIGINT,
sqft DOUBLE,
bedrooms DOUBLE,
bathrooms DOUBLE,
age_years DOUBLE,
price_usd DOUBLE
);
INSERT INTO tutorial.housing.listings VALUES
(1, 1200, 2, 1, 30, 320000),
(2, 1800, 3, 2, 15, 480000),
(3, 2400, 4, 3, 5, 720000),
(4, 900, 1, 1, 45, 210000),
(5, 3200, 5, 4, 2, 980000),
(6, 1500, 3, 2, 20, 410000),
(7, 2000, 3, 2, 10, 560000),
(8, 2800, 4, 3, 8, 820000);
Step 2: Train the scaled model
The first column is the scaled price label. The four subsequent columns correspond, in order, to the four input arguments.
CREATE OR REPLACE MODEL tutorial.housing.price_model
(DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'linear'
OPTIONS (learning_rate = 0.01, l2_lambda = 0.0, max_iters = 5000, tolerance = 1e-6)
AS SELECT
price_usd / 100000.0 AS label,
sqft / 1000.0 AS sqft_k,
bedrooms,
bathrooms,
age_years / 10.0 AS age_decades
FROM tutorial.housing.listings;
SHOW MODELS;
EXPLAIN MODEL tutorial.housing.price_model;
Step 3: Convert predictions back to dollars
The hypothetical listing has 2,100 square feet, three bedrooms, two bathrooms, and an age of seven years. Multiplying the output by 100,000 restores dollar units.
SELECT tutorial.housing.price_model(2.1, 3.0, 2.0, 0.7) * 100000 AS predicted_price_usd;
SELECT
listing_id,
price_usd,
tutorial.housing.price_model(sqft / 1000.0, bedrooms, bathrooms, age_years / 10.0) * 100000 AS predicted_usd
FROM tutorial.housing.listings
ORDER BY listing_id;
Step 4: Read evaluation in the correct units
EVALUATE MODEL compares scaled labels and outputs. Its MAE and RMSE are in $100,000 units, not dollars.
EVALUATE MODEL tutorial.housing.price_model
ON SELECT
price_usd / 100000.0 AS label,
sqft / 1000.0 AS sqft_k,
bedrooms,
bathrooms,
age_years / 10.0 AS age_decades
FROM tutorial.housing.listings;
SHOW MODEL EVALUATIONS FOR tutorial.housing.price_model;
What to check in the results
The scalar example produced about $581,702.42 in the recorded run. The batch query returns eight rows, one per listing. Small numeric differences can occur across builds.
The recorded scaled RMSE was about 0.181647 and MAE about 0.139930: approximately $18,164.70 RMSE and $13,993.00 MAE after conversion. Both were measured on the training fixture, so neither is an out-of-sample performance estimate.
Adapt it to real data
Add relevant features such as location and property type with a stable encoding. Fit any learned scaling on the training partition only, then reuse it for validation and scoring. Compare with a simple median-price baseline on later transactions and inspect residuals by segment.
Keep predictions and error metrics labelled with their units. A good fit to eight examples is not enough to use this model for pricing decisions.