Tutorial
5 min read

From spreadsheets to automated data pipelines - and how this can be achieved with support of Google Cloud

CSVs and XLSXs files are one of the most common file formats used in business to store and analyze data. Unfortunately, such an approach is not scalable and it becomes more and more difficult to provide all team members access to one common file in which they can cooperate and share the results of their work with different teams.

Surely, there are available solutions to implement  in the spreadsheet files in real-time, but it’s still difficult to share data with different teams, process it or decide about the target data format, especially when using autodetection data formatting.

Where should we start?

The first aspect is about preparing the landscape of all processed spreadsheets. We start with the most important thing: understanding the data, verifying which information is important for target users and therefore  possibly reducing  the amount of processed data by deleting unused data.

The second part is focused on understanding how to create new spreadsheets - can we automate that step? Can it be only done by users manually? How frequently is data uploaded? How can we verify if there are any changes in the data schema?

When we have defined the logistics and we know what the input and desired output will be, we can hop over to the next step to define how to process data, create some aggregations for example, save data in the target database or storage, clean data and how to deliver output to the target users.

It is necessary to mention that we must know if there are any tools used by different teams to analyze or visualize data further. If users utilise one solution, it might be worth implementing in our new process to simplify the users’ onboarding process.

The last step is about adding a monitoring layer. Who can take this action if there's a problem with source data and how should we notify the analysts? How can we check data quality? What should we do to avoid human error in the case of a manual process? We should implement metrics reporters to our application and queries to detect incorrect records or those  with data that is too varied. We can create alerts and dashboards based on the findings.

The multistage process 

Public cloud such as Google Cloud Platform helps companies to improve their data pipelines and move quickly from local Excel development to scalable tools. It makes work faster, more efficient and with no human errors or problems with data formatting.

The first step is about data ingestion. The perfect place to store raw, unprocessed data is Google Cloud Storage. Users can upload data there or add a sync script between Cloud Storage and some remote drives. Here we start the journey with process alignment and data integration from multiple sources.

For data processing pipelines, we can go with multiple solutions. Due to the different use cases in each project, the best way is to create a custom Python script(s) to process data while the scripts themselves can be scheduled by tools like Google Cloud Composer (managed Apache Airflow), self-managed Apache Airflow, Google Cloud Tasks, Google Cloud Scheduler or even a mix of Cloud Pub/Sub with Cloud Functions.

In the example scenario, we use Composer with Python scripts executed on the Kubernetes pods of the Composer that is the most flexible solution and can easily be extended in the future.

As the final part of the CSVs and XLSXs processing platform, we need to ingest processed data somewhere. It depends on the exact use cases, the most common of which can be solved by inserting data into BigQuery which works perfectly as the data warehouse and can be used as the engine for Business Intelligence. The performance is great.

Last but not least, everything must be managed by the Infrastructure-as-a-Code. A mix of Terraform and CICD tools like GitHub Actions or GitLab Ci helps in making it happen fast and provides possibilities to easily manage infrastructure. If you want to read something more about terraform, check our blog post  “Terraform your Cloud Infrastructure”.

We also need to mention the monitoring layer. It's powered by Cloud Monitoring, Cloud Logging and BigQueries tables in which we can store information about potential errors in the source data. It can be visualized in Data Studio or a similar tool, while alerts can be sent via email to the stakeholders who can then take action.

Automate work and simplify the processes with Google Cloud

Another benefit of this solution is that it's not expensive. It delivers High Availability and can easily be scaled up, depending on the company's needs and the complexity of the next tasks that must be implemented by the processing platform. Here's an example of how we can quickly move from local Excel development to the automated cloud environment to simplify data management and start data-driven development in the cloud.

local-excel-development-the-automated-cloud-environment-simplify-data-management

Would you like to chance your spreadsheet files to automated data pipelines with Google Cloud? Let’s discuss about this, contact us!

big data
technology
google cloud platform
cloud
data pipelines
GCP
8 February 2022

Want more? Check our articles

data driven fast track 3 steps make you data driven company
Tech News

Data-driven fast-track: 3 steps to make your company more data-driven

Hardly anyone needs convincing that the more a data-driven company you are, the better. We all have examples of great tech companies in mind. The…

Read more
getindata big data blog apache spark iceberg
Tutorial

Apache Spark with Apache Iceberg - a way to boost your data pipeline performance and safety

SQL language was invented in 1970 and has powered databases for decades. It allows you not only to query the data, but also to modify it easily on the…

Read more
big data technology warsaw summit 2020 getindata
Big Data Event

Review of presentations on the Big Data Technology Warsaw Summit 2020

It’s been exactly two months since the last edition of the Big Data Technology Warsaw Summit 2020, so we decided to share some great statistics with…

Read more
1sK7ModpT4v02ujZ379Samg
Tech News

Celebrating GetinData’s Inclusion on Clutch’s Lists of Top Big Data and IoT Companies!

Founded by former Spotify data engineers in 2014, GetInData consists of a team of experienced and passionate Big Data veterans with proven track of…

Read more
getindata 2021 lets celebrate our achivements big data world blog

GetInData in 2021 - let’s celebrate our achievements in the Big Data world!

The year 2021 passed in the blink of an eye and the time has come to summarize our goals at GetinData and define our challenges for the next year…

Read more
lean big data 1
Tutorial

Lean Big Data - How to avoid wasting money with Big Data technologies and get some ROI

During my 6-year Hadoop adventure, I had an opportunity to work with Big Data technologies at several companies ranging from fast-growing startups (e…

Read more

Contact us

Interested in our solutions?
Contact us!

Together, we will select the best Big Data solutions for your organization and build a project that will have a real impact on your organization.

The administrator of your personal data is GetInData Poland Sp. z o.o. with its registered seat in Warsaw (02-508), 39/20 Pulawska St. Your data is processed for the purpose of provision of electronic services in accordance with the  Terms & Conditions. For more information on personal data processing and your rights please see  Privacy Policy.

By submitting this form, you agree to our Terms & Conditions and Privacy Policy