This is the third article in our Data Snapshots series. In this post, we’ll show you how to build a simple and effective snapshot pipeline using Dataflow Gen2 in Microsoft Fabric.
In our previous article (insert link), we introduced the concept of using exchange rate data, which changes daily, as our example. For this demonstration, we will use FreecurrencyAPI, which offers a free API for currency data with certain limitations.
Step-by-Step: Creating a Data Snapshot with Dataflow Gen2
To begin, you’ll need to set up access to the FreecurrencyAPI. Once registered, you’ll receive a unique URL that can be used to pull data. Here’s how to get started:
Connect to the API:
In Dataflow Gen2, go to Get Data → Web API and connect to the FreecurrencyAPI using the unique URL
Retrieve the Data:
After connecting, you’ll receive data in the following format:
However, this raw data is not in an ideal format for analysis. To make it usable, we’ll need to apply some transformations.
Transform the Data:
After applying the necessary transformations, the data will be structured into a more analyzable format, including columns for currency pairs and their rates. We’ve also added a Timestamp column to track when the data was retrieved.
Set Up a Destination:
Next, select a destination for your data. Choose either Lakehouse or Warehouse and specify a table name. You’ll then be prompted to select an update method. If you choose Append, new data will be added to the existing dataset each time the data refreshes. The Timestamp column will help you identify when each data entry was recorded.
Finalize and Publish:
After verifying the column types and saving your settings, you’re ready to publish the dataflow. Once published, you can set up a refresh schedule (daily, weekly, or monthly) to ensure the data is updated regularly.
Accessing Your Snapshot Table via SQL Endpoint
With the dataflow published and the refresh schedule in place, you can now access the table via the SQL endpoint at any time. This eliminates the hassle of losing access to historical data, ensuring that you always have the information you need for analysis.
Conclusion
This streamlined process not only simplifies data storage but also ensures that your data is always up-to-date and ready for analysis. In the next article, we’ll show you how to implement the snapshot process using T-SQL in Microsoft Fabric – including versioning and table cloning techniques. Stay tuned!
Read Part 4 : Daily Data Snapshot Automation with T-SQL in Microsoft Fabric
