Alchemist: from Brickbuilder to a Databricks Market App

For practically six years, T1A has partnered with Databricks to end-to-end SAS-to-Databricks migration tasks to assist enterprises modernize their information platform. As a former SAS Platinum Associate, we possess a deep understanding of the platform’s strengths, quirks, and hidden points that stem from the distinctive habits of the SAS engine. At the moment, that legacy experience is complemented by a group of Databricks Champions and a devoted Information Engineering follow, giving us the uncommon potential to talk each “SAS” and “Spark” fluently.

Early in our journey, we noticed a recurring sample: organisations needed to maneuver away from SAS for a wide range of causes, but each migration path appeared painful, dangerous, or each. We surveyed the market, piloted a number of tooling choices, and concluded that the majority options had been underpowered and handled SAS migration as little greater than “switching SQL dialects.” That hole drove us to construct our personal transpiler, and Alchemist was first launched in 2022.

Alchemist is a robust software that automates your migration from SAS to Databricks: 

• Analyzes SAS and parses your code to supply detailed insights at each degree, closing gaps left by primary profilers and providing you with a transparent understanding of your workload
• Converts SAS code to Databricks utilizing finest practices designed by our architects and Databricks champions, delivering clear, readable code with out pointless complexity
• Helps all frequent codecs, together with SAS code (.sas recordsdata), SAS EG venture recordsdata, and SAS DI jobs in .spk format, extracting each code and worthwhile metadata
• Offers versatile, configurable outcomes with customized template features to satisfy even the strictest architectural necessities
• Integrates AI LLM capabilities for atypical code buildings, reaching a 100% conversion charge on each file.
• Integrates simply with frameworks or CI/CD pipelines to automate your complete migration move, from evaluation to remaining validation and deployment

Alchemist, along with all our instruments, is now not only a migration accelerator; it is the principle engine and migration driver on our tasks.

So, what’s Alchemist in depth?

Alchemist analyzer 

At first, Alchemist isn’t just a transpiler, it’s a highly effective evaluation and evaluation software. The Alchemist Analyzer shortly parses and examines any batch of code, producing a complete profile of its SAS code traits. As a substitute of spending weeks on guide evaluation, purchasers can receive a full image of code patterns and complexity in minutes.

The evaluation dashboard is free and is now out there in two methods:

This evaluation gives perception into migration-scope measurement, highlights distinctive parts, detects integrations, and helps assess group preferences for various programmatic patterns. It additionally classifies workload sorts, helps us to foretell automation-conversion charges, and estimates the trouble wanted for result-quality validation.

Greater than only a high-level overview, Alchemist Analyzer gives an in depth desk view (we name it DDS) displaying how procedures and choices are used, information lineage, and the way code parts depend upon each other. 

This degree of element helps reply questions reminiscent of:

• Which use case ought to we choose for the MVP to show enhancements shortly?
• How ought to we prioritize code migration, for instance, migrate often used information first or prioritize crucial information producers?
• If we refactor a selected macro or change a supply construction, which different code segments will probably be affected?
• To liberate disk area, or to cease utilizing a pricey SAS part, what actions ought to we take first?

As a result of the Analyzer exposes each dependency, management move, and information touch-point, it provides us an actual understanding of the code, letting us do excess of automated conversion. We are able to pinpoint the place to validate outcomes, break monoliths into significant migration blocks, floor repeatable patterns, and streamline end-to-end testing, capabilities now we have already used on a number of shopper tasks.

Alchemist transpiler

Let’s begin with a quick overview of Alchemist’s capabilities:

• Sources: SAS EG tasks (.egp), SAS base code (.sas), SAS DI Jobs (.spk)
• Targets: Databricks notebooks, PySpark Python code, Prophecy pipelines, and so on.
• Protection: Close to 100% protection and accuracy for SQL, frequent procedures and transformations, information steps, and macro code.
• Put up-conversion with LLM: Identifies problematic statements and adjusts them utilizing an LLM to enhance the ultimate code.
• Templates: Options to redefine converter habits to satisfy refactoring or goal structure visions.

