← All workshopsRead Which Sensor Records Should Reach the Training Table?

SQL

Find readings that need repair before feature building

BOTIE

Connect the raw rows, asset list and range to Check the reading contract. Inspect each valid flag before collecting failures.
Ready to test
0 passed · 0 failed · 2 not run

An empty floor. Start with the information your machine needs.

Received result

Your answer will arrive here.

Examples, experiments and workspace controls
Live values

Connect a tool's inputs to see what it produces. You can try a branch before connecting it to Result.

Try your own inputs

Use a copy of the example to try your own data. Keep the problem's assumptions in mind, and predict the effect of a change before checking the output. You can also add another branch to compare two ways of doing the calculation.

Start at an output arrow, then click or drag to an input. Drag empty space to pan. Delete removes a selection. Ctrl/Cmd+Z undoes a change.
Context for this challenge

Connect the raw rows, asset list and range to Check the reading contract. Inspect each valid flag before collecting failures.

A raw sensor batch contains malformed relationships and measurements.

Inspect the component calculation
Check the reading contract(
  Reading records: Raw reading records
  Known asset IDs: Registered asset IDs
  Permitted temperature range: Inclusive temperature range
)
Examples and results
Raw reading records
[
  {
    "id": "R1",
    "asset_id": "P7",
    "value": 20,
    "event_seq": 1,
    "ingest_seq": 2
  },
  {
    "id": "R2",
    "asset_id": "P99",
    "value": 20,
    "event_seq": 1,
    "ingest_seq": 2
  },
  {
    "id": "R3",
    "asset_id": "P7",
    "value": null,
    "event_seq": 1,
    "ingest_seq": 2
  }
]
Registered asset IDs
  1. 0P7
Inclusive temperature range
{
  "min": 0,
  "max": 100
}
Required result
[
  {
    "id": "R1",
    "valid": true
  },
  {
    "id": "R2",
    "valid": false
  },
  {
    "id": "R3",
    "valid": false
  }
]
Raw reading records
[
  {
    "id": "R5",
    "asset_id": "P7",
    "value": 0,
    "event_seq": 1,
    "ingest_seq": 1
  },
  {
    "id": "R6",
    "asset_id": "P7",
    "value": 100,
    "event_seq": 1,
    "ingest_seq": 2
  }
]
Registered asset IDs
  1. 0P7
Inclusive temperature range
{
  "min": 0,
  "max": 100
}
Required result
[
  {
    "id": "R5",
    "valid": true
  },
  {
    "id": "R6",
    "valid": true
  }
]
Try a fresh construction

This clears this draft, including its undo history. Earlier successful attempts stay recorded. Build again without opening hints to record an independent attempt.

Stuck? Inspect a worked construction

The tools below form one possible solution. You can assemble it, follow its signals, and undo that change to return to your own construction.

Assumptions and test precision

The toy contract assumes comparable clocks and a declared unit. It does not prove label truth, representativeness or freedom from leakage. Numerical tests allow a difference of 0.001. Lists and structures must also have the right shape.

Your last tested pieces can start the full construction. An existing draft there keeps its own pieces.

A new accepted range changes which value needs review

This check uses the pieces from “Audit the raw reading contract”.

BOTIE

Both readings have known assets and consistent timestamps. The declared accepted range is now 10 through 40. R7 is 8 and R8 is 40. Predict the rejected IDs.
Raw reading records
[
  {
    "id": "R7",
    "asset_id": "P2",
    "value": 8,
    "event_seq": 1,
    "ingest_seq": 2
  },
  {
    "id": "R8",
    "asset_id": "P2",
    "value": 40,
    "event_seq": 2,
    "ingest_seq": 2
  }
]
Registered asset IDs
  1. 0P2
Inclusive temperature range
{
  "min": 10,
  "max": 40
}

Use a number, text, or an array matching the requested result. For a matrix, put each row in brackets. Saving your prediction runs your current construction on these inputs and checks the earlier examples too.

Build a construction in the workspace above first. This case will test those pieces.

Why can an empty batch have no row-level violations while still be an unhealthy sensor delivery?

Check completeness, freshness and source coverage separately from individual record validity.

The toy contract assumes comparable clocks and a declared unit. It does not prove label truth, representativeness or freedom from leakage.

Work through the SQL challenges →
Continue the coursePick Up New Changes Without Losing Late Readings →
← Let a Search Value Stay a Value