Basic SQL Client Application

In order to enable the transformation and modelling of relational data according to business rules, Sidra provides a template for a vanilla version of Client Application, called Basic SQL Client Application.

The main component of this Client Application template is a client SQL Database, that will host two sections of data:

  • A reduced version copy of the metadata tables in Sidra Core, in order to track Assets metadata as well as Data Factory Metadata and configuration.
  • Any data model and stored procedures required for data transformation according to business rules and final usage of golden data records.

This document refers to key concepts of a Client Application in Sidra, which can be reviewed here.

Purpose

This Client Application template allows to accelerate the creation of a Client Application, by abstracting from all the main synchronization elements with Sidra Core. As long as the Client Application which is created with this template is configured to have the required permissions to access the DSU data, this application transparently and automatically retrieves data from the DSU into the staging tables.

This Client Application integrated with Sidra shares the common security model with Sidra Core and uses Identity Server for authentication. A copy of the relevant ingested assets metadata is kept always synchronized with Sidra Core. The metadata synchronization is performed by an automated Sync job, explained here.

The actual data flow orchestration is performed through a specific instance of Azure Data Factory installed in the Client Application.

High-level installation details

As with any other type of Client Application in Sidra, the process of installing this Client Application consists of the following main steps:

  • A dotnet template is installed, which launches a build and release pipeline in Azure DevOps defined for the Data Labs Client Application.
  • As part of the build and release pipeline for this Client Application, the needed infrastructure is installed. This includes the execution of the ClientDeploy.ps1 and Databricks deployment scripts, and also the different WebJobs deployment.

Build+Release is performed with multi-stage pipelines, so no manual intervention is required once the template is installed by default. For more information on these topics you can access the following Documentation.

Architecture

The Basic SQL Client Application resources are contained into a single resource group, separated from the Sidra Core and DSU resource groups. The services included in the ARM template for this Client Application contain the following pieces:

  • Storage: used for storing the copy of the data that is extracted from the DSU, and for which the Client Application has access.
  • Data Factory: used for data orchestration pipelines to bring the data from the DSU.
  • Client Database: used for keeping a synchronized copy of the assets metadata between Sidra Core and Data Labs and hosting the relational models and transformation procedures.
  • Key Vault: used for storing and accessing secrets in a secure way.

Besides the Azure infrastructure deployed, several Webjobs are also deployed for the Basic Client Application, responsible for the background tasks of data and metadata synchronization:

  • Sync
  • DatabaseBuilder
  • DatafactoryManager