Developing a Shiny App to Manage a Sandbox Environment - Part I.
Actualizado: 10 jul
Sandbox Data Environments are design to fulfill multiple purposes in the process that Enterprises implement to generate value and knowledge from its Data. As a secure, fault-tolerant laboratory, the Sandbox Environment provides Analysts, Scientist and BI Developers with a safe area that mirrors the Production instance where they can test hypothesis, train statistic models with large datasets and summarize the data to be presented in dashboards and reports while preventing the risk of affecting operative processes that could compete with these experiments for compute resources.
To enable this capabilities, the Sandbox Environment has to be constantly managed to ensure that the relevant data is available for the consumption of the different projects and experiments that the Data Teams need to run and test for the discovery of new insights and other valuable metrics that can help business teams to make better and informed decisions. In this article series, I will explain how to develop a Shiny App to manage and optimize the process of replication Data from Production to a Sandbox Environment.
The source application code, architecture design and the scripts to configure the Database environments to run tests are available in the next repository:
Defining the Objective.
For this first Deliverable, we're gonna work to enable the Data Replication for one single Data Base Object, with the following functionalities included in our application:
A user module to configure the name of the Schema and Table for the Data Replica.
A validation process that ensures that the Table has the same Structure in both the source (Production) and the destination (Sandbox) environments.
A user module to configure the Type of Replica (Overwrite, Date-Specific or Interval Between Dates).
The Data Replica Process.
Access Management for the application requesting user and password.
Since all the code is located in the repository mentioned at the beginning of this article, I'll be only describing the process I followed for designing the UI and explaining in general terms the logic of the application's features.
Designing the UI based on the Process.
Considering the importance and risks that the task implies (like accidentally deleting Data that is being used by a Team in the Sandbox Environment for its final Tests before going to Production), the Design of our app should be simple and straight-foward, with every Label describing it's widget purpose and a pop-up requesting for user confirmation before executing the process of Data Replica. Also, we want to use a sober and formal design, considering that we maybe want to propose this application as an Enterprise solution. Lastly, above all considerations, the design of every application's UI that is used for an operative process needs to fulfill the facilitation of the tasks that are going to be executed inside them, making it intuitive enough for the users. The design follows the process.
Structure Validation.
The first task is selecting the Table Name and the Schema in which that table is located inside the Source DB. Next, since we want to automatize the process of replica, we need to validate that in both instances, Production and Sandbox, the tables are equal in structure (Column Names, Column Types, Extensions, etc.) that prevents us to make a custom query for matching the differences for each replica and, most important, to ensure that the Sandbox environment is mirroring Production. So the three widgets we need to achieve our first objective are one select input for the Schema, one text input for the Table Name and a Button for execute the Validation, nested inside the same card.
Once we configure the information of the Table and click on the Button to execute Validation process, we'll get the following message in case that everything's alright:
On the contrary, we'll get a warning message in case that exists any difference between the Tables, describing the detail of the mismatch, for example, if one table has more columns than the other we'll obtain the next message:
or, if there's a difference in the extension between a pair of numeric columns, or a difference in their names, we'll get the following:
also, if the application detects a mismatch between the tables, the Button for executing the Replica process will remain disabled:
Selecting the Type of Replica and Executing the Process.
With our Tables validated, we can now move foward to the second part of the process: the Replica. I considered for this demo three types of replica based on the most common cases I've encountered in real enterprise scenarios:
Overwrite replica (or full backup): Delete everything that exists in the destination table and load everything that exists in the source table.
Date specific replica: Delete all the records that exists in the destination table that matches with the date that the user selects (usually the end of one month) and load everything that exists in the source table that matches that same date.
Date interval replica: Delete all the records that exists in the destination table that matches the interval between the dates that the user selects and load everything that exists in the source table that matches that same interval.
These options will be displayed in a "select input" widget. Based on the user's selection for the Type of Replica, the application can display more widgets to complement the configuration of the replica, for example:
In case that the user's selection is the "Date specific" replica, another two widgets will appear. The first for selecting the column that the process will use for filtering the records that will be deleted in destination and extracted from source to load them again in destination, the "pivot column". The second, as you imagine, to configure the date for the replica.
If the user selects the "Date interval" replica, the application will display three more widgets, one for selecting the "pivot column" and the other two for selecting the "floor" and "ceiling" dates that compose the interval to consider for the replication.
In both cases, the application will scan the Table's structure that was selected for the Replica to display the options in the "Date Column for Data Replica" widget. If the user selects "Whole Data Replica", no additional widget will be displayed.
With all configured (and double-checked) we can procede to execute the replication. For safety reasons, once we click on the "Execute Data Replication" button, a pop-up will appear displaying a "Replication summary" based on the users selections and asking for confirmation:
Then, clicking on "Execute Replication" will detonate the process. Once the replication is completed, another pop-up will appear to display a summary that includes the number of records replicated and the total execution time:
and this concludes the Replication Process.
Comentarios