This blog covers our experiences using FiveTran’s Square Data Connector and syncing to an Azure SQL Database.
We are currently using Square as our POS (Point of Sale). This POS has been phenomenal when compared to our experience with NCR Aloha. Many, many pluses for using Square over Aloha but that is a different blog for another day. However, Aloha did have SQL Replication as a feature so we could easily replicate sales data to an on premise SQL Server or to the cloud using Azure SQL Database. Square does not have this feature out of the box.
It might be possible if your sales data is small enough that you can use Square’s APIs or SDK to build Power BI reports and perform well. But if you have a large volume of data or need data transformations, then more than likely you will need a third party tool or roll your own solution.
Prior to rolling out Square, I did setup the Azure Square Data Connector (https://docs.microsoft.com/en-us/azure/data-factory/connector-square?tabs=data-factory); however it did not work well. At the time it was in preview and the database schema was downright terrible. We abandoned that approach and started using Square APIs to retrieve the data for our reports. This was a band-aid and not a long term solution. We are a high volume retail chain with each store averaging 1500 transactions a days so this approach was not feasible in the long term.
In addition, we were a small team so we needed a quick easy fix. Square recommended FiveTran. I was a little skeptical at first when I checked out FiveTran. But it seemed like the best when compared to other options out there at the time.
Advantages:
- Extremely easy to setup and configure. You only need your Square API Key and the connection info to your destination.
- Database Schema that FiveTran creates is clean and easy to understand.
- You do not need to learn the intricacies of the Square API or Square SDK
Disadvantages:
- You are at the mercy of FiveTran when they introduce bugs. Ok, so that’s true with any third party tool. We had a painful three weeks back in September 2021 when a new enhancement they rolled out did not work so well. Constant timeouts with syncs, etc… Solution was to start an entire new Data Connector. That took about 36 hours to reload a years worth of data.
- Square Offline Mode – oh what a pain when trying to keep your data synced for reporting purposes.
- Immature tools – you can’t run a sync for a specific time period for missing data. This is a problem when using Square Offline Mode.
- Square’s Loyalty, Customer and Catalog APIs do not let you query using a last updated filter; therefore at least one sync per data is rather long to re-pull all of this data. We have over 200,000 customers and almost 50,000 items in our catalog. Hopefully Square will fix that so FiveTran can adjust their Data connector.
Configuring FiveTran
FiveTran has plenty of documentation on setting up your Data Connector. Depending on your security setup for your destination, don’t forget to whitelist the APIs as outlined in this documentation:
Once you setup your Data Connector, check out the Settings configuration:
Yea, that’s it. Seriously. No tool to rerun a sync for the last week which would be VERY helpful if some of your registers ran in offline mode. That was a real let down.
FiveTran Square Data Schema
Here is a quick link to the DB Schema that will be created by FiveTran:
Square Offline
Square Registers can be configured to run in offline mode. Offline mode allows you to take swiped credit card payments when your internet is temporary unavailable. However, the payments are not processed until the register is back online. See this article for reference:
I highly recommend offline mode if your internet is spotty. For instance, if you have Comcast in your retail locations then you probably really want Offline Mode turned on. This means you can still take credit card/debit cards up to X dollars based on how you have offline mode configured. However, there is a catch. If a register is in offline mode, you need to get it back online ASAP. You have 72 hours to get those transactions up to to Square for it to process the credit cards otherwise you run the risk of those transactions being “cancelled” thus losing sales. In addition, once that payment is actually processed by Square it might get declined due to insufficient funds. Offline is a risk the business owner needs to fully understand and accept before turning it on.
There are other ways around using offline mode or minimizing the risk of offline mode. You can keep a 5G MIFI so when your internet is down you can switch your registers to use the MIFI. The 5G MIFI works GREAT! We actually ran 10 registers on it for over a month while waiting for Verizon FIOS to be setup at a store. Or you can go a little high tech and setup a failover cradle point for your internet provider. When the internet is out, this will failover to a wireless data plan until your internet is back up.
Square API Problem
FiveTran DataConnector has a sweeper. This is to minimize a Square API problem with Square Offline Mode and any latency with transactions being processed. When transactions are uploaded to Square from the register, Square uses the date/time of the transaction on the register. So if a transaction is processed on Monday, but the register does not get back online till Tuesday, FiveTran will not sync that transaction since it already synced for the timeframe when the transactions was processed offline.
When the sync runs, it syncs the data for the appropriate timeframe based on your configuration. If your data connector is configured to sync every 6 hours, then it syncs the last 6 hours of data as well as a 6 hour timeframe 72 hours ago. In theory this should work well BUT during that timeframe, your reports will not match the Square Dashboard regarding sales data. Business users are complaining…
Also, if a register is offline for more than 72 hours which can happen, those transactions are never synced. EVER! Until you manually intervene. See the architecture below on how I handled it.
This is an issue with Square. Square should really expose a date in the API for when the transaction is uploaded into the Square cloud. Then they should allow filtering by that date. Then the FiveTran Data Connector could use that date to sync which will then catch all offline transactions even if uploaded days later.
In addition, FiveTran could build a tool that lets you sync data for a specific timeframe. Then you could easily run this tool to sync missing transactions due to offline mode. But it is just a band-aid requiring intervention.
Buggy Upgrade
Unfortunately FiveTran introduced a bug in their Square Data Connector with a release in September 2021. This was a rather painful experience. Syncs were timing out, data was missing, reports were wrong, etc. The upgrade of the database schema was the issue and would time out, then somehow magically work, timeout, then work, etc…
This led us to a decision to build our own sync tool as a “safety net” to FiveTran. We built this using the Square API and Azure Data Factory. It took several weeks to build but well worth the time. As of now, we are running the FiveTran Square DataConnector. But in addition, we run our sync tool throughout the day and use FiveTran as a final sync for the day.
After three weeks having FiveTran sync issues, we created a new Square Data Connector pointing to a new Azure SQL Database. This created the new schema and synced to our database within 36 hours. Then it was rather simple for use to switch reporting to use the new database and solved the issues we were having with FiveTran syncing.
Architecture
Currently we implemented our architecture as such.
It has the following advantages:
- We are a small team. We have the best of both worlds where FiveTran syncs everything to our FiveTran Square database while our “Square API Pipeline” syncs just sales data into our Data Warehouse. If either process goes down, we are still getting sales into our DataWarehouse and keeping reports up to date in a timely fashion.
- We can manually kickoff the Square API Sales Pipeline for a specified time period thus cleaning up any missing (offline mode) sales/transactions easily. Currently we cannot do that using the FiveTran Data Connector.
With a larger team, I would probably change my architecture. I would remove FiveTran completely from the solution in order to eliminate that expense and probably just use Azure Data Factories. The results from the Square API would be pumped into a Data Lake. Then another ETL Tool will transform the data into a Data Warehouse for reporting. This architecture should allow us to easily bolt on additional AI/Analytics tools using the raw data in the Data Lake or in the ETL Tool. In addition, it would also allow us to reload the DataWarehouse (without reloading data from Square) in the event that was needed for a feature requests to the ETL pipeline.
Conclusion
Hopefully you found this useful if you are building your own custom Power BI Dashboards for your Square POS data.
Doylestown Coder 