It worked, but nobody trusted it
Every day, an automated flow took production data into the BI data lake. First it restored the latest database dump into a dedicated PostgreSQL. Then Airbyte replicated that database by CDC. Finally, everything was loaded into BigQuery.
The jobs ran, sometimes failed and were retried. On the dashboard, the pipeline worked. But BI complained that the data was always late, syncs took 2 to 11 hours, and some days ended without ingesting anything.
Always a day behind
The first thing that stood out was that BigQuery showed a snapshot of the database from 9 p.m. the day before, even with the sync running overnight. I cross-checked the CronJob schedules and the explanation was simple: the restore took the latest available dump, but ran before the overnight backup reached the bucket. It always got the 9 p.m. one. I moved the restore to after the backup, in the cluster and in Git, and the data delay for BI dropped by about six hours.
In the same place was the reason every sync scanned all 153 tables in full: recreating the database on each restore destroyed the CDC replication slot.
Failures that made no noise
Then came the silent failures, one by one. A schema fix step had never worked: a library was missing from the image, and the error was swallowed by three except blocks and an exit 0. The step "passed" in four seconds without doing anything. I rebuilt the image with the corrected script, dry-run by default and an explicit failure in the log.
One table had a column with the same name as the table, and BigQuery read the identifier as the whole row rather than the column. I swept all 157 streams to confirm it was the only case. That problem cost a full pass of about 50 GB a day.
The Airbyte server also crashed on its own: an internal routine tried to cancel a job with an invalid id every minute and blew through the 6 GB heap. And the sync alert lied, with a six-hour timeout that reported failure on syncs that finished successfully shortly after. That was four false alarms in 50 days.
After those fixes, the next sync finished on the first attempt.
The daily 500 error
The most frequent error was a BigQuery 500 on upload. Airbyte's API ran the server out of memory when I asked for the history, so I read the failures attempt by attempt straight from its internal database. The connector uploaded about 2.5 GB in a single upload, with no staging area. A 500 in the middle killed the whole attempt, which started over from scratch. I measured 7.7 times the necessary work on one job and about 45 hours of wasted attempts in 27 days.
I wrote the post-mortem with the timeline and those 27 days of data. I tested the connector upgrade, but as a hypothesis, not a fix, and compared the result with the baseline. The difference was within the noise: it hadn't solved it. BigQuery's documentation recommends loading large files from Cloud Storage, where retrying with exponential backoff is enough. I opened that study as the next step, with cost, permissions, validation and rollback.
GitOps saying everything was fine
On another product, Airbyte was down for 24 hours while ArgoCD showed the application as synced and healthy. The chart declared the ServiceAccount as a Helm hook, and ArgoCD doesn't include hooks in the desired state. When it was deleted, nothing recreated it. I restored the account, versioned the resources as normal objects and documented a second drift: a sync policy active in the cluster that didn't exist in Git.
I also helped prepare the backup that feeds the pipeline to be encrypted with KMS, with privilege separation tested in practice: the backup credential can generate a data key, but gets access denied when it tries to decrypt.
What changed
Data reached BI about six hours earlier. The four silent failures were eliminated, and the sync started finishing on the first attempt. The cause of the most frequent error was pinned down in a post-mortem, with the next solution based on the vendor's official recommendation.
What I take from it
A tested hypothesis is not a fixed problem. Upgrading the connector looked like it solved things, and it would have been easy to declare victory. Comparing with the baseline showed it hadn't, and that kept the team from giving up on the real fix.
And data pipelines need SRE too. A step that ends with exit 0 without doing anything is worse than one that fails, because nobody goes looking.