topic Re: Intraday Data for Reporting in Data Warehouse

Intraday Data for Reporting

Digidank — Mon, 17 Jun 2024 13:42:43 GMT

We have 2 sources of data for reporting.

Nightly files for historical data
Intraday changes on transactional data

The nightly files are the result of the days data with some processing by the source system. We have built out a warehouse of Dimensions and Facts from this using spark jobs for the ELT process. Then transformed into Dimesnion/Facts in the Synapse Data Warehouse. This all is working great.

We are not using our Fabric warehouse in production yet as I need to solve the intraday reporting problem first. There is some data that comes in nightly files we process that does not come in the intraday data, but is needed to fully report the intraday data. Currently, we are just writing everything to a regular Azure SQL database for both historical and intraday.

My question is whether Data Warehouse is capable of keeping up with intraday changes? Will writing those changes as they come in to Data Warehouse be feasible? I presume it all just depends on the Capacity we choose, but wanted some input from y'all more knowledgable on it.

Re: Intraday Data for Reporting

Anonymous — Tue, 18 Jun 2024 08:22:45 GMT

Hi Digidank ,

Thanks for using Fabric Community.
Synapse Data Warehouse (DW) can definitely handle your intraday data updates. It's designed for large-scale data ingestion and frequent updates, so capacity shouldn't be a major obstacle. Here's how we can tackle both nightly and intraday data:

1. Nightly Data:

Keep using your existing Spark jobs to process and load nightly data files into Synapse DW. This is a familiar and efficient approach.

2. Intraday Updates:

Break down your intraday data into smaller batches and ingest them into Synapse DW throughout the day. This provides near real-time updates for your reports.

Important Note:

While Fabric notebooks are a valuable tool for data exploration and analysis, they currently cannot directly load data into the Data Warehouse. We recommend using Data Factory pipelines for this purpose.

Additional Tips:

Partition your tables in Synapse DW to optimize query performance for both historical and intraday data.

Consider using materialized views for frequently used aggregations on intraday data to further improve reporting speed.

Docs to refer -
Ingesting data into the warehouse - Microsoft Fabric | Microsoft Learn

Hope this is helpful. Please do let me know incase of further queries.

Re: Intraday Data for Reporting

Digidank — Tue, 18 Jun 2024 14:03:33 GMT

Perfect! Thanks, that was the answer i was hoping to hear. I have already started work on the intraday and have a few things updating quickly.

Re: Intraday Data for Reporting

Anonymous — Wed, 19 Jun 2024 09:23:41 GMT

Hi Digidank ,

Glad to know that we have answered your query. Please continue using Fabric Community on your further queries.