A practical approach to agentic development.
Data platform migrations can be complex and resource-intensive, particularly when business logic is spread across years of legacy code and interconnected processes. As part of an organization-wide strategy to modernize its data platform with Azure Databricks, a major energy provider partnered with One51 to migrate its existing SAS framework. The objective was to fully retire SAS and take advantage of Databricks' advanced data and AI capabilities.
This blog explores how GitHub Copilot was used throughout the migration, focusing on three key elements: model selection, prompt engineering and context engineering. Although the case study centers on SAS to Databricks, these principles can be applied across a wide range of data engineering projects and development activities.
GitHub Copilot is an AI-powered development assistant that helps you understand and write code. Beyond traditional code completion, it allows developers to analyse existing codebases, plan implementations and execute development tasks using different AI models.
The project made extensive use of GitHub Copilot Business, an organisation-managed subscription that provides access to models from providers such as Anthropic and OpenAI through a single managed subscription and development environment. Developers can select supported models within organisational policies, with usage managed through GitHub's AI-credit system. Copilot provides three primary modes of interaction: Ask, Plan and Agent. Each serves a different purpose in the development process.
Ask Mode — Understanding the existing solution
Ask Mode allows developers to explore code, investigate dependencies and ask technical questions without modifying files. This was particularly useful for understanding unfamiliar macros, tracing %INCLUDE dependencies and investigating how datasets moved through the existing processing framework.
Plan Mode — Defining the implementation
Plan Mode supports more complex development tasks by analysing requirements and proposing a structured implementation plan before making changes. This helped break down SAS to Databricks development into manageable steps, identify dependencies and establish validation requirements. Engineers could review the proposed approach before proceeding, reducing the risk of changes based on incorrect assumptions.
Agent Mode — Implementing the changes
Agent Mode takes development further by allowing Copilot to execute multi-step tasks, modify multiple files and use available development tools. This was particularly valuable for converting SAS macros into PySpark modules, developing parameterised notebooks, configuring Databricks workflows and implementing supporting framework components. Copilot could work across related files and iterate on changes, while engineers reviewed the implementation and validated the resulting outputs. The combination of these modes provided a practical development workflow: understand the existing implementation, establish an execution plan and then implement the changes. Throughout the migration, engineers remained responsible for reviewing code, investigating reconciliation differences and approving production deployments.
Throughout the migration, three factors proved important in getting consistent results from GitHub Copilot: the prompt, model and the context. Together, they influenced how effectively Copilot could understand the existing SAS framework, implement changes and support the migration.
1. Prompt: Define the Task Before Writing Code
The quality of AI-generated code depends heavily on the prompt that is provided. Rather than simply asking Copilot to convert a SAS program to PySpark, development tasks were defined with clear objectives, constraints and acceptance criteria. One effective technique was using AI to improve the prompts themselves.
For example, an initial prompt might simply be:
"Create a lineage graph to understand how SAS Process X works."
Rather than executing this immediately, AI could expand it into a more detailed engineering task:
"Analyze SAS Process X and trace its complete execution flow, including nested %INCLUDE statements, macro dependencies, source tables, intermediate transformations and final outputs. Identify which scripts are actually executed, distinguish inactive processing paths and generate a Mermaid lineage diagram showing the relationships between components. Reference the relevant source files and highlight any dependencies that cannot be confirmed."
This turns a simple request into a structured investigation with clear deliverables. For larger changes, Plan Mode was used to establish an implementation approach before switching to Agent Mode for execution. This gave engineers an opportunity to review the proposed solution, identify incorrect assumptions and adjust the scope before any code was modified.
The objective was to make the requirements sufficiently clear that the agent could execute the task with minimal ambiguity, while keeping engineering decisions and validation under human control.
2. Model: Balance Reasoning, Performance and Cost
Model selection directly affects development quality, speed and cost. Different tasks require different levels of reasoning, and the most expensive model is not necessarily the most appropriate for every change.
During the migration, Claude Sonnet 5 provided the best balance between cost and performance for the team's requirements. It performed particularly well when analysing interconnected SAS macros, tracing dependencies across multiple files and investigating reconciliation differences.
For more complex activities, higher reasoning settings were generally preferred, allowing the model to spend more effort analysing the problem before proposing changes. Simpler tasks, such as documentation updates or straightforward code modifications, could use less computationally demanding models.
One important consideration is that model capabilities and pricing continue to evolve rapidly. The preferred model during this migration should not be considered a permanent recommendation. Model selection should be reviewed regularly based on actual development results, available capabilities and AI-credit consumption. Ultimately, a more capable model can reduce development iterations, but its output still needs to be validated against the expected results.
3. Context: Give AI the Knowledge It Needs
Context was arguably the most important factor in making Copilot effective across the migration.
Initially, converting SAS programs individually provided inconsistent results because the model lacked visibility into the wider framework. Individual macros often depended on shared configuration, other macros, input datasets and execution sequences that were not apparent from a single file.
Working directly within the repository allowed Copilot to access relevant source code, dependencies, configuration and documentation. For demanding tasks, the team selected the largest supported context window, up to approximately one million tokens where available, to analyse larger parts of the codebase. This was particularly useful for work involving multiple interconnected components.
An important practice was documenting significant decisions, technical findings and architectural changes in Markdown files within the repository. This included migration rules, reconciliation findings, known issues and implementation decisions. The agent could then reference this documentation during subsequent development tasks instead of relying on information from previous chat sessions.
Repository-level Copilot instructions were also introduced to establish consistent development standards. General rules were kept in always-available instructions, while more detailed procedures were maintained in task-specific skills and documentation.
Copilot was also instructed to update relevant Markdown documentation when important technical decisions were made or significant issues were resolved, subject to engineering review.
This created a process in which knowledge gained during development became reusable project context. As the project progressed, the repository became both the source of the implementation and a reference for subsequent AI-assisted work.
The team used GitHub Copilot to review the migrated modules and identify opportunities for optimization, particularly by replacing iterative serial processing with Spark native transformations. This included reviewing driver-side operations, repeated joins and unnecessary data scans. Proposed changes were assessed using Spark execution plans and cluster performance metrics, with reconciliation against the original SAS outputs remaining essential to ensure that optimizations did not alter the business logic.
Focus was given to expensive shuffles, data materialization and unnecessary processing, while performance improvements were only considered verified once measured on a Databricks cluster.
One example was a processing program that initially took approximately 18 hours to complete. By reviewing the execution flow and making better use of parallel processing, the team reduced its runtime to under two hours at a fraction of the total cost. This improvement came primarily from optimising how workloads were distributed and executed, rather than changing the underlying business calculations.
The repository's .github configuration became part of the development workflow. General instructions described how Copilot should inspect, plan, change and validate. Folder-specific instructions set conventions for macro modules, notebooks, jobs and framework code. Reusable skills covered common procedures such as SAS porting, reconciliation investigation, failed-run triage and Spark review. Read-only reviewer agents could assess proposed changes without editing the code under review.
Confirmed findings were recorded in the documentation, with instructions and skills linked to the relevant documentation. This avoided competing copies of the same rule and reduced repeated errors around missing values, table names and run-version handling. It also made the workflow less dependent on whichever engineer had last investigated an issue.
The instructions did not function as a substitute for review. A skill could tell Copilot what query to prepare and what conditions to inspect, but the result still depended on real repository content and actual Databricks execution. Read-only diagnosis and review roles were useful for keeping a second opinion separate from an implementation change.
The delivered work demonstrated how GitHub Copilot can support complex data engineering work beyond code generation. From understanding dependencies to developing PySpark transformations, investigating reconciliation differences, improving workflow orchestration and performance, Copilot became an integral part of the development process.
Three factors were particularly important: clear prompts, selecting the right model and maintaining relevant project context. Together, these allowed the team to delegate more complex tasks while keeping development consistent with the existing architecture and business requirements. Capturing decisions and lessons in the repository also made that knowledge reusable across development sessions.
The same approach can be applied to other data engineering projects, not just migrations. While AI can significantly reduce manual development effort, engineering expertise remains essential for validating results, making architectural decisions and ensuring production readiness. The greatest value comes from combining AI-assisted development with established engineering practices.
Throughout this migration, GitHub Copilot was primarily used with a single AI model at a time, selected based on reasoning capabilities, performance and cost. Looking ahead, multi-model orchestration offers an opportunity for several models to collaborate on the same development task.
GitHub's Project HydraFusion , currently in an early-stage preview, explores this approach by combining different models for code generation, reasoning and independent review, with the aim of improving development quality while optimising performance and AI-credit consumption.
This also opens opportunities for hybrid AI development, where locally hosted open-source models work alongside more powerful cloud-based models. Using tools such as Ollama, simpler development tasks could be handled locally, while more complex reasoning and implementation work is delegated to cloud models. Together, these developments point towards a future where AI models are automatically selected and coordinated based on task complexity, capabilities and cost.
As models become more capable and better integrated into development workflows, the opportunities to improve how data platforms are built will continue to grow. For data engineers, staying current with these developments and learning how to apply them effectively will become an increasingly valuable skill.
Planning a similar migration or looking to modernize your data platform? Get in touch with One51 to explore how we can support your data engineering and modernisation initiatives.
References
https://docs.github.com/en/copilot/get-started/plans
https://docs.github.com/en/copilot/how-tos/copilot-in-your-ide/chat-with-copilot/chat-in-ide
https://docs.github.com/en/copilot/concepts/billing-and-usage/organizations-and-enterprises/billinghttps://docs.github.com/en/copilot/reference/customization-cheat-sheet
https://docs.github.com/en/copilot/how-tos/copilot-in-your-ide/use-copilot-agents/use-plan-mode https://github.blog/ai-and-ml/github-copilot/project-hydrafusion-frontier-quality-via-multi-model orchestration/
https://ollama.com/