Databricks for IoT Analytics and Machine Learning

Short answer

Databricks stores data as Delta Lake tables (Parquet files plus a transaction log) in your cloud object storage, and runs batch and streaming work on Spark with the same APIs. It is an analytics and ML platform, not an operational time-series database.
The medallion pattern fits sensor data well: bronze keeps raw messages as received, silver holds validated, deduplicated readings with late data resolved, and gold holds roll-ups and features for dashboards and models.
Ingestion options include Kafka through Structured Streaming or read_kafka, Auto Loader for files landed in cloud storage, and Zerobus Ingest (available only in some regions) for pushing records straight into Delta tables without a message bus.
For ML on telemetry, time series feature tables give point-in-time correct training sets, ai_forecast forecasts from SQL (its recommended version 2 was in Beta on 27 October 2026), MLflow tracks and registers models, and Model Serving exposes them as REST endpoints.
Databricks charges per-second for usage measured in Databricks Units (DBUs); with classic compute your cloud provider also bills the virtual machines. Pipelines that run continuously cost more than triggered ones.

How we know: Research-based from the Databricks documentation for AWS (medallion architecture, Structured Streaming, watermarks, Auto Loader, Lakeflow pipelines and expectations, Kafka and read_kafka, Zerobus Ingest, Delta Lake, liquid clustering, VACUUM, MLflow, Model Serving, time series feature tables, ai_forecast, Unity Catalog) and the databricks.com pricing page, all checked 27 October 2026. The bronze-to-silver deduplication, quarantine and roll-up logic was run on PostgreSQL 18.6 (PGlite 0.5.11) to show the rules, as was the daily energy logic (time-weighted kW integration with a maximum gap, coverage and a complete or incomplete status, plus the cumulative register difference with rollover handling) on illustrative sample data in a single session. Databricks SQL statements (streaming tables, read_kafka, expectations, the payload:field::type JSON path casts, the gold_daily_energy view ported from the PostgreSQL script, ai_forecast) are adapted from the documentation and were not run here; each code label says which parts come from which documentation page and which are ours. The ai_forecast status, the JSON path syntax and Zerobus Ingest regional availability were re-checked and quoted on 27 October 2026. No Databricks workspace was used.

What Databricks brings to IoT data, and who this is for

Databricks is a managed data and AI platform built on Apache Spark. Its storage layer is Delta Lake, open source software that adds a transaction log to Parquet files so tables get ACID transactions; Delta is the default table format on Databricks. Tables live in your own cloud object storage (S3, ADLS or GCS), and the same table can be a streaming source, a streaming sink and a batch table.

For IoT that combination matters because sensor work rarely stops at SQL: teams want hourly energy roll-ups, but also anomaly models per asset class, forecasts of chiller load and features joined from maintenance history. Databricks keeps those workloads on one copy of the data with one governance layer, Unity Catalog.

This page is for data engineers and data scientists who already have telemetry flowing and are considering Databricks as the analytics and ML platform behind it. It does not compare Databricks with other platforms; for that see Snowflake vs Databricks, Databricks vs BigQuery, ClickHouse vs Databricks and the Databricks alternatives guide. For time-series storage concepts start with the time-series database overview.

Architecture: streaming, storage and analytics roles

Keep three roles distinct. Messaging moves readings from gateways: MQTT brokers on site, Kafka or a cloud event service in the middle (see Kafka vs MQTT). The operational store, if you need one, serves live trends and alarms with low latency: a time-series database such as TimescaleDB or InfluxDB. Databricks is the analytics layer: full history, cross-site analysis, data quality, feature engineering and model training.

A typical building-management flow: gateways publish AHU, chiller, CO2 and meter readings to MQTT; a bridge forwards them to Kafka; Databricks reads the Kafka topic into a bronze table; pipelines refine it to silver and gold; BI dashboards query gold through a SQL warehouse; and the CMMS receives predicted-fault work orders from a served model. Asset registers and CMMS work-order history are ingested as ordinary tables so they can be joined to readings.

Streaming ingestion options

