Case: Helping a Financial Sector SaaS Company to Utilize Google Cloud for External Data Integration
What was the challenge?
Customer exports and produces data from CRM/ERP system and wants to import the data to BigQuery. JSON files need data preprocessing and mapping, before it can be inserted into BigQuery. Solution needs to be scalable, as there can be thousands of files in the first bulk imports. Solution needs to be automatically triggered when new files arrive, after the first bulk insert is done.
Main tool to approach the task at hand was proposed to be Dataflow, as it is serveless and scalable.
Cloud Storage Bucket trigger for a new file. Trigger launches Cloud Function that runs Dataflow Template for the new file. Dataflow pre-processes the data and uses its built-in APIs to write data into BigQuery weekly-table. BigQuery Stored Procedure updates the master data on weekly level from weekly-table to master-table. Multiple BigQuery master tables and their weekly-tables involved.
Codento was recommended by another organization as the right vendor for Google Cloud data consulting. Codento reacted fast and provided approipriate proposal and iterated it to fit with the initial needs.
What were the results?
Customer was able to run the Dataflow Pipeline bulk-inserts via Google Cloud Cloud Shell command-line. Dataflow Templates and Cloud Functions handover [ongoing]
Customer was impressed by the first bulk-inserts: When testing with 480 files, (mean size around 4 MB each), the run took less than 15 minutes for a one weekly-table. Customer gained knowledge of Dataflow and BigQuery debugging and logging, and felt that they can proceed to develop something similar now also in-house.
In customer’s own words
“Codento was recommended by another organization as the right vendor for Google Cloud data consulting. Codento reacted fast and provided approipriate proposal and iterated it to fit with our initial needs. We are satisfied with their work so far during the cooperation.”
Ask more about Codento’s data and AI services.