There was once a small team of engineers and analysts at Providence who spent twice as long keeping their pipelines flowing as they did using their data. The maintenance had grown to consume the work it was meant to support.
Something had to change.
No Hands Needed
The team was given a short timeline to migrate off of Databricks, but they took it as a chance to rethink the entire architecture of how data would arrive in Snowflake and asked for our help.
The path that looked obvious from the outside was a chain of vendor tools, each a partial answer. Azure Blob and ADLS feeding Snowpipe. Event Grid triggering Azure Data Factory (ADF). Fivetran managing the ingest. Snowflake Notebooks with GitHub integration.
But along with these options came boundaries the team had no say over, like what they could change, what they could automate, what they had to wait for another team to approve. They felt the work lacked creative stewardship, reducing them to mere knob-turners in external systems.
Every time we talked through the different architectures, we landed in the same place. Any of those paths could move the data. But the team would end up working for the tool, not the other way around, and how the data actually moved would stay sealed behind interfaces they could operate but never see into.
So we stopped talking and chose to build a proof of concept. An open-source alternative could take less time to stand up than describe, and it could be tested against reality right away. It would either prove viable or fail fast.
The working prototype was unassuming and repeatable. It was a Python cronjob running in a Kubernetes pod, pulling data from a Citrix API on a schedule with retry and logging, and writing to a staging table in Snowflake where dynamic tables automatically prepared the data for Power BI dashboards.
One pod per run, gone when the work was done, costing nothing in between.
The team could build it again next month without relearning it, and they could scale it without adding complexity. Or they could extend it to support an event-triggered pipeline they may be needed for triggering a job manually.
flowchart LR
subgraph Source["Source"]
API["Citrix OData API"]
end
subgraph AKS["AKS — Kubernetes cluster"]
Cron["Python cronjob\none pod per run"]
end
subgraph Snowflake["Snowflake"]
STG["STG table\n(staging)"]
DYN["Dynamic table"]
end
subgraph Reporting["Reporting"]
PBI["Power BI report"]
end
API -- "batch pull\nUTC schedule" --> Cron
Cron -- "MERGE write\nidempotent" --> STG
STG -- "automatic refresh" --> DYN
DYN -- "automatic refresh" --> PBI
classDef automated stroke-dasharray: 0, fill: #f8f8f8, stroke: #333, color: #111
class API,Cron,STG,DYN,PBI automated
Around the flow was the GitOps delivery machinery, with pipeline code in one repository, Helm charts in a second, container images in Artifactory, ArgoCD syncing DEV and PROD, and the Git manifest as the single source of truth.
The cronjob code was version-controlled, scanned for vulnerabilities, and tested before anything touched production. And with an AI agent working alongside the engineers, the team could generate, test, and debug at the speed of a prompt.
Documentation described and evolved alongside the code, helping anyone arriving new, human or AI agent, could read their way into the codebase.
The command over flow that would have been confined to a vendor's view of the process was now under the team's control. Few places for something to go wrong, but known places to look when it did.
Data could propagate hands-free from one end of the pipeline to another on a schedule, every step running automatically and predictably.
One Prompt, One Pipeline
The first pipeline was the proof of concept itself, carried forward. It was mostly AI-assisted, built from patterns that had proven easy to live with in other products.
The requirements became a structured prompt: not "write me a pipeline," but a precise contract stating what the pipeline would do in terms experience had shown to matter most.
The pipeline would run on a UTC schedule, to sidestep the trappings of timezones and daylight saving, inside a locked-down, deploy-only Kubernetes cluster: no kubectl, no OS access. Anything an operator might need to do would be done from outside: a backfill, a config change, a rerun, each through a Git commit or a row in a control table, and the cluster would follow.
It would back off exponentially on 429, 404, and 500; page in batches of 1,000; pull all columns; and use a consistent modified-date field for update queries. Writes would MERGE on natural key, making results idempotent. And it would log API call counts and data volumes in a format an observability platform could read.
It was also built against live data. Real conditions, real API responses, and real failure modes. Better to meet the hard problems early on than to leave them for the engineers and analysts to discover after the handoff.
It began small: a single table for Citrix User data, on a desktop Kubernetes cluster with local secrets, where we watched the logs and iterated. When it worked, we took it forward: adding it to AKS, wiring up the key vault, running it on the production schedule.
With this pipeline proven, adding the next one no longer meant composing the whole contract again. The prompt became a single, well-crafted instruction: "Refer to <xxx> and add a new Citrix pipeline to this codebase called <yyy> that sends data to DATABASE.SCHEMA.TABLE fetched from the Citrix API…"
The second pipeline, for Citrix Sessions data, then became a test of whether the pattern was reliable enough to hand off. This pipeline too was AI-generated, but when deployed it would log "starting," process 450,000 records, log "completed successfully," but then immediately start again, in an endless loop, never creating its target table.
We revised the prompt to include the problem alongside the working twin, and asked the AI agent to explain the failure. Within minutes, the mystery resolved into a solid prompt leading to a smooth-running pipeline in AKS.
The prompts for the next three pipelines worked flawlessly, and by the fifth, what had begun as a precise contract and careful iteration had become something the team's engineers and analysts could generate code from on their own.
Adjusting the Valves
The work done to overcome obstacles was not obvious, and the team was surprised to find each solution already in place, handling problems more effectively than they expected. The five pipelines flowed smoothly because of the following:
-
Backoffs that treat rate-limiting as normal. Most of the Citrix APIs allow only one concurrent call per tenant. This chokepoint often throws
HTTP 429 Too Many Requests(rate limit) errors which can cause jobs to fail without warning. To workaround these errors, the jobs automatically retry failed calls using exponential backoff—waiting 1, 2, 4, and 8 seconds between attempts until the request succeeds. The logs for a job's run might show 58 to 200 retries, while still completing cleanly. -
Idempotent ingestion. Some Citrix sessions start but do not complete during the data ingestion runs, causing counts to vary when re-fetching. A morning run might capture 249,153 sessions, and an afternoon re-fetch return 249,155. The MERGE logic reconciles the two, updating existing records and adding new ones. Nothing is duplicated, nothing is lost. And records ingested earlier stay even after Citrix deletes the session data upstream.
-
Backfill as a single row. Insert a row in the Snowflake
PIPELINE_CONTROLtable, and the next run reprocesses the history it names. No orchestration to build. -
Performance. Each pipeline processes around 25,000 rows per minute, whether it ingests 5,000 rows or 500,000.
-
Kubernetes-level resilience. Intermittent "pod won't even start" blips are covered by
backoffLimit,startingDeadlineSeconds, andactiveDeadlineSeconds.
Working Out the Fittings
Getting the pipeline code right was only part of the work. The plumbing around it governed delivery and throughput. The fittings chosen to control the flow were both architectural and environmental:
-
Helm chart design for trunk-based development — moving environment-specific config out of a shared
values.yamland into per-environment overlay files (values-dev.yamlandvalues-prod.yaml), leaving a singlemainbranch that serves both environments without long-lived environment branches. -
Pipeline-to-Snowflake authentication without secrets to manage — both Kubernetes and Snowflake use the same Azure Managed Identity. The pipeline never handles a credential. No passwords or certificates to rotate meant one less thing to manage.
-
Learning what Terraform could and couldn't manage in Azure Key Vault — Terraform's Key Vault permissions differed between DEV and PROD. Azure Portal access restrictions limited what we could see, and boundaries weren't documented. We had to probe our way through the config.
-
OIDC versus static Artifactory tokens — for pulling container images from Artifactory, both approaches were genuinely fragile to automate. Each warranted its own Architecture Decision Record and planning document.
-
Snowflake grant requests — typically took three round trips with DataOps to finalize. Their first and second "all done" emails did not prove true.
-
The Windows development environment — a local Kubernetes cluster (k3d) was easy to set up and ran perfectly on a Mac, allowing rapid iteration of pipeline code. But on the team's Windows WSL2 desktops, the experience was the opposite: slow, frustrating, and unreliable. the setup never transferred. After a month of struggles, the local-first approach was abandoned in favor of developing directly on AKS.
Streamlining the Flow
With five pipelines built, tested, and running in DEV, what remained was a streamlined deployment process the team could run and rerun on their own, using the fewest possible mouse clicks
The pipelines repository had two automated workflows, one for testing and validating code on every push (CI) and one for releasing the code (Release).
A merge to main with changes to one or more code files automatically triggered the Release workflow, which built and pushed Docker images to Artifactory, tagged with a date and SHA for consistent sorting and a unique fingerprint.
The workflow then added the image tags to a values-dev.yaml file and created a pull request in the Helm manifest repository, closing any older open PR to keep the branches clean. After a developer approved and merged the pull request, an ArgoCD sync automatically updated the AKS DEV environment.
When the images ran successfully in DEV, a developer promoted them to PROD by activating a Copilot skill to copy the image tags to a values-prod.yaml file, create a pull request, and merge to main. An ArgoCD sync then updated the AKS PROD environment.
The Helm repo used a single main branch for both environments, which reduced the complexity of managing multiple branches. And the manual gates were intentional. They provided a clear, human-in-the-loop moment to verify that the code was ready for production.
The pipelines now ran in PROD, connecting API endpoints to staging, each on its schedule in AKS, no one needing to tend them every day.
Preparing the As-Builts
When the construction crew finishes a building, what's covered by walls remains hidden. For the future lifecycle of the building, the trade leaves behind "as-built" document: revised design drawings that show what was actually installed.
Here, the walls are time and turnover. The people who built the pipelines may not be around to answer questions. But for the future lifecycle of the pipelines, they leave what they had learned in the code and the notes, where it can be found without asking. Every new person joining the team can pick up where the last left off.
The choices that would otherwise be re-litigated went into Architecture Decision Records, reasoning and all: the CI/CD orchestration model, OIDC versus static tokens, the Artifactory service-account approach, the end-to-end ingest process itself.
Links to enterprise standards for database objects and pipeline conventions reside in the repository's README to avoid being re-argued over every new database, schema, or pipeline.
Help with triaging security vulnerabilities is available through an AI skill that groups fixes into cleanly revertible commits and preserves an audit trail for dismissed findings.
And the repeatable patterns — Superset to Snowflake, Excel to Snowflake, Citrix to Snowflake — are in the runbooks and playbooks, with standard extract and load gates, transform and promote gates, and observability baked in.
Handing Over Control
The team already knew the business. What they needed was something in their hands, and the time to make it theirs.
Some of the knowledge exchange was foundational: crafting AI prompts and choosing models; Docker with WSL; Helm and YAML; the kubectl basics of creating a job and reading a pod's logs.
Some of the insights shared were small, but they were the kind that save an afternoon: that a fine-grained GitHub PAT only works if you pick the enterprise user, not your personal one, or that a concurrencyPolicy: Forbid annotation in a cronjob prevents manually triggered jobs from colliding with a scheduled run.
The full PR, vulnerability-remediation, and audit workflow was practiced until it was routine. And K3D for localhost development was taught even though the pattern had been abandoned, leaving the path open if they ever wanted to try it again on a Linux VDI or Windows desktop.
Two of the team's engineers stood up the app_instances pipeline on their own. Other pipelines followed, completed independently. The understanding spread from hand to hand, person to person, unlike the vendor path, where they would have worked for the tool and kept the understanding to themselves.
Our part shrank to consulting: a knotty piece of Python or SQL, an architectural question, something new to learn. The hours once spent on plumbing were going back into analysis. The care was passing.
Settling In
There are 34 pipelines running now, source to reporting, each triggered by its own schedule or button click. Any can be rerun or repurposed, or a new one built. The team's questions are starting to move beyond getting the data to whether Snowflake AI can catch anomalies on its own, or show session trends by hour for the past seven days without anyone building a report.
The maintenance load that once consumed their days has settled, the way water clears when silt sinks to the bottom of a still pond. They teach each other; we are present when asked, and out of the way otherwise.
What remains is plumbing the team can easily follow and fix, from source to consumer.