Seen from a distance, a data ingest pipeline looks unremarkable. A conduit where information flows like water. But observed up close, through the eyes of the people who depend on it day after day, it becomes not just plumbing, but a thing they tend, where the tending shapes their days.

There was a small team of engineers and analysts who spent twice as long keeping the pipes flowing as they did using their data. The maintenance had grown to consume the work it was meant to support. Something had to change.

data_pipeline

The Shape of It

The team was leaving Databricks, which meant rethinking how data would arrive in Snowflake. The landscape seemed to point toward an obvious path: a chain of vendor pieces, each a partial answer. Azure Blob and ADLS feeding Snowpipe. Event Grid triggering ADF. Fivetran managing the ingest. Snowflake Notebooks with GitHub integration. But along with these options came boundaries the team didn't choose: what they could change, what they could automate, what they had to wait for someone else to approve. Work that felt less like maintaining a flow than like working inside someone else's product.

Every time we talked it through, 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 round, and how the data actually moved would stay sealed behind interfaces they could operate but never see into.

There was no point arguing it. We could build an open-source alternative in about the time it would take to describe one, and the real problems weren't going to show up until we did. So we built a proof of concept, and let it speak for itself.

The shape that emerged was unassuming and repeatable. It was a Python cronjob running in a Kubernetes pod, pulling batch data from a Citrix API and writing to a Snowflake staging table. One pod per run. It was the kind of thing one person could hold in their head: the schedule, the retry, the logging, the write to staging, and not much else. Few places for something to go wrong, and few places to look when it did. The team could build it again next month without relearning it, and scale it the same way.

Everything a vendor platform would have handled out of sight lived in one place where the team could reach them. The code was version-controlled, scanned for vulnerabilities, built and tested before anything touched production, which meant the team knew what was actually running. Because it was plain Python in a plain pod, an AI assistant could read it, generate it, test it, debug it alongside them. The team could move at the speed of a prompt.

Around that core sat the rest of the stack. The cronjobs ran in AKS on a UTC schedule. From staging tables, Snowflake dynamic tables automatically carried the data the rest of the way to the Power BI dashboards. The stack was delivered through GitOps: pipeline code in one repository, Helm charts in a second, container images in Artifactory, and ArgoCD syncing DEV and PROD with the Git manifest as the single source of truth: a shape well suited to the eventual arrival of event-triggered FastAPI pipelines that would run when called, rather than on a timer.

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

Every step in the flow above is automated. New data from the Citrix API propagates through to the Power BI report on its schedule, with no manual intervention.

The First One

This first pipeline was mostly AI-assisted, built from a pattern that had already proven easy to live with in other products. The requirements became a structured prompt: not "write me a pipeline," but a precise contract encoding every constraint we had learned mattered.

It would retrieve batch data from the Citrix OData Sessions API and write to a Snowflake STG table, one pod per run, on a UTC schedule to sidestep the quiet traps of timezones and daylight saving. It would run inside a locked-down, deploy-only Kubernetes cluster: no kubectl, no OS access, all operational flexibility living outside the cluster. 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 (or DELETE+INSERT) so results were idempotent. At startup it would poll a Snowflake PIPELINE_CONTROL table and execute any backfill instruction it found. And it would log API call counts and data volumes in a shape that could ship to Datadog later.

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 staged a single table for User data, and ran on a desktop Kubernetes cluster with local secrets, where we watched the logs and iterated. When it held, we took it forward: adding it to AKS, wiring up the key vault, running it against the real schedule.

With this pipeline proven, the prompt became a reusable thing. Adding the next pipeline no longer meant composing the whole contract again; it 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 Sessions data, was a test of whether the pattern was reliable enough to hand off. It was AI-generated from a prompt that referred back to the first, and it hit a bug no one expected. It would log "starting," process 450,000 records, log "completed successfully," and then immediately start again, in an endless loop, never creating its target table. We handed the AI the full context: the loop, the schedule, the missing table, the shared secrets, the working twin. We asked it to audit the codebase and explain the failure. Within minutes, the mystery had become a concrete, three-bug fix plan. The fix was all that was needed. The next three pipelines came without a hitch, and by the fifth, what had begun as a precise contract and careful iteration had become something the team's engineers and analysts could run on their own from a single prompt.

The Hardening

Five pipelines running without a hitch can look like calm water. But the calm was built, not found. Most of the Citrix APIs allow only one concurrent call per tenant, and they throw HTTP 429 errors without warning. The old ADF world had been full of these errors during ingest. So the hardening was not polish applied at the end; it was where most of the build went.

