Data path verification

Step 1: Review AI's SQL queries

Ask AI to show the SQL queries it used for the join and aggregation. Read each query line by line.

Check three things:

• Did the pond mapping use the correct assignments (SID-001 = Pond C, SID-003 = Pond E, SID-006 = Pond G)?
• Did any filter exclude data unexpectedly?
• Did the temporal aggregation group by cycle date ranges, not by month or by the full period?

This is data path verification. When AI queries data directly, the student's job includes verifying the queries -- not just the results.

Step 2: Verify join completeness

Direct AI to count: how many production records have matching sensor data? How many sensor readings were included versus excluded? The join should cover all records for the 3 sensor-equipped ponds and exclude the 5 ponds without sensors.

If the counts do not match expectations, trace back to the query. A mismatch here usually means the join condition or the temporal alignment has an error.

Step 3: Plot sensor readings over time

Direct AI to plot sensor readings over time for each of the three sensors. Line plots with dates on the x-axis and one parameter (dissolved oxygen works well) on the y-axis.

Look at the patterns. Seasonal variation is expected -- conditions change across months. Random noise is expected. But look for anything that does not fit the pattern.

Step 4: Discover the calibration drift

One sensor's readings look different during a specific period. SID-003 has approximately two weeks in mid-March where the readings shift systematically. Not random noise -- a consistent offset. The values still fall within the plausible range, which is why it was not obvious in the summary statistics. But in the time series, the shift is visible.

AI did not flag this. AI treats all connected data as equally trustworthy. It queried the sensor_readings table, computed aggregates, and used the results without questioning whether the underlying readings were reliable. This is a concrete limitation: the infrastructure gave AI access to the data, but AI does not evaluate the data's quality.

Step 5: Ask Budi about sensor reliability

Message Budi and ask about the sensors. Any issues, any maintenance events, any periods where readings might be unreliable?

He reveals the calibration issue. One sensor drifted for about two weeks before the vendor recalibrated it. He forgot about it because the readings looked mostly normal.

Step 6: Decide how to handle the drifted readings

This is a genuine design choice. Options:

• Exclude the two-week period entirely from the analysis
• Flag the readings as potentially unreliable and include them with a caveat
• Keep them and note the limitation

The decision depends on how much data is at stake (about 336 readings out of ~4,300 for that sensor) and how the drift affects the per-cycle aggregates. Direct AI to quantify the impact: how much do the aggregates change if you exclude versus include the drifted readings?

Make the decision and document the rationale.

Step 7: Check for power outage gaps

Direct AI to identify the gap periods in the sensor data -- hours where readings are missing entirely (not zero values, just absent rows). Confirm these are real outage gaps, distributed across the 6 months. Decide whether to impute or leave as-is. For hourly data with ~2% missing, leaving gaps is usually acceptable -- the aggregates are robust to occasional missing hours.

The idea of hooks surfaces here. Imagine if a check ran automatically every time new sensor data was loaded -- it could flag readings that are statistically anomalous before you build an analysis on top of them. That kind of deterministic guardrail is called a hook. You will not build one in this project, but the concept is worth noting: verification that fires automatically, regardless of whether the analyst remembers to check.