How to handle temporal table in ADF Data Flow?

Anthony Jones 21 Reputation points
2021-10-05T11:10:11.87+00:00

I have a temporal table in Azure SQL that I'm attempting to perform some data transformation on based on a date.

So I want to say on this specific date (say 2021-10-04) I want to know what an aggregate of a specific integer column was.

The problem I have currently is that I can add a dataset for the temporal table in ADF but that table doesn't include ValidFrom and ValidTo in the schema and I can add the history table as a dataset but that table only includes the rows which aren't currently the most recent row.

I'll try explain as an example.
137650-image.png

So in the above example for 2021-10-04 as the date I want to get ID 1 (0) from the TTableHistory table and ID 2 (2) and 3 (3) from the TTable table and SUM them to get 5.

Is there an accepted way to handle temporal tables in ADF?

Thanks in advance.

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,128 questions
0 comments No comments
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2021-10-06T23:39:08.48+00:00

    Hi @Anthony Jones ,

    Welcome to Microsoft Q&A forum and thanks for reaching out here.

    As per my repro, I was able to see all the columns of my Temporal Table in Projection section of the Data flow source transformation.

    138259-image.png

    Also when I do data preview using a query to get the values that were actual (current) at the specified point in time in the past, the data preview returns a table with a rows containing the values that were actual (current) at the specified point in time in the past.

    138245-image.png

    138260-image.png

    Here is my source data in Azure SQL table and it's history for reference:

    138290-image.png

    In the above sample you have shared for TTable, I don't see ValidFrom and ValidTo columns, which could be the reason you are not seeing them while doing projection in Dataflow.

    Hence I would recommend you to please review these docs related to temporal tables and cross check if your temporal tables have been created accordingly.

    As per my repro, I don't see an issue with ADF dataflow handling temporal table.

    Hope this helps. Do let us know if you have further query.

    ----------

    • Please don't forget to click on 130616-image.png and upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful