[A01] analyze/apply that loads 300+ CSV files per table hangs CheckAndFinalize until Step Functions execution timeout

[ogawsh]

[A01] analyze/apply がテーブルごとに 300+ ファイルの CSV を読む時、CheckAndFinalize が無限ポーリングで SFn タイムアウトになる

再現条件

• 1 プレフィックス配下に 同一ヘッダの CSV が 300 本以上（今回は 350 本、各 ~10GB / 合計 ~1TB 規模を想定）
• Admin UI から analyze → apply
• apply が裏で起動する Step Functions AdminBackendInitWorkflowStateMachine が TIMED_OUT

観測された挙動

• Step Functions execution が 7200 秒ちょうどで TIMED_OUT
• Admin UI は "Unknown error" としか表示しない
• StartBuild Lambda は 140 秒で正常終了（DROP / CREATE / _execute_sql_async で COPY を 350 本発行し Data API から Id を受領）
• CheckAndFinalize Lambda は 10 秒 Wait → 140ms で {all_done: false, completed_tables: 0} を返すループを ~700 回繰り返して 2 時間
• Redshift Serverless の sys_query_history / sys_load_history を同時間帯で検索しても COPY 履歴は 0 件
• 発行した statement_id を redshift-data describe-statement で直叩きすると ResourceNotFoundException: Query does not exist

原因（推定）

1. CheckAndFinalize に独自タイムアウト / 最大試行回数がない（致命的）

lambda/redshiftinitworkflow/handlers.py の handle_check_and_finalize:

for table_name, stmt_ids in statement_ids.items():
    ...
    for stmt_id in stmt_ids:
        desc = redshift_data.describe_statement(Id=stmt_id)
        status = desc["Status"]
        ...
        elif status != "FINISHED":
            table_running = True
            break
    ...
if still_running:
    return {"all_done": False, ...}

• still_running になったら 10 秒後にまた呼ばれて再評価するだけで、独自のタイムアウトや再試行上限がない
• COPY が何らかの理由で Redshift に届かず Data API の SUBMITTED / PICKED で滞留した場合、このループから抜けられない
• SFn の TimeoutSeconds 7200 が唯一の脱出口で、失敗時に意味のあるエラーを返せていない（→ UI「Unknown error」）

2. StartBuild が同時実行数制御なしに execute_statement を大量投入

• _execute_sql_async は Data API に直列で 350 本投げるだけ
• Redshift Serverless は同時実行数制限があり（RPU / ワークグループ設定依存）、Data API 側の Active Statement 制限（default 200）もある
• 上限超えで SUBMITTED のまま滞留すると、Redshift 側で実行されず sys_query_history に記録されない
• COPY ... MANIFEST を使って 1 テーブル = 1 COPY に集約する / 同時投入数に上限をつける等の保護が無い

3. 進捗の可視化がない

• CheckAndFinalize はテーブル単位で「最初の非 FINISHED で break」するので、completed_statements / total_statements の粒度で進捗を返せない
• UI 側は completed_tables しか受け取れず、3 時間進捗 0 のまま等の判別ができない

再現手順

1. 任意の S3 プレフィックスに同一ヘッダの CSV を 300 本以上配置（1 ファイル数 GB 推奨）
2. Admin UI から該当プレフィックスを指定して Agent を作成、analyze 実行
3. 生成された DDL で apply 実行
4. AdminBackendInitWorkflowStateMachine が 2 時間で TIMED_OUT することを観測

期待される挙動

• ファイル数が多い場合でも合理的な時間内に完了するか、明示的に「ファイル数が多すぎる」「COPY が詰まっている」旨のエラーを返す
• 具体的には以下のいずれか / 組み合わせ:
  • COPY を MANIFEST オプションで 1 テーブル = 1 COPY にまとめる
  • CheckAndFinalize に試行回数上限を設け、超過時は status: stuck で SFn を明示失敗させる
  • Data API の同時実行数制限を意識して execute_statement をバッチ投入する
  • Admin UI が SFn の ExecutionTimedOut / ExecutionFailed を受け取って具体的なメッセージを表示する

参考情報

• 対象スタック: arn:aws:cloudformation:ap-northeast-1:411521242467:stack/devDwhAgentStack/6619ffe0-3f8b-11f1-a3dc-068ad41190bd
• SFn execution ARN: arn:aws:states:ap-northeast-1:411521242467:execution:AdminBackendInitWorkflowStateMachine4193125B-ArEyUWKHNQd8:16479835-502e-4e6d-86a9-4ed68ba7b173
• Redshift Serverless workgroup: Base 8 RPU / max 未設定
• 関連ファイル: lambda/redshiftinitworkflow/handlers.py の _execute_sql_async, handle_check_and_finalize

[ogawsh]

Ran a smaller-scale variant of the same workload to help narrow the failure boundary.

A01-b (100 GB / 30 files × ~3.1 GiB each, same sensor schema, same prefix style, fresh staging stack):

