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 initiatives to assist enterprises modernize their information platform. As a former SAS Platinum Companion, we possess a deep understanding of the platform’s strengths, quirks, and hidden points that stem from the distinctive conduct of the SAS engine. At this time, that legacy experience is complemented by a staff of Databricks Champions and a devoted Information Engineering follow, giving us the uncommon means to talk each “SAS” and “Spark” fluently.

Early in our journey, we noticed a recurring sample: organisations wished to maneuver away from SAS for quite a lot of causes, but each migration path regarded 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 strong software that automates your migration from SAS to Databricks: 

• Analyzes SAS and parses your code to offer detailed insights at each degree, closing gaps left by primary profilers and supplying 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 undertaking recordsdata, and SAS DI jobs in .spk format, extracting each code and worthwhile metadata
• Supplies versatile, configurable outcomes with customized template capabilities 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 circulation, from evaluation to last 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 initiatives.

So, what’s Alchemist in depth?

Alchemist analyzer 

Initially, Alchemist isn’t just a transpiler, it’s a highly effective evaluation and evaluation software. The Alchemist Analyzer rapidly parses and examines any batch of code, producing a complete profile of its SAS code traits. As an alternative of spending weeks on guide evaluate, shoppers can receive a full image of code patterns and complexity in minutes.

The evaluation dashboard is free and is now obtainable in two methods:

This evaluation offers perception into migration-scope measurement, highlights distinctive parts, detects integrations, and helps assess staff preferences for various programmatic patterns. It additionally classifies workload varieties, 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 presents an in depth desk view (we name it DDS) exhibiting how procedures and choices are used, information lineage, and the way code parts rely upon each other. 

This degree of element helps reply questions comparable to:

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

As a result of the Analyzer exposes each dependency, management circulation, and information touch-point, it offers us an actual understanding of the code, letting us do way over 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 consumer initiatives.

Alchemist transpiler

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

• Sources: SAS EG initiatives (.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.
• Publish-conversion with LLM: Identifies problematic statements and adjusts them utilizing an LLM to enhance the ultimate code.
• Templates: Options to redefine converter conduct 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 contains 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 state of affairs? 

Lets share some metrics from a current multi-business-unit migration wherein we moved lots 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 varied RDBMS tables; outputs ranged from Excel/CSV extracts and e mail alerts to parameterized, on-screen summaries. The migration was executed with Alchemist v2024.2 (an earlier launch than the one now obtainable), so right now’s customers can anticipate even increased automation charges and richer outcome high quality.

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

We should start with a temporary disclaimers:

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

So, we obtained close to 75% conversion charge with close to 90% accuracy (90% circulation’s steps handed validation with out modifications):

With the newest 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 following “rabbit gap” for migration.

By the best way, 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 determine and isolate, but they considerably impression migration timelines.

Information validation workflow and agentic method

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

Typical enterprise challenges we face with our shoppers are:

• What number of exams are wanted to make sure high quality with out increasing the undertaking scope?
• The best way to obtain check isolation so that they measure solely the standard of the conversion, whereas remaining repeatable and deterministic? “Apple to apple” comparability.
• Automating your complete loop: check preparation, execution, and outcomes evaluation, fixes
• Pinpointing the precise step, desk, or operate that causes a discrepancy, enabling engineers to repair points as soon as and transfer on

We have settled on this configuration: 

• Computerized check technology primarily based on actual information samples robotically collected in SAS
• Remoted 4-phase testing:
  • Unit exams – remoted check of every transformed assertion
  • E2E check – full check of pipeline or pocket book, utilizing information copied from SAS
  • Actual supply validation – full check on check surroundings utilizing goal sources
  • Prod-like check – a full check 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 properly at fixing and adjusting unit exams and E2E exams. This is because of their restricted context, quick validation outcomes, and iterability by information sampling. Nonetheless, brokers had been much less useful within the final two phases, the place deep experience and expertise are required.
• Studies. Outcomes ought to be consolidated in clear, reproducible studies prepared for quick evaluate by key stakeholders. They normally do not have a lot time to validate migrated code and are solely prepared to just accept and check the complete use case.

We encompass this course of with frameworks, scripts, and templates to realize velocity and suppleness. 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 consumer participation. However nonetheless, set up and configuration ought to be quick. 

The mixture of Alchemist’s technical sophistication and our confirmed methodology has constantly delivered measurable outcomes: virtually 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 impression on consumer operations. At T1A, we concentrate on extra than simply the technical aspect 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 once they’re already benefiting from working in Databricks. That is why we not solely migrate but in addition present superior post-migration undertaking assist with our specialists to make sure a smoother consumer onboarding, together with:

• Customized monitoring on your information platform
• Customizable instructional workshops tailor-made to totally different audiences
• Help groups with versatile engagement ranges to handle technical and enterprise person requests
• Finest 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 method throughout numerous healthcare and insurance coverage organizations, every with distinctive challenges, regulatory necessities, and business-critical workloads.

We have been studying, creating our instruments, and enhancing our method, and now we’re right here to share our imaginative and prescient and methodology with you. Right here you may see only a little bit of our undertaking’s references, and we’re able to share extra in your request. 

So what’s subsequent?

We solely began our adoption of an Agentic method, but we acknowledge its potential for automating routine actions. This contains making ready configurations and mappings, producing personalized check information to achieve full protection of the code, and creating templates robotically to fulfill architectural guidelines, amongst different concepts.

Alternatively we see that present AI capabilities aren’t but mature sufficient to deal with sure extremely advanced duties and situations. Due to this fact, 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 Finest Practices: Classes from 20+ Enterprise Undertakings“ →

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

Able to speed up Your SAS migration?

Begin with Zero Threat – Get Your Free Evaluation At this time

Analyze Your SAS Atmosphere in Minutes →

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

Take the Subsequent Step

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

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

• Request a Proof of Idea – Validate our method with a pilot migration of your most crucial workflows

For Early-Stage Planning:

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