Need to build a ADF data flow pipeline for complex query

Question

Hi Everyone, I want to build a data flow pipeline in ADF for the below query.

WITH RankedRows AS (
  SELECT
    a.*,
    ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY a.Id) AS RowNum
  FROM
    [Lms.Core].[Sections] a
    INNER JOIN [Lms.Core].[SectionModuleInstances] (nolock) b ON a.id = b.SectionId
    INNER JOIN [Lms.Core].[CourseSections] (nolock) c ON b.SectionId = c.SectionInstanceId
  WHERE
    c.CourseId in( select a.Id 
from [Lms.Core].[Courses] a (nolock)
inner join [Lms.Core].[CatalogItems] b (nolock)
on a.id=b.scopeId
inner join [Lms.Core].[CatalogDefintions] c (nolock)
on b.CatalogDefinitionId=c.Id
where b.CatalogDefinitionId in(722,752,814) and b.Scope=50)
)
SELECT
  *
FROM
  RankedRows
WHERE
  RowNum = 1;

so for partitioning, I know need to use window and filter column so I will apply that one no issues but in where condition we added a query in() so how to build a ADF pipeline for that. this is my question. Thanks & Regards Aravind

Answer 1

Hi Aravind ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As I understand you want to implement data transformation using dataflow in adf pipeline with reference to the sql query you have provided. In addition to the query, it would be helpful if you could share the sample input and expected output data.

There are different components in this query:

1. ROW_NUMBER() function can be applied using a window transformation to partition the data by Id and order it by Id.
2. To apply join you can use Join transformation and select inner join that would only outputs rows that have matching values in both tables.
3. In place of whereclause, opt Filter tranformation . The filter transformation is similar to a WHERE clause in SQL. It allows row filtering based upon a condition. The output stream includes all rows that match the filtering condition.

You can checkout different real time scenarios of dataflow implementation in the below playlists:

Dataflow scenarios

Dataflow scenarios

In case you need assistance with the implementation, kindly share the sample input and expected sample output data.

Hope it helps . Kindly accept the answer if it helped. Thankyou