Unit_Fact job failed again

[shahbazkhan1999]

failed with this error.. FnName: Execute Direct -- [Informatica][ODBC PostgreSQL Wire Protocol driver]Socket closed., SQLSTATE [08S01]

[spetros-do]

The 3/12 discussion b/w EPA/DPC identifies multiple potential issues contributing to the ETL/DM job's failure and explores various solutions:

1. Extended Truncation Time:

   • A job attempted to truncate a table at 1:44 AM but failed at 4:23 AM, taking approximately three hours. This delay is hypothesized to be due to a streaming service API causing locks on the table.

2. Socket Closed Error:

   • A job failed due to a "socket closed" error, speculated to be because another job might have prematurely closed the tunnel needed for database connection.

3. Streaming API and External Queries:

   • The possibility of external users running intensive queries through a public API, potentially flooding the system. This led to considering rate limiting or setting a maintenance window to restrict access during critical job operations.

4. Investigation and Next Action Items:

   • DPC team is tasked with further investigation, including adjusting API rate limits and potentially identifying users causing the load.
   • A consideration to move away from truncate and reload strategies towards using a log table approach for data management.
   • Implement a technical/policy level approach by limiting user queries during specific maintenance times.
   • Confirming driver versions and database compatibility, along with the potential need to update system configurations to support current operational requirements.

[mark-hayward-erg]

Follow-up question:

• Can the Informatica job be updated to recover from the "socket closed' error?

Another option for altering the reloading process:

• Add a transitional data load schema (i.e., not the real schema/tables) on the Postgres database that Informatica loads into. The data would then be moved to the real tables in a database transaction. This would avoid a situation where the tables get truncated and not reloaded, leaving empty tables.

[annalbrecht]

Blocked by #5997.

[j-tafoya]

"[Mike] and I configured the API gateway for staging to limit the streaming requests to 1 every 10 seconds. If a user exceeds this threshold they will receive an http status 429 “Over Rate Limit”. This does not effect any requests coming from the CAMPD application, only users that have their own API key. We’ll test this in staging for a bit and then plan for a rollout to production. If you are testing in staging and notice any strange behavior please let us know. "

[alangmaid]

@ibarra-michelle @maheese DPC/ERG suggest punting this to 2025 Q1

[ibarra-michelle]

@alangmaid and @annalbrecht , yes @maheese and I agree to punt to 2025 Q1, thx.

[szintgraff]

Record of DataMart Job Failure. Reruning this job at 9 am caused production issues. Refer to RT tickets: CAMPD Support #74781 and CAMPD Support #74782

s_m_Load_CAMD_UNIT_FACT_1719389958.log

DataMart Job Failure Notification.pdf

[szintgraff]

Record of DataMart Job Failure Notification Saturday 7/6 at 4:23 am.

s_m_Load_CAMD_UNIT_FACT_1720254150.log