# GCP TopSQL Query Cold-Start and Execution Repair ## Background The GCP TopSQL Parquet source repair reached production and two nearly concurrent logical TopSQL requests then spent about 63 and 72 seconds in table setup before the final SELECT was submitted. BigQuery job metadata showed that both requests ran the same four external-table DDL statements from separate Data Service processes. The final SELECT jobs then failed validation because the rewritten physical table name was unqualified and the BigQuery query did not set a default dataset. The earlier side-path smoke test intentionally read only BigQuery metadata and GCS prefixes. It did not execute the public Data Proxy request path, create or join setup jobs, or submit an unqualified logical-table query. Unit tests also used a fake table manager and therefore did not expose either the sequential DDL latency or the missing BigQuery default dataset. Read-only query probes also exposed two source-shape cases that metadata-only coverage missed: a reserved wildcard for an empty component fails BigQuery file pattern expansion, and an `instance=` directory can exist without any usable `date=` partition. Either condition makes the complete external table unqueryable. ## Goal - Make unqualified logical Data Proxy SQL execute in the selected regional BigQuery dataset. - Bound table setup and query execution with the request timeout as one operation. - Run the four independent TopSQL physical-table reconciliations concurrently, then create or validate the logical view. - Reuse an identical in-flight BigQuery setup job across Data Service replicas so concurrent cold requests do not submit duplicate DDL. - Represent an empty component with a typed zero-row view, and include only instances that have a real date partition in physical-table source URIs. - Add regression and opt-in read-only integration coverage for the actual final BigQuery query boundary. ## Current behavior - The Data Proxy HTTP API is synchronous. BigQuery DDL and SELECT operations are jobs internally, but the API waits for them and exposes no asynchronous job ID or polling contract to callers. - `timeout` is applied only after automatic table management, so setup time is excluded from the advertised query timeout. - Logical TopSQL view dependencies are reconciled serially. - In-memory `singleflight` and fingerprints deduplicate only within one process; separate replicas can race and submit identical `CREATE OR REPLACE` jobs. - SQL rewriting changes `topsql` to the cluster-specific physical view but does not add a database to an originally unqualified table. `BigQueryEngine.Execute` does not set `DefaultProjectID`, `DefaultDatasetID`, or the job location. ## Target contract - One synchronous request timeout covers automatic table management and the final engine query. - BigQuery SELECT jobs always use the routed project, regional dataset, and location, including when input SQL uses an unqualified logical table. - TiDB, TiKV, SQL metadata, and plan metadata external tables reconcile in parallel; the view is reconciled only after every dependency succeeds. - Identical setup DDL observed against the same table generation within a bounded retry window receives a deterministic BigQuery job ID. A replica that sees HTTP 409 joins and waits for the existing job instead of submitting a duplicate. - Setup failures remain retryable after the bounded job-ID window and retain the existing safe diagnostic contract. - Empty TopSQL components are typed zero-row views and never reference a GCS wildcard that has no files. - Instance discovery validates date partitions with at most eight concurrent GCS prefix checks; stale or empty instance directories are excluded. ## Affected modules and routes - Go module: `clinic_data_service`. - Packages: `pkg/engine`, `pkg/service`, and focused tests. - Entrypoint: `clinic_data_service/cmd/api`. - Image: `clinic/data-service`. - Routes: legacy `POST /data-proxy/query` and canonical `POST /data-proxy/api/v1/orgs/{orgID}/clusters/{clusterID}/sql-queries` when routed to GCP BigQuery; the setup optimization is specific to TopSQL. No module ownership, entrypoint, image, workflow, or app-delivery mapping changes are planned, so `docs/clinic-module-overview.md` does not require an update. ## Implementation steps 1. Configure every BigQuery execution query with the routed project, dataset, and region. 2. Create the request execution context before table management and reuse it for setup and the final query. 3. Reconcile TopSQL view dependencies concurrently and preserve deterministic dependency error reporting. 4. Add deterministic, bounded setup job IDs and join an existing BigQuery job on duplicate insertion. 5. Replace empty component wildcards with typed zero-row views and validate instance date partitions with bounded concurrency. 6. Add unit tests for query routing, parallel dependency setup, deterministic job reuse, and timeout propagation. 7. Extend the integration test with an explicitly gated read-only zero-row query against an existing logical TopSQL view. Do not execute DDL or return customer rows from this test. ## Exclusions - No new asynchronous Data Proxy job API or persistence schema. - No background cluster-wide table provisioning service. - No out-of-band BigQuery mutation in tests and no GCS mutation. Managed legacy objects may be replaced by the normal request-time reconciliation script. - No staging or production deployment in this task without separate approval. ## Compatibility and safety - Existing synchronous HTTP response shapes remain unchanged. - Qualified SQL continues to resolve to its explicit dataset; the default is used only for unqualified names. - Deterministic setup job IDs contain only hashes and time buckets, not GCS paths, table names, SQL, cluster IDs, or credentials. - Dependency concurrency is bounded to four fixed TopSQL component tables. - Per-component instance partition discovery is bounded to eight GCS requests. - Integration output contains only hashed target identifiers and timing/status summaries. ## Test plan From `clinic_data_service/`: ```bash go test ./pkg/engine -run 'TestBigQueryQueryRouting|TestBigQueryTopSQL|TestTopSQL|TestDiscoverTopSQL' -count=1 go test ./pkg/service -run 'TestDataProxyQueryContext|TestBigQueryFailureLogFields' -count=1 go test ./pkg/engine ./pkg/service ./pkg/handlers ./cmd/api -count=1 go test -tags integration ./pkg/engine -run '^$' -count=1 git diff --check ``` When ambient production ADC is available, compile and run only the read-only integration query smoke test from a production Data Service Pod. It must use a nonmatching date and `LIMIT 0`, run no DDL, and log no table name or customer row. Read-only production probes performed during implementation found that the currently deployed physical objects do not match the strict discovered source definitions: one query failed file-pattern expansion and a bounded scan found no already-reconciled nonempty physical table. These probes intentionally performed no DDL. The subsequent single-region discovery retry was blocked by expired AWS SSO, so the final logical and physical query smoke tests remain staging checks after this branch is deployed. ## Staging verification and release boundary No deployment is authorized by this task. After a future staging deployment, verify one cold logical TopSQL request, two concurrent requests routed across replicas, one warm request, query job location/default dataset, and absence of duplicate setup job hashes. A production release requires separate explicit authorization and the normal Clinic CD and app-delivery GitOps flow. ## Deployment impact - Required Clinic CD component for a future runtime rollout: `clinic-data-service`. - app-delivery application: `clinic-data-service`. - Kubernetes workload: `Deployment/clinic-data-service`. - Target environments only when separately authorized: staging, then `prod-cloud`. - Nearby components not requiring deployment: `clinic`, `internal-api`, `task-manager`, Kong, NGM, cloud-server, and UI.