What it looked like, in practice:

  • Backoff that treats rate-limiting as normal. The retry logic backs off geometrically — one second, two, four, etc. — and retries until the call succeeds. The audit table for a single day's run might show 58 to 200 retries, and still complete cleanly. The 429s are operational noise, not an incident, so we lowered their log level from WARNING to DEBUG, and added a small inter-page delay to avoid them proactively. We taught the system to wait.

  • Idempotency we could prove. When the Citrix Monitor Service quietly aged sessions out of its roughly 90-day retention window mid-run — counts drifting 248,153 to 248,151 to 248,149 — the MERGE logic held. Re-fetching a date range simply updated existing rows and inserted new ones. Records ingested earlier stayed in CITRIX_SESSIONS even after Citrix deleted them upstream. No data was lost. What had been captured, remained.

  • Backfill as a single row. One insert into the pipeline control table, with a start and end date, triggers a backfill run. The mechanism is so small it is almost invisible.

  • Speed we could count on. Ingest runs averaged 250,000 rows in about ten minutes, roughly 25,000 rows per minute, within an acceptable performance range.

  • Kubernetes-level resilience. Intermittent "pod won't even start" blips were tuned away with backoffLimit, startingDeadlineSeconds, activeDeadlineSeconds, and matched history limits.

What You Cannot See

The highlights read simply: a pipeline, fault-tolerant, fast. But a great share of the build was not pipeline code at all. It was the plumbing around it, and it needed the most care:

  • Helm chart design for trunk-based development — moving environment-specific config out of a shared values.yaml and into per-environment overlay files (values-dev.yaml and values-prod.yaml), so a single main branch serves both environments without long-lived environment branches. The complexity that would have burdened the team was absorbed here, in the foundations.

  • Pipeline-to-Snowflake authentication without secrets to manage — using the same Managed Identity that Kubernetes already used for Azure credentials as the Snowflake user, so the pipeline authenticates to Snowflake through the identity it already carries. No static passwords. No tokens to rotate, expire, or drift. One less thing for the team to tend, one less thing to go wrong.

  • Learning what Terraform could and couldn't manage in Azure Key Vault — Terraform's permissions to read, write, and restore key vault secrets were scoped differently across DEV and PROD, and the boundaries were not always documented. We had to discover, through trial, what Terraform could create, what it could only read, and what the Azure Portal would refuse to show us.

  • OIDC versus static Artifactory tokens — both genuinely fragile to automate, and each worth its own Architecture Decision Record.

Building on an enterprise cloud is not building on open ground. RBAC, service principals, key vaults, and OIDC quietly took more of the build than the pipeline everyone talks about. And this is the layer the team reaches into when something drifts. It mattered that the pipes were exposed, visible, and built by hands that understood them, rather than sealed behind an interface no one on the team could open.

Some of the friction was not architectural at all. It was the environment pushing back:

  • A mis-scoped GRANT that took three round trips with DataOps; the first "work was done" didn't actually remove the FUTURE grants. It took a screenshot of the Snowflake permissions page to finally land it.

  • A VDI dev environment that seemed to work against the person using it: launch, wait for it to fail, launch again, then run out of memory after a few minutes in WSL2.

  • A ServiceNow request for permission to install Docker on Windows that sat unfulfilled for weeks. The delay caused a pivot: the team skipped the local Kubernetes cluster in Windows WSL and built on the AKS and Azure infrastructure directly.

  • A Docker Desktop install from the Company Portal that silently hung for an hour, while the Company Software Center did the same job in five minutes.

The localhost pattern itself became a casualty. It had run fast and without a hitch on a Mac, but it never transferred to the team's Windows machines, and after the first pipeline it was abandoned in favor of developing in AKS directly.

None of this appears in the architecture diagram. All of it was the job.

The Path to Where It Would Live

Built and hardened, the pipeline now had to reach the place where it would live. The path to PROD had to be one the team could walk again on their own, with the fewest possible portal clicks.

This is where the trunk-based Helm design earned its keep. With the single main branch and the per-environment overlay files already in place, promotion became an orderly, gated process, as simple as redeploying main with the next environment's values. A merge to main builds a Docker image, tagged YYYYMMDDHH plus SHA for sort order and traceability, and raises a PR that updates values-dev.yaml in the Helm repo. ArgoCD notices, and syncs DEV on its own. And when the work has proven itself there, a developer promotes it to PROD by updating values-prod.yaml. One deliberate act, at the end of an automatic chain.

