# Googlecloudplatform Dataflowtemplates Dataflowtemplates

> Skill: Dataflow Template Logical Error Debugging

- Skill: `tomevault-io/googlecloudplatform-dataflowtemplates-dataflowtemplates` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/googlecloudplatform-dataflowtemplates-dataflowtemplates`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/googlecloudplatform-dataflowtemplates-dataflowtemplates/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/googlecloudplatform-dataflowtemplates-dataflowtemplates

---


# Skill: Dataflow Template Logical Error Debugging

## Trigger
`/smt-e2e-dataflow-debugging <FILE_NAME>.tfvars`

## Scope
This skill is **STRICTLY** restricted to testing the following templates:
*   `gcs-spanner-dv`
*   `sourcedb-to-spanner`
*   `datastream-to-spanner`
*   `spanner-to-sourcedb`

## Goal
To debug logical errors and data discrepancies by comparing source data (e.g., Cloud SQL) with destination data (e.g., Spanner).

## Prerequisites
*   The Dataflow job must be able to launch and run to a terminal state (Succeeded, Failed) using the provided `.tfvars` file. This skill is NOT for debugging startup/runtime crashes.
*   `terraform` CLI installed and configured.
*   `gcloud` CLI installed and authenticated (`gcloud auth login`).
*   `mvn` (Maven) and a compatible JDK installed.
*   `git` installed.
*   Access to the source database (e.g., Cloud SQL) instance, database, and credentials.
*   Access to the destination Spanner instance and database.
*   Appropriate IAM permissions for Dataflow, Cloud SQL, Spanner, GCS, and Cloud Logging.
*   Database client tools installed (e.g., `psql`, `mysql` client, or willingness to use `gcloud sql connect`).

## Variables
*   `<FILE_NAME>.tfvars`: The Terraform variables file.
*   `<YOUR_PROJECT_ID>`: The target Google Cloud Project ID.
*   `<YOUR_REGION>`: The Google Cloud region for the Dataflow job.
*   `<JOB_ID>`: The Dataflow Job ID.
*   `<JOB_NAME>`: The name of the Dataflow job.
*   **Cloud SQL Connection Info (Extracted from .tfvars):**
    *   `<CSQL_INSTANCE>`: Cloud SQL instance name.
    *   `<CSQL_DATABASE>`: Cloud SQL database name.
    *   `<CSQL_USER>`: Cloud SQL username.
    *   `<CSQL_PASSWORD>`: Cloud SQL password (if applicable).
    *   `<CSQL_PROJECT>`: Project of the Cloud SQL instance.
*   **Spanner Connection Info (Extracted from .tfvars):**
    *   `<SPANNER_INSTANCE>`: Spanner instance name.
    *   `<SPANNER_DATABASE>`: Spanner database name.
    *   `<SPANNER_PROJECT>`: Project of the Spanner instance.
*   `<MAVEN_MODULE_PATH>`: The relative path to the template's maven module (e.g. `v2/sourcedb-to-spanner`).
*   `<TEMPLATE_NAME>`: The name of the template.
*   `<YOUR_STAGING_BUCKET>`: Cloud Storage bucket for staging.

## Global Agent Rules
* **Fail-Fast Protocol**: If any executed terminal command returns an error or non-zero exit code, STOP IMMEDIATELY. Output the error to the user and ask for intervention. Do not attempt autonomous retries.
* **Data Privacy**: Ensure no real PII, credentials, or production connection details are exposed in logs or command outputs.

## Workflow Phases

### Phase 1: Deployment & Job Monitoring
1.  **Deploy the Dataflow Job**:
    *   Navigate to the directory containing `<FILE_NAME>.tfvars`.
    *   Initialize Terraform:
        ```bash
        terraform init
        ```
    *   Launch the job:
        ```bash
        terraform apply --var-file=<FILE_NAME>.tfvars -auto-approve
        ```
2.  **Monitor Status**: Wait for the job to reach a terminal state (Succeeded or Failed). Monitor status via Google Cloud Console or `gcloud`.

### Phase 2: Log Collection & Initial Analysis
1.  **Retrieve Job IDs**: Infer `<JOB_ID>` and `<JOB_NAME>` from the Terraform output or by listing active jobs:
    ```bash
    gcloud dataflow jobs list --project=<YOUR_PROJECT_ID> --region=<YOUR_REGION>
    ```
2.  **Inspect Job Logs**: Retrieve job message logs to identify warnings or non-obvious issues:
    ```bash
    gcloud logging read 'resource.type="dataflow_step" AND resource.labels.job_id="<JOB_ID>" AND logName=~"projects/.*/logs/dataflow.googleapis.com%2Fjob-message"' \
      --project=<YOUR_PROJECT_ID> \
      --limit=200 \
      --format="table(timestamp, textPayload, severity)" --order=asc
    ```
3.  **Inspect Worker Logs**: Check worker execution logs for specific exceptions or stack traces:
    ```bash
    gcloud logging read 'resource.type="dataflow_step" AND logName=~"projects/.*/logs/dataflow.googleapis.com%2Fworker" AND resource.labels.job_id="<JOB_ID>"' \
      --project=<YOUR_PROJECT_ID> \
      --limit=500 \
      --format="table(timestamp, jsonPayload.message, severity)" --order=asc
    ```

### Phase 3: Data Inspection & Validation
1.  **Inspect Source Data (Cloud SQL)**: Connect to the Cloud SQL instance and query source tables:
    ```bash
    # For PostgreSQL
    gcloud sql connect <CSQL_INSTANCE> --user=<CSQL_USER> --project=<CSQL_PROJECT>
    # For MySQL
    gcloud sql connect <CSQL_INSTANCE> --user=<CSQL_USER> --project=<CSQL_PROJECT>
    ```
    Execute validation queries:
    ```sql
    SELECT COUNT(*) FROM your_source_table;
    SELECT * FROM your_source_table LIMIT 10;
    ```
2.  **Inspect Destination Data (Spanner)**: Execute SQL queries on the destination Spanner database:
    ```bash
    gcloud spanner databases execute-sql <SPANNER_DATABASE> \
      --instance=<SPANNER_INSTANCE> \
      --project=<SPANNER_PROJECT> \
      --sql="SELECT COUNT(*) FROM your_destination_table"

    gcloud spanner databases execute-sql <SPANNER_DATABASE> \
      --instance=<SPANNER_INSTANCE> \
      --project=<SPANNER_PROJECT> \
      --sql="SELECT * FROM your_destination_table LIMIT 10"
    ```

### Phase 4: Discrepancy Verification & Resolution
1.  **Analyze Differences**:
    *   **Row Counts**: Do source and destination row counts match?
    *   **Schema Mapping**: Are column types and names mapped correctly?
    *   **Value Assertions**: Check NULL values, string encoding, timestamps, and precision.
2.  **Code Correction**: Locate the transformation logic in the `.java` files under the `GoogleCloudPlatform/DataflowTemplates` repository and fix the bug.
3.  **Re-stage Template**: Rebuild and upload the updated Flex Template:
    ```bash
    mvn clean package -PtemplatesStage -DskipTests \
      -DprojectId="<YOUR_PROJECT_ID>" \
      -DbucketName="<YOUR_STAGING_BUCKET>" \
      -DstagePrefix="templates" \
      -DtemplateName="<TEMPLATE_NAME>" \
      -pl <MAVEN_MODULE_PATH> -am
    ```

### Phase 5: Re-testing & Iteration
1.  **Clean Destination Table**: Delete destination records to prepare for a clean run:
    ```bash
    gcloud spanner databases execute-sql <SPANNER_DATABASE> \
      --instance=<SPANNER_INSTANCE> \
      --project=<SPANNER_PROJECT> \
      --sql="DELETE FROM your_destination_table WHERE true"
    ```
2.  **Re-run Job**: Deploy the job again with the newly built template (Phase 1) and verify the fix.

## Important Considerations
*   **Idempotency**: Ensure clean test tables before re-running to avoid duplicate count errors.
*   **Data Volume**: Limit queries to subsets when working with large volumes.
*   **Terraform State**: Avoid modifying resources manually outside of Terraform to prevent state drift.

---
> Source: [GoogleCloudPlatform/DataflowTemplates](https://github.com/GoogleCloudPlatform/DataflowTemplates) — distributed by [TomeVault](https://tomevault.io).
<!-- tomevault:4.0:skill_md:2026-07-03 -->