Kafka. Structured Streaming reads Kafka as a source (and writes to it as a sink). Keys and values arrive as bytes and are cast or parsed explicitly. In SQL, the read_kafka table-valued function feeds a streaming table in a Lakeflow pipeline. For periodic rather than continuous loading, Databricks recommends Kafka with Trigger.AvailableNow, which processes what is available and stops.
Auto Loader. If gateways or an edge service write files (JSON, CSV, Parquet, Avro) to cloud storage, Auto Loader picks up new files incrementally and records progress in its checkpoint; the documentation says this key-value store ensures each file is processed exactly once by Auto Loader (what your own downstream code writes is a separate question).
Zerobus Ingest. A push-based, serverless API that writes records directly into Unity Catalog Delta tables with no message bus to run. It suits producers that only need to land data in the lakehouse; if Kafka already serves other consumers, reading from Kafka avoids a second ingestion path. The documentation states that "Zerobus Ingest is only available in some regions" and links to a list of supported regions, so check that list for your workspace region before you rely on it.

Structured Streaming runs in micro-batch mode by default, with latency in seconds or minutes, and also offers a real-time mode for sub-second processing. Our editorial view: for building telemetry, micro-batches are often enough, because analytics a minute behind the plant is rarely a problem for reporting, and the alarm path should not depend on the analytics platform.

Bronze streaming table from a Kafka topic (Databricks SQL in a Lakeflow pipeline, adapted from the read_kafka documentation examples, which use STREAM read_kafka with bootstrapServers and subscribe and cast value with ::string; table, column and server names are ours. Illustrative configuration, not run here: check every setting against the Databricks documentation for your runtime before use)

CREATE OR REFRESH STREAMING TABLE bms.bronze_reading AS
SELECT
  value::string        AS payload,      -- raw JSON as received
  timestamp            AS kafka_ts,     -- broker timestamp, not the reading time
  current_timestamp()  AS ingested_at
FROM STREAM read_kafka(
  bootstrapServers => 'kafka-1:9092,kafka-2:9092',
  subscribe        => 'bms.readings'
);

The medallion pattern for sensor data

Databricks describes the medallion architecture as bronze (raw), silver (validated) and gold (enriched) layers, and calls it a recommended practice rather than a requirement. Applied to telemetry:

Bronze keeps every message as received, appended incrementally. Databricks recommends storing most fields as strings, VARIANT or binary here so an unexpected schema change from a firmware update does not drop data. Bronze lets you reprocess when a mapping or calibration rule changes.
Silver is where readings are parsed, typed, validated and deduplicated, and where out-of-order and late-arriving data is resolved. Databricks advises against writing to silver directly from ingestion; read bronze as a stream instead.
Gold holds roll-ups and features: hourly mean values per point, daily energy per meter with its coverage and a complete or incomplete status (see daily energy from power readings), daily runtime per AHU, fault counts per asset class, often joined to the asset register and CMMS history.

Data-quality rules in Lakeflow pipelines are expectations: SQL boolean constraints on each record that can warn, drop the row or fail the update, with metrics in the pipeline UI. Deduplication in a stream is a stateful operation, and watermarks bound how long the stream waits for late data and remove old state. A longer watermark tolerates later readings but holds more state and delays results.

The rules themselves are plain SQL. The script below shows the silver and gold logic on PostgreSQL with seven bronze rows: a redelivered message, a sensor fault code, a late reading and a missing timestamp. The same predicates go into expectations and the same dedup key into the silver stream on Databricks.

Silver and gold expectations as a Lakeflow pipeline (Databricks SQL, adapted: the CONSTRAINT ... EXPECT ... ON VIOLATION DROP ROW clause is from the expectations documentation, and payload:field::type uses the JSON path (:) and :: cast syntax from the JSON path expression reference; the combination, names and the plausible-value range are ours. Illustrative, not run here: check every clause against the Databricks documentation for your runtime before use)

CREATE OR REFRESH STREAMING TABLE bms.silver_reading (
  CONSTRAINT has_timestamp   EXPECT (ts IS NOT NULL)                ON VIOLATION DROP ROW,
  CONSTRAINT plausible_value EXPECT (value > -100 AND value < 5000)  ON VIOLATION DROP ROW
) AS
SELECT payload:device_id::string   AS device_id,
       payload:point::string       AS point_name,
       to_timestamp(payload:ts::string) AS ts,
       payload:value::double       AS value,
       kafka_ts
FROM STREAM bms.bronze_reading;

