BI,Sucess Story
MIGRATION TO A CLOUD-BASED DATA FABRIC PEOPLE, TECHNOLOGY & METHODOLOGY SUCESS STORY
INTRODUCTION In today’s rapidly evolving business landscape, organizations are increasingly turning to cloud-based solutions to improve their data management capabilities and gain competitive advantages. Recognizing this need, Sparebank 1 - Sør-Norge embarked on a transformative journey in 2023 by partnering with Scalefree International GmbH to migrate their on-premise BI solution to the cloud by additionally following new methodologies. This strategic move aimed to enhance data agility, optimize performance, and align with industry best practices. INITIAL SITUATION AND CHALLENGES Prior to the migration, Sparebank 1 - Sør-Norge faced several challenges with the on- premise infrastructure. The lack of automation, code generation, a scalable data model, and general scalability of the infrastructure resulted in fragmented data management practices. Furthermore, the migration project presented its own set of challenges, primarily related to identifying and validating new technologies and methodologies. After researching and a proof of concept, the team decided in Q1 2023 to migrate into the Snowflake Cloud by using dbt Core as a data transformation tool and Data Vault 2 as methodology. The team then needed to acquire knowledge in Data Vault 2, Snowflake, dbt, Dev- Ops, Data Mesh, and agile methodologies while also increasing their general understanding of data management in the cloud.
OBJECTIVES AND HOW THEY GOT REACHED
The primary objectives of the migration project were to:
— Setup standards at the beginning
— Define a Migration Strategy for Information Marts from the on-premise solution to the Snowflake Cloud
— Usage of a Medallion Architecture within a Data Fabric
— Refactor inefficiencies and improve code quality
— Development, Testing, and Deployment Strategy
— Adopt a Data Mesh-like approach to facilitate organizational scalability
— Governed data sharing
— Cost monitoring
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
2/12
SETUP STANDARDS AT THE BEGINNING (Q2 2023) Naming conventions for several technical entities, like databases, schemas, tables/views, and columns, were defined. Additionally, business terms were standardized as much as possible at the beginning and mapped to a common understanding. Integrating data across different systems into an enterprise data platform by using business terminology was a fundamental base for scalability in the future. By working focused in an agile manner, the first Information Marts were organized and prioritized in a kanban board upfront by the product owner, and the way of (agile) working was discussed. The full Scrum ceremonies were not used, as not all fit into the team(s) way of working, but the agile mindset of short iterations for delivery was adapted as much as possible. Thus, a proper deployment strategy was in the plans as well from the very beginning. Additionally, how to deal with identified challenges from the previous proof of concept and the definition of done before a deployment was discussed. A kick-off meeting with the expectations from each team member was also very useful, to get to know each other and to increase the level of mutual respect. A lot of first technical steps, like setting up the data platform (Snowflake) or the dbt Core setup, were already done in the proof of concept, which continued to be used.
“ The Data Vault methodology helped a lot with its scalable modeling approach and standardized loading patterns.”
DEFINE A MIGRATION STRATEGY FOR INFORMATION MARTS FROM THE ON- PREMISE SOLUTION TO THE SNOWFLAKE CLOUD To ensure a smooth and agile data migration from SQL Server to Snowflake, a prioritization of the data products (Information Marts) was set by the Product Owner to migrate those step by step. The core model could be extended very easily in multiple iterations by just adding new entities instead of re-engineering them every time. The migration strategy followed a tracer bullet approach.
By identifying the target first (the information marts), and then going back through the lineage of data until the source, enabled an accelerated migration process was enabled by focusing on specific data sources for each iteration. Furthermore, a lot of code could be generated by using Scalefree’s dbt package DataVault4dbt. Data validation and quality assurance processes were implemented as well to guarantee the accuracy and integrity of the migrated data. Standard tests were defined to ensure this.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
3/12
Additionally, metadata tests were implemented as well, so that no standard tests were missing during the migration. Testing of the end result was the final step for an iteration. A copy of the Information Marts from the on-premise solution to the cloud solution, and a standardized comparison testing approach against the migrated Information Marts, helped a lot to ensure its quality and to measure the differences due to code improvements and adjustments during the migration process. EVOLVING THE DATA ACQUISITION LANDSCAPE At the beginning, only Snowflake for data storage, the software Prefect for orchestration, and a custom-developed component were available to copy data from the source to Snowflake. It worked reasonably well, but was cumbersome to maintain, wherefore the data platform team decided to test the Python library dltHub. dltHub supports a wide range of data sources and destinations and could be used for delivering data out of the data platform at a later point in time. Additionally, the team decided to build a framework with a GUI on top of dltHub, that could be used with different clients to set up new integrations without the need for deep dltHub or programming knowledge. Support has now been built for various source types, incremental loading, different transformations after data ingestion, triggering of Prefect flow runs, and launching dbt Core jobs. Once dltHub completes the data transfer from source to destination, an additional transformation layer takes over. This is not a replacement for dbt Core, but rather an addition that processes staging data before it reaches the final repositories. The layer consists of a Jinja- based template system that transforms the staged data from dltHub into structured sources that dbt Core can further process. The main templates convert the data into CDC (Change Data Capture) structures, which are stored in a Persistent Staging Area (PSA). In addition, some templates store data in snapshot format and other structures for different use cases.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
4/12
USAGE OF A MEDALLION ARCHITECTURE WITHIN A DATA FABRIC
A Data Fabric is an architectural approach that utilizes metadata-driven automation and intelligent capabilities to create a unified and consistent data management layer across diverse and distributed data environments, enabling seamless data access and delivery.
The Medallion Architecture organizes the data in a data platform into three layers: Bronze, Silver, and Gold, each representing increasing levels of data refinement and quality. This approach allows for incremental improvements in data quality as it flows through the layers. The Bronze layer represents the raw, unfiltered data ingested from various sources. The Silver layer contains cleansed and transformed data from the Bronze layer. The Gold layer represents the final, curated data ready for business analysis and reporting.
Almost everything within the Data Fabric’s Silver layer got modeled with the Data Vault 2 modeling approach. Yes, just “almost”. Not every Team went through the core Data Vault, but their solutions are still part of the whole Data Fabric using its governed standards. Speaking in the Medallion Architecture: Only the silver layer was different in a few cases. Those teams used the same Bronze layer as a foundation and the same Gold layer definition as the presentation layer by following the same governed and automated processes as any other team.
The main reason why some teams didn’t use the Data Vault modeling approach was that the data objects they handled were either not directly related to what was defined as the “core”, or Data Vault knowledge was not enough available in each team. This decision allowed these teams to move forward, producing data products without becoming a bottleneck, but still following the overall data governance rules.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
5/12
LOADING PROCEDURES FOR THE DATA LAKEHOUSE
BRONZE With dltHub, data is retrieved directly from the source systems at a frequency needed. The data gets written directly into Snowflake’s internal tables, the Bronze layer, without any modifications. The tool stack is Snowflake for data storage, Prefect for orchestration, and a custom framework built on dltHub for data ingestion. The framework uses a Django web application with a built-in admin module to manage the process via a graphical user interface (GUI) and a command-line interface (CLI). This integrated system allows for efficient data loading and transformation, providing a user-friendly way to manage the data pipeline. SILVER The dbt package DataVault4dbt (created by Scalefree and available open source) provided all Data Vault standard loading patterns to generate the code for almost the whole Silver layer. Complex business logics are written manually by developers, as this requires individual thinking. But also here, templates (in dbt they are called “macros”) based on best practices, as well as generative AI, helped to generate this code as much as possible. Additionally, database queries and micro partitioning got optimized in Snowflake for faster data transformations. Query optimizations needed to be utilized as the database technology is different in Snowflake compared to SQL Server. While SQL Server is a row-based database technology, designed for efficient writing and reading datasets, Snowflake comes with a different data storage method, designed for analytical purposes. An insert-only strategy helped to leverage the strengths of Snowflake infrastructure. Furthermore, the separation of storage and computing in Snowflake allows for increased loading and querying performance, as there are resource conflicts between the different layers (Bronze, Silver, and Gold) as well as developers will never slow down the production environment, as they are using completely isolated computing resources. GOLD The Gold layer got completely virtualized. All data transformations were already handled in the Silver layer, so that only a last step is missing: representing the results in a user-friendly way and applying role-based access control. As Data Vault is a very good approach to model data at scale, it is not the best modeling technique to represent the data to non-technical end users. As almost no computationally intensive transformations are necessary, only a structural change, the Gold layer uses virtualized entities to represent the data to end users. The benefits are to save storage as well as eliminate data movement processes in the Gold layer. An additional business value is achieved by adding data descriptions to the Gold layer by using the data catalog functionalities from dbt Cloud. Information like table descriptions, column descriptions, data owners, granularity of the data, data test definitions, and orchestration information are part of the data catalog, which is the menu for the end users when selecting their data.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
6/12
REFACTOR INEFFICIENCIES AND IMPROVE CODE QUALITY DURING THE MIGRATION To enhance performance and maintainability, the Data Vault methodology provides highly standardized methods from industrial best practices of building a Data Fabric. Especially the integration of the raw data by business keys from the very beginning was very useful for downstream processing, as the data got well “integrated” by business terminology. Furthermore, inefficiencies were identified and eliminated by reviewing the codebase for redundant code, removing existing technical debts, and automating as much as possible. Code refactoring techniques were implemented by breaking down large functions, simplifying expressions, implementing consistent naming conventions, and coding styles. Additionally, coding standards and best practices were established by code review processes and version control systems. By implementing these measures, the codebase became more efficient, better understandable, maintainable, and extensible, making it easier for developers to work with the code, identify and fix bugs, and add new features.
DEVELOPMENT, TESTING, AND DEPLOYMENT STRATEGY
Common development strategies consist of at least 3 permanent environments: Development, Testing (or Quality Assurance), and a Production environment. Those cause additional costs due to maintenance and redundant storage, especially in the cloud, where you pay per usage. The Data Fabric explained so far consists of Snowflake and dbt Core, which allows the usage of just one permanent environment, the Production environment. The Development as well as the Testing environment are dynamic environments, starting up when needed, shutting down when no longer needed. With Snowflake’s “zero-copy cloning” as well as dbt’s “defer” functionality, there is no need to have all 3 environments permanently online, but just the Production. A new feature or fix is developed on a freshly created database. By using the defer functionality from dbt Core, there is no need to load all predecessor tables. You can directly access the data from the Production environment and focus on the entities where the changes need to be applied. A zero-copy clone on the table itself can be helpful in case the table does not need to be fully reloaded, but continues incremental loading.
Remember
Due to the separation of storage and computing, this approach will not interrupt anything that runs on the Production environment.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
7/12
A Testing environment will be started as soon as a new feature or fix is ready for deployment. Actually, the same procedure as in the development will happen here again, but in this case, completely automated and in a freshly created environment. This ensures that the changes will also work in the Production environment. As soon as deployed, the development and testing environment will be shut down again.
Adopting these agile development methods made it possible to create a collaborative and iterative environment for software development. This approach encourages continuous improvement through regular feedback, accelerates feature delivery via smaller tasks, enhances adaptability to evolving requirements, and boosts team productivity and morale through transparent, empowering processes. IMPROVEMENTS OVER TIME The starting point was a low-budget approach that mostly relied on open-source tools, self- written code, and custom processes. However, recognizing the limitations in scalability, a strategic transition to more robust, often paid, alternatives was made. This led to a migration from dbt Core to dbt Cloud, significantly easing the workload for business analysts and data engineers, replacing the self-developed DevOps pipelines, and leveraging numerous features that reduced development and deployment cycle times.
Furthermore, a shift occurred to dltHub for loading data into the Persistent Staging Area (PSA), incorporating Change Data Capture (CDC) mechanisms.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
8/12
“ A Data Mesh approach was adopted to facilitate organizational scalability and remove bottlenecks.”
ADOPT A DATA MESH-LIKE APPROACH TO FACILITATE ORGANIZATIONAL SCALABILITY Decentralizing data ownership and management empowered individual business units to manage their own data products by collecting, storing, and managing their data. This made the business units more agile and responsive to customers. Implementing a data mesh architecture enabled seamless data sharing and integration across different business units
Additional benefits of decentralizing data ownership and management, implementing a data mesh architecture, and utilizing data virtualization techniques included improved data governance, reduced data silos, increased data agility, improved data security, and reduced costs. By implementing these strategies, organizations improved their data management practices and gained a competitive advantage. GOVERNED DATA SHARING Data sharing was enabled by implementing access control mechanisms, such as role-based access control and Row-Level-Security, to regulate data access based on user permissions and attributes. Guidelines for data anonymization were established to protect sensitive information. With dbt Cloud’s data lineage functionality, it is possible to provide lineage tracking visualizations to trace the origin and transformation of data throughout its lifecycle.
The data governance policies were regularly reviewed and updated to adapt to changing regulatory requirements and business needs.
COST MONITORING The more people get access to the Data Fabric and use the cloud resources, the more expensive it gets. It is crucial to keep an eye on the costs to figure out the high, unnecessary cost reasons quickly. Snowflake itself provides good cost monitoring on a high level, but a deeper analysis was implemented in addition to point to expensive single loading procedures, especially when the same code gets executed regularly, i.e., the daily production load, which consists of thousands of table loadings and tests.
The overall strategy is to load as much as possible with an incremental “insert-only” approach, instead of full-table loads. This allows stable reporting and very efficient loading procedures.
Additionally, virtualization is used as much as possible, where no complex calculations take place. This affects especially the Gold layer.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
9/12
SUMMARY AND CONCLUSIONS Sparebank 1 - Sør-Norge and Scalefree International GmbH worked collaboratively to develop a phased approach that included a proof of concept. The adoption of Data Vault 2’s flexible and scalable data modeling approach played a crucial role in creating a centralized data repository in Snowflake while ensuring data integrity and consistency. Regular communication and agile methodologies fostered a seamless collaboration between the teams, enabling them to address challenges effectively and deliver desired outcomes.
“Scalefree provided expert guidance and tailored solutions for our data cloud needs, demonstrating their reliability and commitment to quality. Their deep understanding of our business requirements and their ability to consistently deliver high-quality results have earned our complete trust.”
Karsten Kristoffersen Head of Data Warehouse & BI at SpareBank 1 Sør-Norge ASA
BENEFITS AND RESULTS The migration to a cloud-based Data Fabric yielded significant benefits for Sparebank 1 - Sør- Norge. Data management was notably improved through the implementation of the Data Vault methodology, leading to more accurate and reliable insights. Moreover, performance was enhanced with faster and scalable data processing and improved query performance. The increased flexibility provided greater scalability and adaptability to changing business needs. By modernizing technology and methodologies to align with industry best practices, Sparebank 1 - Sør-Norge gained a competitive edge and positioned themselves for continued growth and success in the digital age.
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
10/12
CONCLUSION The successful migration to a cloud-based BI solution marked a significant milestone for Sparebank 1 - Sør-Norge. The partnership with Scalefree proved invaluable, as their expertise and guidance were instrumental in achieving the desired outcomes. By embracing innovative technologies and methodologies, Sparebank 1 - Sør- Norge gained a strategic advantage, enabling them to leverage further data-driven insights for improved decision-making and enhanced customer experiences. The migration to a cloud-based BI solution yielded significant benefits for Sparebank 1 - Sør-Norge, including improved data quality through Data Vault 2’s modeling approach, enhanced performance with faster data processing, improved query performance, and increased flexibility for greater scalability and adaptability to changing business needs. Modernizing technology and methodologies aligned with industry best practices, giving Sparebank 1 - Sør-Norge a competitive edge and positioning it for continued growth and success in the digital age. The successful migration, facilitated by the valuable partnership with Scalefree International GmbH, marked a significant milestone for Sparebank 1 - Sør-Norge, enabling them to gain a strategic advantage, leverage data-driven insights for improved decision-making, and enhance customer experiences.
YOUR PROJECT SUCCESS IS OUR MISSION Schedule a free consultation now and discover how our Project Consulting can empower you to solve your data challenges on your own.
SCHEDULE YOUR FREE INITIAL CONSULTATION
https://scalefr.ee/project-consulting
SUCCESS STORY: MIGRATION TO A CLOUD-BASED DATA FABRIC - PEOPLE, TECHNOLOGY & METHODOLOGY
11/12
Scalefree International GmbH Schützenallee 3 30519 Hannover Germany
P: +49 (511) 879 893 41 F: +49 (511) 879 893 49
contact@scalefree.com www.scalefree.com
CEOs: Michael Olschimke & Christof Wenzeritt
Page 1 Page 2 Page 3 Page 4 Page 5 Page 6 Page 7 Page 8 Page 9 Page 10 Page 11 Page 12Powered by FlippingBook