Data Lakes and Lakehouses for Maintenance Telemetry

"Put the sensor data in a data lake" is one of those instructions that sounds like an architecture and is actually a sentence. It says nothing about which data, at what resolution, organised how, joined to what, curated by whom, or retained for how long. In my experience advising on asset and maintenance systems, the projects that go wrong here almost never go wrong on the storage technology. They go wrong because nobody decided what the lake was for, nobody owned the mapping between operational technology tags and asset numbers, and nobody was funded to curate what landed. This guide is deliberately platform-neutral. The vendors change their service names every couple of years; the data shapes and the failure modes do not.

The message up front: a maintenance operation produces three fundamentally different shapes of data, and forcing them into one store is the original sin. The CMMS or EAM stays the system of record for work and assets. The lake is for analysis, history and model training, never a replacement for the transactional system. And the single determinant of whether the whole thing produces value is whether someone owns the mapping from OT tag to asset number. That is a person, not a technology.

1. Three data shapes, three different stores

Start by separating what you have. Maintenance and condition-monitoring environments generate three categories of data with almost nothing in common except the assets they describe. They differ in volume by orders of magnitude, in write pattern, in query pattern, in how often they change, and in who owns them.

• High-frequency sensor telemetry. Vibration, temperature, current, pressure, flow, power. Append-only, timestamped, enormous in row count, narrow in schema, and almost never updated after it is written. Queries are overwhelmingly by tag and time range. It arrives from a historian, a building management system, a SCADA layer or an IoT gateway, and it is owned by operations or engineering.
• Transactional maintenance records. Work orders, work order tasks, labour and time bookings, parts issued, failure codes, meter readings, permits, purchase lines. Modest in volume, wide in schema, heavily relational, and mutable: a work order changes status half a dozen times in its life. It lives in the CMMS or EAM and is owned by the maintenance function.
• Asset master data. The asset register, the location and functional hierarchy, classifications, criticality ratings, manufacturer and model, warranty and commissioning dates, the parent-child structure. Small, slow-changing, and the reference frame that gives everything else meaning. It is owned, or should be owned, by an asset data function.

The reason this matters architecturally is that each shape has a store it suits. Trying to hold high-frequency telemetry in a relational CMMS database is how you end up with a system nobody can back up. Trying to hold mutable work order records in an append-only object store without a table format on top is how you end up with a directory of files that disagree about the truth. A store optimised for one of these is usually poor at the others, and the sensible architecture is not one store but a small, deliberate set of them with clear ownership at each boundary.

2. The vocabulary, sorted out honestly

Most of the confusion in these conversations is vocabulary theatre. Each of these terms was invented to solve a specific, real problem, and once you know which problem, the choices stop being ideological.

• Data warehouse. Invented to solve the problem that operational databases are terrible at analytics. Data is modelled up front, cleaned on the way in, schema enforced, and queried with SQL by many people at once. It is excellent at governed reporting on structured data. Its historical weakness was cost and rigidity at very high volumes of semi-structured or raw data, and the awkwardness of holding things you have not yet decided how to model.
• Data lake. Invented to solve the cost and flexibility problem. Land anything, in open file formats, on cheap object storage, and decide the schema when you read it. That flexibility is genuine and it is also exactly how lakes become swamps, because "decide later" often means "never decide".
• Lakehouse. Invented to solve the problem that lakes could not behave transactionally. Plain files on object storage give you no atomic updates, no consistent reads while a write is in progress, no schema enforcement and no time travel. The lakehouse pattern puts a metadata and transaction layer over the same open files so you get warehouse-like guarantees on lake-like storage, queryable by multiple engines.
• Open table formats. The mechanism that makes the lakehouse possible. Delta Lake , Apache Iceberg and Apache Hudi each add a transaction log or manifest layer over columnar files, so a set of files becomes a table with atomic commits, snapshot isolation, schema evolution and, typically, some form of historical snapshot access. Their capabilities and their interoperability have moved quickly and continue to, so check the current documentation at those project sites rather than trusting a comparison written a year ago, including mine.

