Alchemist: from Brickbuilder to a Databricks Market App

For almost 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 Accomplice, 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 this time, that legacy experience is complemented by a workforce of Databricks Champions and a devoted Information Engineering observe, giving us the uncommon capacity to talk each “SAS” and “Spark” fluently.

Early in our journey, we noticed a recurring sample: organisations needed 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 almost all options have 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 instrument 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 fundamental profilers and providing you with a transparent understanding of your workload
• Converts SAS code to Databricks utilizing greatest 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 challenge recordsdata, and SAS DI jobs in .spk format, extracting each code and beneficial metadata
• Supplies versatile, configurable outcomes with customized template features to fulfill even the strictest architectural necessities
• Integrates AI LLM capabilities for atypical code buildings, attaining a 100% conversion charge on each file.
• Integrates simply with frameworks or CI/CD pipelines to automate your entire migration move, from evaluation to remaining validation and deployment

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

So, what’s Alchemist in depth?

Alchemist analyzer 

Firstly, Alchemist isn’t just a transpiler, it’s a highly effective evaluation and evaluation instrument. The Alchemist Analyzer shortly parses and examines any batch of code, producing a complete profile of its SAS code traits. As an alternative of spending weeks on handbook overview, purchasers can get hold of 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 offers perception into migration-scope measurement, highlights distinctive components, detects integrations, and helps assess workforce preferences for various programmatic patterns. It additionally classifies workload varieties, helps us to foretell automation-conversion charges, and estimates the hassle wanted for result-quality validation.

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

This degree of element helps reply questions comparable to:

• Which use case ought to we choose for the MVP to display enhancements shortly?
• How ought to we prioritize code migration, for instance, migrate ceaselessly used information first or prioritize important information producers?
• If we refactor a selected macro or change a supply construction, which different code segments will probably be affected?
• To unencumber disk house, 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 offers 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 we have now already used on a number of consumer tasks.

Alchemist transpiler

Let’s begin with a short 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 fulfill 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 Outcome 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 a whole lot of SAS Enterprise Information flows to Databricks. These flows dealt with day-to-day reporting and information consolidation, carried out routine enterprise checks, and have 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 out there), so at present’s customers can count on even increased 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 routinely remodeled into executable in databricks code. Nevertheless, the true accuracy of this conversion can solely be decided after operating 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 obtained close to 75% conversion charge with close to 90% accuracy (90% move’s steps handed validation with out modifications):

With the most recent Alchemist model that includes AI-powered conversion, we achieved a 100% conversion charge. Nevertheless, the AI-provided outcomes nonetheless skilled the identical drawback with an absence of accuracy. This makes information validation the subsequent “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 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 challenge threat and price. Over time, we have now experimented with a number of validation methods, frameworks, and tooling to shorten the “validation section” with out shedding 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 challenge scope?
• Easy methods 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 entire 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 routinely collected in SAS
• Remoted 4-phase testing:
  • Unit assessments – 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 setting utilizing goal sources
  • Prod-like check – a full check on a production-like setting 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 way of information sampling. Nevertheless, brokers have been much less useful within the final two phases, the place deep experience and expertise are required.
• Experiences. Outcomes needs to be consolidated in clear, reproducible stories prepared for quick overview by key stakeholders. They often do not have a lot time to validate migrated code and are solely prepared to simply accept and check the total use case.

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

The mix 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 consumer operations. At T1A, we concentrate on 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 challenge assist with our specialists to make sure a smoother consumer onboarding, together with:

• Customized monitoring in your information platform
• Customizable academic workshops tailor-made to completely different audiences
• Help groups with versatile engagement ranges to deal with technical and enterprise person requests
• Finest observe 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 various 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 may see only a little bit of our challenge’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 contains getting ready configurations and mappings, producing custom-made check information to succeed in full protection of the code, and creating templates routinely to fulfill architectural guidelines, amongst different concepts.

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

Be part of Our Subsequent Webinar – “SAS Migration Finest Practices: Classes from 20+ Enterprise Ventures“ →

We’d share intimately what we realized, what could be subsequent, and what are the very best practices for the full-cycle migration to Databricks. Or, watch our migration strategy 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 Surroundings in Minutes →

Add your SAS code for an prompt, 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]):

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

• Request a Proof of Idea – Validate our strategy 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