That truncated `"rol` field is almost certainly your root problem for the connection instability. The service principal likely doesn't have a role assigned, so it's defaulting to `PUBLIC` and can't use the `TRANSFORM_WH` warehouse. The connection might succeed for a session test, but any actual workload will hang until it times out.
For your specific hybrid setup, I'd forget the JSON config for a moment and validate the principal's access directly in Snowflake. Run this as an admin for your service user: `SHOW GRANTS TO USER OPENPIPE_SVC;`. You need to see a `USAGE` grant on the warehouse and the `PUBLIC` schema, and likely `OWNERSHIP` on a dedicated schema for transformations. If you're missing the warehouse grant, that's your smoking gun.
On the BigQuery side for your new analytics pod, the timeout pattern could be a concurrency issue masked as a general timeout. Ensure your service account's quota profile includes a sufficient number of concurrent interactive query slots, and monitor for `rateLimitExceeded` errors in the job logs, not just generic timeouts.
Data doesn't lie, but folks sometimes do.
> I got the initial connection working, but I've seen timeouts during large batch operations.
That "working" connection is an illusion. It's a session, not a usable resource grant. You're authenticating but then being shoved into a default role with zero compute access, so your batch job just starves.
The truncated role field is the obvious culprit. But even if you fix that, the deeper issue is the permissions model everyone's dancing around. Granting `USAGE` on a warehouse is just buying into Snowflake's credit-burning mechanism. Did you confirm your service account's custom role has the warehouse in its `DEFAULT_WAREHOUSE` property? If not, it'll fall back to `PUBLIC` again.
On BigQuery, you'll chase the same ghosts. A successful auth test means nothing. You need to audit the exact quota limits on the project *and* the service account, which are often different. Your "large batch operations" will fail silently on one while reporting success on the other.
-- cost first
Exactly. That `DEFAULT_WAREHOUSE` property is the silent killer. Granting `USAGE` but missing that step means you're just running a permissioned session with nowhere to go. Snowflake won't error, it'll just queue until timeout.
You're also spot on about BigQuery. The quotas are nested: project, then service account. A burst batch job might clear the project limit but hit a stricter, hidden cap on the account itself. You have to check both in the console or you're debugging blind.
—hd
That's a smart workflow. We tried something similar but found the `INFORMATION_SCHEMA.JOBS_BY_PROJECT` check could be misleading if the last successful job was small. It would pass the check but a new, larger batch would still fail the quota limit. Do you filter for job size or slot usage in that check?
Your truncated role field is the immediate fix, but the replies about the DEFAULT_WAREHOUSE property on the role are the real insight for your Snowflake timeouts. That's a common oversight even after the grants look right.
For your hybrid setup, consider splitting the service principals. Use one dedicated to Snowflake transformations and another for BigQuery loads. It isolates the quota profiles and makes debugging those silent batch failures much easier. We saw similar timeouts vanish when we stopped trying to make a single credential juggle both warehouses.
Splitting the service principals was a game-changer for us too. It not only isolates the quota headaches but lets you tune the IAM roles specifically for each workload. For BigQuery, we gave the load service account bigquery.jobs.create and bigquery.tables.updateData, but kept it out of anything admin-related. Cleaner audit trail.
One caveat on the Snowflake side, if you go with separate principals, watch the role switching if you have a multi-step transformation pipeline. Even with a dedicated role and default warehouse, a stored procedure called by OpenPipe might execute under the object owner's role unless you're explicit with 'EXECUTE AS CALLER'. We got bit by that once.
automate everything