The practical reading for a maintenance data platform: you almost certainly want an open table format rather than raw files, because maintenance data is not purely append-only. Work orders get corrected, sensor backfills arrive late, an asset gets reclassified and you need to restate. Without a table format those operations mean rewriting directories and hoping nobody was reading. With one they are ordinary commits. Which of the three formats you pick matters much less than picking one deliberately, checking your query engines and your governance tooling actually support it, and not accumulating a different one per team.

3. Time-series store and lake are complementary, not alternatives

This is the question I am asked most often and it is usually framed wrongly: should we use a time-series database or a data lake? The honest answer is that they do different jobs and mature architectures run both.

A time-series store, and a process historian is a specialised time-series store with decades of engineering behind it, is built for a particular access pattern: write a very high rate of timestamped values, then read a small number of tags over a recent window, fast, with interpolation and aggregation semantics that engineers expect. Operator screens, trend displays, alarm evaluation and near-real-time analytics all want this. A lake is built for the opposite pattern: scan an enormous amount of history across many tags, join it to other datasets, and produce features or aggregates in bulk. Model training, multi-year reliability analysis and cross-domain joins all want that.

The split that works, and that I would advise as the default:

Note the direction of travel. The historian keeps the hot window because that is what it is good at, and the lake accumulates the long history because that is what it is good at. The export between them is a first-class pipeline that needs monitoring, not a one-off extract someone ran in a workshop. For the mechanics of getting data out of a historian and the pitfalls of tag selection, compression settings and interpolation semantics, see SCADA historian integration rather than re-deriving it here.

4. Bronze, silver, gold applied to maintenance data

The medallion pattern, bronze for raw, silver for conformed, gold for consumption, is popular because it gives you somewhere to put the messy reality without letting it contaminate what analysts use. It is also frequently implemented as three folders with no actual difference in discipline between them. Applied concretely to maintenance and condition-monitoring data, it looks like this.

The discipline that makes this real rather than decorative is a simple rule: nothing in silver was produced by hand, and nothing in gold reads directly from bronze. The moment an analyst starts pulling raw historian files into a notebook because silver did not have what they needed, you have two pipelines, and the second one is undocumented. That is not a tooling failure. It is a signal that silver is not being curated fast enough to keep up with demand, which is a funding and ownership problem.

5. Partitioning, file sizes and the small-files problem

If a maintenance lake performs badly, my first guess, before anything else, is small files. It is the most common cause by a wide margin and it is entirely self-inflicted.

The mechanism is straightforward. Object storage and the query engines over it are efficient at reading a moderate number of reasonably large columnar files and inefficient at opening an enormous number of tiny ones, because every file carries per-file overhead in listing, opening, and reading metadata. Streaming telemetry ingestion naturally produces tiny files: if a pipeline commits every few seconds across hundreds of tags, and the partitioning scheme splits by tag and by hour on top of that, you can generate millions of files representing a modest amount of actual data. The query that should scan a month of one tag instead spends its life on file metadata.

What I would advise:

• Partition on the columns you actually filter by, and stop there. For telemetry that is usually date, and often a coarse site or plant identifier. Partitioning by individual tag is the classic mistake: it feels natural because that is how engineers think, and it shatters the dataset.
• Do not partition by high-cardinality keys. Asset number, tag name and equipment serial are high cardinality. Keep them as ordinary columns and rely on the table format's statistics, clustering or sort ordering to prune within a partition.
• Aim for substantial files rather than many small ones. Exact targets depend on the engine and format, and the guidance genuinely changes, so take the number from your engine's current documentation rather than from a blog. The principle is stable even when the number is not.
• Schedule compaction as a first-class job. All the mainstream open table formats provide some mechanism for compacting small files and expiring old snapshots. Treat that maintenance as part of the platform, monitored and alerted, not as something you run when queries get slow.
• Separate the streaming landing zone from the analytical table. Let the stream write small files into a landing area, then compact on a schedule into the partitioned analytical table. Trying to make one table serve both low-latency writes and large scans is where most small-file problems originate.

6. Schema evolution as sensors and assets change

Maintenance estates are not stable. Sensors are added, replaced with a different model that reports different fields, recalibrated, moved to another asset. Assets are replaced, reclassified, split, or merged. The CMMS gets upgraded and a vendor renames or adds columns. A data model that assumes today's schema is permanent will break within a year, and it will break silently, which is worse.

