Moving Home Assistant Data to Cloud PostgreSQL with Python ETL

What You’ll Learn

In this article you’ll learn:

• How to select new numeric Home Assistant sensor states without copying the full history on every run.
• How Python, PostgreSQL server-side cursors, and COPY support batched ETL loads.
• How an SSH tunnel carries data to a cloud PostgreSQL warehouse.
• How Bronze, Silver, Gold, monitoring, and a success watermark fit into one batch workflow.
• Why operational monitoring should remain available when the data transaction fails.

Summary

Home Assistant keeps sensor history local, but long-term analysis can benefit from a separate cloud warehouse. This project uses Python and PostgreSQL to copy only new numeric sensor.* states from the Home Assistant database to cloud PostgreSQL. The Python ETL reads incrementally using the largest state_id already loaded, streams rows with a server-side cursor, and bulk loads them with PostgreSQL COPY through an SSH local port forward. The warehouse stores the landing data in Bronze, then runs Silver and Gold transformation procedures. A separate autocommit connection records run and step status, while the success watermark changes only after every stage succeeds. The outcome is a scheduled batch pipeline that preserves local Home Assistant operation while making selected historical data available for analysis, with monitoring to distinguish a complete run from a failed one.

Why Keep Home Assistant Local While Sending Sensor Data to the Cloud?

Home Assistant is excellent at collecting data locally. Over time, however, a home server can become an awkward place for long-term analysis. I wanted to keep Home Assistant close to the devices while making historical sensor data available in a separate cloud PostgreSQL warehouse.

This post explains how I built that connection with a small Python ETL pipeline. The design extracts numeric sensor states from a self-hosted PostgreSQL database, sends them through an SSH tunnel, and loads them into a Bronze/Silver/Gold data model. It also records enough operational information to answer a practical question: did the pipeline actually complete?

The project catalogue includes this Home Assistant pipeline, and the portfolio homepage explains how these case studies fit into the wider site. For the website’s own static publishing workflow, see the related Astro portfolio article. The complete ETL implementation is ha_etl.py; its configuration and database contract are documented in the project README.

The goal was not to move Home Assistant itself. Local control, privacy, and day-to-day availability still belong on the home server. The goal was to move a carefully selected copy of historical data to a system better suited to reporting, experimentation, and integration with other cloud services.

That separation creates a useful boundary:

• Home Assistant remains the operational source system.
• The cloud database becomes an analytical target.
• The ETL job controls what crosses the boundary.
• The warehouse can evolve without changing the home automation installation.

The pipeline currently transfers numeric entities such as temperatures, humidity, power readings, and other sensor values. Textual states such as unknown and unavailable are deliberately left out.

What Does the Home Assistant ETL Architecture Look Like?

The diagram shows both the data path and the separate monitoring path. Only new numeric sensor.* states move from the local Home Assistant database; the SSH tunnel carries the load into the cloud warehouse, where Bronze, Silver, and Gold run in sequence.

The script uses three database connections conceptually. One reads from the Home Assistant database. One handles the target data transaction. A third target connection uses autocommit for monitoring, so status updates are not trapped behind a long-running data transaction.

What Happens During Each ETL Run?

Each run follows the same sequence:

1. Load configuration from environment variables and a .env file beside the script.
2. Validate the database credentials and SSH settings.
3. Start an SSH tunnel to the target database.
4. Read the largest state_id already present in bronze.ha_states.
5. Count new numeric sensor rows in the Home Assistant database.
6. Stream those rows to Bronze in batches.
7. Run the Silver database procedure.
8. Run the Gold database procedure.
9. Update the successful-run watermark.
10. Record the result and close every connection and the SSH process.

This makes the job suitable for a scheduler such as cron. A failed run exits with status code 1, while a completed run exits with 0.

How Does the Pipeline Select Numeric Sensor States?

Home Assistant stores state values in a form that can contain both numbers and text. The extraction query joins states with states_meta, selects entities whose ID starts with sensor., and accepts values matching a numeric expression.

In simplified form, the filter is:

WHERE s.state_id > %s
  AND sm.entity_id LIKE 'sensor.%'
  AND s.state ~ '^-?[0-9]+(\\.[0-9]+)?$'

The selected value is then converted to double precision before it is copied into the target table. This keeps the Bronze table focused on data that can be aggregated and compared without carrying the complete variety of Home Assistant state strings into the warehouse.

The filter is also an explicit data-quality decision. It does not claim that every numeric-looking value is physically sensible. A separate Silver transformation can add unit handling, validation ranges, sensor dimensions, and other domain rules later.

How Does state_id Prevent Full Reloads?

The pipeline does not reload the full source history on every run. It queries MAX(state_id) from bronze.ha_states and requests only source rows with a greater ID.

That gives the job a simple, durable incremental boundary. The source rows are ordered by state_id, and the Bronze table preserves that identifier. If a run has already loaded a row, the next run can skip it without maintaining a separate local checkpoint file.

The success watermark is updated only after Bronze, Silver, and Gold have completed. If a later stage fails, the monitoring record shows the run as failed instead of claiming that the pipeline is current. That distinction is more important than a fast success message: a transformation failure should remain visible and recoverable.

How Does the Pipeline Keep Memory Use Predictable?