CREATE OR REFRESH MATERIALIZED VIEW bms.gold_point_hourly AS
SELECT point_name,
       date_trunc('HOUR', ts) AS hour,
       count(*)               AS readings,
       avg(value)             AS avg_value,
       max(value)             AS max_value
FROM bms.silver_reading
GROUP BY point_name, date_trunc('HOUR', ts);

Bronze to silver to gold rules: dedup, quarantine and hourly roll-up (run on PostgreSQL 18.6)

-- Bronze: readings exactly as received (duplicates, late rows and a bad value included)
CREATE TABLE bronze_reading (
    device_id   text,
    point_name  text,
    ts          timestamptz,
    value       double precision,
    received_at timestamptz
);
INSERT INTO bronze_reading VALUES
    ('gw-01', 'AHU01.SAT',  '2026-10-27 09:00:00+00', 13.2,  '2026-10-27 09:00:02+00'),
    ('gw-01', 'AHU01.SAT',  '2026-10-27 09:00:00+00', 13.2,  '2026-10-27 09:00:09+00'),  -- redelivered
    ('gw-01', 'AHU01.SAT',  '2026-10-27 09:01:00+00', 13.4,  '2026-10-27 09:01:01+00'),
    ('gw-01', 'AHU01.SAT',  '2026-10-27 09:02:00+00', -999,  '2026-10-27 09:02:01+00'),  -- sensor fault code
    ('gw-02', 'CH01.KW',    '2026-10-27 09:00:00+00', 148.5, '2026-10-27 09:00:03+00'),
    ('gw-02', 'CH01.KW',    '2026-10-27 09:01:00+00', 151.0, '2026-10-27 09:14:40+00'),  -- late arrival
    ('gw-02', 'CH01.KW',    NULL,                     150.2, '2026-10-27 09:02:02+00');  -- missing timestamp

-- Silver: valid rows, one per (point, ts); keep the first delivery
CREATE TABLE silver_reading AS
SELECT device_id, point_name, ts, value
FROM (
    SELECT b.*, row_number() OVER (PARTITION BY point_name, ts ORDER BY received_at) AS rn
    FROM bronze_reading b
    WHERE ts IS NOT NULL
      AND value > -100 AND value < 5000
) d
WHERE rn = 1;

-- Quarantine: what the checks rejected, kept for the data-quality report
SELECT point_name, ts, value,
       CASE WHEN ts IS NULL THEN 'missing timestamp' ELSE 'out of range' END AS reason
FROM bronze_reading
WHERE ts IS NULL OR NOT (value > -100 AND value < 5000)
ORDER BY point_name;

-- Gold: per-point summary for dashboards and model features
SELECT point_name,
       date_trunc('hour', ts) AS hour,
       count(*)               AS readings,
       round(avg(value)::numeric, 2) AS avg_value,
       max(value)             AS max_value
FROM silver_reading
GROUP BY point_name, date_trunc('hour', ts)
ORDER BY point_name;

Results on PostgreSQL 18.6 (PGlite)

quarantine
 point_name |           ts           | value  |      reason
------------+------------------------+--------+-------------------
 AHU01.SAT  | 2026-10-27 09:02:00+00 |   -999 | out of range
 CH01.KW    |                        |  150.2 | missing timestamp

gold (the duplicate is gone; the late CH01 reading is kept in its own minute)
 point_name |          hour          | readings | avg_value | max_value
------------+------------------------+----------+-----------+-----------
 AHU01.SAT  | 2026-10-27 09:00:00+00 |        2 |     13.30 |      13.4
 CH01.KW    | 2026-10-27 09:00:00+00 |        2 |    149.75 |       151

Table layout, retention and sizing

Layout. Databricks recommends liquid clustering for all new tables, including streaming tables and materialized views, in place of partitioning and Z-ordering; the two cannot be combined on one table. For readings, cluster by the columns most queries filter on, such as site or point and the reading timestamp. Clustering keys can be changed later without rewriting existing data, which helps when query patterns change.

Retention. Deleting old readings from a Delta table marks files as removed; VACUUM then deletes unreferenced files older than the retention threshold, 7 days by default, after which time travel to older versions is no longer possible. Predictive optimisation can run VACUUM automatically on Unity Catalog managed tables. One possible policy (an illustrative design choice, not a Databricks recommendation) keeps bronze for a limited period, silver for as long as models may need retraining, and gold for years; set the periods from reprocessing needs, model retraining windows, reporting obligations and storage cost.