The Alchemist transpiler works in three steps:

1. Parse Code: The code is parsed into an in depth Summary Syntax Tree (AST), which totally describes its logic.
2. Rebuild Code: Relying on the goal dialect, a selected rule is utilized to every AST node to rebuild the transformation within the goal engine, step-by-step, again into code.
3. Analyze Consequence and Refine: The result’s analyzed. If any statements encounter errors, they are often transformed utilizing an LLM. This course of consists of offering the unique assertion together with all related metadata about used tables, calculation context, and code necessities.

This all sounds promising, however how does it present itself in an actual migration situation? 

Lets share some metrics from a latest multi-business-unit migration wherein we moved tons of of SAS Enterprise Information flows to Databricks. These flows dealt with day-to-day reporting and information consolidation, carried out routine enterprise checks, and had been maintained largely by analytics groups. Typical inputs included textual content recordsdata, XLSX workbooks, and numerous RDBMS tables; outputs ranged from Excel/CSV extracts and electronic mail alerts to parameterized, on-screen summaries. The migration was executed with Alchemist v2024.2 (an earlier launch than the one now out there), so at this time’s customers can count on even greater automation charges and richer outcome high quality.

To provide you some numbers, we measured statistics for a portion of 30 random EG flows migrated with Alchemist.

We should start with a transient disclaimers:

1. When discussing the conversion charge, we’re referring to the proportion of the unique code that has been mechanically remodeled into executable in databricks code. Nonetheless, the true accuracy of this conversion can solely be decided after working assessments on information and validating the outcomes.
2. Metrics are collected on earlier Alchemist’s model and with out templates, further configurations and LLM utilization have been turned off. 

So, we acquired close to 75% conversion charge with close to 90% accuracy (90% move’s steps handed validation with out adjustments):

With the most recent Alchemist model that includes AI-powered conversion, we achieved a 100% conversion charge. Nonetheless, the AI-provided outcomes nonetheless skilled the identical downside with a scarcity of accuracy. This makes information validation the subsequent “rabbit gap” for migration.

By the way in which, it is price emphasizing that thorough preparation of code, objects mappings and different configurations is essential for profitable migrations. Corrupted code, incorrect information mapping, points with information supply migration, outdated code, and different preparation-related issues are usually tough to establish and isolate, but they considerably affect migration timelines.

Information validation workflow and agentic strategy

With automated and AI-driven code conversion now near “one-click”, the true bottleneck has shifted to enterprise validation and person acceptance. Typically, this section consumes 60–70% of the general migration timeline and drives the majority of venture danger and value. Through the years, now we have experimented with a number of validation methods, frameworks, and tooling to shorten the “validation section” with out dropping high quality.

Typical enterprise challenges we face with our purchasers are:

• What number of assessments are wanted to make sure high quality with out increasing the venture scope?
• obtain take a look at isolation in order that they measure solely the standard of the conversion, whereas remaining repeatable and deterministic? “Apple to apple” comparability.
• Automating your complete loop: take a look at preparation, execution, and outcomes evaluation, fixes
• Pinpointing the precise step, desk, or perform that causes a discrepancy, enabling engineers to repair points as soon as and transfer on

We have settled on this configuration: 

• Automated take a look at era primarily based on actual information samples mechanically collected in SAS
• Remoted 4-phase testing:
  • Unit assessments – remoted take a look at of every transformed assertion
  • E2E take a look at – full take a look at of pipeline or pocket book, utilizing information copied from SAS
  • Actual supply validation – full take a look at on take a look at surroundings utilizing goal sources
  • Prod-like take a look at – a full take a look at on a production-like surroundings utilizing actual sources to measure efficiency, validate deployment, collect outcomes statistics metrics, and run a number of utilization situations