A large sensor history should not be read into Python in one operation. The source query uses a named PostgreSQL server-side cursor, and the script fetches a configurable number of rows at a time. The default batch size is 50,000 rows.

Each batch is written to an in-memory text buffer and sent to PostgreSQL with COPY. This is considerably better suited to bulk ingestion than issuing one INSERT statement per sensor state. The batch size remains configurable through ETL_BATCH_SIZE, so it can be adjusted for the available memory, network latency, and database capacity.

The Bronze transaction is committed after the batches have been copied successfully. If an exception occurs before that commit, the main error handler rolls the target transaction back.

What Happens in the Bronze, Silver, and Gold Layers?

Bronze is the landing layer. It keeps the extracted state_id, metadata ID, timestamp, and numeric value close to the source representation. Keeping this layer simple makes it easier to inspect what arrived and to reprocess downstream logic.

Silver is where the warehouse can standardize the data. The silver.load_silver() procedure is responsible for the target-specific transformation stage, such as joining sensor metadata, applying quality rules, and creating a more useful analytical shape.

Gold is the reporting layer. The gold.load_gold() procedure can publish aggregates or business-facing structures without forcing dashboards to query raw ingestion tables.

This is a small implementation of the medallion pattern. Databricks Staff describe the pattern as a progression where Bronze preserves raw data, Silver cleans and standardizes it, and Gold provides refined data for analytics. Their article, “What is Medallion Architecture?”, was a useful reference when structuring this project.

Home Assistant’s Recorder documentation is also useful background when working with its database history. It explains how recorder runs relate to the stored data, which is worth understanding before building queries over long-running installations.

How Does Monitoring Distinguish Success from Failure?

A data transfer is not production-ready merely because it can insert rows. It should also explain what happened during the run.

The script writes a run record to etl.run_log and creates a step record for stages such as connect_source, read_source, load_bronze, load_silver, and load_gold. The monitoring data includes start and end timestamps, duration, row counts, the current step, success status, error messages, and a stack trace for failed runs.

The separate autocommit monitoring connection is an important detail. If the data connection is in a transaction or has to be rolled back, the monitoring connection can still record the failure. This makes operational troubleshooting possible without searching only through process logs.

How Does the Pipeline Protect Its Network Connection?

The target PostgreSQL database is reached through an SSH local port forward. The ETL process starts the tunnel itself with BatchMode=yes and ExitOnForwardFailure=yes. The latter prevents the job from continuing when the forwarding rule did not start correctly.

Passwords are read from the environment or a local .env file, and the private SSH key is referenced by path. In a real deployment, the .env file and key must be excluded from version control. A dedicated source read user, a narrowly scoped target user, and an SSH account limited to the required tunnel reduce the impact of a compromised credential.

The tunnel is stopped in the finally block. Source, target, and monitoring connections are closed before the tunnel is terminated, which keeps resource ownership explicit even when the run fails near the beginning.

What Did I Learn from Building a Small ETL Pipeline?

The most important lesson is that a small ETL script still benefits from data-engineering discipline. Incremental boundaries, transaction handling, monitoring, and cleanup are not only for large platforms.

I also found that the Bronze layer should remain boring. It is tempting to perform every transformation while extracting the data, but separating ingestion from modelling makes failures easier to locate and later changes less risky.

Finally, security and reliability are connected. The SSH tunnel limits exposure, while explicit startup checks and failure logging prevent a partial connection from looking like a successful data run.

References

Databricks. (n.d.). What is medallion architecture? Retrieved September 15, 2026, from https://www.databricks.com/blog/what-is-medallion-architecture

Home Assistant. (n.d.). Recorder. Retrieved September 15, 2026, from https://data.home-assistant.io/docs/recorder

Key Takeaways

• The Python ETL copies only numeric sensor.* states with a state_id beyond the current Bronze maximum.
• PostgreSQL server-side cursors and batched COPY load history without fetching the full source result into Python at once.
• Silver and Gold run only after the Bronze load succeeds, and the success watermark is updated after all three layers complete.
• A separate autocommit monitoring connection can record a failure independently of the data transaction.
• The scheduled batch design keeps Home Assistant local and provides an analytical copy in cloud PostgreSQL.

FAQ

Why keep Home Assistant data local if a cloud warehouse is used?

Home Assistant remains the operational source for home automation. The ETL transfers a selected history for analysis without moving the source installation to the cloud.

How does the pipeline decide which sensor rows are new?

It reads the greatest state_id in bronze.ha_states, then extracts later source rows whose entity ID begins with sensor. and whose state is numeric.

Why use a server-side cursor and PostgreSQL COPY?

A server-side cursor fetches source rows in batches, limiting how much data Python holds at once. PostgreSQL COPY loads each batch more efficiently than issuing one INSERT per row.

When is the success watermark updated?

The watermark is updated only after Bronze, Silver, and Gold complete. A failure in a later transformation is recorded as a failed run instead of being reported as current.

Conclusion

This project moves selected numeric Home Assistant history into cloud PostgreSQL while keeping the automation system local. Python coordinates incremental extraction, SSH carries the connection, and PostgreSQL stores and transforms the data through Bronze, Silver, and Gold.

The main lesson is that even a modest scheduled ETL job benefits from explicit boundaries, bounded batches, transaction handling, and separate monitoring. Together, these choices make the pipeline easier to understand and its failures easier to detect and recover from.