Freshness checks are warning/failing

[ian-r-rose]

We've started to get some warnings (which will shortly be errors) in our source freshness checks: https://california.getdbt.com/deploy/6/projects/10/runs/5414

Unsure if the issue is with the checks or with the relay process right now, @pingpingxiu-DOT-ca-gov can you take a look?

[ian-r-rose]

@pingpingxiu-DOT-ca-gov the freshness checks are now failing, are you able to take another look?

https://california.getdbt.com/deploy/6/projects/10/runs/5449

[pingpingxiu-DOT-ca-gov]

I’m looking into it now.

From: Ian Rose @.> Sent: Friday, October 4, 2024 8:56 AM To: cagov/caldata-mdsa-caltrans-pems @.> Cc: Xiu, @. @.>; Mention @.***> Subject: Re: [cagov/caldata-mdsa-caltrans-pems] Freshness checks are warning/failing (Issue #411)

EXTERNAL EMAIL. Links/attachments may not be safe.

@pingpingxiu-DOT-ca-govhttps://urldefense.com/v3/__https:/github.com/pingpingxiu-DOT-ca-gov__;!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMSppU320$ the freshness checks are now failing, are you able to take another look?

https://california.getdbt.com/deploy/6/projects/10/runs/5449https://urldefense.com/v3/__https:/california.getdbt.com/deploy/6/projects/10/runs/5449__;!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMsLFpEQU$

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/cagov/caldata-mdsa-caltrans-pems/issues/411*issuecomment-2394010826__;Iw!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMMDvXSzk$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/BDVOYUMO6NDKKT25FACAVSTZZ23AZAVCNFSM6AAAAABPKE2N6CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGOJUGAYTAOBSGY__;!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMAdq2P2Y$. You are receiving this because you were mentioned.Message ID: @.**@.>>

[pingpingxiu-DOT-ca-gov]

My latest 3 day’s changes on ES had some bug that impacted data relay, which I fixed. However, the freshness check continue to fail.

 Now, the source data, is the remaining cause. I checked on the Oracle source table,

And found the SELECT * FROM PEMS.DETECTOR_CONFIG_LOG where time_id > TRUNC(SYSDATE) – 2

returns empty data. And it is the case for the DETECTOR, STATION and CONTROLLER.

 Ken, is there any upstream changes that cause the stale for the config tables?

From: Ian Rose @.> Sent: Friday, October 4, 2024 8:56 AM To: cagov/caldata-mdsa-caltrans-pems @.> Cc: Xiu, @. @.>; Mention @.***> Subject: Re: [cagov/caldata-mdsa-caltrans-pems] Freshness checks are warning/failing (Issue #411)

EXTERNAL EMAIL. Links/attachments may not be safe.

@pingpingxiu-DOT-ca-govhttps://urldefense.com/v3/__https:/github.com/pingpingxiu-DOT-ca-gov__;!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMSppU320$ the freshness checks are now failing, are you able to take another look?

https://california.getdbt.com/deploy/6/projects/10/runs/5449https://urldefense.com/v3/__https:/california.getdbt.com/deploy/6/projects/10/runs/5449__;!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMsLFpEQU$

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/cagov/caldata-mdsa-caltrans-pems/issues/411*issuecomment-2394010826__;Iw!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMMDvXSzk$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/BDVOYUMO6NDKKT25FACAVSTZZ23AZAVCNFSM6AAAAABPKE2N6CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGOJUGAYTAOBSGY__;!!LWi6xHDyrA!97aMtB7LRI2XcWjdleMwxBss753BNqDz6y5v_cjvH3KFt6uLakW8Z83oTaWiXj25L9rtS9BNvFe1kT1MMQtrM6tMAdq2P2Y$. You are receiving this because you were mentioned.Message ID: @.**@.>>

[ian-r-rose]

Interesting, so it's with the underlying system? That's a problem, but it's good that the freshness checks are working!

[pingpingxiu-DOT-ca-gov]

Agree, an effective quality safeguard!

From: Ian Rose @.> Sent: Friday, October 4, 2024 11:29 AM To: cagov/caldata-mdsa-caltrans-pems @.> Cc: Xiu, @. @.>; Mention @.***> Subject: Re: [cagov/caldata-mdsa-caltrans-pems] Freshness checks are warning/failing (Issue #411)

EXTERNAL EMAIL. Links/attachments may not be safe.

Interesting, so it's with the underlying system? That's a problem, but it's good that the freshness checks are working!

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/cagov/caldata-mdsa-caltrans-pems/issues/411*issuecomment-2394339812__;Iw!!LWi6xHDyrA!4vp7vaLEiGPf6hXby3vPsuKdwIxiHQcFkQ68Kld9mr7yxxIlplYyjaLRow1btXiB-2w8m8uWEEOZS4WZlmc8Tk1zNVaXwSM$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/BDVOYUMQCQHTXOO5X5NZWVTZZ3M7TAVCNFSM6AAAAABPKE2N6CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGOJUGMZTSOBRGI__;!!LWi6xHDyrA!4vp7vaLEiGPf6hXby3vPsuKdwIxiHQcFkQ68Kld9mr7yxxIlplYyjaLRow1btXiB-2w8m8uWEEOZS4WZlmc8Tk1zxlorHHU$. You are receiving this because you were mentioned.Message ID: @.**@.>>

[kengodleskidot]

Good morning and I see that the last equipment set update occurred on 10/1/24 for D3. It looks like the D8 daily equipment feed update last occurred on 9/16/24. I will follow up with our consultant to see what may have changed to affect the D8 feed but since all other equipment feeds require manual updates they are not updated as frequently.

[jkarpen]

Per @kengodleskidot the issue appears to be that the districts are not sending data updates. He will follow up with them to see if there is a failure on there side or if this is expected behavior.

If this is expected, @pingpingxiu-DOT-ca-gov says we can add a new timestamp which would detect if the data relay is the issue vs. the equipment data feed.

[pingpingxiu-DOT-ca-gov]

I added https://github.com/cagov/caldata-mdsa-caltrans-pems/pull/436 PR for making less sensitive on the detector config log freshness.