• “Vibe testing” – AI brokers carried out nicely at fixing and adjusting unit assessments and E2E assessments. This is because of their restricted context, quick validation outcomes, and iterability by means of information sampling. Nonetheless, brokers had been much less useful within the final two phases, the place deep experience and expertise are required.
• Studies. Outcomes needs to be consolidated in clear, reproducible stories prepared for quick evaluation by key stakeholders. They normally do not have a lot time to validate migrated code and are solely prepared to simply accept and take a look at the complete use case.

We encompass this course of with frameworks, scripts, and templates to attain pace and adaptability. We’re not attempting to construct an “out of the field” product as a result of every migration is exclusive, with totally different environments, necessities, and ranges of shopper participation. However nonetheless, set up and configuration needs to be quick. 

The mixture of Alchemist’s technical sophistication and our confirmed methodology has persistently delivered measurable outcomes: nearly 100% conversion automation charge, 70% reductions in validation and deployment time. 

Finalizing migration

The true measure of any migration resolution lies not in its options, however in its real-world affect on shopper operations. At T1A, we deal with extra than simply the technical facet of migration. We all know that migration is not completed when code is transformed and examined. Migration is full when all enterprise processes are migrated and consuming information from the brand new platform, when enterprise customers are onboarded, and after they’re already making the most of working in Databricks. That is why we not solely migrate but additionally present superior post-migration venture assist with our specialists to make sure a smoother shopper onboarding, together with:

• Customized monitoring on your information platform
• Customizable academic workshops tailor-made to totally different audiences
• Help groups with versatile engagement ranges to deal with technical and enterprise person requests
• Greatest follow sharing workshops
• Help in constructing a middle of experience inside your organization.

All these,parameterized from complete code evaluation and automatic transpilation to AI-powered validation frameworks and post-migration assist, have been battle-tested throughout a number of enterprise migrations. And we’re able to share our experience with you. 

Our success tales

So, it’s time to summarize. Over the previous a number of years, we have utilized this built-in strategy throughout numerous healthcare and insurance coverage organizations, every with distinctive challenges, regulatory necessities, and business-critical workloads.

We have been studying, growing our instruments, and bettering our strategy, and now we’re right here to share our imaginative and prescient and methodology with you. Right here you’ll be able to see only a little bit of our venture’s references, and we’re able to share extra in your request. 

So what’s subsequent?

We solely began our adoption of an Agentic strategy, but we acknowledge its potential for automating routine actions. This consists of getting ready configurations and mappings, producing personalized take a look at information to achieve full protection of the code, and creating templates mechanically to fulfill architectural guidelines, amongst different concepts.

However we see that present AI capabilities should not but mature sufficient to deal with sure extremely advanced duties and situations. Subsequently, we anticipate that the simplest path ahead lies on the intersection of AI and programmatic methodologies.

Be a part of Our Subsequent Webinar – “SAS Migration Greatest Practices: Classes from 20+ Enterprise Challenges“ →

We might share intimately what we realized, what could be subsequent, and what are one of the best practices for the full-cycle migration to Databricks. Or, watch our migration strategy demo → and lots of different supplies relating to migration in our channel.

Able to speed up Your SAS migration?

Begin with Zero Danger – Get Your Free Evaluation At the moment

Analyze Your SAS Surroundings in Minutes →

Add your SAS code for an prompt, complete evaluation. Uncover migration complexity, establish fast wins, and get automated sizing estimates, fully free, no signup required.

Take the Subsequent Step

For Migration-Prepared Organizations ([email protected]):

• E book a Strategic Session – 45-minute session to evaluation your evaluation outcomes and draft a customized migration roadmap

• Request a Proof of Idea – Validate our strategy with a pilot migration of your most important workflows

For Early-Stage Planning:

• Obtain the Migration Readiness Guidelines → Self-assessment information to judge your group’s preparation degree