In April 2021 I made my first commit to the monorepo behind CARTO’s cloud-native platform. It was PR number 9, a docker-compose. Five years later I’ve looked back with some calm and found around 450 commits, around 447 pull requests, and a presence in virtually every service in the repository.
The numbers aren’t the interesting part. What’s interesting is that, going through that history, a thread shows up that I hadn’t fully identified myself: I’ve spent half a decade connecting the platform to other people’s data warehouses. Six different providers — BigQuery, Snowflake, Redshift, Databricks, Oracle and PostgreSQL — each with its own credential model, its pooling, its timeouts and its error messages.
This article is what I’ve taken away from that work. It isn’t a guide, it’s more the residue of solving the same problem six times and discovering that only the surface changes.
Connecting to someone else’s database isn’t “connecting to a database”
When you write a PostgreSQL connection in your own project, there’s a pile of decisions you never even consider: you know the version, you control the network, you have the credentials, and if something fails you see it in your logs.
None of that applies when the connection is configured by a customer against their own infrastructure. The warehouse isn’t yours. The credential belongs to the customer and can expire, be rotated or be revoked without warning. Latency depends on a network you don’t control. The schema might have ten tables or ten thousand. And when something fails, the error you get comes from a system you can’t inspect.
That turns what looks like plumbing into a considerably deeper design problem. The surface is always the same — “store these credentials and run this query” — but underneath, each provider has its own personality.
The credential model is where the real difference lives
If I had to keep a single lesson, it would be this: what distinguishes one data warehouse from another isn’t the SQL dialect, it’s how it proves who you are.
SQL is reasonably similar across providers. Differences exist, but they’re manageable. Authentication, on the other hand, is where each one has made radically different decisions, and where you find the problems that actually cost you.
A quick tour of what I’ve run into:
Service accounts with a key. The classic model: the customer hands you a JSON with a private key and you store it. It works, it’s simple to implement, and it’s exactly what keeps any security officer awake at night. You’re storing a long-lived credential that grants access to another company’s data.
Workload Identity Federation. The answer to the above, and a significant shift in mindset. Instead of storing a key, your service presents its own identity and the provider decides whether to trust it. There’s no secret to store, to rotate, or to leak. It costs more to implement — you have to configure the trust relationship on both sides — but it removes an entire category of risk.
OAuth in two flavors. There’s a distinction here that took me a while to internalize and that’s worth being clear about from the start: user-to-machine is not the same as machine-to-machine. In the first, the connection acts on behalf of a specific person, inherits their permissions and dies when that person loses access. In the second, the connection has an identity of its own. Choosing wrong means a published map stops working the day someone changes departments.
Key-pair authentication. A middle ground: asymmetric cryptography instead of a password, without the complexity of federating identities. A good balance when the provider doesn’t offer federation.
The practical conclusion is that the authentication model has to be chosen before writing the first line, because it conditions everything else: where the secret lives, what happens when it expires, who can use the connection, and what happens when that someone leaves the company.
Tokens expire at the worst possible moment
Of all the bugs I’ve touched over these years, the ones involving cached tokens have taught me the most.
The pattern is always the same. You cache a token to avoid requesting a new one on every request — reasonable, it’s an expensive network call. The token expires. Your cache doesn’t notice. From then on you serve a dead token to every incoming request, and the error the provider returns rarely says “your token expired”: it says something generic about permissions or about the connection.
What I learned from this isn’t “invalidate the cache”, which is obvious. It’s something subtler: the event that invalidates a token doesn’t always happen in your system. When a user re-authenticates with the provider, your cache receives no notification at all. It stays perfectly happy with its stale copy. Any design that assumes it will see every relevant state change go by is built on a false premise.
The general lesson goes well beyond tokens: when you cache something whose lifecycle a third party controls, time-based invalidation isn’t enough. You need a path to detect that what you cached is no longer valid, and to recover without the user having to do anything.
Unhandled errors are the real enemy
There’s a pattern I’ve repeated so many times over these five years that it’s become almost a reflex: turning an unhandled 500 into a proper response.
It looks like a minor detail. It isn’t. A 500 means your service didn’t know what to do with a situation, and in a multi-tenant platform that has consequences beyond the request that failed.
When an exception from a provider’s driver bubbles up uncaught, it takes down things that have nothing to do with it: it can bring down the process serving other customers, or leave connections hanging in the pool. One specific customer’s failure, with their specific misconfigured warehouse, ends up degrading the service for everyone else.
That’s why provider-specific error handling isn’t cosmetic. Each driver has its own taxonomy of failures — timeouts, permissions, schemas that don’t exist, exceeded quotas — and translating them into correct HTTP responses is what maintains isolation between tenants.
It also has a side effect I didn’t expect: when errors are properly classified, support stops escalating tickets to you. A message saying “the user doesn’t have permissions on this schema” gets resolved by the customer. A generic 500 always ends up in your inbox.
Listing schemas is harder than it looks
A small example that illustrates well what I said about not controlling the other side.
For someone to pick a table, you first have to show them which tables exist. The obvious way to do it is to walk the hierarchy and ask about each object. It works perfectly when the customer has twenty tables.
With a customer who has tens of thousands, that same code turns a selection screen into a multi-minute operation, or straight into a timeout. And there’s nothing you can do from your side: the cost lives in someone else’s warehouse.
The fix means no longer asking object by object, and using the provider’s metadata views instead, which return the full catalog in a single query. It’s more work, because each provider exposes those metadata differently.
What I take from this is a rule I now apply almost without thinking: any operation whose cost grows with data you don’t control is an incident waiting for its moment. It’ll work in development, it’ll work in the demo, and it’ll fail with the largest customer, who tends to be the one you least want failures with.
Five years, one conclusion
If I had to sum all of this up in a single idea, it’s that integrating with other people’s systems is fundamentally an exercise in humility. Your code is the small part. The big part is everything happening on the other side of the wire: credentials that expire, schemas that grow, networks that run slow, providers that change their API.
Work done well here goes unnoticed. Nobody writes to thank you because their Snowflake connection has run for two years without intervention. It’s the opposite that gets noticed, and that’s why most of the effort goes into the paths that should never execute.
When I started I thought connecting to six different data warehouses would be six times the same work. It turned out to be one time the same work and six times a different authentication problem.
