Variability in data transformation: towards data migration product lines

Variability in data transformation: towards data migration product lines David Romero-Organvidez Dept. of Computer Languages and Systems University of Seville Seville, Spain [email protected] David Benavides Dept. of Computer Languages and Systems University of Seville Seville, Spain [email protected] Jose-Miguel Horcas CAOSD, ITIS Software University of Málaga Málaga, Spain [email protected] María Teresa Gómez-López Dept. of Computer Languages and Systems University of Seville Seville, Spain [email protected] ABSTRACT Software evolution often requires data management and more concretely data migration. Data migration follows an ETL process: extracting (E) data from a source, transforming (T) the data depending on migration needs, and loading (L) the data in a target data storage. Data migration projects are recognised to be complex and challenging to manage, which can lead to resource loss and planning delays. Among the reasons for data migration project failure is the lack of systematic artifact reuse (e.g., transformation script) in the data migration process. Every new data migration project is often developed from scratch. Software product line (SPL) engineering has been applied in many different domains to systematically reuse artifacts (e.g., code platforms, test cases) in software development processes and there are many positive experiences when applying SPL to reduce cost and time. In this paper, we present an approach using SPL techniques for data migration projects, concretely, in the data transformation stage. Our solution facilitates the automated creation of scripts that can be reused in different data migration projects. The feasibility of the proposal is validated in the domain of web information systems modernization. The validation shows how various migration scripts can be created to transform data between different content management systems. With this work, new opportunities are opened for studying the synergies of SPL and data migration. To the best of our knowledge, this is the first proposal that uses a complete stack of SPL that materializes the reuse of artifacts for data migration. Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]. https://doi.org/10.1145/3634713.3634724 CCS CONCEPTS •Software and its engineering → Software product lines; Requirements analysis;Software design engineering;Software implementation planning. KEYWORDS data migration, data transformation, SPL, feature model ACM Reference Format: David Romero-Organvidez, David Benavides, Jose-Miguel Horcas, and María Teresa Gómez-López. 2024. Variability in data transformation: towards data migration product lines. In 18th International Working Conference on Variability Modelling of Software-Intensive Systems (VaMoS 2024), February 07–09, 2024, Bern, Switzerland. ACM, New York, NY, USA, 10 pages. https: //doi.org/10.1145/3634713.3634724 1 INTRODUCTION Modernizing the software of legacy systems is a crucial problem that exists in software and systems evolution [ 4 ]. One of the fundamental parts of this modernization is data migration, considered a critical stage due to the several potential failures that can be produced in migration projects [ 2 , 13 ]. Studies such as [ 19 ] reveal an investment of billions of dollars in migration projects, where 64% of the projects exceed budget forecasts and 54% exceed time forecasts. Moreover, 84% of the migration projects are either prolonged or directly aborted over time [ 12 ]. The increasing sophistication of different migration techniques, such as data profiling [ 26 ], data cleansing [ 29 ], and data integration [ 27 ], increases the success rate of migration projects up to 62%. The migration project encompasses various levels of complexity but typically follows a three-stage process known as extraction, transformation, and loading (ETL) [ 28 ]. One particular challenge arises during the data transformation phase when migrating data between different systems. Regardless of the storage paradigm employed (e.g., structured through SQL, semi-structured via No-SQL), all the paradigms support specific domain modeling, incorporating entities, their relationships, and their attributes. To execute a successful migration, several key factors must be considered: (1) understanding the structure of both the source and target information systems, (2) establishing a mapping that defines the correspondence 83 VaMoS 2024, February 07–09, 2024, Bern, Switzerland David Romero-Organvidez, David Benavides, Jose-Miguel Horcas, and María Teresa Gómez-López between entities, and (3) identifying the precise steps necessary to migrate all information while minimizing potential data loss [ 30 ]. The usual approach to achieve this migration is through the design and implementation of scripts [ 23 ]. For instance, in relational databases, SQL scripts outline the necessary steps for querying the source and transforming the data to the target data model [33]. A script comprises a set of transformations and action queries detailed by the domain expert. These transformations are linked together — i.e., some transformations depend on the execution of previous transformations to be successful. Organizations develop scripts that encode sequences of fine-grained data transformation to automate the data migration process. However, there exists a certain variability if the same data source must be transformed into a new data target, requiring the development of new scripts. Additionally, if another data source must be transformed into a data target, the previous scripts cannot be directly applied, and an adaptation is required. Depending on the context of the problem, certain features (e.g., transformations) are common to every script and thus can be reused within different migration projects. Meanwhile, other features must be customized to the specific stakeholder’s requirements to face a migration process. For instance, let us consider a source and a target database involved in a data transformation process. The two databases have different data models. Suppose that the source database includes users and news created by those users. In addition, users participate in forums including messages and reactions. A possible target database may include only users and news but is not interested in transforming the forum information. Additionally, we may have a second, different target database model in which only the data associated with the forums and the messages from the last year will be migrated. In short, there are different sources, targets for data, and different elements of the information to be migrated, that result in defining new migration projects. These migration projects generate a variability management problem with several possible migration projects sharing many characteristics. In this paper, we bring the advantages of applying software product line (SPL) techniques [ 1 , 6 ] to data transformation in migration projects to manage the variability in an efficient way. SPLs allow the generation of the scripts by composing the required transformation features in a data transformation process. We propose the use of a transformation feature model as the basic structure for organizing these features where the domain engineer identifies the common and variable features by analyzing the essential transformations and the constraints between them. Then, an application engineer uses the transformation feature model to select a valid combination of transformation features and generates a valid migration script. The transformation feature model abstracts the problem of creating the script by hand and turns it into a product made by feature selection. With this model, the stakeholders do not have to know the details of the transformation, the storage paradigm, or the incompatible actions with each other. The contributions of this paper are as follows: • We define the main elements for conceptualizing product lines in data migration projects. These elements constitute the essential parts of the data transformation process (Section 3). • We propose a transformation SPL in the case of relational databases where the domain experts identify the main features to be migrated and, through a valid selection, generate several scripts adapted to the needs of the migration projects (Section 4). • We provide a proof of concept on a common scenario in the migration of content between two web-based content management systems (CMS). We have developed a transformation product line supported by our implemented tool (Section 5). The scope of our proposal is focused on the reuse of transformations within the same migration project between relational databases. These transformations are defined with a transformation feature model that can be extended and adapted to new customer requirements. Although there is some related work in the literature (Section 6), to the best of our knowledge, this is the first proposal bringing together SPLs and data migration. This opens the door for further investigations such as automating the construction of the transformation feature model or empirically measuring the benefits of the approach further (Section 7). 2 DATA MIGRATION BACKGROUND According to J. Morris [ 22 ], "data migration is the selection, preparation, extraction, transformation and permanent movement of appropriate data that are of the right quality, to the right place, at the right time, and the decommissioning of legacy data stores, to deliver the business transformation aspirations of the organization". Data migration can address different types of data, including, but not limited to, structured data, such as relational databases, and unstructured data, such as text, images, and multimedia files. Successful execution of a data migration requires careful planning, requirements analysis, migration solution design, rigorous testing, and a quality control process to ensure the integrity, accuracy, and consistency of the transferred data. 2.1 Data migration phases Data migration encompasses three phases: extraction,transformation, and loading (ETL). These phases enable organizations to transfer data from multiple sources, reorganize and cleanse them, and then load them into a database, data mart, or data warehouse [ 16 ]. Extraction. This process obtains data from multiple sources, e.g., XML documents, CSV documents, Excel sheets, relational and nonrelational databases, and unstructured information (websites). Data extraction can be (1) incremental extraction (i.e., data that has been added or updated since the last time data extraction was performed), or (2) full extraction (i.e., extraction of all data from a data source in a single operation). Both types are intended to extract data for further analysis or use in operational environments. Transformation. The extracted data are processed and modified to correct errors, eliminating duplicates and inconsistencies, and adapting it to the required format and structure. Transformation actions include filtering information, removing empty values, converting data types, and consolidating data. Business rules are also used for adapting the operations and constraints in organizations. Loading. The load phase involves entering the transformed data into a final destination, which could be a data warehouse, a working 84 Variability in data transformation: towards data migration product lines VaMoS 2024, February 07–09, 2024, Bern, Switzerland database, or other types of systems to store information. The load process can be performed incrementally or completely. The purpose is to provide clean, consistent data that are easily accessible for analysis or application in an operational environment. 2.2 Data migration strategies In the data transformation process between a source and a target data repository, a migration transformation is defined as the composition of a finite sequence of transformation steps. Specifically, in the transformation process, there are three types of strategies, Big Bang,Chicken Little and Butterfly: Big Bang. The way to migrate one software system to another in one step is known as the Big Bang (or Cold Turkey) [ 7 ] strategy. Big Bang migration is intended to replace an old software artifact (e.g., a database or component) among others, with a new one that replaces it, leaving the old one in disuse. This methodology runs the risk of needing a high process preparation [8]. Chicken Little. The Chicken Little strategy [ 5 ], also known as the incremental or phased migration strategy, is a way of migrating the software step-by-step, i.e., each step produces a significant but controlled change. This strategy aims to minimize disruption and risks by migrating data in manageable portions, and thus, if it fails, it does not corrupt the rest of the system. Butterfly. The Butterfly strategy involves freezing the legacy information sources and employing a crystallizer to progressively modify the data from the old system to the new one [ 34 ]. First, the data from the read-only legacy information source is modified and then the conversion of subsequent temporary information repositories is continued. Unlike the Chicken Little strategy, the butterfly strategy separates the information sources involved at each stage of the information transfer procedures. 3 SPL FOR DATA TRANSFORMATION Our approach is based on the idea of creating a transformation product line to handle the difficulties of reusing scripts when dealing with different but similar data migrations. In this paper, we adopted the Chicken Little strategy for the following reasons: • Incremental approach: Instead of migrating all the data at once, the migration is divided into smaller units or subsets. • Migration phases: Each migration phase focuses on a specific subset of data. It involves extracting, transforming, and loading that subset from the source data to the target data source. • Iterative process: The migration process is repeated for each subset until all the data is migrated. • Validation and testing: After each migration phase, thorough validation and testing are performed to ensure the accuracy and integrity of the migrated data. • Gradual transition: As each subset is successfully migrated, it is gradually integrated into the target system while maintaining the coexistence of the old and new data. Thus, we adapt this strategy by defining a set of actions A that represents the atomic operations (e.g., create entity, delete attribute) for smaller units or subsets of data. A combination of these actions Figure 1: Transformation product line overview. in a specific order forms a transformation T . We model the variability of the transformations in a feature model, called a transformation feature model ( F M𝑇 𝐹 ). We assume the following hypothesis: the source database ( DB𝑠𝑜𝑢𝑟𝑐𝑒 ) has a defined structure and data, and the target database ( DB𝑡𝑎𝑟𝑔𝑒𝑡 ) only has the structure. The actions A are necessary for our Chicken Little strategy to successfully transform the data. The selection of transformations in the F M𝑇 𝐹 generates the different final products of our proposal. The final product is a composition of transformations 𝑡∈ T in a specific order that results in the migration script between the two databases. Figure 1 shows an overview of our proposal following the wellknown SPL process [ 20 ]. As typically in an SPL, there are two main activities, Domain engineering (DE) and Application engineering (AE). A feature model is one of the main outputs of DE. In our case, atransformation feature model ( F M𝑇 𝐹 ). The similarity between a feature model and an F M𝑇 𝐹 lies in the semantics of the features, as defined below. For the construction of the F M𝑇 𝐹 , the domain expert identifies the transformation features and the relationships among them. This F M𝑇 𝐹 depends on the specific domain, i.e., the specific data and target/sources structures. Additionally, the expert determines which set of actions A corresponds to each transformation in the DE process. We formally define the main concepts of our proposal. First, we introduce the concepts of action and transformation as the main elements of the transformation process, and then introduce the concepts used in our transformation product line. 3.1 Actions and transformations Definition 1 (Action).An action 𝑎∈ A is a basic operation that is executed to transfer data from a source database DB𝑠𝑜𝑢𝑟𝑐𝑒 to a target database DB𝑡𝑎𝑟𝑔𝑒𝑡 . The set of possible actions A can vary depending on the context, the type of database, and the structure of the databases, among other considerations. For instance, in the particular case of relational databases (see proof of concepts in Section 5), we define twelve actions that can be applied over entities ( A𝑒 ), attributes ( A𝑎 ), and relationships (A𝑟) of the database [25]: • Actions for entities ( A𝑒 ): create entity , rename entity and delete entity. •Actions for attributes (A𝑎): create attribute, move attribute (from one entity to another), rename attribute,change attribute type, 85 VaMoS 2024, February 07–09, 2024, Bern, Switzerland David Romero-Organvidez, David Benavides, Jose-Miguel Horcas, and María Teresa Gómez-López Figure 2: Example of a transformation 𝑡with three actions. delete attribute , insert references , update field and update value. •Actions for relationships (A𝑟): create relationship. Note that A=A𝑒ÐA𝑎ÐA𝑟 , but the set of actions can be extended with additional actions depending on the specific database. Definition 2 (Transformation).Atransformation 𝑡∈ T is a list of actions to be executed in order, i.e., 𝑡=[𝑎1, 𝑎2, . . . , 𝑎𝑛], 𝑎𝑖∈ A. An example of transformation is illustrated in Figure 2. In this example, we know DB𝑠𝑜𝑢𝑟𝑐𝑒 and DB𝑡𝑎𝑟𝑔𝑒𝑡 structures. Our objective is to make a series of changes in DB𝑠𝑜𝑢𝑟𝑐𝑒 to resemble DB𝑡𝑎𝑟𝑔𝑒𝑡 . We define a transformation with three actions 𝑡=[𝑎1, 𝑎2, 𝑎3] to migrate an entity from a DB𝑠𝑜𝑢𝑟𝑐𝑒 to a DB𝑡𝑎𝑟𝑔𝑒𝑡 . We first apply the action 𝑎1 ( rename entity ) to change the name of the entity of the DB𝑡𝑎𝑟𝑔𝑒𝑡 from “Author” to “User” . Then, Action 𝑎2 ( change attribute type ) is applied over the type of the “name” attribute because in the target entity such attribute has a different length size. Finally, Action 𝑎3 ( delete attribute ) removes the “surname” attribute since it no longer exists in DB𝑡𝑎𝑟𝑔𝑒𝑡 . The actions are applied in the order described and the transformation is self-contained, i.e., those actions are part of a single transformation and have no dependencies on actions of other transformations. The execution of the transformation makes sense within the same context (transforming the entity of our domain). Although a transformation is a list of actions, this does not mean that an action necessarily belongs to a single transformation. Actions such as change attribute type may exist to change an attribute Decimal to Integer in two different transformations, 𝑡1 and 𝑡2 . This reuse level is beyond this paper’s scope but will be explored in future work. 3.2 Transformation feature model Definition 3 (Transformation feature).Atransformation feature T F is a list of actions to be executed in order, i.e., T F =[𝑎1, 𝑎2, . . . , 𝑎𝑛], 𝑎𝑖∈ A. The list of ordered actions in a transformation feature represents a feature, encapsulating a visible characteristic of the transformation process to satisfy a stakeholder’s requirements [1]. An example of a transformation feature is the Migrate author entity feature that encapsulates the transformation already presented in Figure 2 with the three actions rename entity , change attribute type , and delete attribute . The transformation feature Migrate author makes sense as a characteristic that satisfies the stakeholder’s needs by migrating the data through a set of actions that are semantically related to each other. By specifying the transformation features in a feature model, we can model the variability of the data migrations between two databases, enabling the reusing of the transformations. We provide the following definition of F M𝑇 𝐹 : Definition 4 (Transformation feature model).Atransformation feature model F M𝑇 𝐹 is a feature model [ 1 ], where the features are transformation features and the relationships and constraints represent dependencies between these transformation features. Figure 3 shows an example of an F M𝑇 𝐹 that we will use later in our proof of concepts (see Section 5). The basic data transformation feature is mandatory in this scenario, there is no valid product without this transformation. The transformation features users and content are optional, containing in turn other transformations, where the relationships are imposed by the domain expert who wants to perform the transformation between two databases. In this F M𝑇 𝐹 , only the concrete transformation features, i.e., the leaves features, are the ones that have a list of actions. Abstract transformation features, i.e., those that are not leaves, are conceptual transformations that encompass domain-specific characteristics, for example, the transformation feature posts. Definition 5 (Feature ordering).Afeature ordering F M𝑂 is an ordered list of transformation features associated with a transformation feature model F M𝑇 𝐹 , i.e., F M𝑂=[T F 1,T F 2, . . . , T F 𝑁], T F𝑖∈ T F . The feature order is detailed by the domain expert who also designs the transformation feature model. This ordering responds to the problem’s context and is essential to building the final artifact. The semantics of F M𝑇 𝐹 , similarly to the semantics of a feature model [ 1 ], is its set of products. A product is a valid combination of transformation features, according to the relations and constraints of the F M𝑇 𝐹 , which by choosing in a specific order imposed in F M𝑂, forms a valid transformation script. Definition 6 (Transformation script).Atransformation script 𝑡𝑠 is an ordered list of transformation features, that is, a 𝑡𝑠 is a product of F M𝑇 𝐹 satisfying all relations and constraints of F M𝑇 𝐹 , and where the selection order imposed in F M𝑂of F M𝑇 𝐹 is valid. A difference between a product of a feature model and a transformation script 𝑡𝑠 of F M𝑇 𝐹 is that the transformation features in 𝑡𝑠 are in a specific order imposed in F M𝑂 to form the transformation script, in contrast to the products in a typical product-line where the ordering of the features is not always relevant. The execution of a transformation script allows for a complete data transformation within a data migration process. The application’s scope lies in the need to reuse migration artefacts in contexts where data transformation often occurs. For example, in companies where the source and target sources are known, it is desired to customize the granularity and the amount of information available in the target base. A real case would be companies that collect billing information from different customers but want to customize the final information that reaches them. 4 TRANSFORMATION PRODUCT LINE FOR RELATIONAL DATABASES A transformation product line emphasizes the data transformation process within the data migration. The management of variability, as in SPLs, is done through a feature model, in our case, in a transformation feature model. Figure 4 shows our approach to building 86 Variability in data transformation: towards data migration product lines VaMoS 2024, February 07–09, 2024, Bern, Switzerland Figure 3: Example of transformation feature model. Figure 4: Overview of our transformation SPL approach. a transformation product line with a DB𝑠𝑜𝑢𝑟𝑐𝑒 and DB𝑡𝑎𝑟𝑔𝑒𝑡 as input parameters for the construction of the transformation feature model F M𝑇 𝐹 . There are four phases in the data migration process using our approach: (1) transformation feature model design, (2) implementation of the transformation features, (3) selection of the transformation features, and (4) product building. 4.1 Transformation feature model design At this stage, domain expert knowledge is needed about both databases and understanding the transformation requirements between the two databases for the specific domain. For this stage, the expert relies on two databases, a DB𝑠𝑜𝑢𝑟𝑐𝑒 and a DB𝑡𝑎𝑟𝑔𝑒𝑡 . These databases are needed for feature identification based on data. This stage aims to achieve two objectives. The first objective is to analyze the structural differences between the two databases. We define these differences as any discrepancies in entities, attributes, or relationships between DB𝑠𝑜𝑢𝑟𝑐𝑒 and DB𝑡𝑎𝑟𝑔𝑒𝑡 . Some example are the non-existent entities in either database, or entities present in both, but with varying attributes or names to mention a few. The second objective of the domain expert is to abstract and pinpoint the transformation features to be modeled, called features. These transformation features will be represented in a F M𝑇 𝐹 (see Definition 4). At this stage, the domain expert identifies the transformation features to be modeled and designs the constraints among them according to domain knowledge. The F M𝑇 𝐹 is abstractly built by indicating: (1) the feature model, including the alternative features, the “or” features, the optional features, and the mandatory features, and; (2) the constraints between the defined features. When designing F M𝑇 𝐹 , the order in which the features should be executed when applying a selection must be detailed in F M𝑂 . This order is specified by the domain expert and is associated with F M𝑇 𝐹 and must meet the needs of a migration. As in any SPL, this is a crucial step of domain engineering that requires effort investment for later reuse. In this paper, we consider that this task is done semi-automatically by using automated capabilities provided by our implementation (see Section 5). However, the automated construction of the feature model from the source and target databases is out of the scope of this paper. 4.2 Transformation feature implementation A transformation feature comprises a sequence of actions that must be executed in a specific order to accomplish a particular transformation. As the domain expert identifies a feature, this stage elaborates on the actions that must occur sequentially for the transformation of that feature to be coherent. A problem arises in this phase, since the order of executed actions is critical. Actions that depend on an entity, such as creating it, cannot be detailed before the entity itself exists. For instance, when defining the transformation comments , it would be illogical to first define the action delete author at a certain point and then subsequently define the action change author type , since the attribute would have been deleted in the previous execution. The order of actions is imposed by the domain expert, taking into account the limitation of the actions themselves, i.e., if the action delete attribute is executed, there cannot be an action change attribute type that refers to the same attribute. In this step, different implementation mechanisms can be used ranking from frameworks, architectures, annotations, 87 VaMoS 2024, February 07–09, 2024, Bern, Switzerland David Romero-Organvidez, David Benavides, Jose-Miguel Horcas, and María Teresa Gómez-López feature-oriented development or templates to just mention a few [ 1 ]. In our case (see Section 5) we use a template-based approach to assess the feasibility of the solution. However, any other approach can be explored in a more sophisticated empirical validation. 4.3 Transformation feature selection At this phase, in the AE activity, the stakeholder initiates the transformation process by selecting a predefined transformation feature. It is important to note that the selection of transformation features is subject to the constraints specified by the domain expert, which are referred to as configuration restrictions in our feature model. Due to there are restrictions in F M𝑇 𝐹 , the selection must be made by studying the propagation of constraints [ 36 ]. The tool uses an external automatic feature model analysis reasoner [ 10 ] to validate the selection of transformation features in a specific order. As a result, any selection of features in our model will produce a valid configuration [3], i.e, a valid transformation script. The selection of transformation features generates a valid product, i.e., a list of features that are compatible given the constraints imposed in the transformation feature model design. The correct selection of transformation features is a valid product as is feature selection in a feature model. However, but it may not be a valid script. For a script to be valid, it must be fulfilled: (1) the selection of transformation features must be valid given the constraints imposed in F M𝑇 𝐹 ; and (2) the order of the selection must be according to what is described in F M𝑂. 4.4 Product building The objective of this stage is to obtain the final product in our approach, which is a transformation script. The transformation script is the result of a combination of one or more features that are chosen by the application expert in the previous stage. The selected features define the transformation script, and each transformation feature encompasses a set of supported system actions, such as creating or renaming entities and attributes, among others. The transformation script, which is our final product, is an SQL script that allows the data transformation between two databases based on a previous selection of transformation features. The transformation features are then mapped into an SQL script code, which enables data transformation by querying data from the source database and inserting it into the target database. This script only considers the mapped code of the transformation features selection. It does not consider other possible modifications, such as particular encoding to a different one, data filtering, or transformation actions not contemplated in the tool because they are specific to the problem domain. However, it is essential to note that the final product may not entirely fulfill the stakeholder’s needs. This can occur because the supported actions may be insufficient or F M𝑇 𝐹 may not address certain elements, such as encryption or the storage engine, in sufficient detail. These scenarios may require additional customization and adjustments to ensure that the migration artifact meets the customer’s requirements. Being a software artifact, it can be modified to meet the needs of the domain expert. This modification allows adding features not contemplated in the original modeling, that are left out of the migration artifact because they are considered a refinement of the final product. 5 PROOF OF CONCEPTS To validate our proposal, we have used a recurring real case study in web development, the migration from a Drupal data structure to suit the WordPress data structure. Additionally, we have implemented a tool 1 as proof of concepts. This tool receives two inputs in a descriptive language specific to the tool, which are two relational databases. It also uses a UVL file [ 31 ] that describes the transformation feature model. Our tool performs a systematic construction of the transformation feature model and allows the generation of customized scripts based on the selection of migrations according to the constraints imposed by the domain expert. 5.1 Transformation feature model design The basic information structure in Drupal, DB𝑠𝑜𝑢𝑟𝑐𝑒 , is a node. Nodes are used to create and organize content on the site, from articles and pages to products and events. WordPress, on the other hand, DB𝑡𝑎𝑟𝑔𝑒𝑡 , uses a traditional structure where each domain entity is represented by one or more tables depending on its cardinality and uses primary and foreign keys to relate them. Drupal includes Tables users and user_roles to store user information. Similarly, WordPress has Table wp_users , where it stores basic information, wp_usermeta where it stores additional fields, and wp_userroles . A common feature of both information systems is the user roles. However, how data is organized is different in DB𝑠𝑜𝑢𝑟𝑐𝑒 than in DB𝑡𝑎𝑟𝑔𝑒𝑡 . Similarly, in Drupal, posts are represented as nodes, so the information for each post is stored in Table node and the related Table node_revision for node revisions. In addition, Drupal also uses additional tables to store node-related information, such as Tables field_data and field_revision for specific custom fields. WordPress stores the posts in Table wp_posts . This table includes fields, such as title, content, post date, and post type. In addition, WordPress also uses other related tables to store additional information, such as Table wp_postmeta for post metadata and Table wp_terms for categories and tags. To work with the DB𝑠𝑜𝑢𝑟𝑐𝑒 and DB𝑡𝑎𝑟𝑔𝑒𝑡 databases, we devise a specific format that models the entities, the relationships between entities, and the internal attributes. We call this model Simple Database Model, SDM , which is independent of the storage paradigm. Thanks to SDM , the domain expert is able to easily analyze the structures of both databases and detect the necessary features within the context of the problem to design our F M𝑇 𝐹 . Figure 5 shows an excerpt of the transformation feature model with a total of 3 main transformation features, which are T F𝑏𝑎𝑠𝑖𝑐_𝑑𝑎𝑡𝑎,T F𝑢𝑠𝑒𝑟𝑠 , and T F𝑐𝑜𝑛𝑡𝑒𝑛𝑡 : • T F𝑏𝑎𝑠𝑖𝑐_𝑑𝑎𝑡𝑎 transformation contains basic information, such as site name or URL. • T F𝑢𝑠𝑒𝑟𝑠 transformation is composed of two alternative transformations, which are: T F𝑢𝑠𝑒𝑟𝑠_𝑑𝑎𝑡𝑎 with a user’s basic information (e.g., username and password), and; T F𝑢𝑠𝑒𝑟𝑠_𝑟𝑜𝑙𝑒𝑠 , where users have an associated role. • T F𝑐𝑜𝑛𝑡𝑒𝑛𝑡 , composed of the mandatory T F 𝑝𝑜𝑠𝑡𝑠 and optional T F𝑐𝑜𝑚𝑚𝑒𝑛𝑡𝑠 transformation. The T F 𝑝𝑜𝑠𝑡𝑠 transformation is composed of two non-mutating transformations, which are T F 𝑎𝑟𝑡𝑖𝑐𝑙𝑒𝑠 and T F 𝑝𝑎𝑔𝑒𝑠 . The T F𝑐𝑜𝑚𝑚𝑒𝑛𝑡𝑠 transformation is related to comments associated with one or more posts. 1https://doi.org/10.5281/zenodo.10277588 88 Variability in data transformation: towards data migration product lines VaMoS 2024, February 07–09, 2024, Bern, Switzerland Figure 5: Transformation feature model to migrate Drupal to WordPress. To design the transformation feature model, F M𝑂 must be also designed, where the order in which the features should be executed after a previous selection is detailed. In this case, the order of the features in F M𝑂 is: (1) basic data, (2) user data, (3) user roles, (4) posts, and (5) content. The transformation feature model has constraints that fall into two types: • Constraints imposed by F M𝑇 𝐹 , which we define model constraints and are the classical cross-tree constraints of the form of require, exclude or any other propositional formula. If in a transformation feature selection, the customer selects user roles transform, he/she is forced to select the user data transformation. This is due to the constraints requires that exist between these two transformation models. • Constraints imposed by F M𝑂 , which we call ordering constraints. Following the previous example, the selection user_roles forces to select also user_data . However, the execution of these transformation features must be done in the order described in F M𝑂 . This order is given by the context of the problem, i.e., it does not make sense to transform user roles if those roles refer to users that have not yet been transformed (user_data). 5.2 Transformation feature implementation The tool details the actions of features that are not abstract, i.e., leaves features (but can be extended to abstract features). Starting with the abstract transformation feature model, each transformation feature leaf is described, i.e., which actions will compose each feature. For example, actions within the transformation feature pages,T F pages, are detailed: (1) insert_reference (wp_post) (2) update_field (uid) (3) update_field (created) (4) change_attribute_field (created, unixtime) (5) update_field (title) (6) update_field (body) In our proof of concepts, the DB𝑡𝑎𝑟𝑔𝑒𝑡 structure, WordPress, is already instantiated and created but empty. Transformation actions are going to consist of simple updates. In our case, we move the basic information of the pages, which is stored in tables node and node_revision . For each field in the original Drupal node , it is Listing 1: Extract from the ST M of T F pages. 1<stm > 2< actio n ty pe = " up dat e_ fro m_f iel d " > 3< fr om > no de_ fie ld _d at a < / fr om > 4<to > wp_ po st s </ to > 5< attribute_from >uid </ attribute_from > 6<attribute_to>post_author</attribute_to> 7<primary_key_from>nid</primary_key_from> 8<primary_key_to>ID</primary_key_to> 9< type > var ch ar (254) </ type > 10 </ acti on > 11 ... 12 </ stm > performed an update action to WordPress, as is the case for actions (3), (4), (5) and (6). However, we must first move the original Drupal post identifiers to WordPress to maintain referential integrity, as in actions (1) and (2). Finally, in action (4), we transform the date format, which is different in WordPress. Actions are modeled in a tool-specific format called Simple Transformation Model, ST M . This model describes the exact order of the actions to be executed. As an example, Listing 1 shows an excerpt (action 𝑎2 ) from the STM for ST M of T F pages. Each time the domain expert selects an action imposed on an interactive console, it generates a new internal SDM𝑠𝑜𝑢𝑟𝑐𝑒 with the state of how the DB𝑠𝑜𝑢𝑟𝑐𝑒 would look after applying that action. Each transformation feature implementation has (1) an ST M file where the actions to be executed are described, and (2) each of the states of the source database after applying each action. In other words, for each 𝑁 actions defined in ST M , there will be 𝑁SDM files. Each action has an associated template. Mapping is possible using 𝐽𝑖𝑛𝑗𝑎 3as a template engine 2 . Following with the previous example, Listing 2 shows the equivalent template. 5.3 Transformation feature selection The transformation feature model described in Figure 4 is passed to an external reasoner [ 10 ]. This reasoner converts the transformation feature model to a SAT model and then calls a SAT solver [ 3 ]. This operation allows us to discover all the valid products of our 2https://jinja.palletsprojects.com/en/3.1.x/ 89 VaMoS 2024, February 07–09, 2024, Bern, Switzerland David Romero-Organvidez, David Benavides, Jose-Miguel Horcas, and María Teresa Gómez-López Listing 2: Jinja template for action update_from_field. 1UPDATE `{{ database_name_to }}`.`{{ acti on . e nt ity_ to_i d () }} `table_target 2INNER JOIN `{{ database_name_from }}`.`{{ actio n . enti ty_f rom_ id () }} `table_source 3ON table_source.`{{ action . pr ima ry _key_f rom () }} `= table_target .`{{ action . pr imar y_ke y_t o () }} ` 4{% if action . filter () is not none %} 5SET table_target . `{{ act ion . at tribut e_t o_ nam e () }} `= {{ a cti on . filter () .type () }}( t able _s ourc e . `{{ action.attribute_from_name() }}`) 6{% else %} 7SET table_target . `{{ act ion . at tribut e_t o_ nam e () }} `= table_source . `{{ action.attribute_from_name() }}` 8{% endif %} 9WHERE table_source .`{{ a cti on . p rim ar y_ke y_f ro m () }}`= table_target .`{{ action . pr imar y_ke y_to () }} `; model. For our particular case, we have obtained fifteen valid products, i.e., transformation scripts, which are: 1[basic] 2[basic, user data] 3[basic, user data, articles] 4[basic, user data, articles, pages] 5[basic, user data, pages] 6[basic, user data, articles, comments] 7[basic, user data, articles, pages, comments] 8[basic, user data, pages, comments] 9[basic, user data, user roles, pages, comments] 10 [basic, user data, user roles, pages] 11 [basic, user data, user roles, articles, pages] 12 [basic, user data, user roles, articles, pages, comments] 13 [basic, user data, user roles, articles, comments] 14 [basic, user data, user roles] 15 [basic, user data, user roles, articles] The valid selection of each product is solved by the SAT solver, while the order is the one set in F M𝑂 . For example, script 3 is composed of the features T F =[T F basic,T F user_data,T F articles]. A product that would be validated by the SAT solver is T F = [T F basic,T F articles,T F user_data] . However, according to F M𝑂 , T F user_data should be executed immediately after T F basic . The valid selection of transformation features is passed as a parameter to a sorting algorithm [ 9 ], taking into account the constraints imposed in F M𝑂 . The final list of transformation features will comply with both the constraints imposed in F M𝑇 𝐹 and F M𝑂. 5.4 Product building Let’s take product 3: [T F basic,T F user_data,T F articles] . We observe that the user data transformation must be executed before transforming the articles since there are references from the author to a user in the system, according to F M𝑂 . The final database may not be stable if the order is not respected, even if the final script was valid when executed. The hand-drawn design of the item articles is similar to the following SQL syntax in Listing 3. This script has problems when reusing the code. For example, we would have to design another script identical to the previous one but change the article filter to post filter for the transformation pages . Moreover, it is a complex action since it transforms data from more than one source table, node and field_data_body , increasing the code’s complexity and maintenance. Listing 4 shows a part of the script associated with T F articles . Specifically, the first two actions are shown. The first action, wp_posts , is the field change that refers to the original Drupal posts node. Our transformation script only transforms posts of type article , so the previous selection, taking into account the constraints of F M𝑇 𝐹 and F M𝑂 , allows us to generate a valid selection. The Listing 3: Hand-drawn SQL script. 1INSERT INTO wo rdpress . wp_pos ts 2(id , post_author , post_date , post_content , 3post_title , po st _type ) 4SELECT 5n. nid `id `, 6n. uid `post_author `, 7FROM_ UNIX TI ME (n . creat ed ) `post_date `, 8body.body_value `post_content `, 9n. t itle `post_title `, 10 'post'`post_type `, 11 FROM drup al . n ode n 12 JOIN drupal . fi eld _d ata _b ody body 13 ON n . nid = b ody . e ntit y_ id 14 WHERE n. t ype IN ('article '); Listing 4: Transformation script using our proposal. 1INSERT INTO `wordpress `.`wp_posts `(`ID `) 2SELECT `nid`FROM `drupal `.`node_field_data ` 3WHERE `drupal`.`node_field_data `.`type ` 4IN ('article ') 5ORDER BY `nid`; 6 7UPDATE `wordpress `.`wp_posts`table_target 8INNER JOIN `drupal `.`node_field_data `table_source 9ON table_source.`nid`= tab le_t arge t .`ID ` 10 SET table_target.`post_author`= tab le _sou rce .`uid` 11 WHERE table_source.`nid`=table_target.`ID `; second action, post_author , transforms the fields referring to the author identifiers. These identifiers have already been changed in the previous action, maintaining referential integrity. Our proposal focuses on a transformation product line within the data migration process (T). In the extraction process (E), we have set the variability to an input type: the two relational databases of Drupal and WordPress. On the other hand, in the load process (L), we have also set the variability to an output type, in our case, WordPress, which is another relational database. Therefore, our transformation product line omits the variability in (E) and (L), and the actions perform the entire data migration process. However, for the proof of concepts, we have implemented also the extraction and load part to be able to validate that the data was really migrated and not only generating the transformation script. The execution of the final script should provide us with a stable product that has not broken any functionality of the application or tool for which we are performing the migration. Since it is somewhat cumbersome and difficult to carry out manually, there are tools such as Selenium Webdriver [ 24 ] or more sophisticated ones such as Selenium IDE [ 35 ], which allow us to automate the interface testing of our application. 90 Variability in data transformation: towards data migration product lines VaMoS 2024, February 07–09, 2024, Bern, Switzerland 5.5 Performance and validation We executed our tool 3 by using our running case study. It is possible to create all the 15 scripts represented in the feature model in Figure 5 in less than 260 ms. Obviously, if the feature model were bigger, the generation of all the transformation scripts would take longer. However, we created all those to validate that all of them generate a valid transformation script. In this case, the feature model was executed and checked in a docker instantiation of WordPress. 6 RELATED WORK The data transformation complexity is not a new problem. There are several proposals that have tried to reduce the effort of the programmers and minimize the bugs generated. Some of the most relevant proposals are analyzed below. Transformation by example. FOOFAH [ 14 ] is a system designed to reduce the burden of data transformation in modern data analysis. It allows users to describe the transformation using input-output examples and uses search techniques to generate programs that perform the desired transformation. The authors demonstrated that FOOFAH could create data transformation programs for various cases, with 60% less user effort instead of the WRANGLER system [ 15 ]. TDE (Transform-Data-by-Example) [ 11 ] is a data transformation search engine developed by Microsoft. Given all the recurring logic in the transformation of source code, and web services, among others, Microsoft proposes to reuse all that effort to find, interactively, how to simplify and transform data sets before their analysis. TDE demonstrates, on its test bench, a correct transformation of 72% of the tasks. Domain specific languages for data migration. There are examples, such as MongooseDSL [ 32 ], which abstracts the actual logic, in this case, from MongoDB, to migrate data between non-relational systems. MigrationExp [ 18 ] is another approach that allows the definition of programming language migrations, e.g., translating a Java application to Kotlin. This tool tries to support legacy systems that present an excellent resistance to being migrated. Such migrations are incremental, i.e., small functional increments that avoid a catastrophe in case of failure. However, using DSLs does not solve the problem of variability between data transformation features. DSLs still have the problem of code redundancy, complex actions and difficult reuse. Moreover, a DSL does not allow to design final script’s features, nor does it generate custom scripts based on the selection of features. Data workflows. Data cleaning workflows are known to be used to improve data quality and save time and money. However, these workflows are rarely reused in the scientific community. Li and Ludäscher [ 17 ] propose a high-level conceptual model where they identify operations that will be used in other workflows. For example, a database may store a geographic information system based on decimal latitude and longitude. The target database stores the same data but using a sexagesimal system of hours, minutes, and seconds. This transformation problem, data workflows, appears recurrently in these domains, it is interesting to be able to apply it to similar domains. Our transformation feature model could be a 3 https://doi.org/10.5281/zenodo.10277588. See README.md for replication instructions. case study as data workflows. Designing the transformation feature model is the most expensive within our transformation product line. Actions such as update reference are often common to several transformations, since referential integrity does not apply to just one transformation but to most (or all) of them. A study of data workflows could identify common actions for reuse in other migration projects with similar data structures without starting from scratch. Our proposal acts as reusing reusable parts using a transformation feature model as a configurator element to create customized scripts for data transformations within a standard process such as SPLs. To the best of our knowledge, this is the first proposal that uses a complete stack of SPL that materializes the reuse of artifacts for data migration. This provides a mechanism for reducing the creation of specific transformations in data migration projects. 7 CONCLUSIONS AND FUTURE WORK Using a transformation feature model can eliminate the redundancy of code in transformation scripts, which can otherwise cause significant problems as the product scales [ 21 ]. By studying the variability between migrations that share joint SQL statements, the transformation feature model enables the creation of products with reduced complexity. This reduction in complexity is because the model generates simple actions that update single fields in a table, rather than attempting to cover multiple transformations simultaneously. This paper presents a novel approach to migrating information between two relational databases using a transformation feature model. By leveraging product line software techniques, we can create multiple products, namely SQL scripts, that can be tailored to specific feature selections. Each script represents a distinct composition of the migration process that adheres to the constraints of the domain expert, making them all valid solutions. Our approach addresses the challenge of migrating data between different databases by providing a flexible and customize solution. Our proposal is an exciting starting point for data migration between information systems. Our validation suggests that the effort to create the transformation feature model is less than designing scripts one by one from scratch, with a higher probability of redundant and erroneous code due to duplication. In addition, new specifications in a migration project can be added to the transformation feature model and generate new artifacts without the need to modify the final code. This advantage increases scalability and the probability of success in data migration projects. For future work, we plan to extend our approach to analyze the variability in the Extraction and Loading stages of the data migration process. Moreover, we plan to provide inference engines to automatically determine the transformation features that are manually defined by domain experts in our current SPL. MATERIAL All the source code of the tool implementation and the data used as evaluation scenario can be downloaded and deployed from this repository: https://doi.org/10.5281/zenodo.10277588 ACKNOWLEDGMENTS This work was partially supported by FEDER/Ministry of Science, Innovation and Universities/Junta de Andalucía/State Research Agency/CDTI with the following 91