Forum Discussion
Datamarts Preview Appending data
You need a separate ELT solution. No Power BI solutions are a proper datawarehouse - not meant be. Azure SQL, or other tools are meant for that. All Power Bi tools (dataflows/datasets/datamarts) wipe all data on refresh (or keep some historical data in an incremental refresh scenario, but that is still not meant to be a datawarehouse for storing snapshots, and those historical partitions can be purged on a schema change, losing all data).
You can create a dataflow outside of your datamart, set it up with the Bring Your Own Storage option so that you have access to the underlying datalake, then set it to a monthly scheduled refresh. This will generate monthly snapshots:
In your datamart you can then ingest from the ADLS Gen 2 bucket where you saved the output of the initial dataflow, and add the files' timestamp as your snapshot date.
Thank you otravers.
In this model I think we'd have an outside dataflow reading a folder from a growing set of source files. Over time I am concerned over the management of these source files and growing costs of reading all of these every refresh unless the data flow knows to only read what has changed since the last refresh.
Yes, you'll have to manage these snapshot files eventually. Cloud storage cost is really the least of your concerns these days if "100GB is plenty" for you.
You can set up incremental refresh against ADLS Storage Gen 2:
That's really neat! I was concerned over PowerBI having to read from every file from the growing data set every time and the associated network costs. If it only does this incrementally based on what has changed that does change things.
Microsoft won't charge you egress costs as long as the Azure data lake is in the same region as your Power BI tenant ("The Power BI workspace tenant region should be the same as the storage account region"):
The main concern here is ever increasing refresh times that might eventually lead to time outs if you were to always fully refresh from your growing collection of snapshots.