CODEWITHSUNDEEP
azurepysparkazure-data-factorydatabricks
Aditya Bhure
Aditya Bhure

Reputation: 1

Azure Data Factory issue

i Have recently given a Data engineering interview. In that interviwer asked me a question on ADF where i faced a issue while responding to it. Your help would be helpful here.

  1. there is a emp table on prem sql server db feilds : emp table -> emp_id, emp_name, dept_id

  2. there a csv file in blob storage dept.csv -> dept_id,dept-name

  3. we need to create a target table in azure db emp_details - > dept_name, total_emp_count

here we can only use ADF pipeline in order to complete this scenario.

Appropiate answer based on the given scenario

Upvotes: 0

Views: 108

Answers (1)

Bhavani
Bhavani

Reputation: 5297

You can follow below approach to achieve your requirement:

Copy your emp table to csv file from on-premises to blob storage using ADF copy activity. Use data flow to join above two files to get in required format as follows:

Create two datasets with two csv files dept.csv and emp table.csv, use them as source dataset, add Join transformation to them. Use dept_id as key column, configure Join transformation as shown below:

enter image description here

Add aggregate transformation to the Join transformation in the Group By field, add dept_name and total_emp_count as aggregate column with function count(emp_id) as shown below:

enter image description here

You will get the required format of table as shown below:

enter image description here

Add Azure SQL dataset as sink, debug the pipeline with dataflow activity with created dataflow, then you will get the target table in Azure SQL database.

Upvotes: 0

Related Questions