Skip to content

Components

This section shows the different technical components, their names, as well as where they are deployed.

Load Protocol

Platform

We call:

  • Vacancy: vacancies from the platform

  • Vacancy Allocation: vacancy allocation from the platform

  • Shifts: the content of the DWH shift table, i.e. the result of the SQL joining Vacancies, Vacancy Allocations, and more.

Function Run Time Cron Description
delplatform Every weekday at 1:50 UTC 0 50 1 * * 1-5 Download bulk tables
Download incremental tables (except shifts)
Empty shifts table
delplatform_shifts Every weekday, every 5 min between 2:00 and 5:00 0 0/5 2-5 * * 1-5 Delete last vacancy
Download 20k shifts

This is, in effect:

  • doing a full load every weekday
  • making sure each load takes less then 10 min (as this is a restriction on azure function side)
  • taking into account that the vacancy_id is not unique among the shifts
  • allowing to move to a more intelligent delta load strategy later

Initially, we tried to implement an incremental load, though we failed for these reasons:

  • some of the tables in the shift query do not have soft deletes
  • some of the vacancies to not have any vacancy allocations (in this case the shift.id becomes #.)
  • the sql logic using the updated_at from various tables proved to be unreliable

Lineage

Configuration

flowchart LR
    Platform-->|Azure Function|Datalake;
    Prato-->|Azure Function|Datalake;
    HiAnt-->|manual|StorageAccount-->|Azure Data Factory|Datalake;
    Datalake-->|PBI Refresh|PBIS;

Containers

Platform

Technology: PostgresSQL
IP: 3.120.103.251
Database: devprod
Manager: Tayfun
Connection Details: Tunnel to database.
Prerequisites: 
- Key installed on server by Tayfun. 
- IP added to whitelist

Microsoft Tenant

The microsoft tenant is managed by Power Partners.

Tenant ID: 8564eb7f-4f10-4823-ab38-405550829895
Primary Domain: deltaworxx.onmicrosoft.com

Azure

Directory: 
Directory ID: Deltaworx (deltaworxx.onmicrosoft.com)
Subscription: a29b8d1d-47fd-4ac1-8081-0aca47540a13
Subscription Name: #del pax8

Power BI Service

The Power BI Service is on the Deltaworxx Microsoft tenant.

Power BI Service: on deltaworxx.onmicrosoft.com

Components

Azure Resource Group

All ressources of this solution go into the same resource group.

Resource Group: management

Azure Function App

Name: del-management-af

Azure Data Factory

Name: del-management-adf

SQL Server

Name: del-sql
Server: del-sql-server
Type: SQL Server with DTU

Keystore / Vault

Used to store secrets.

Name: del-management-ks
Vault-URI: https://del-management.vault.azure.net/

PBI Service

Workspace: Power Partners
Dataset: del_management

Configuration and Code

Ingestion Prato -> Storage Account

Function App: del-management-af
Function Name: ingest_prato
Storage Account: delmanagement
Sink: datalake/prato
Frequency: nightly
Time: 6:00 UTC

Ingestion Platform -> Storage Account

Function App: del-management-af
Function Name: delplatform
Storage: SQL Server del-sql
Sink: datalake/platform
Frequency: nightly
Time: 6:00 UTC

Wrangling Prato

Azure Data Factory: del-management-adf
Pipeline Name: wrangling_prato
Storage Account: delmanagement
Bucket: del-management
Path: dwh/prato/
Format: parquet <!-- TODO: confirm -->
Frequency: nightly
Time: 6:30 UTC

Wrangling Platform

Azure Data Factory: del-management-adf
Pipeline Name: wrangling_platform
Storage Account: delmanagement
Bucket: del-management
Path: dwh/platform/
Format: parquet <!-- TODO: confirm -->
Frequency: nightly
Time: 6:30 UTC

Power BI Dataset Refresh

Frequency: Daily
Time: 7:00