カナダ大手鉄道、Genie Code でデータパイプラインを大規模化

One of Canada’s largest railway networks spans roughly 20,000 route miles across Canada and into the United States, supporting the movement of more than C$250 billion in goods annually. For an organization operating at that scale, modernizing a decades-old data estate was never going to be a one-table-at-a-time exercise.

With hundreds of pipelines in flight, growing demand for real-time analytics and AI, and deep institutional knowledge embedded in legacy systems, the company needed a way to scale modernization without scaling manual development effort.

Using Databricks Genie Code, Unity Catalog, custom Agent Skills and a Streamlit app built on Databricks Apps, the team turned pipeline development itself into a repeatable factory. A short YAML prompt can now generate production-ready ingestion code grounded in live catalog metadata and aligned to enterprise conventions by default, including table definitions, historical load logic, streaming ingestion logic, incremental merge logic and automated tests.

The result is more than 90% automation for new table ingestion, pipeline delivery compressed from days to minutes, and a modernization program that can scale with the business instead of being constrained by developer bandwidth.

Modernizing a complex data estate at enterprise scale

Like many large enterprises, the company built its analytical estate over decades across mainframe systems, legacy data warehouses, enterprise ETL platforms and purpose-built appliances. As the company moved toward a modern lakehouse architecture, the challenge was bigger than migration alone: the team needed to simplify and standardize how pipelines were built while preserving critical business logic across a large legacy footprint.

Before automation, building a pipeline for a single table was a multi-day effort. Teams had to inspect source schemas, define business logic in a Source-to-Target Mapping spreadsheet, build historical and streaming ingestion logic, write incremental merge pipelines, implement downstream transformations and create test coverage for cases like schema evolution, column renames, type casts and soft deletes.

That work was manageable for one table; it was not manageable for hundreds. The real constraint was the manual effort required to translate legacy logic into lakehouse pipelines repeatedly and consistently.

The company needed to modernize not just its pipelines, but the process of building hundreds of pipelines.

Databricks as the modernization engine

The solution centered on two capabilities working together: Genie Code with custom Agent Skills to generate production-ready ingestion artifacts, and a Databricks App for mapping source fields to target lakehouse tables and generating transformation logic.

Together, they created an end-to-end workflow from metadata discovery to generated code, all within Databricks. Genie Code serves as the autonomous AI partner, while a custom Agent Skill encodes the company’s ingestion patterns and merge logic. Unity Catalog provides schema introspection across raw, historical, and prep layers, while Databricks Apps supports the source-to-target mapping experience. The resulting pipelines use PySpark, Spark SQL, and Delta Lake and are designed to run through Lakeflow Jobs.

This approach allowed the team to extend Genie Code with its own ingestion standards and pipeline conventions. Audit conventions, deduplication logic, change-sequence merge guards, soft-delete reconciliation, and test patterns are embedded directly into the generation process rather than relying on every developer to apply them manually.

Adding determinism to a probabilistic workflow is the key. We chose to automate what we know is correct, and we leave the interpretation layer optional. The LLM helps as you think. The framework ensures explainability is built in.—Dinesh Chandrasekaran, Data & AI Leader at a Leading Canadian Transportation & Logistics Company

That philosophy became central to the entire approach: use AI where reasoning and discovery matter, and use strict patterns where consistency and reproducibility matter most.

From a short prompt to production-ready pipelines

A developer begins with a compact YAML prompt. In the simplest case, that prompt can be as small as two lines for raw ingestion. For a full table pipeline, it includes core inputs such as source and target table names, primary keys, deduplication logic and refresh behavior.

From there, Genie Code follows a structured workflow. It parses and validates the prompt, discovers historical and trusted layer schemas through Unity Catalog metadata, auto-matches columns with source, identifies type-cast and rename requirements, resolves transformation patterns, generates the requested artifacts using the company’s standard patterns and validates each output against required enterprise invariants. Those invariants include primary-key coverage, audit-column placement, change-sequence-guarded merges, REFRESH-aware deduplication and test-suite coverage.

Depending on the mode, the workflow supports a single table, multiple tables in one request or a bulk run driven by a CSV or Excel file stored in a Unity Catalog volume. In practice, the workflow can generate six production-ready outputs: DDL, historical load, raw streaming ingestion, first incremental merge, ongoing incremental merge and an automated test suite.

Every generated notebook follows the same enterprise conventions for audit columns, deduplication, change-sequence-aware merges and soft-delete reconciliation.