• Step Functions execution: arn:aws:states:ap-northeast-1:411521242467:execution:AdminBackendInitWorkflowStateMachine4193125B-SxMdod2fTY64:3f0ec29f-47a8-45b3-b705-1d5302ed3069
• Status: SUCCEEDED in 45:09 (07:32:18 → 08:17:27 JST)
• Redshift side (SYS_QUERY_HISTORY, query_type = 'COPY'):
  • 30 COPY statements, all SUCCESS
  • All 30 submitted within the same second (22:33:24 UTC)
  • First success: 22:33:24 UTC → last success: 23:17:11 UTC (43m elapsed)
  • Average elapsed per COPY: 1,351 s ≒ 22.5 min
• During the run CheckAndFinalize was observed to enter the same "completed_tables: 0, all_done: false" polling loop, but the loop eventually terminated successfully because the 30 COPY statements actually reached Redshift and completed.

Comparison with the failing A01 (1 TB / 350 files):

	A01 (350 files, ~1 TB)	A01-b (30 files, ~100 GB)
COPY statements reached Redshift	0 (none in SYS_QUERY_HISTORY at all)	30 / 30
Describe-statement result later	ResourceNotFoundException: Query does not exist	all reachable and FINISHED
SFn outcome	TIMED_OUT after 7200 s	SUCCEEDED after 2709 s
CheckAndFinalize polling	Infinite loop (never unstuck)	Unstuck once all COPYs finished

So the failure mode is not just "CheckAndFinalize has no timeout" — the underlying trigger for A01 is that the ~350 COPY statements never reach Redshift at all. At 30 files they all reach Redshift and finish. The boundary sits somewhere between 30 and 350 files; I will follow up with the 150-file variant (A01-a, 500 GB) to narrow it further.

Regardless of where the exact boundary lives, both the root cause (unbounded burst of execute_statement calls) and the lack-of-timeout in CheckAndFinalize should be addressed.

[ogawsh]

Followup with A01-a (150 files × ~3.1 GiB = 500 GB) — this one also fails with the same "Unknown error" on the Admin UI, but via a different failure mode than A01 (350 files).

A01-a results:

• Execution ARN: arn:aws:states:ap-northeast-1:411521242467:execution:AdminBackendInitWorkflowStateMachine4193125B-SxMdod2fTY64:c65b82fa-e030-48e6-92bf-3953f36796ce
• Status: TIMED_OUT at exactly 7200 s (09:19:12 → 11:19:12 JST)
• Admin UI: "Unknown error"
• Redshift side (SYS_QUERY_HISTORY, query_type = 'COPY'):
  • 150 COPY statements, all submitted within ~1 second starting 00:19:29 UTC
  • 83 succeeded, 67 still running when Step Functions timed out
  • That is, at SFn timeout the tail of the batch was still ~45% incomplete and would have continued to run in Redshift past the 2-hour window if the state machine hadn't died

Updated comparison table:

	A01-b (30 files, 100 GB)	A01-a (150 files, 500 GB)	A01 (350 files, 1 TB)
COPY statements in SYS_QUERY_HISTORY	30 / 30 (all success)	150 / 150 (83 success, 67 running at timeout)	0
describe_statement(id) later	all findable, FINISHED	all findable, mix of FINISHED/STARTED at timeout	ResourceNotFoundException: Query does not exist
SFn outcome	SUCCEEDED in 2709 s (≈45 min)	TIMED_OUT at 7200 s	TIMED_OUT at 7200 s
Root failure mode	none	CheckAndFinalize polls forever because 67 COPYs genuinely aren't done in 2 h	Something in the Data API / Serverless submission pipeline silently drops the 350 statements; they never even reach Redshift

Implications for the fix:

1. At least two distinct symptoms map to the same "Unknown error" in the UI. Both are ultimately caused by the pair "StartBuild fires a large batch of execute_statement calls" × "SFn's 7200 s hard cap is the only exit condition", but the underlying Redshift-side state is totally different. The fix must handle both cases.

2. CheckAndFinalize should bail out with a descriptive error (not just silently time out at the SFn level) when (a) a statement_id becomes Query does not exist and (b) the total polling time exceeds some threshold. Right now the Admin UI gets nothing actionable.

3. For realistic loads of 150+ files of several-GB each, Redshift Serverless Base 8 RPU on this workgroup simply cannot process them in the 2-hour SFn window. Either:

   • Use COPY ... MANIFEST to collapse N files into 1 statement (N files → 1 queue slot, batch-friendly)
   • Or raise the SFn TimeoutSeconds and let CheckAndFinalize poll much longer
   • Or split the apply into multiple state-machine runs with an explicit progress endpoint

Per-COPY timing stayed roughly comparable between A01-b and A01-a (≈22 min average per 3.1 GiB file on Base 8 RPU), so the throughput ceiling is on the Redshift side. The tool needs to match its polling timeout to that reality instead of pretending 2 hours is always enough.

Average elapsed per COPY in A01-a: ~1,000 s for the 83 that succeeded within 2 h, comparable to the 1,351 s observed on A01-b. Serverless scaled a bit (max capacity on this workgroup is unset, so the RPU default applies).