Optimizing Analytics Infrastructure: Lessons from Migrating Snowflake to Databricks

Key Takeaways

Thank you everyone. Thanks for joining the session this afternoon. My name is Ahmed and for this session I'll be sharing my experience for migrating snowflake to datab bricks and the journey that includes migration rational migration planning implementation opportunities and solution and I will conclude the session with lesson learned and the results. But before I delve into the details of the session I would like to do a simple poll. How many in the audience are running snowflake as well as data bricks or looking forward to do such a migration? So would like to flash this code doing more with less is new imperative. I don't think this needs any additional justification very well said in the current environment where everything is about the cost and resource optimization. I think it's important every company right now the environment we are in everybody wants to do actually more with the less resources whether it's around cost resources yes how do we productize how do we implement or basically make AI a part of it and yes we will see that so I will start with the migration rational so within the migration rational there are key considerations so let me start with basically what was the situation ation. So the data team was very productive but siloed analytics lived in multiple tools and machine learning lived another pool and collaboration suffered. So it needed more than a cloud data warehouse which is Snowflake. It needed unity. There was a growing ask from the data engineers, data scientists, data analysts asking for a same environment for accelerating the delivery with the simplifying governance. That's when the datab bricks became the answer. Bringing all of these teams into a one platform. As the data starts growing to hundreds of terabytes, scalability becomes a concern. Datab bricks cloudnative architecture. It scales effortlessly uh and allocating the resources without any manual tuning. And datab bricks does support flexibility by supporting all the latest AI and ML frameworks as well as the most commonly used the languages used for machine learning like Python, SQL and R badge and streaming workflows. They can be integrated seam seamlessly enabling the rapid innovation. And the last one is the decoupling of the storage and compute. Optimize the spend with the autoscaling that eliminates the idle cost and the resource granularity. It prevents the overprovisioning and by having a one platform which is the unified lake platform which is the data bricks it less the tooling overhead. So that was the first thing uh in the migration rational the key consideration. Other thing is about the performance benchmarks. So now imagine the use case a data warehouse like a snowflake platform straining under its own weight queries lagging throughput choking and teams are waiting for the results. So this was the another factor actually for switching to the data bricks. The transformation began with the performance all those the complex workloads where the snowflake was basically suffering the datab bricks sword and the datab bricks spark power engine along with uh the data lake. uh it processed massive data set data analyst or the data engineers they weren't running queries anymore they were flying through them then came the throughput so what is a database secret actually uh for throughput massive parallel processing so it's like an adding multiple lanes actually during a high traffic time so it handled thousands of concurrent operations s effortlessly and real time analytics machine learning pipelines no longer a bottleneck just a smooth unimpended flow. So but the real game changer for this whole transformation was about the query execution time Delta lakes optimized storage along with the photons lightning engine I'm sure basically all of you are using that one so those two uh slash the latency like anything what used to take minutes now basically start taking seconds with all such improvements now the decisions can happen faster leading to the accelerated innovation and a data system was reborn with a faster, scalable and a co costefficient data platform. The teams can collaborate without friction being on the unified platform and this wasn't just a migration. It was a strategic leap forward and datab bricks unified lakehouse empowered the teams actually to build an AI ready foundation for the future. So those were those were the two basically the key rationals uh that why basically there was a migration that we said that we are going to move away from uh snowflake to data bricks. Moving over to the second thing which is about the migration planning. So migration planning again has uh several subtopics. The first one is the architecture assessment. So let me take you behind the scenes of the snowflake to data bricks migration journey. It began with understanding the DNA of the data, right? Meticulously mapped every table, relationship, constraints and all the dependencies within the snowflake like an archaeologist basically deciphering the script uh script and uncovering the hidden patterns that made the data tick. So then came the data excavation which means so trying to find out how the data is flowing within the snowflake instance which tables being accessed uh most and where the bottle bottlenecks learned. So all these insights they became like a compass actually to to help out how to reorganize the data within data bricks actually for maximum performance. So the final act was the architecting the future for the datab bricks environment wasn't just a new home for the data. It was a powerhouse which was blending the lakehouse capability and reliability with the scalable compute. The result it was a platform where the data ingestion, data processing and all the advanced AI and machine learning they were working they started working in a perfect harmony and all with with the security it was a metamorphosis from the rigid structures that we had before to the limitless possibilities and every decision from the schema mapping to the tool selection was a deliberate step forward towards this transformation. So continuing on this the migration planning second thing was about the tool selection. So let me talk about the three components that guided the migration journey. First was the schema migration tool. It was a specialized tool that was created by the team actually leveraging the open source which was modified and it worked like a schema translator. It carefully unpacked all the snowflake table definitions and reassembled them in the datab bricks language preserving all the relationship data type and what could have taken several weeks of manual effort we were able to do that actually within days right and second thing is the data migration tool a tool leveraging the dynamic duo which is like snowflakes copy command and the datab bricks autoloader it orchestrated the safe passage age for terabytes of the data that was stored over there and moving it actually carefully mostly in scheduled batches and always checking the manifest to ensure that nothing get basically lost in the transit and finally the meta data tool did its magic and as all of you are familiar the datab bricks basically has got this unity catalog so unity catalog became the meticulous catalog of the new system carefully transcribing thing every view functions and all the access rule info that we used to have everything was moved over right so no security permission was overlooked no date lineage were broken so these three components the schema migration tool the data migration and the the metadata uh mover they became the foundation in this transformation and together with all these components that turned would have been a risky leap turned into a graceful stride into the data future. Moving on with the migration planning, the next thing was around the risk mitigation. So every time basically you do such a massive migration, you have to make sure that the risk is properly mitigated. So during this journey from Snowflake to data bricks, the top priority was clear. We need to mitigate risk while modernizing this data platform. Migration strategy was built around three core foundation pillars. Incremental migration, governance and observability each playing a very vital role in a smooth secure transition. So instead of doing a big bang switch as I said before we embrace the incremental data migration moving the data in a carefully validated stages help to avoid the downtime reduce the operational risk and maintain the trust with the various business teams and the end users of the data. Every batch was checked for accuracy before processing along to preserve the data integrity throughout the data pipeline. Governance was embedded and uh of course using the with a unity catalog and from the day one and with a unity catalog fine grain access control track the data lineage and enforce compliance policy across the domain. Uh this was needed actually to give a confidence that the sensitive data. So there was a lot of sensitive data actually in the snowflake and this helped actually to give the confidence that the sensitive data remained protected even as we were rearchitecting and replatforming this data platform. So equally important was observability realtime insights into the pipeline performance and then system health to detect issues early and act quickly on them. So team always stayed ahead of the failures and thanks to the integrated alerting and clear visibility of this migration process. So together with these three pillars it helped actually to reduce the risk and laid out the foundation of a resilient governed and a scal scalable analytic systems using the core lakehouse platform. So these these were the migration planning and then obvious next step was around the implementation. So for the implementation again there are basically sub areas and the very first area is data extraction. So at the beginning of this migration it was clear that the data extraction would be a very critical phase of this. So one that will demand both the precision and agility to balance the performance with the reliability. A hybrid extraction strategy was adopted combining the batch processing for historical data and with the incremental loading for all the real-time changes that were occurring within the systems. This approach allowed actually to transfer massive volume using the batch approach very efficiently and while ensuring that no data was left behind during this transition and it also enabled a smooth low-risk cut over without doing any service disruption. But moving the data wasn't enough. It has to be accurate. So automated validation was basically part of the process. It was implemented on every extracted file that we had. uh we checked for record counts, we applied check sums, we validated formats to catch the discrepancies early in the process. This not only safeguarded the downstream analytics but also boosted the confidence of our stakeholders uh in the integrity of this migration. So to go further we conducted a deep data profiling on the extracted data sets. So this helped actually with the analysis, the distribution, the surface anomalies and then reviewing the key attributes. This all test actually to assess the quality and informed how we would model all this this transform data onto the data bricks. So together with this abstraction framework which consist of hybrid loads, rigorous validation and intelligent and smart profiling gave us a transparent highquality data pipeline. It became the launchpad for a intelligent governed and a AI platform on data bricks. So the next component actually for the implementation was data loading. We just extracted all the data using those three components. Then the next thing is the data loading for the migration process. Building a robust data loading framework was a non-negotiable. It anchored on three principles. load manifest tracking, IDM potent processing and delta life tables for orchestration. So it began with a a load manifest tracker and a monitoring layer. So the purpose of this load manifest was to it's a system that will provide a complete transparency into the data that has been ingested, what is still pending and where the anomalies occurred. It enabled the real-time alerts on the failed or the delayed loads and help to respond quickly to troubleshoot effectively and ensure that the data is complete across the pipeline and building an ad important loading process for most steps wherever basically we could do it we did that. This meant that pipeline could safely reprocess the data without the risk of duplicating or any inconsistency. So it's it was it really helped actually because it was an essential feature when dealing with doing a retries or the changes in the upstreams and you have to do correction. So item potent loading really helped with those things. So it preserved the data integrity while improving the resilience of the injection process. So to tie it all together we adopted the delta live tables. The delta live tables orchestrated the workflow declaratively. So gained the automatic data lineage and benefited from the built-in uh error handling and the retries. So this dramatically reduced the operation overhead and of course it scaled effortlessly as the data was basically uh growing in size. So by integrating these three strategies the load manifest, add important loading and then delta live tables uh we created a transparent reliable and a high performance data loading system forming the backbone for an advanced analytics and AI system which is again part of your unified lakehouse platform as a back end. So next component on the implementation was pipeline refactoring. So during this migration we saw an opportunity and I'm sure basically if many of you are looking for migrating from snowflake uh to datab bricks use this as an opportunity to go beyond a simple replication take a look at your full pipeline refactor initiative and uh to modernize the data flows uh using the data uh bricks native capabilities and that's what basically we leveraged right so for the data ingestion uh We moved to data bricks autoloader and the copy into which gave the power to seamlessly and reliably ingest the both the batch and streaming data and into the lakehouse. These tools which are cloudnative they scale effortlessly and with the data volumes and require very minimal intervention. So this dramatically improved the reliability and the reduce the uh operating load. So in the transformation layer rebuilding all the logic using pi spark and then delta live tables uh this shifted the empowering the teams to express the complex transformation declaratively. So, so this is basically again a change as the you may know actually if you're using the the delta live tables on the data bricks you can basically start expressing uh the transformation declaratively and that was basically power for this particular component. This really helped actually for data lineage quality enforcement and simplified error handling with the DT. It ensured the trusted analytics ready data while boosting the development velocity and maintainability. So for orchestration we adopted basically the data bricks workflows and then giving a unified cloudnative interface to automate scheduling manage dependencies monitor jobs in the real time. So this eliminated all the fragment tooling that was basically there before and increased the visibility across the full pipeline and by aligning these pipelines with the databicks native services. So it unlocked the agility, scalability and operational efficiency building a future ready data platform primed for AI and advanced analytics on the unified lakehouse. So the last component actually on the implementation is on the performance optimization. So during this data migration we also turned our attention actually to uh uh unlocking the performance gain and we did it by fine-tuning in three areas storage layout query engine and cost performance equation. So first rearchitected the storage layout using the data lake and with a smart partitioning and indexing strategies which drastically reduce the IO overhead and by aligning uh it drastically reduced the IO overhead and how the teams were accessing the data. They saw immediate improvements in rewrite speeds especially across very large scale analytic workloads. And next was optimizing the query engine to fully leverage the data bricks spark platform as well as the photon execution engine refactor the pipelines and then SQL to take the advantage of features like adaptive query execution. So that was another key vectorize reads strategic caching. So all this really resulted into a dramatically faster query response times and snapier dashboard enabling the analyst and scientists to explore data with more agility. And lastly we pursued a cost performance balance by right sizing the compute uh enabling autoscaling and tightening the resource monitoring. We ensured that workload ran with the just the right amount of power, right amount of resources, no more, no less. And this not only optimize the performance but the cut the unnecessary spend making it an optimized spend actually on the data infrastructure. So together these efforts transformed the uh platform into a high performance cost environment and it can it really helped actually doing the innovation at scale at a in a costefficient manner. So moving into the next area which is about the challenges and solutions. So during this journey there were lots of challenges in several areas and along with these challenges basically the solutions were identified and that's what the challenge and solution area is about. So the first one is around the schema compatibility. I think that's what basically I mentioned before. So during the migration one of the intricate challenges that faced was the schema compatibility right navigating the differences between the two platform means now you have to look at the data the the data types the governance model to ensure a seamless high integrity transition. So in this case yes we started looking into the data types actually across the two systems and that's when uh we said that this cannot be manual we need to use automation we need to use uh some software translation and that's when basically we took an open source modified and started basically with all this data type incompatibility for example snowflake the unique like variant it needs a careful mapping to databicks format like struct and all that. So all this was automated DL DDL conversion tools and everything it was all taken care at the end a very I would say very few things were done manually but majority of it was done by basically the schema translator so uh the null handling was the next area null handling and default values they also pose issues snowflake often basically there's an implicit behavior for nulls but data bricks require an explicit definition. So in order to address this one, we built the ETL logic that assigned clear default values and treated nulls consistently across the whole data pipeline ensuring that there are no surprises in the downstream and schema evolution was basically the another area. So uh while the snowflakes allows the adding the columns on the fly and the delta lake uh or the data bricks it is much stricter. So in order to do that governance was implemented around the schema changes using comparison tools and delta's native evolution features keeping the pipeline stable and trustworthy. So uh constraints and keys were addressed by documents all the relational logic enforcing it through ETL validations and in the data bricks uh and with a snowflake manage tables and views and finally the permissions were remapped into the unity catalog where it all restored our back lineage tracking audibility ensuring that governance never skipped a bit. So that was the summary actually in the six areas. Anything related to the schema, what were the problems and how we basically address those. The second uh challenge is around the the data consistency. So during the migration, one of the critical focus was ensuring the data consistency. So this wasn't just about moving the data like move the data from snowflake to data bricks. No, it was much more than that. It was moving it route. Every record, every time stamp and every access policy had to arrive intact and more than that it must be trustworthy. So we begin it by solving it issues the the partial loads. So we used uh the checkpointing load manifest tracking could track every batch and resume or roll back when it is needed. So this approach ensured that end to end completeness even in the face of unexpected failures and next came the time spent drift. So a silent culprit in the analytical errors. So to solve this one we enforced the standard UT format and align the timestamp precision across the platform to avoid any sort of cons inconsistencies around the time stamp. So that helped actually during the reporting and training the the data science models. So the data ordering also mattered. So without consistent sequence the trend analysis and time based aggregation could break. We built the order preserving pipelines and use the data links native support for maintaining the record sequences. So to prevent null value misrepresentation we explicitly map the net value handling between the platforms avoiding any sort of quality issues in the downstream uh data sets. We addressed the concurrency by leveraging Delta Lakes asset transactions which handled basically the parallel right safety. So which was needed uh and to avoid any sort of a metadata loss that can happen. uh we automated the schema and lineage capture from snowflake and restored into the unity catalog ensuring that governance never skip a bit and all this using these six steps uh using this systematic approach it became the foundation for a trust and consistency for this migration from snowflake to data bricks. So the next challenge area was around query rewriting. So for this migration the rewriting SQL queries became one of the most no and critical task. Each challenge demanded precision and approach with a very targeted innovative solution to ensure compatibility and performance. So the first major hurdle was the SQL function compatibility. Many snowflakes specific function they lacked the uh direct compatible datab bricks equivalent. In order to solve this, we created the custom UDFs and uh we used the LLM power translation. So again, so AI basically now being used actually to do the translation. So uh for the this particular one uh we use the llams actually to translate and workflows to convert and optimize queries for the spark SQL without changing their behavior. joins posed another challenge and datab bricks basically uses a different uh optimization strategy. So performance was announced by adding the broadcast hints and optimizing several operations dramatically improved the join speed for very large data sets for the time handling discrepancies. As I said before the time stamp logic is threatened the consistency. So we standardized everything on UTC and created the utility function to manage the time zone arithmetic uniformly across the platform. So snowflake procedure logic which basically had like store procedure had nothing direct equivalent in data bricks. So we restructured into the datab bricks notebook and delta live tables. So this was basically one translation uh the another area of translation gaining better visibility easier maintenance and with the window function and subtle syntax differences they required exact translation. So we built an automated testing framework to validate the identical behavior post migration. And lastly we mapped all the security policies to the Unity catalog preserving the compliance uh gaining finer access controls and together all these six areas with all these effort uh we delivered basically a fast secure and a fully functional queries on data bricks. So the summary basically in this one I think the prime challenges were around SQL around the joints around the procedure and where basically we used AI was actually do the SQL translation. So the next area of challenge was the downtime management. Of course you are doing like a major migration. The end users they're already uh using snowflake and when you are doing this plat platform a mo oration and you are moving uh so end users should not be affected. So during the migration highest priority was actually avoiding any sort of disruption to the business. Every challenge in that journey demanded a precise preemptive solution and was tackled with a purpose. So to reduce the data injection down time we use the parallel pipelines. So I'm sure basically you guys have used this uh this sort of when you you move from one system to another. So the parallel pipelines were established using the datab bricks autoloader. So this dual right strategy what it does is it let the data to flow in both systems going into snowflake going into data bricks simultaneously ensuring the business continuity without a pause. So it was built using the extensive validation and testing window with the automated comparison across the two platforms that they are basically uh exactly same and from query results to performs the benchmark uh for the user acceptance testing. So that now the users can see what what it takes actually on snowflake and everything that is getting transformed on the data bricks what are the the difference. So now they can compare basically the two and the difference basically that this migration is making to them. So from query results to the performance benchmark and user acceptance testing everything was thoroughly tested uh ensuring the confidence in the accuracy before the fin the final cut over in order to do an additional assurance we had a roll back strategy in place. So roll back strategy was again basically the key and it has to be done very intelligently like how do you roll back and then once you roll back how do you b bring it to the clean state again. So that what was done but it was uh it was needed. So after after that so the back fill and incremental load strategy combined bulk historical transfer with the change data capture to keep the data aligned and fresh through the migration was another thing that was performed. So to handle the access control uh changes we faced the user migration. So again for the users and users of the system like all the users that were the users actually on the snowflake they were not like a big bang boom they were moved actually in phases. So finally we also uh simplified the reauthentication uh through the SSO integration enabling secure seamless access without frustrating the users. So this new system the datab bricks lakehouse platform was also basically integrated with the SSO and so the users basically they don't get frustrated. So by addressing all the these six critical areas in a very coordinate strategy we delivered a smooth secure and a transition by which basically we were able to manage the downtime and uh for the end users as basically we were working on platform modernization moving snowflake to data bricks. So these were the areas and uh now we are done. So basically we talked about uh uh migration we talked about uh challenges solutions and now finally what the results look like. So let's look at the results. So results was uh around the performance gains. So for this migration target was to look at a new level of performance gains across the entire data stack. The impact was immediate and measurable across three critical areas with the data bricks photon and delta lake query engine. The performance skyrocketed delivering the query results basically twox to 5x faster than before and photons C++ vector engine paired with a smart caching and adaptive query execution enabling the lightning fast analytics even own the most complex and wide data sets team that used to wait for minutes now basically they were able to get their results in seconds and by tapping into the GPU acceleration as well as the ML ML pro integration Again I would like to highlight this there is no MLflow integration actually in the snowflake right. So with uh these two now it reduced the machine learning and ETL runtimes by up to six times. Data scientist now they can uh iterate the model faster track experiments effortlessly and automate the deployment pipelines all within the datab bricks ecosystem truly delivering on the promise of unified lakehouse platform. It improved the productivity, shortened the time to value for every AI initiative and delta live tables and structured streaming. So it achieved uh very low latency for the real-time data pipelines and few use cases they used to be batch before and delayed. Now basically during this process we were able to turn them into realtime and responsive. So which helped actually for powering alerts, personalized user experience and this whole data bricks migration. It turned actually performance from a bottleneck into a competitive advantage. Faster queries, faster models and real time through lots of use cases. Of course it will be incomplete actually to uh talk uh not to talk about the cost reduction. So that was basically second results during the during the migration. The goal wasn't just the platform agreation. It was about the unlocking real measurable impact. The results were transformative across cost operation and AI efficiency in just 4 months. So normalized to uh 12 months. The numbers basically they are here is normalized based on the four months. Uh the compute cost slashed by 30% and storage expenses reduced by 25%. This came from leveraging data bricks autoscaling cluster and data lakes efficient storage formats and cost-saving techniques like cluster right sizing and spot instances uh intelligent job orchestration used compute only when it is needed eliminated lot of waste and optimizing every dollar spent on the data platform. So data bricks unified platform eliminate basically data silos. Now you don't basically need to move the data around between the various teams and that's also basically led to lot of cut down on the manual integration and with the automated pipeline and job scheduling it really helped actually to cut down the cost and reduce the operational overhead and freeing up the engineering bandwidth allowing the teams to basically save focus from maintenance to the innovation. So with the built-in GPU support and MLflow experiments, there was a 30 to 40% reduction in the AI and ML compute cost. Data science basically they were able to iterate faster, more efficiently, scale models without overprovisioning resources leading to better and faster outcomes. So the migration reduced cost, simplified operation and made the AI more affordable and scalable, freeing up the resources to reinvest in the growth and innovation. So another thing on the results is enhanced analytics. Besides cost migration was more than a platform change, more than cost-saving. It was a leap forward in how to deliver value through data. It unlocked new capabilities across three strategic dimension. End to end analytics, generative AI and real-time intelligence. Data bricks unified lakehouse. It platform transformed the collaboration how the various teams worked and this reduced the handoffs between the various teams. improved the data quality, accelerated the delivery, all while enforcing a consistent governing standards and access control using the Unity Gatloud with a built-in support for the large language models and vector search data bricks unified platform empowered to create AI powered experiences previously out of reach because the time I'm talking about like the recent the AI services they were not available actually in Snowflake. So that was again a win. So making the AI analytics much faster, intuitive and inclusive for the business users and structured and unstructured data are now unified for richer insight. So real-time decision is a reality now with uh robust support for streamline pipeline. It can monitor key metrics, detect anomalies and personalize customer experiences very quickly. So the platform handles scales effortlessly. uh that's another key ensuring the low latency and analytics across large number of events. So wanted to quickly wrap up actually with a lesson learned. So for the migration three powerful lesson emerged proof of concept automation and stakeholder alignment. So all of the a lot of things basically I talked about. So my advice my guidance to all of this start with basically a very focused proof of concept. This allows actually to validate the strategy on a real workload, assess performance and uncover the potential issues early in the process and also gives the confidence actually that the migration strategy is technically sound and also scalable to the production. And the next thing is basically quickly realize the role of the automation and migrating large complex schemas s like it through a manual process. I think it's not sustainable. So in order to do that automation is needed and the last thing is the stakeholder alignment. So when you basically uh plan for such a complex migration uh keep the business basically uh updated informed all the time about why when and how of the migration so that they are not surprised and together with these pillars I think testing early automating smartly and alignment strategically they form the foundation of a success. they helped actually derisk the migration accelerating the timeline and uniting the organization. So that's the light slide. So this is about the conclusion. So this migration unlocked the unified analytics through the lakehouse architecture breaking down silos and streaming the uh streamlining the collaboration between various teams by moving to data bricks. It achieved a cost-effective high performance environment that scales effortlessly and empowering faster insights, better decision for a future ready foundation to advance analytics and AI innovation. And that's it.