In this case study, we highlight how our expert team partnered with a consulting firm to deliver a robust, scalable, and high-quality data integration solution for a restaurant chain. By leveraging open source tools like dlt, OpenTofu, GCP, and GitHub Actions, we transformed a complex data challenge into a seamless, future-proof pipeline.
Situation
Our partner Soli&Co needed to quickly implement a solution to download data from Square‘s API for a restaurant chain. The restaurant chain required robust reports and analytics on its orders and customers, but off-the-shelf solutions didn’t have an existing integration and were quite expensive.
Task
Our task was to develop a cost-effective, easy-to-maintain data import pipeline using open source tools and best practices. The key objectives were:
- Flexibility: Build a solution that could adapt to future changes, such as API updates or the addition of new endpoints.
- Transparency: Ensure that the full code is available for our partner and their client.
- Operational Value: Allow fast and reliable changes in case of API update, with effortless error recovery and low operational costs.
Action
Open & Extensible Setup
- Technology Stack: We built the pipeline using widely adopted technologies including Python, OpenTofu (which is just a drop-in replacement for Terraform), and GCP. By leveraging dlt, we ensured that the solution was open source and fully transparent, giving us complete visibility and control over the code and not just over a configuration.
- Serverless Architecture: The pipelines runs on Cloud Run with Docker containers and orchestrated using Cloud Scheduler, allowing us to maintain a lean infrastructure with minimal overhead and low costs.
Customized Integration with Square’s API
- Custom Authentication: We extended one of the authentication methods for the dlt REST API Source to pass an additional header required by Square.
- Custom Paginator: For Square’s POST endpoints that return multiple pages, we added an extra key-value pair to the JSON payload to include the cursor, meeting Square’s pagination requirements.
- API Response Cleanup: We developed an ad-hoc cleanup method that filters the API response based on an easily configurable setup per endpoint, ensuring that only the necessary (even nested) fields are retained.
Best Practices & CI/CD
- Infrastructure as Code & Code Versioning: By leveraging Infrastructure as Code with OpenTofu and maintaining strict code versioning, we ensure every update is consistent, traceable, and reversible.
- Idempotency & Incremental Loads: These techniques ensure that each data update is processed accurately without duplication, providing consistency and reliability across the pipeline, but also save costs.
- Automated Testing & Deployment: By automating our CI/CD workflows, we enable rapid detection and resolution of issues, allowing the system to recover quickly from errors and continue operating smoothly, all while minimizing manual intervention and associated costs.
Result
- Significant Cost Savings: Our solution is roughly 1,000 times more cost-effective than the alternatives (~$0.30 instead of ~$290).
- Rapid Deployment: Our partner firm was able to quickly deliver essential dashboards and analytics to the restaurant chain.
- Enhanced Flexibility & Control: The client has full access to the code, thank to the open source nature of dlt. This allows them to independently update or extend the solution if Square’s API changes or if additional functionalities are needed (e.g., notifications, shifting compute environments) or extend it to other end-clients.
- Scalable and Future-Proof Design: Our implementation not only met the immediate data needs but also laid a solid foundation for future expansion and adaptation to evolving requirements.
Leave a Reply