Agent Skills made enterprise standards reusable

A key part of the architecture was the custom Agent Skill, which gives Genie Code a reusable way to apply the company’s ingestion standards, naming conventions, and pipeline patterns.

The skill is versioned like any other codebase. It includes a SKILL.md entry point and supporting pattern files for catalog discovery, conventions, raw ingestion, historical loads, incremental merges and test generation. That structure lets the company maintain its generation logic centrally while making it available to developers through Genie Code.

The skill is a single folder uploaded to workspace/.assistant/skills/lakehouse-ingestion/.

It contains a SKILL.md entry point plus seven pattern files, one per artifact type:

The SKILL.md frontmatter is what Genie Code uses to decide when to load the skill:

Instead of documenting standards in one place and asking every developer to interpret them manually, the team encoded those standards into the workflow itself. The agent handles the context gathering and orchestration. The skill ensures the generated artifacts follow the same patterns every time.

A developer starts code generation with a short YAML prompt inside a Genie Code session. The minimum is two lines for raw ingestion only. A full pipeline takes six.

Minimal example, generates only the raw ingestion notebook:

Full example, generates the complete six-artifact pipeline for one table:

The six artifacts execute in this order at runtime:

Grounded in Unity Catalog, governed by default

Another key design principle was grounding code generation in live metadata rather than static assumptions.

Genie Code uses Unity Catalog to inspect schemas across raw, historical and prep tables in real time. That metadata-driven approach eliminates the need for a separate discovery layer and gives the agent the context it needs to generate mappings, infer transformations and validate required fields before code is emitted.

Just as important, all generated artifacts remain inside the Databricks workspace and operate within the same governance model as the rest of the data platform. Access controls, metadata policies and revision history remain native to Databricks. That combination of metadata grounding and governed execution helped the team close a common gap in enterprise AI adoption: moving faster without introducing inconsistency or weakening controls.

Human-in-the-loop where it matters

The company did not treat this as a fully hands-off generation problem. Before code is generated, data designers use a databricks app to inspect how fields from legacy source systems should map to target lakehouse tables.

This step, called Source-to-Target Mapping, captures business logic that should not be guessed or automated blindly. Built with Streamlit based Databricks Apps, the app scans source-system tables, prepopulates column mappings and lets data designers review and refine transformation logic in the browser.

Each edit is tracked in a change log, and the final mapping can be exported and used as input for the generation workflow. This made the process faster without removing expert review from the parts of the workflow where business interpretation still matters. Data designers could focus on transformation intent and business logic, while Genie Code and the generation framework handled repeatable implementation patterns.

Deterministic by design

One of the most important decisions in the architecture was to keep the reasoning layer intelligent and adaptive while making the emitted pipeline code deterministic.

Genie Code handles the parts of the workflow that benefit from agentic reasoning: interpreting prompts, discovering schemas, selecting the right generation path and stitching together the correct sequence of actions. But the generated PySpark code itself is rule-driven and reproducible. Merge statements, deduplication windows, audit-column placement, type casts and test patterns are all defined through explicit templates and invariants.

For the company, that was essential. In production pipeline generation, small variations in merge logic, deduplication windows or audit-column placement can create downstream data quality risk. Deterministic emission made the system trustworthy enough to use at enterprise scale and consistent enough to preserve hard-won engineering standards.

Results: from developer throughput to modernization throughput

The impact was immediate and practical:

• More than 90% automation for new table ingestion into the Databricks Lakehouse
• Pipeline development time reduced from days per table to minutes
• Support for single, multiple and bulk generation modes across ad hoc requests, batched migrations and sprint-scale modernization efforts
• Consistent application enterprise standards across every generated artifact, without requiring manual compliance review

What changed was not only developer productivity. The company increased the throughput of the modernization program itself.

Instead of treating every table migration as a bespoke engineering project, the team created a repeatable system for translating legacy assets into governed lakehouse pipelines at scale.

Looking ahead

The company sees this as the foundation for broader modernization automation. The team is now exploring a more modular skill architecture for orchestration, transformation, business logic and observability; extending discovery beyond Unity Catalog into the broader enterprise data catalog; evaluating AI-assisted conversion of legacy DataStage, COBOL and stored-procedure logic to PySpark; and using emerging background-agent capabilities to support routine pipeline triage, DBR upgrades and schema-mismatch repair.

The long-term goal goes beyond faster code generation. It is to create a modernization model that scales continuously, even as legacy complexity, business demand and platform scope continue to grow.