The patterns that hold up:

• Additive change by default. New sensor attributes become new nullable columns, or land in a flexible attributes structure if the shape is genuinely unpredictable. Renaming and retyping existing columns in place is what causes downstream breakage, so avoid it and deprecate instead.
• Keep bronze faithful to the source. If the source changes its schema, bronze records both the old and new shapes with their ingestion dates. That is your escape hatch when you discover six months later that a unit changed.
• Version the conforming logic, not just the data. The transformation from bronze to silver is code, and it needs the same review, testing and release discipline as any other production code. A quiet change to a unit conversion is a data incident.
• Treat sensor replacement as a new series with a link, not a continuation. If a vibration sensor is swapped for a different model, the readings before and after are not strictly comparable. Record the change, keep the identity continuity at the asset level, and let analysts see that a discontinuity exists rather than discovering a step change and modelling it as a fault.
• Handle asset change as slowly-changing dimensions. An asset that was criticality B last year and criticality A now must be reportable both ways. Effective-dated master data is the mechanism, and it is a master data discipline before it is a lake feature. The asset-side design work is covered in asset hierarchy design and master data management for assets.

7. Downsampling and retention: how much raw data is worth keeping

This is the question people avoid because the honest answer is "it depends", and because storage looks cheap enough to postpone the decision. Postponing it is itself a decision, and usually an expensive one, because the cost is rarely the storage. It is the scan cost, the pipeline runtime, the compaction load and the governance surface of data nobody uses.

The way I would frame it. Raw high-frequency data has analytical value for exactly two things: detecting signatures that only exist at high frequency, which mainly means vibration spectra and electrical waveforms, and reconstructing what happened in a narrow window around an event. For everything else, statistical aggregates over a short window carry nearly all the information at a fraction of the volume. So a defensible policy usually looks like tiering rather than a single retention number.

• Keep full-resolution raw data for a bounded recent window on the tags where high-frequency analysis is genuinely done, and only on those tags. This is where reliability engineers do diagnostic work.
• Keep event windows at full resolution indefinitely. When a failure, trip or alarm occurs, preserve the raw data for a window around it permanently. Those windows are your labelled training data and your forensic record, and they are a small fraction of total volume. This is the single highest-value retention rule I would push for.
• Downsample the rest to aggregates at a resolution matched to how the asset actually degrades. Slow thermal and wear processes do not need second-level history. Keep mean, min, max, standard deviation and a count of the underlying samples, so you know how much data each aggregate represents.
• Set retention per data shape, and write it down. Master data and work order history are small and worth keeping for the life of the asset and beyond, for warranty, statutory and reliability reasons. Raw telemetry is large and usually not. Those are different policies and conflating them leads to either losing records you needed or hoarding samples you never touch.

8. The join that makes it valuable: telemetry to work orders and failures

Everything above is plumbing. The analytical value of a maintenance data platform comes almost entirely from one join: sensor behaviour over a period, connected to what was found and done on that asset during and after that period. That join is what lets you say a given vibration pattern preceded a bearing failure, or that a corrective work order followed a temperature excursion, or that an asset class fails differently under a particular duty. Without it you have trend charts. With it you have reliability engineering.

It is also the hardest join in the domain, and the reason is unglamorous: the two sides do not share an identifier. The OT side identifies things by tag, and tags encode plant, unit, equipment and measurement in a naming convention that was designed for control engineers, not for analytics. The CMMS side identifies things by asset number, drawn from an asset register with its own hierarchy and its own history. Nothing automatically connects PLT1.PMP.104.VIB.RMS to asset PU-00104. Somebody has to say so.

A workable mapping table has more in it than people expect:

• The tag, exactly as the source emits it, including case and separators, because near-matches will bite you.
• The asset number as the register holds it, and the functional location if your model separates equipment from position.
• What the tag measures, in a controlled vocabulary, so a query can ask for "all vibration RMS on centrifugal pumps" without parsing strings.
• Unit of measure and expected range, which is also how you detect unit drift later.
• Valid from and valid to dates. Tags get reassigned when equipment moves. A mapping without effective dates will silently attribute one asset's history to another.
• Confidence and provenance. Whether this row was confirmed by an engineer, inferred from a naming convention, or guessed. Mark the guesses, because analysts need to know which conclusions rest on them.