Sizing. Estimate rows per day from points and intervals as in the sensor data storage guide; storage is your cloud object storage, billed by your cloud provider, and Parquet compression depends on the data. Compute is the larger lever: right-size the clusters or SQL warehouses and choose triggered over continuous pipelines unless you need minutes of latency.

Daily energy from power readings: coverage before totals

Daily kWh per meter is a typical gold table, and it is easy to get wrong. Adding up 24 hourly mean kW values only approximates the day's energy when every hour is fully and evenly sampled. With real BMS data it is not: a point that reports on change of value sends many readings while the load moves and few while it is steady, so a plain mean over-weights the busy minutes; an hour with a single reading counts as a full hour; and missing hours either drop out silently or, if the day is simply discarded, hide how much data was lost. An earlier version of this page summed hourly means and kept days with 24 hours present; the method below replaces it.

Prefer a cumulative energy register where the meter has one. Many electricity meters expose a running kWh register. Daily energy is then the difference between register readings at the start and end of the day, and a gap in the readings loses resolution, not energy, because the register keeps counting. Two things still need handling: the register rolling over at its maximum (take the maximum from the meter's documentation) and resets when a meter is replaced, which look like a rollover with the wrong size and should be reconciled from the asset or CMMS record. The second query below adds the register maximum back when a difference is negative, counts rollovers so they can be reviewed, and marks a day complete only when it has readings at both midnights.

When only power (kW) is available, integrate it over time. Each pair of consecutive readings of a point forms a segment, and its energy is the mean of the two kW values times the segment length (the trapezoid rule, which assumes power changed linearly between readings). Segments longer than a maximum gap are not counted, because power during the gap is unknown. Segments that cross midnight are cut at the day boundary, with kW interpolated at the cut. Coverage is the number of seconds covered by counted segments, divided by the length of the day, and the daily total is reported only when coverage meets a threshold; otherwise the day is marked incomplete and its total is left NULL, while the energy for the covered time stays visible for diagnosis.

The 15-minute maximum gap and the 98 percent coverage threshold below are illustrative assumptions for this example, not recommendations. Choose your own from the meter's reporting interval (and, for change-of-value points, its heartbeat interval: a long gap from such a point may mean an unchanged value, for which holding the last value is a better model than a straight line), how much missing time your energy reports can tolerate, and any metering or reporting obligations that apply to you. The example uses UTC days; for local-time days, compute the day boundaries in the site's time zone, where a day can be 23 or 25 hours long, which is why coverage divides by the actual day length rather than by 86,400.

In the output, 25 October is fully covered, but the old method gives 3,560.0 kWh against 3,509.0 kWh time-weighted, because ten one-minute readings during a short 300 kW start-up dominate that hour's mean. 26 October has a three-hour gap, so coverage is 87.50 percent and the day is flagged incomplete. On 27 October hour 15 has a single reading: the old method still found 24 hours and reported 3,480.0 kWh, while the time-weighted query finds 95.83 percent coverage and flags the day. The register query reports both days in full despite the two missing reads and the rollover. All figures come from the small sample data in the script, not from a real building.

Time-weighted daily kWh with maximum gap, coverage and status, plus the cumulative register alternative (run on PostgreSQL 18.6, PGlite 0.5.11, single session; sample data, maximum gap, coverage threshold and register maximum are illustrative)

SET TimeZone = 'UTC';

-- Silver power readings for one chiller (kW), unevenly sampled; timestamps in UTC
CREATE TABLE silver_power (
    point_name text             NOT NULL,
    ts         timestamptz      NOT NULL,
    kw         double precision NOT NULL,
    PRIMARY KEY (point_name, ts)
);

-- Normally a reading every 10 minutes: 180 kW from 08:00 to 17:59, 120 kW otherwise
INSERT INTO silver_power
SELECT 'CH01.KW', t, CASE WHEN extract(hour FROM t) BETWEEN 8 AND 17 THEN 180 ELSE 120 END
FROM generate_series(timestamptz '2026-10-25 00:00+00', timestamptz '2026-10-28 00:00+00', interval '10 minutes') t
WHERE NOT (t > '2026-10-26 10:00+00' AND t < '2026-10-26 13:00+00')   -- 26 Oct: three-hour gap
  AND NOT (t > '2026-10-27 15:00+00' AND t < '2026-10-27 16:00+00');  -- 27 Oct: sparse hour, one reading at 15:00

-- 25 Oct: a start-up spike reported once a minute, 08:00 to 08:09 at 300 kW (uneven sampling)
UPDATE silver_power SET kw = 300 WHERE ts = '2026-10-25 08:00+00';
INSERT INTO silver_power
SELECT 'CH01.KW', t, 300
FROM generate_series(timestamptz '2026-10-25 08:01+00', timestamptz '2026-10-25 08:09+00', interval '1 minute') t;

-- Old method, for comparison: sum of hourly mean kW, keeping days with 24 hours present
SELECT point_name, day, round(sum(avg_kw)::numeric, 1) AS kwh_old
FROM (SELECT point_name, date_trunc('hour', ts) AS hour, (date_trunc('hour', ts))::date AS day, avg(kw) AS avg_kw
      FROM silver_power WHERE ts < '2026-10-28 00:00+00'
      GROUP BY point_name, date_trunc('hour', ts)) h
GROUP BY point_name, day
HAVING count(*) = 24
ORDER BY day;

-- Time-weighted daily energy with a maximum gap and a coverage threshold
WITH params AS (
    SELECT interval '15 minutes' AS max_gap,       -- illustrative: longer segments are not counted
           0.98::numeric          AS min_coverage,  -- illustrative: share of the day that must be covered
           timestamptz '2026-10-25 00:00+00' AS report_from,
           timestamptz '2026-10-27 00:00+00' AS report_to   -- start of the last day reported
),
seg AS (   -- one segment per pair of consecutive readings of the same point
    SELECT point_name, ts AS t0, kw AS kw0,
           lead(ts) OVER w AS t1, lead(kw) OVER w AS kw1
    FROM silver_power
    WINDOW w AS (PARTITION BY point_name ORDER BY ts)
),
usable AS (
    SELECT s.* FROM seg s CROSS JOIN params p
    WHERE s.t1 IS NOT NULL AND s.t1 - s.t0 <= p.max_gap
),
days AS (
    SELECT pts.point_name, d::date AS day, d AS day_start, d + interval '1 day' AS day_end
    FROM (SELECT DISTINCT point_name FROM silver_power) pts
    CROSS JOIN params p
    CROSS JOIN generate_series(p.report_from, p.report_to, interval '1 day') d
),
clipped AS (   -- cut segments that cross midnight at the day boundary
    SELECT d.point_name, d.day, u.t0, u.t1, u.kw0, u.kw1,
           greatest(u.t0, d.day_start) AS a,
           least(u.t1, d.day_end)      AS b
    FROM usable u
    JOIN days d ON d.point_name = u.point_name AND u.t0 < d.day_end AND u.t1 > d.day_start
),
energy AS (    -- trapezoid: mean of the (interpolated) kW at both ends x hours
    SELECT point_name, day,
           extract(epoch FROM b - a) AS secs,
           ( (kw0 + (kw1 - kw0) * extract(epoch FROM a - t0) / extract(epoch FROM t1 - t0))
           + (kw0 + (kw1 - kw0) * extract(epoch FROM b - t0) / extract(epoch FROM t1 - t0)) ) / 2
           * extract(epoch FROM b - a) / 3600 AS kwh
    FROM clipped
),
daily AS (
    SELECT d.point_name, d.day,
           coalesce(sum(e.secs), 0) AS covered_s,
           coalesce(sum(e.secs), 0) / extract(epoch FROM d.day_end - d.day_start) AS coverage,
           sum(e.kwh) AS covered_kwh
    FROM days d
    LEFT JOIN energy e ON e.point_name = d.point_name AND e.day = d.day
    GROUP BY d.point_name, d.day, d.day_start, d.day_end
)
SELECT point_name, day, round(covered_s) AS covered_s,
       round(100 * coverage, 2) AS coverage_pct,
       round(covered_kwh::numeric, 1) AS covered_kwh,
       CASE WHEN coverage >= p.min_coverage THEN round(covered_kwh::numeric, 1) END AS daily_kwh,
       CASE WHEN coverage >= p.min_coverage THEN 'complete' ELSE 'incomplete' END AS status
FROM daily CROSS JOIN params p
ORDER BY point_name, day;

-- Preferred where the meter has one: a cumulative energy register (kWh)
CREATE TABLE silver_register (
    point_name text          NOT NULL,
    ts         timestamptz   NOT NULL,
    kwh_reg    numeric(10,1) NOT NULL,
    PRIMARY KEY (point_name, ts)
);
-- Hourly register reads; 130 kWh an hour; the register rolls over at 100,000.0;
-- the 10:00 and 11:00 reads on 26 Oct are missing
INSERT INTO silver_register
SELECT 'CH01.KWH', t,
       mod(99000 + 130 * extract(epoch FROM t - timestamptz '2026-10-26 00:00+00') / 3600, 100000)
FROM generate_series(timestamptz '2026-10-26 00:00+00', timestamptz '2026-10-28 00:00+00', interval '1 hour') t
WHERE t NOT IN ('2026-10-26 10:00+00', '2026-10-26 11:00+00');

WITH params AS (SELECT 100000::numeric AS register_max),   -- illustrative: from the meter's documentation
d AS (
    SELECT point_name, ts AS t0, lead(ts) OVER w AS t1,
           lead(kwh_reg) OVER w - kwh_reg AS raw_delta
    FROM silver_register
    WINDOW w AS (PARTITION BY point_name ORDER BY ts)
)
SELECT point_name, t0::date AS day,
       min(t0) AS first_read, max(t1) AS last_read,
       sum(CASE WHEN raw_delta < 0 THEN raw_delta + p.register_max ELSE raw_delta END) AS daily_kwh,
       count(*) FILTER (WHERE raw_delta < 0) AS rollovers,
       CASE WHEN min(t0) = t0::date::timestamptz AND max(t1) = t0::date::timestamptz + interval '1 day'
            THEN 'complete' ELSE 'check boundaries' END AS status
FROM d CROSS JOIN params p
WHERE t1 IS NOT NULL
GROUP BY point_name, t0::date
ORDER BY day;

Results on PostgreSQL 18.6 (PGlite 0.5.11)

old method (sum of hourly means, days with 24 hours present)
 point_name |    day     | kwh_old
------------+------------+---------
 CH01.KW    | 2026-10-25 |  3560.0
 CH01.KW    | 2026-10-27 |  3480.0     <- reported although hour 15 has one reading

time-weighted (maximum gap 15 minutes, coverage threshold 98%)
 point_name |    day     | covered_s | coverage_pct | covered_kwh | daily_kwh |   status
------------+------------+-----------+--------------+-------------+-----------+------------
 CH01.KW    | 2026-10-25 |     86400 |       100.00 |      3509.0 |    3509.0 | complete
 CH01.KW    | 2026-10-26 |     75600 |        87.50 |      2940.0 |      NULL | incomplete
 CH01.KW    | 2026-10-27 |     82800 |        95.83 |      3300.0 |      NULL | incomplete

cumulative register (two reads missing on 26 Oct; one rollover)
 point_name |    day     |       first_read       |       last_read        | daily_kwh | rollovers |  status
------------+------------+------------------------+------------------------+-----------+-----------+----------
 CH01.KWH   | 2026-10-26 | 2026-10-26 00:00:00+00 | 2026-10-27 00:00:00+00 |    3120.0 |         1 | complete
 CH01.KWH   | 2026-10-27 | 2026-10-27 00:00:00+00 | 2026-10-28 00:00:00+00 |    3120.0 |         0 | complete

The same time-weighted daily energy as a Lakeflow materialized view (Databricks SQL; illustrative, not run here: ported by us from the PostgreSQL script above, using lead, unix_timestamp, sequence and explode; assumes the session time zone is UTC; the 900-second maximum gap and 0.98 threshold are illustrative; check every function against the Databricks SQL reference for your runtime before use)

CREATE OR REFRESH MATERIALIZED VIEW bms.gold_daily_energy AS
WITH seg AS (
  SELECT point_name,
         unix_timestamp(ts) AS s0, value AS kw0,
         unix_timestamp(lead(ts) OVER (PARTITION BY point_name ORDER BY ts)) AS s1,
         lead(value) OVER (PARTITION BY point_name ORDER BY ts)              AS kw1
  FROM bms.silver_reading
  WHERE point_name LIKE 'CH%.KW'
),
usable AS (
  SELECT * FROM seg
  WHERE s1 IS NOT NULL AND s1 - s0 <= 900           -- maximum gap, illustrative
),
days AS (
  SELECT point_name, explode(sequence(d0, d1, INTERVAL 1 DAY)) AS day
  FROM (SELECT point_name, min(to_date(ts)) AS d0, max(to_date(ts)) AS d1
        FROM bms.silver_reading
        WHERE point_name LIKE 'CH%.KW'
        GROUP BY point_name)
),
clipped AS (
  SELECT u.point_name, d.day, u.s0, u.s1, u.kw0, u.kw1,
         greatest(u.s0, unix_timestamp(CAST(d.day AS TIMESTAMP)))            AS a,
         least(u.s1, unix_timestamp(CAST(date_add(d.day, 1) AS TIMESTAMP)))  AS b
  FROM usable u
  JOIN days d
    ON d.point_name = u.point_name
   AND u.s0 < unix_timestamp(CAST(date_add(d.day, 1) AS TIMESTAMP))
   AND u.s1 > unix_timestamp(CAST(d.day AS TIMESTAMP))
),
energy AS (
  SELECT point_name, day, b - a AS secs,
         ( (kw0 + (kw1 - kw0) * (a - s0) / (s1 - s0))
         + (kw0 + (kw1 - kw0) * (b - s0) / (s1 - s0)) ) / 2 * (b - a) / 3600 AS kwh
  FROM clipped
)
SELECT d.point_name, d.day,
       coalesce(sum(e.secs), 0)         AS covered_s,
       coalesce(sum(e.secs), 0) / 86400 AS coverage,      -- UTC days are 86,400 s long
       sum(e.kwh)                       AS covered_kwh,
       CASE WHEN coalesce(sum(e.secs), 0) / 86400 >= 0.98 THEN sum(e.kwh) END AS daily_kwh,
       CASE WHEN coalesce(sum(e.secs), 0) / 86400 >= 0.98
            THEN 'complete' ELSE 'incomplete' END AS status
FROM days d
LEFT JOIN energy e ON e.point_name = d.point_name AND e.day = d.day
GROUP BY d.point_name, d.day;

Machine learning on telemetry

The ML case for Databricks in IoT is training on large, joined histories and deploying the result close to the data:

Point-in-time features. Time series feature tables have a timestamp key, so a training set for "fault in the next 7 days" gets the latest feature values as of each label's time and avoids leaking future readings into training. Any Unity Catalog Delta table with primary and timestamp keys can serve as one; Databricks' own example uses room sensor data (temperature, humidity, light, CO2).
Forecasting from SQL. ai_forecast() is a table-valued function that extrapolates time series forward, per group and for several metrics, which suits load or consumption forecasts per meter. The function page (last updated 18 September 2026, checked 27 October 2026) says: "Version 1 of this function is in Public Preview and HIPAA compliant. Version 2 (recommended) is in Beta." It lists a Pro or Serverless SQL warehouse as a requirement, and enrolment in the Predictive AI Functions preview as mandatory for version 2. Beta features can change, so treat forecasts from it as experimental until it is generally available. The example below forecasts only days whose energy total was marked complete, so its input has missing dates; the function page says "ai_forecast does not materialize 0s for missing or NULL entries in the table" and that "For sparse data, it is best practice to coalesce missing values or provide a frequency value explicitly to avoid unexpected output from the "auto" frequency inference.", so the query sets frequency to daily rather than filling incomplete days with zeros.
Tracking and registry. MLflow records experiments and parameters and holds a model registry in Unity Catalog.
Serving. Model Serving exposes a model as a REST endpoint for real-time calls, or as a function for batch inference from SQL, so a nightly job can score every AHU and raise CMMS work orders for the highest risk.

Model quality depends on labels. In facilities data, labels usually come from CMMS work orders (fault type, asset, opened and closed times), which are often incomplete or coded inconsistently; budget time for cleaning them. The anomaly detection overview covers simpler statistical methods that need no labels.

Forecast daily kWh per chiller for the next 30 days (Databricks SQL, adapted from the ai_forecast documentation: argument names horizon, time_col, value_col, group_col and version => '2' are from its syntax section; the query around it is ours and reads the gold_daily_energy view from the daily energy section; version 2 is Beta. Illustrative, not run here: check every argument against the ai_forecast documentation for your workspace before use)

WITH daily AS (
  -- only days whose coverage met the threshold; incomplete days are left out,
  -- so the series can have missing dates (see the note above the query)
  SELECT point_name, day AS ds, daily_kwh AS kwh
  FROM bms.gold_daily_energy
  WHERE status = 'complete'
)
SELECT * FROM AI_FORECAST(
  TABLE(daily),
  horizon   => date_add(current_date(), 30),
  time_col  => 'ds',
  value_col => 'kwh',
  group_col => 'point_name',
  frequency => 'D',   -- stated explicitly because incomplete days are missing from the input
  version   => '2'
);

Security, governance and operations

Governance. Unity Catalog enforces access control on tables, volumes, models and functions, records lineage and logs access for auditing. It is enabled automatically for workspaces created after 8 November 2023. Give ingestion jobs write access to bronze only, and analysts read access to gold.
Data location. Managed tables sit in cloud storage you configure; keep the workspace, storage and Kafka in the same region to avoid transfer charges.
Pipelines. Lakeflow pipelines manage checkpoints, retries and recovery for streaming tables. Monitor expectation metrics so a firmware change that sends values in different units shows up as dropped rows, not silent errors.
Schema changes. New BMS points and renamed tags are routine; keep bronze schemaless enough to accept them and handle mapping in silver.
Separation from operations. Do not put the life-safety or alarm path through the analytics platform; keep it on the BMS and the operational store.

Limitations and when not to use Databricks for IoT

Low-latency operational queries. "Latest value of every point on this floor, refreshed every second" belongs in an operational time-series database, not a lakehouse.
Small estates and SQL-only needs. Our editorial view: if the work is a few dashboards over one site, PostgreSQL or a single warehouse is likely to be cheaper and simpler to run.
No data engineering capacity. Databricks rewards teams that can write and operate pipelines in SQL or Python. Without that, a managed warehouse with simpler loading may fit better; see BigQuery for historical IoT analytics.
Always-on streaming for little benefit. Continuous pipelines keep compute running. If hourly freshness is enough, triggered runs cost less.
Edge or disconnected sites. Databricks runs in the cloud; buffering and local control at the site still need edge software and a local store.

Pricing

Prices from the provider's official pricing pages, checked 27 October 2026, region any (pricing model only; rates vary by cloud, region and plan), in USD, excluding tax. List prices only; discounts, commitments and your actual usage change the bill.

The databricks.com pricing page describes pay-as-you-go pricing with no up-front cost, billed at per-second granularity, with usage measured in Databricks Units (DBUs) at rates that differ by product (for example SQL warehouses, jobs and model serving) and by classic or serverless compute. With classic compute in your own cloud account, the cloud provider also bills the virtual machines, storage and network. Rates differ by cloud, region and plan, so we do not reproduce them here; use the pricing calculator for your configuration.

For IoT workloads the main cost drivers are how often pipelines run (continuous ingestion costs more and has lower latency than triggered runs, as Databricks' medallion guidance notes), the size and uptime of SQL warehouses behind dashboards, and model serving endpoints left running.

Frequently asked questions

Is Databricks a time-series database?

No. It is a lakehouse platform for analytics and machine learning that can store and analyse time-series data as Delta tables. For live dashboards and alarms with second-level latency, pair it with an operational time-series database.

What is the medallion architecture for IoT data?

A layering pattern: bronze stores raw messages as received, silver stores validated and deduplicated readings with late data resolved, and gold stores roll-ups and features for dashboards and models. Databricks recommends it but does not require it.

Can Databricks read directly from Kafka or MQTT?

It reads Kafka natively through Structured Streaming and the read_kafka SQL function. MQTT is usually bridged to Kafka or a cloud event service first, or devices push records with Zerobus Ingest.

How do I handle late or duplicate sensor readings on Databricks?

Keep everything in bronze, deduplicate on a key such as point and reading timestamp when building silver, and use watermarks to bound how long a streaming query waits for late readings. Expectations drop or flag invalid rows.

Does Databricks need partitioned tables for sensor data?

Databricks now recommends liquid clustering for new tables instead of partitioning; cluster by the columns you filter on, such as point and timestamp. Clustering keys can be changed later without rewriting data.

Sources

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.