PingCAP Datapipelines ===================== GCP-native extract/load jobs for the ingestion work that previously lived under `/Users/ywang/projects/pingcap-datalake/ingestion`. The pipeline shape is: 1. Extract source tables into Parquet under a Terraform-managed GCS bucket. 2. Write a run manifest to GCS with the exact Parquet object URIs and source schema metadata. 3. Load each unprocessed manifest into BigQuery raw history tables. 4. Record the loaded run in BigQuery control tables so later load jobs skip already processed manifests. Target project: `gcp-digital-bi`. Sources ------- Managed source configs live in `configs/sources/`: - `data_foundation_finance`: TiDB Cloud `finance`, excluding backup/heartbeat tables - `helix`: read-only MySQL projections for canonical cloud accounts and employee-directory users, including soft-deleted rows - `nexuscrm`: TiDB Cloud `nexuscrm`, excluding `_ops_*` and `EmailMessage` Each managed source has its own GCS bucket plus BigQuery raw, final, and control datasets. NexusCRM outbound ----------------- The first outbound job reads `gcp-digital-bi.google_sheets.arr_sync_integration_nexuscrm` and upserts `Month_End_ARR__c` through the NexusCRM v63 Composite Collections API. It is a separate Cloud Run Job in this repository and image; it does not write the CRM TiDB database directly. Run source-only validation locally: ```bash uv run pingcap-pipeline outbound \ --job configs/jobs/month_end_arr_outbound.yaml \ --dry-run ``` After supplying `NEXUSCRM_CLIENT_ID` and `NEXUSCRM_CLIENT_SECRET`, validate the live staging object contract without writing: ```bash uv run pingcap-pipeline outbound \ --job configs/jobs/month_end_arr_outbound.yaml \ --validate-only ``` The write command omits both mode flags. It rejects invalid or duplicate source rows before the first API write, skips rows with blank `account_id`, writes in batches of at most 200, and records run/per-record outcomes in `gcp-digital-bi.outbound_control`. Successful rows whose normalized payload has not changed are skipped on later runs. Source deletions intentionally do not delete CRM records in this first version. Before validation or writes can pass, NexusCRM must have a unique string field `Month_End_ARR__c.Outbound_Key__c`, and the Connected App run-as user must have create/update access to the object and mapped fields. See `docs/month-end-arr-outbound-runbook.md` for the exact contract and staging rollout sequence. Terraform leaves `enable_month_end_arr_outbound=false` by default so the first apply can create the empty Secret Manager secrets; enable the job only after both credential versions have been added. NetSuite revenue voucher NexusCRM API outbound ----------------------------------------------- `netsuite-revenue-to-nexuscrm` is a separately governed Design v0.8 job. It freezes the fixed NetSuite revenue-voucher export, applies exact-one active CRM Account admission, and writes only through the NexusCRM Composite Records API using `NetSuite_Source_Key__c`. It does not reuse `run-pipeline`, `load`, or BigQuery, and it never connects to or writes CRM TiDB. It is safe by default: a local workbook dry-run produces a 35-field candidate only; it does not contact NetSuite, NexusCRM, GCS, Secret Manager, or Cloud Run. The verified July fixture has 1,789 rows, but it has no raw/display RESTlet admission evidence and can never be submitted to CRM. ```bash uv run pingcap-pipeline netsuite-revenue-to-nexuscrm \ --posting-period-id 245 \ --run-id local-workbook-245 \ --fixture-workbook "/Users/amber/Downloads/2607 revenue voucher list.xlsx" ``` The task admits a row only when its raw/display NetSuite Account ID is truly nonblank and resolves through CRM query API to exactly one active `Account.Account_ID__c`; the resulting `CRM_Account__c` is mandatory. Blank, missing, and ambiguous rows are quarantined in the candidate prefix and never call the target write API. A later API validation checks the Design v0.8 51-field Describe contract, and a commit needs explicit target/API/Describe, candidate manifest, and candidate-run gates. Production API access, deployment, and Scheduler enabling all require separate authorization. See `docs/netsuite-revenue-nexuscrm-api-runbook.md` for the full contract. The currently OPEN/BLOCKED L2 PR #5216 is not treated as deployed. Runtime ------- Install locally with a project-local uv environment: ```bash uv sync --extra dev ``` Inspect or run one source locally: ```bash uv run pingcap-pipeline inspect --source configs/sources/nexuscrm.yaml uv run pingcap-pipeline inspect --source configs/sources/nexuscrm.yaml --compact uv run pingcap-pipeline extract --source configs/sources/nexuscrm.yaml uv run pingcap-pipeline load --source configs/sources/nexuscrm.yaml --all-unloaded ``` Use `inspect --compact` when the complete table set must survive a bounded runtime log collector as one JSON receipt. It performs the same read-only metadata inspection as the default pretty output. For TiDB Cloud sources, the runtime needs these environment variables or matching Secret Manager versions in Cloud Run: - `TIDB_SOURCE_DSN` - `TIDB_CLOUD_PUBLIC_KEY` - `TIDB_CLOUD_PRIVATE_KEY` - `TIDB_EXPORT_GCS_SERVICE_ACCOUNT_KEY` Terraform generates `TIDB_EXPORT_GCS_SERVICE_ACCOUNT_KEY` from a per-source GCS writer service account. The TiDB DSN and TiDB Cloud API keys must be supplied as secret versions after the secrets are created. TiDB Cloud export fault domains ------------------------------- `export.tidb_fault_domains` can split one source's selected tables into named, independent Provider exports before the existing byte-budget chunking is applied. Each domain has `include` patterns and optional `exclude` patterns. The generic engine fails closed if a selected table matches zero or multiple domains, a configured domain is empty, a name is duplicated, or an export pattern is duplicated. It never silently drops a table. NexusCRM configures two L1 domains: non-`_System_*` business tables and `_System_*` operational tables. This is an export failure-boundary change, not partial acceptance: one run-level manifest is still published only after every configured domain and every byte-budget chunk succeeds. An explicit TiDB `--tables` list is a different, run-scoped contract. It accepts exact table names only (qualified names must use the configured database), resolves every requested name against live metadata and primary-key policy, and passes only the resolved qualified patterns to Provider. Duplicate, missing, excluded, wildcard, wrong-database, or unexpected tables stop before CreateExport. Fault domains with no selected table are skipped only for this explicit subset mode; the normal configured full run continues to require all domains. Subset manifests use schema v3 and bind requested/resolved names, sorted fully qualified patterns, and their SHA-256. Manifest publication and load both fail closed unless exported objects equal that selection exactly. Consequently an unselected table is neither present in the manifest load loop nor eligible for raw/final merge or full-snapshot deletion. For a manually admitted subset load, pass the same exact list to `load --tables ...`. The loader compares that operator-bound expectation with the manifest contract before it marks the run or performs any table write. When a TiDB Cloud export reaches a non-success terminal state, the job writes a structured `extract_failed` event under the configured runtime-log prefix and prints the same event as JSON. Provider diagnostics are allowlisted to export identity/state/timestamps plus `reason`/error code fields, redacted, and capped; target credentials and arbitrary Provider payload fields are not recorded. Load no-op and manifest freshness --------------------------------- `load --latest` and `load --all-unloaded` return a structured `no_new_manifest` item instead of an empty JSON array when there is no unloaded manifest. Its `freshness` object reports the age of the latest published manifest, explicitly scoped as `latest_published_manifest_age`. A source can set `load.manifest_freshness_max_age_hours` to classify that signal as `fresh` or `stale`; without a threshold it reports `unconfigured`, and without a valid published manifest timestamp it reports `unknown`. This signal does not by itself prove BigQuery table or downstream business freshness. The Helix query source needs only `TIDB_SOURCE_DSN`. It runs as the combined `pingcap-helix-cloud-accounts` job, so one execution extracts and loads the same multi-table manifest. Its canonical tables are `gcp-digital-bi.helix.cloud_accounts` and `gcp-digital-bi.helix.users`; raw history and run controls remain in `helix_raw` and `helix_control`. Terraform --------- Provision buckets, datasets, service accounts, the Artifact Registry repository, Cloud Run Jobs, and Cloud Scheduler jobs. The image has to exist before Cloud Run Jobs can be created, so bootstrap in two steps: ```bash terraform -chdir=terraform init terraform -chdir=terraform apply -target=google_artifact_registry_repository.pipeline gcloud builds submit --config cloudbuild.yaml \ --substitutions=_IMAGE="$(terraform -chdir=terraform output -raw default_pipeline_image)" terraform -chdir=terraform apply ``` TiDB Cloud extract jobs are enabled in this deployment after their Secret Manager versions were populated from the original ingestion `secrets.env` files. For a fresh bootstrap before those versions exist, override the variable with `-var='enabled_credentialed_extract_sources=[]'`, then add versions: ```bash gcloud secrets versions add pingcap-nexuscrm-tidb-source-dsn --data-file=dsn.txt --project=gcp-digital-bi gcloud secrets versions add pingcap-nexuscrm-tidb-public-key --data-file=public-key.txt --project=gcp-digital-bi gcloud secrets versions add pingcap-nexuscrm-tidb-private-key --data-file=private-key.txt --project=gcp-digital-bi terraform -chdir=terraform apply \ -var='enabled_credentialed_extract_sources=["data_foundation_finance","nexuscrm"]' ``` The scheduled jobs are split by phase: - `pingcap--extract`: extracts to GCS and writes a manifest - `pingcap--load`: loads every unloaded manifest in order - `pingcap-month-end-arr-outbound`: upserts Month End ARR into NexusCRM; its scheduler is paused by default Small read-only query sources can instead use one combined job: - `pingcap-`: runs `run-pipeline` to extract and load one snapshot atomically at the run level Loaded tables are in each source's `*_raw` and `*_final` datasets. For example: ```sql SELECT * FROM `gcp-digital-bi.nexuscrm_raw.nexuscrm_account_raw`; ``` Validation ---------- ```bash uv run pytest terraform -chdir=terraform fmt -check terraform -chdir=terraform validate ``` GitHub Actions deployment ------------------------- `.github/workflows/deploy.yml` deploys after a PR merge lands on `main` because GitHub emits that merge as a `push` to `main`. The workflow runs tests and Terraform validation, builds the container with Cloud Build, pushes it to Artifact Registry, and updates the Terraform-managed Cloud Run Jobs to the new image tag. Configure these GitHub repository secrets before enabling the workflow: - `GCP_WORKLOAD_IDENTITY_PROVIDER`: Workload Identity Federation provider name - `GCP_DEPLOY_SERVICE_ACCOUNT`: deploy service account email Optional repository variables override the defaults: - `GCP_PROJECT_ID` defaults to `gcp-digital-bi` - `GCP_REGION` defaults to `us-west2` - `PIPELINE_IMAGE_TAG` defaults to `main` The deploy service account needs permission to submit Cloud Build builds and update Cloud Run Jobs. The Cloud Build service account needs permission to write to the `pingcap-datapipelines` Artifact Registry repository. Empty TiDB tables and export retries ------------------------------------ The NexusCRM and Finance extractors materialize typed zero-row Parquet for schema-only tables after verifying emptiness at the completed export snapshot. Each failed Provider export has at most three attempts with separate prefixes; only successful attempt objects enter the complete manifest. Missing schema or unverifiable emptiness still blocks publication. See the [recovery contract and receipts](docs/extractor-recovery-2026-09-08.md). Feishu failure alerts --------------------- Cloud Run execution failures are routed from the existing Monitoring policy through authenticated Pub/Sub delivery to a signed Feishu bot. Setup, secret handling and end-to-end acceptance are documented in [Feishu alerts](docs/feishu-alerts.md).