# Cloud Storage to Bigquery: Data Warehousing and Ingestion ## Introduction In today's data-driven world, effectively managing and analyzing large datasets has become crucial for business success. Utilizing Cloud Storage and BigQuery is a fundamental skill for data engineers, analysts, and organizations looking to leverage their data for powerful insights. This process, known as data warehousing and ingestion, forms the backbone of modern data analytics infrastructure. In this post, we'll explore the seamless process of transferring data from Google Cloud Storage to BigQuery, understand why this integration is vital for businesses, and provide essential tips for optimizing your data pipeline. Whether you're a data professional or a business leader looking to enhance your data analytics capabilities, this guide will help you master the fundamentals of data warehousing with Google Cloud Platform (GCP). ## What is Data Warehousing and Ingestion, and Why Does it Matter? Data warehousing is the process of collecting, storing, and managing data from various sources in a centralized repository for analysis and decision-making. Data ingestion, specifically, refers to the process of importing data from different sources into a storage system – in this case, moving data from Cloud Storage to BigQuery. This matters because: * It enables real-time data analysis and reporting * Improves data accessibility across organizations * Enhances decision-making capabilities * Reduces data processing costs and complexity * Ensures data consistency and reliability ## How to Transfer Data from Cloud Storage to BigQuery ### 1\. Create a Project within your Google Console On the upper left hand side of your google console, a section with three dots will appear as your project. Make a project with a corresponding name, make sure you also attach a payment method in any form to get to access all google products. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729058562600/98553485-86cd-417b-8eee-60c9f177058b.png align="center") ### 2\. Setting up Google Storage Head over the upper left side of the screen, and click the hamburger icon to see some pinned products. There you will see a section called “Cloud Storage” This is where you will upload your dataset or whatever form of data you have. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729058618995/1b70be2c-215f-45d8-85cb-ebc185a618bd.png align="center") ### 3\. Creating a Bucket in Google Cloud Storage There are several ways to move data over Google Cloud Storage, but in this case we will just be uploading a 300mb CSV for a quick demo. Click over the create button on the upper left hand side ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729058737048/db6f29a3-97bb-4ce1-9a5c-57ffcc03caf9.png align="center") After clicking Create, you will be redirected towards this UI. Create a unique bucket name and select a region that will be the place for storage of your data. In our case, Singapore might be ideal (P.S. make sure your storage region is same as BigQuery region). We will leave everything else as default for now. You will then be prompted for “Enforce public access prevention on this bucket”. Just click okay then proceed. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729058974990/7ba7843a-d226-4067-bc6a-b5abd2ce1d94.png align="center") We will then be redirected towards this UI. This is the part where we upload files from our local storage or wherever. But in this demo, we will just upload a 300mb csv from local storage. Note that you can do this for any size of data, especially in the cases where you have Big Data ranging terabytes of size. After clicking upload, you will have a load job started. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729059043649/63e80873-4ae9-42cd-87c9-1131f7545b49.png align="center") After a few minutes, completed load job will look like this: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060262974/5ffbb44e-1c15-4b03-9b1b-ac7ee8897dba.png align="center") ### 4\. Using Cloud Storage data for Big Query analysis Let’s head over the upper left corner again and click the hamburger icon, there we will see the Big Query option. Click the Big Query Option ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060315819/f253e232-80ce-4308-a139-3ead20587b6a.png align="center") You will then be greeted with this UI, make sure to click “Add” button on the upper left portion of the screen. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060414531/df96c330-8da4-42d4-831d-2ebfac179207.png align="center") Click the Google Cloud Storage option ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060433003/5c678108-6eb9-4806-a89e-47cd58c802b9.png align="center") Click browse and find the file we just uploaded inside Google Cloud Storage, in our case, its the “btcusd\_1-min\_data.csv” in the bucket named “demo\_bucket\_harvey” we just created ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060470259/f1da5df8-f0b3-4d9c-b8cb-4bc3232cadbc.png align="center") Head over the destination portion, and create a dataset. For our case, lets create a dataset named “bitcoin\_data\_datsci” and select a region similar for our storage, in our case its singapore. Leave everything as default and click create dataset. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060606048/fdbd2711-26a7-40c8-be30-8af2aa43f0bf.png align="center") We can then name our table “bitcoin\_data\_datsci\_demo”. Make sure to click auto-detect schema unless you want to define a schema of your own. Its easier to just auto-detect it for the sake of practice. Leave everything else by default and click create table. A load job will then be created ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060731320/c655bbee-a704-41ac-917c-7a90383091d6.png align="center") It will then notify you of the table made. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729060761590/9bf9fc28-09c7-4daa-b6ff-59432b5740c2.png align="center") Let us try for a quick Data Exploration using jupyter in Big Query. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729061304272/2022ac9f-9b8c-4cd7-80b3-f2df6b23ca9d.png align="center") Make sure to enable ALL APIs. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729061451307/5531caa5-c315-407e-8011-b2ebf977cde9.png align="center") We are then met with this Jupyter notebook, let’s just query the first 20 rows just to get to see the dataset we have just uploaded (Note that using Limit clause or head for jupyter won’t reduce the amount of data scanned by the query). ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1729061645208/2a16433a-7be4-4f02-a9ef-0e30e1dc4ea7.png align="center") ## Tips and Reminders for Successful Data Warehousing 1. **Optimize File Formats** * Use compressed files when possible * Consider columnar formats like Parquet * Maintain consistent schema definitions 2. **Plan for Scale** * Implement proper partitioning * Use clustering for better query performance * Monitor costs and optimize accordingly 3. **Maintain Data Quality** * Implement data validation checks * Set up monitoring and alerting * Document your data pipeline ## Conclusion Successfully moving data from Cloud Storage to BigQuery is essential for modern data analytics. By following this guide, you've learned the fundamental steps and best practices for implementing a robust data warehousing solution. Remember that efficient data ingestion is the foundation for meaningful data analysis and business insights.