Bronze → silver IoT medallion

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Medallion. About 25 minutes. Part of the Pro drill bank.

Factory sensors send readings to a raw table called telemetry (the bronze layer). The raw data is messy: some readings are empty, some are not numbers, and when a device resends a message the same reading_id appears twice. Your job is to build the cleaned 'silver' table that dashboards use.

From telemetry, build the silver table with these columns: reading_id, device_id, plant, metric, reading (as a DOUBLE, a decimal number), and event_time. Drop every row where reading is empty, NULL, or text that is not a number. If the same reading_id was received more than once, keep only the row with the earliest ingest_time. Sort the result by event_time, then reading_id.

Requirements

  • Convert reading to a DOUBLE and remove rows where this is not possible.
  • Keep only 1 row per reading_id: the one with the earliest ingest_time.
  • Return reading_id, device_id, plant, metric, reading, event_time.
  • Do not return ingest_time.
  • Order by event_time, then reading_id.

Constraints

  • reading in bronze can be NULL, an empty value, or a non-numeric string.
  • Rows with a bad reading are excluded completely. They are not replaced with 0.
  • Use TRY_CAST(reading AS DOUBLE) so a bad value becomes NULL instead of stopping the query.

Examples

Input: reading_id | device_id | metric | reading | event_time | ingest_time R00007 | DEV-007 | current_a | 11.54 | 2026-08-01 10:12:29 | 2026-08-01 10:13:14 R00008 | DEV-008 | temp_c | (empty) | 2026-08-01 10:34:12 | 2026-08-01 10:35:19 R00009 | DEV-009 | vibration_mm | 2.663 | 2026-08-01 10:56:05 | 2026-08-01 12:14:05 Output: reading_id | device_id | plant | metric | reading | event_time R00007 | DEV-007 | berlin-dc | current_a | 11.54 | 2026-08-01 10:12:29 R00009 | DEV-009 | chennai-east | vibration_mm | 2.663 | 2026-08-01 10:56:05 Why this passes: R00008's reading is empty/unparseable, so the whole row is dropped. The surviving rows are cast to DOUBLE and ingest_time is dropped from the output.

Topics: medallion, quality, cast.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

Bronze → silver IoT medallion

Production ticket: Clean sensor readings into a typed silver layer.

`telemetry` is bronze: `reading` can be null/empty, and devices can emit duplicates of `reading_id`. Produce silver with: - reading_id, device_id, plant, metric, reading (DOUBLE), event_time - drop rows where reading is null - keep the earliest ingest_time per reading_id - order by event_time, reading_id