How to handle temporal table in ADF Data Flow?

Question

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.

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.

Answer 1

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.

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.

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

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.

• Temporal tables
• Getting started with temporal tables in Azure SQL Database and Azure SQL Managed Instance

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 and upvote 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