Getting there was its own gauntlet of small, real fixes: an ArgoCD app pointed at values.yaml instead of values-prod.yaml, so PROD cronjobs never appeared; a stale Chart.lock out of sync with Chart.yaml that needed helm dependency update; workflows that failed on wrong image paths and tag-immutability checks (Artifactory doesn't enforce immutability, so those checks were noise); a Helm chart that had to switch from agentpool to nodepool; and CI and Release workflows with enough functional overlap that they needed a clean split: CI owns validation, Release runs on tags.

Unglamorous, every one of them. But this is what "it just deploys now" is actually made of: a hundred small corrections, each one removing a way for the team to get stuck.

And then it was done. The first Citrix pipeline was fully operational in PROD, source-to-staging, running on its schedule in AKS with no one needing to start it each morning. It had arrived where it would live.

The Handing On

The point was never for us to run this pipeline forever. From the beginning, the platform was meant to be turnkey, for the team to pick up, learn, expand, and refine with the business knowledge only they possessed. The arc was deliberate: diagnose, build, teach, step back.

Teaching was as much of the build as the code.

Some of it 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 it was the small, hard-won kind that saves an afternoon: the fine-grained GitHub PAT that only works if you pick the enterprise user, not your personal one, or how to trigger a cronjob from the ArgoCD UI for a one-off run and trust it to revert cleanly, since Git stays the source of truth and concurrencyPolicy: Forbid prevents overlapping runs.

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, so the team would know that path stayed open if they ever wanted to try it again on a Linux VDI or Windows desktop.

And it took. Two of the team's analysts stood up the app_instances pipeline on their own. 14 more Citrix pipelines were completed independently. Knowledge continuity became its own incidental subplot: one of them moved to another team mid-project, so the other picked up more ownership, and the mentoring notes (Bash shortcuts, WSL setup, audit and PR skills) doubled as succession planning.

The understanding was spreading the way understanding does, from hand to hand, person to person. They could not have done this with a system they could operate but not see into. They did it because they could reach the pipes.

Our part shrank to consulting: a knotty piece of Python or SQL, an architectural question, something new to learn. The time spent on plumbing was giving way. The analysts were getting their time back for analysis. The care was passing, as it was always meant to.

What Endures

The same questions return with every new pipeline, and the people who answered them the first time may not be there the next. So the understanding was set down where it could be found without asking.

The choices that would otherwise be re-litigated went into Architecture Decision Records: the CI/CD orchestration model, OIDC versus static tokens, the Artifactory service-account approach.

The end-to-end ingest process went into a Platform Council Decision Record, so the pattern is documented rather than tribal.

Naming standards for database objects, repos, and pipelines live in SharePoint, linked from Atlan and ServiceNow and reviewed on a cadence, so they are not re-argued over every new database, schema, or pipeline.

And the repeatable patterns — Superset to Snowflake, Excel to Snowflake, Citrix to Snowflake — became runbooks and playbooks, with standard extract and load gates, transform and promote gates, and observability baked in.

We even pointed AI at the record-keeping itself: a proposal to triage GitHub security alerts, group the fixes into cleanly-revertible commits, and write its own audit trail for dismissed findings into a README-SECURITY-DISMISSALS.md — the alert's ID and location, its type, and a reviewer-ready reason such as "the table_name is validated against… so in practice this is not SQL injection" — all gated on developer approval before anything is pushed. AI drafts the reasoning and the record. A human decides. The trust and the process, held together.

What It's Worth

Watch the team now, and you see something has shifted. The maintenance that once consumed their days has settled into the background, the way breathing settles, unnoticed and reliable, freeing the mind for other things. They teach each other now; we are present when asked, and out of the way otherwise.

There are 19 pipelines running source to reporting, each on its schedule in AKS or triggered through an Azure Logic app, and the team has turned its attention to what the data reveals, rather than what it takes to get the data there. The next ambitions are already named: Snowflake Cortex for anomaly detection, runbooks that generate themselves, and questions like "show me Citrix session trends by hour for the past 7 days" answered without anyone building a report.

The system lives on because it is understood by the people who tend it. They know how it works, why it fails, and how it recovers. And with that understanding has come something harder to diagram, the confidence to follow the data into directions no one had planned for.

That, in the end, was the thing worth building.