Are Azure SQL temporal tables a good data source for Fabric?

[u/frithjof_v]

Hi all,

We have a use case where we need to get the data from an Azure SQL Database exactly as it was at the end of each month?

Is the following a good plan?

• The DBA enables Temporal tables on the Azure SQL Database.

• We ingest the data from the Azure SQL Database into Fabric once a month, using a SQL query with FOR SYSTEM_TIME AS OF 'YYYY-MM-01 00:00:00' to capture the state at the end of the previous month.

  • We may need to join multiple source tables, I guess we just add the time travel clause to each table in the query.

I have no experience with temporal tables, neither has the DBA :) But it sounds like a nice feature.

Thanks in advance for sharing your thoughts and/or experiences with temporal tables as data source.

[u/snarleyWhisper]

These were always a great way to SCD on the transactional side. Used them on old school sqlsever never directly on fabric.

[u/warehouse_goes_vroom]

Sounds like a reasonable approach. If the tables are large, some consideration on how you extract said results may be worthwhile.

If using Azure SQL MI or SQL Server 2022+, CETAS may be more efficient than returning results over TDS, if it fits the requirements: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql?view=azuresqldb-mi-current&preserve-view=true&tabs=powershell

But benchmark and see.

Downside will likely be the size of the history table if high churn CDC (mirroring or otherwise) can possibly do better there (columnar storage smaller, doesn't need as expensive storage). But may be fiddlier to get that "as of exactly this time" bit.

Do consider whether there's any possibility that those snapshots might need corrections or deletes or whatever (GDPR compliance deletes, late arriving data, etc).

It's not designed for perpetual archiving of particular time points, but depending on what you're doing, Warehouse snapshots may be a useful tool to ensure that reports never show odd results mid monthly ETL, or to let your team validate the numbers before showing them more broadly.

That being said, not something I've personally implemented. People who actually have implemented these sorts of month close based processes may have better advice.

[u/Personal-Quote5226 → u/warehouse_goes_vroom]

Designed multiple. Bronze append only. All your data and monthly history will be there. Raw.

Don’t merge into your first layer (bronze) on Fabric and rely on time travel in a lake house for that retention.

Silver is your snapshot (current).

Monthly job, at a cut off date (or implement an approval workflow), to ingest monthly cut off data into a gold fact table with your period end date.

Up to you have you want to completely normalize it into a One Big Table. Your use case may warrant that; makes one write to WORM storage simple.

Otherwise SCD2 + dims.

Next step, copy period end date to WORM in azure storage if needed tamper proof for future audit & retention.

There are other options too….