Downloads

IoT and smart home (smarthome)

One connected family home: 83 devices, 13 months of sensor readings, solar, battery and EV energy, automations, security, bills, trips and a floor plan.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Connect to the smarthome database and describe how devices, readings and bills join.
  • Analyze solar, battery and EV energy with hourly and monthly queries.
  • Find an outage and a water leak in the sensor readings.
  • Map devices to rooms and floors, and group results by fiscal period.

smarthome is the data of one connected family home, the Rivera household in Bellevue, WA: two adults, a teen, a child and a dog, with 83 devices in 12 rooms, rooftop solar, a home battery and an electric car. It covers 24 September 2025 to 24 October 2026 and holds about 1.9 million rows in 39 tables, identical on SQL Server and MariaDB.

It is built for personal household data: sensor time series, events, energy, bills, weather, security and routines, with a geospatial layer (the home, the places the family drives to, GPS trips and a floor plan) and a fiscal calendar (the October to September "water year" that utilities use).

Connect to it

Everything is connected

The data is generated so that the numbers agree with each other:

Tables at a glance

Area Tables What they hold
Home homes, rooms, people One home with a full address, 12 rooms with floor-plan rectangles, and five household members (the dog included)
Devices device_types, device_models, devices, sensor_channels, energy_meters 83 devices (thermostats, sensors, lights, locks, cameras, appliances, energy gear), their models and positions, 79 sensor channels, 8 energy meters
Readings and events readings, events, commands 1.6 million readings every 15 to 60 minutes, 153,000 events (motion, doors, locks, lights, appliance cycles), 10,800 commands
Automations automations, automation_runs 17 automations and 6,200 runs with their outcomes
Alerts and upkeep alerts, firmware_updates, maintenance_log 98 alerts, 146 firmware updates, battery and filter changes
Energy and bills energy_hourly, device_energy_daily, ev_charging_sessions, rate_plans, utility_bills Hourly meters, daily energy per appliance, 167 EV sessions, time-of-use rate plans, electric, gas, water and internet bills
Weather weather_hourly, weather_daily Hourly and daily weather with sunrise and sunset
People presence_log, routines, sleep_sessions, access_codes Home and away history, routines, 1,500 nights of sleep with the bedroom's temperature and CO2, door codes for family, guests and services
Security camera_clips, alarm_sessions 3,800 camera clips and 580 alarm sessions
Places and trips points_of_interest, geofences, trips, trip_points 11 places, 7 geofences, 704 EV trips and their GPS breadcrumbs
Reference geo_states, geo_places, geo_postal_codes, calendar_dates, fiscal_calendar, dataset_info Geography and calendars

How the tables join

homes ──< rooms ──< devices >── device_models >── device_types
                        │
                        ├──< sensor_channels ──< readings            (the big time series)
                        ├──< events              (motion, doors, locks, lights, cycles)
                        ├──< commands >── automation_runs >── automations
                        ├──< alerts, firmware_updates, maintenance_log
                        └──< camera_clips, alarm_sessions, ev_charging_sessions

energy_meters ──< energy_hourly         devices ──< device_energy_daily
people ──< presence_log, sleep_sessions, routines          people ──< trips ──< trip_points
points_of_interest ──< geofences        fiscal_calendar / calendar_dates join on the date

Rules built into the data

Things that happen in the data

Questions to try

How much of the house's electricity comes from the sun, month by month?

SELECT fiscal_period, ROUND(SUM(consumption_kwh), 0) AS used_kwh, ROUND(SUM(solar_kwh), 0) AS solar_kwh,
       ROUND(SUM(grid_import_kwh), 0) AS imported_kwh, ROUND(SUM(grid_export_kwh), 0) AS exported_kwh,
       ROUND((1 - SUM(grid_import_kwh) / SUM(consumption_kwh)) * 100, 1) AS self_sufficiency_pct
FROM vw_daily_energy
WHERE fiscal_year = 2026
GROUP BY fiscal_period
ORDER BY fiscal_period;

Self-sufficiency is about 6% in January (fiscal period 4) and about 60% in June and July (periods 9 and 10).

What did each utility cost in fiscal 2026?

SELECT utility_type, COUNT(*) AS bills, ROUND(SUM(amount_due), 2) AS total_due
FROM utility_bills
WHERE fiscal_year = 2026
GROUP BY utility_type
ORDER BY total_due DESC;

Which rooms were warmest in July?

SELECT room_name, ROUND(AVG(avg_value), 1) AS avg_temp_f, ROUND(MIN(min_value), 1) AS coldest, ROUND(MAX(max_value), 1) AS warmest
FROM vw_room_comfort_daily
WHERE metric = 'temperature_f' AND reading_date >= '2026-07-01' AND reading_date < '2026-08-01'
GROUP BY room_name
ORDER BY avg_temp_f DESC;

Does stuffy bedroom air hurt sleep?

SELECT CASE WHEN avg_bedroom_co2_ppm < 800 THEN 'under 800 ppm'
            WHEN avg_bedroom_co2_ppm < 1100 THEN '800-1,100 ppm' ELSE 'over 1,100 ppm' END AS co2_band,
       COUNT(*) AS nights, ROUND(AVG(sleep_score * 1.0), 1) AS avg_sleep_score
FROM sleep_sessions
GROUP BY CASE WHEN avg_bedroom_co2_ppm < 800 THEN 'under 800 ppm'
              WHEN avg_bedroom_co2_ppm < 1100 THEN '800-1,100 ppm' ELSE 'over 1,100 ppm' END
ORDER BY avg_sleep_score DESC;

Nights with the highest CO2 score lower than nights with fresh air.

How far did the EV go, and for what?

SELECT purpose, COUNT(*) AS trips, ROUND(SUM(miles), 0) AS miles, ROUND(SUM(kwh_used), 0) AS kwh
FROM trips
WHERE fiscal_year = 2026
GROUP BY purpose
ORDER BY miles DESC;

Which alerts come up most?

SELECT alert_type, severity, COUNT(*) AS alerts
FROM alerts
GROUP BY alert_type, severity
ORDER BY alerts DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

Where is the motion? Count motion events by room from the room sensors:

SELECT r.room_name, COUNT(*) AS motion_events
FROM events e
JOIN devices d ON d.device_id = e.device_id
JOIN rooms r   ON r.room_id = d.room_id
WHERE e.event_type = 'motion' AND e.fiscal_year = 2026 AND d.device_name LIKE 'Room Sensor%'
GROUP BY r.room_name
ORDER BY motion_events DESC
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

All of these run on SQL Server and MariaDB. More ideas: the outage hour by hour (readings for battery_soc_pct and grid_power_w), the leak minute by minute, empty-house energy from presence_log, battery health from the battery_pct channels, appliance cost per cycle (device_energy_daily with the rate plan), and a floor-plan heat map.

Maps, floor plans and fiscal periods

Excel and Power BI

Realistic imperfections

The data has the usual smart-home mess: missing and suspect readings, devices that drop off the network, failed automations and firmware updates, low batteries, duplicate-looking alerts, and a few sensors that are wrong in the same way for a day. It is clean enough to reconcile, and messy enough to need filtering.

Try it on the data

Open the smarthome database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.