If your asset model distinguishes the equipment from the position it occupies, and mature EAM implementations usually do, then the mapping generally belongs to the position rather than the serialised equipment: a sensor measures the pump slot, and the pump in that slot may be swapped. Getting that wrong is a subtle and very common error that makes fleet analysis wrong in a way that is hard to spot.

9. Tag naming, controlled vocabulary and the metadata around it

The mapping problem is smaller where tag naming is disciplined, which is an argument for engaging with naming conventions rather than treating them as someone else's legacy. A convention that encodes plant, system, equipment and measurement in fixed positions with a controlled vocabulary can be parsed into a proposed mapping, which an engineer then confirms rather than authors. A convention that grew organically across three contractors and two decades cannot, and on a brownfield estate that is what you will usually find.

What I would do in each case. On greenfield or a major retrofit, get the naming convention written into the specification and the commissioning documentation, and make the tag list with its asset mapping a deliverable the contractor must hand over, not something you reverse-engineer afterwards. On brownfield, accept that you will parse what you can, confirm the high-criticality assets manually first, and leave the long tail marked as unmapped rather than guessed. An honest gap is far more useful to an analyst than a plausible fabrication.

Around the mapping sits the rest of the metadata that makes telemetry interpretable: what the sensor is, where it is mounted, its sampling rate, its calibration date, its expected range, and any compression or exception settings applied on the historian side, because those settings change what the stored series actually means. If you inherit a historian and nobody can tell you its compression configuration, you do not fully know what your history represents. That is worth establishing early. Sizing, tag counts and collection configuration are covered separately in the historian material; here the point is simply that the metadata has to travel with the data into the lake.

10. Data quality checks that matter for sensor data

Sensor data fails in characteristic ways, and generic data quality tooling built for business data tends to miss all of them because the rows look individually fine. These are the checks I would build into the bronze-to-silver step, emitting flags rather than dropping rows.

• Gaps. A tag that should report at a known cadence and stops. Gaps matter twice over: they corrupt aggregates computed over the window, and a gap during an incident is exactly when you needed the data. Flag the gap and carry a sample-count column on every aggregate so downstream users can see thin coverage.
• Stuck values. A sensor reporting the identical value for an implausibly long run. This is the single most deceptive failure mode because the data looks perfectly healthy, the trend is flat, and a threshold alarm will never fire. Detect runs of unchanged values relative to what is plausible for that measurement type.
• Unit drift and unit inconsistency. Two sensors on identical assets reporting in different units, or a tag whose unit changed after a firmware update or a replacement, with nothing in the data to say so. Expected-range checks against the mapping table are the practical detector.
• Clock skew. Gateways, controllers and historians with unsynchronised clocks, or timestamps stored in local time with a daylight transition in the middle. This is quietly fatal to the telemetry-to-work-order join, because a correlation that is offset by hours looks like no correlation at all. Normalise to UTC, keep the site offset as a column, and check for out-of-order and future-dated timestamps at ingestion.
• Out-of-range and impossible values. Negative flows, temperatures below ambient possibility, spikes far outside the instrument's range. Flag rather than silently clip, because a clipped spike hides a real event.
• Duplicate and replayed data. Re-run exports and retried streams produce duplicates that inflate counts and distort averages. Idempotent ingestion keyed on tag plus source timestamp is the defence.

Publish these as a visible quality summary per tag, not as a hidden log. A reliability engineer who can see that a tag has been stuck for three weeks will get it fixed. A pipeline that quietly filters the stuck values out will produce a clean-looking dataset that misleads everyone.

11. Governance, lineage and access control

Governance in this domain is not primarily a compliance exercise, though in regulated and critical-infrastructure environments it is that too. It is what stops the platform becoming unusable.

• A catalogue people actually use. Every table in silver and gold needs an owner, a description in plain language, a refresh schedule and a definition of its key measures. If a maintenance analyst cannot find out what a table means without asking an engineer, they will build their own.
• Lineage from gold back to source. When a number on a reliability dashboard is disputed, and it will be, you need to trace it back through gold, silver and bronze to the historian export or the CMMS extract it came from. Lineage is what makes that a ten-minute question instead of a two-week investigation.
• Access control that reflects sensitivity, not habit. Telemetry that reveals plant operating patterns, and work order data containing named individuals, contractor rates or security-relevant detail, are not equally shareable. Grant on curated layers and keep broad access out of bronze. The role design thinking here is the same as elsewhere in asset systems, and it is worth reading alongside data governance in asset-heavy organisations.
• Personal data discipline. Labour bookings, technician identifiers and time records are personal data in most jurisdictions. They will end up in the lake if work order detail is replicated wholesale. Decide deliberately whether you need them, and pseudonymise in silver if the analysis does not require identity.
• Boundary rules with the OT side. The flow from control and historian systems to the analytical platform should be one-directional, reviewed by the people responsible for the operational environment, and documented. This is a security question as much as a data one.

12. The honest failure mode: ingestion funded, curation not

Here is the pattern I have watched enough times to state plainly. The project is scoped and funded around ingestion. Connect the historian, connect the CMMS, land the data, demonstrate volume. That part is tractable, has a visible finish line, and demos well: look how much data we now have. The project closes, the integrator leaves, and the curation work, the mapping maintenance, the quality checks, the schema changes, the catalogue entries, the compaction jobs, the definition disputes, has no owner and no budget. Eighteen months later the lake holds a great deal of data that nobody trusts enough to make a decision on, and the organisation concludes that the technology failed.

The technology did not fail. Curation was never funded. The uncomfortable arithmetic is that ingestion is a project and curation is an operating cost, and organisations are much better at approving the former. If you are shaping one of these programmes, the most useful thing you can do is refuse to let it be scoped as a build. Put named ownership, standing effort and a recurring budget for mapping maintenance, quality monitoring, catalogue upkeep and table maintenance into the business case from the start, even if that makes the case harder to approve. A smaller, curated scope that covers your critical assets properly is worth more than a comprehensive lake nobody trusts.

A related and equally honest point: do not build this before there is a consumer. A lake with no analyst, no reliability engineer and no model waiting for it accumulates cost and entropy. Start from a question someone is actually asking, such as which failure modes on the critical pump fleet have a detectable precursor, build the narrow slice of the platform that answers it end to end, and let the architecture grow behind real demand. The broader question of whether your organisation is ready for this at all is closer to the predictive maintenance practitioner's guide than to anything in this article.

The idea to walk away with

A maintenance data platform is three data shapes, two or three stores, one layering discipline and one mapping table that somebody owns. The storage technology is the least consequential decision in that list. Pick an open table format deliberately, keep the hot operational window in the time-series store where it belongs, let the lake hold history and training data, layer it so that raw messiness never reaches the people making decisions, and accept that the tag-to-asset mapping is permanent manual work rather than a one-off cleanup.

The CMMS or EAM remains the system of record for work and assets throughout. The lake exists to answer questions those systems cannot answer alone, particularly the question of what the sensors were doing before something failed. That is the whole point, and it is reachable only through the join that the mapping table makes possible.

Which cloud you run it on is a genuine but secondary question, and one worth separating from this architecture. The platform-specific reference designs are covered in their own right for Azure and AWS, and the question of what stays local is covered in edge versus cloud for predictive maintenance. The layering, partitioning, mapping and curation decisions in this article apply the same way on all of them.

Final thoughts

If you have been handed "put the sensor data in a data lake" as an instruction, the most valuable thing you can do is turn it back into a set of decisions before any storage is provisioned. Which data shapes are in scope. What the hot window is and where it lives. Which table format, and why. What lands in bronze, what conforming silver performs, and which gold tables have named consumers. How tags map to assets, who owns that, and how new sensors get added to it. What is retained at full resolution and what is aggregated. Which quality checks run and how their results are published. And who is funded, permanently, to keep all of that true.

Answer those and the platform will be modest, useful and trusted. Skip them and you will end up with the thing the industry has an unkind name for, and the name will not be the technology's fault.

Related reading: SCADA historian integration, Master data management for assets, Asset hierarchy design, Data governance in asset-heavy organisations, Predictive maintenance: a practitioner's guide.