How to set up my Yelposhere data engineering project on GCP?
Published: 2023-04-17 07:14:09

<h1 id="heading-what-is-yelposphere">What is YelpoSphere?</h1>
<p>YelpoSphere is a data pipeline that <em>extracts, transforms, and loads</em> data from Yelp's public dataset into a GCP environment and performs various analyses on the data. The pipeline combines <strong>GCS, BigQuery, Dataproc, Cloud Composer, Cloud Run and Cloud Build</strong> for data processing.</p>
<p><strong>YelpoSphere helps users provide insights into the restaurant business.</strong></p>
<p>It performs the following tasks:</p>
<ul>
<li><p>Loads the Yelp data into the <strong>GCS</strong> bucket.</p>
</li>
<li><p>Clean, preprocess and ingest data from GCS to <strong>BigQuery</strong> using <strong>Dataproc</strong>.</p>
</li>
<li><p>Uses <strong>DBT</strong> to normalize the data and performs exploratory data analysis (EDA) to identify trends and insights.</p>
</li>
<li><p>Visualizes the results using <strong>Google Data Studio</strong>.</p>
</li>
<li><p>Orchestrates different tasks using <strong>Cloud Composer</strong>.</p>
</li>
</ul>
<p>This is also my capstone project for the <a target="_blank" href="https://github.com/DataTalksClub/data-engineering-zoomcamp">Data Engineering Zoomcamp 2023</a>!</p>
<div class="embed-wrapper"><div class="embed-loading"><div class="loadingRow"></div><div class="loadingRow"></div></div><a class="embed-card" href="https://github.com/Aditya-Gupta1/yelposphere">https://github.com/Aditya-Gupta1/yelposphere</a></div>
<p> </p>
<h1 id="heading-wheres-the-data-coming-from">Where's the data coming from?</h1>
<p>The dataset is taken from the <a target="_blank" href="https://www.yelp.com/dataset">Official Yelp Open Dataset</a> website. It is a subset of Yelp's businesses, reviews, and user data for personal, educational, and academic purposes. It is available in JSON files.</p>
<p>Here's a list of files available:</p>
<ul>
<li><p><code>business.json</code>: Contains business data including location data, attributes, and categories.</p>
</li>
<li><p><code>reviews.json</code>: Contains full review text data including the user_id that wrote the review and the business_id the review is written for.</p>
</li>
<li><p><code>user.json</code>: User data including the user's friend mapping and all the metadata associated with the user.</p>
</li>
<li><p><code>checkin.json</code>: Checkins on a business.</p>
</li>
<li><p><code>tip.json</code>: Tips are written by a user of a business. Tips are shorter than reviews and tend to convey quick suggestions.</p>
</li>
</ul>
<p>For a detailed description of the schema and examples, please visit the <a target="_blank" href="https://www.yelp.com/dataset/documentation/main">official documentation</a> website.</p>
<h1 id="heading-what-is-the-tech-stack">What is the tech stack?</h1>
<ul>
<li><p><strong>Data Storage:</strong> <a target="_blank" href="https://cloud.google.com/storage">Google Cloud Storage (GCS)</a></p>
</li>
<li><p><strong>Data Processing:</strong> <a target="_blank" href="https://cloud.google.com/dataproc">Dataproc</a></p>
</li>
<li><p><strong>Data Transformations:</strong> <a target="_blank" href="https://www.getdbt.com/">Data Build Tool (DBT)</a></p>
</li>
<li><p><strong>Workflow Orchestration:</strong> <a target="_blank" href="https://cloud.google.com/composer">Cloud Composer</a></p>
</li>
<li><p><strong>Infrastructure:</strong> <a target="_blank" href="https://www.terraform.io/">Terraform</a></p>
</li>
<li><p><strong>Programming Language:</strong> <a target="_blank" href="https://www.python.org/">Python</a></p>
</li>
<li><p><strong>Data Visualization:</strong> <a target="_blank" href="https://datastudio.withgoogle.com/">Google Data Studio</a></p>
</li>
<li><p><strong>Extras:</strong> <a target="_blank" href="https://cloud.google.com/build">Cloud Build</a>, <a target="_blank" href="https://cloud.google.com/run">Cloud Run</a></p>
</li>
</ul>
<h1 id="heading-what-does-the-pipeline-look-like">What does the pipeline look like?</h1>
<p>Here's a complete overview of the pipeline:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680953641131/634d3679-d647-40e7-a29f-bec5c03110fe.png" alt class="image--center mx-auto" /></p>
<p>When an airflow DAG starts:</p>
<ul>
<li><p>It copies the data from a public GCS bucket into the project's data lake.</p>
</li>
<li><p>It will wait for files to come in the GCS bucket's <code>data/raw</code> folder.</p>
</li>
<li><p>Once the files are received, it submits Dataproc jobs to the cluster to ingest data into BigQuery raw tables.</p>
</li>
<li><p>Once the data is ingested into raw tables, it will send a GET request to Cloud Run which will execute DBT jobs.</p>
</li>
<li><p>DBT will perform transformations and load the data into a final dataset, making it ready for visualization.</p>
</li>
</ul>
<p>This final dataset acts as a source for Google Data Studio which creates a dashboard using it. On the other hand:</p>
<ul>
<li><p>When commits are pushed to the main branch of the repo, a new Cloud Build is triggered.</p>
</li>
<li><p>This creates a new docker image that gets deployed on Cloud Run.</p>
</li>
</ul>
<p>This ensures that DBT commands perform up-to-date transformations.</p>
<blockquote>
<p><strong>Note</strong>: Cloud Build and Cloud Run are used to dockerize the DBT project and run DBT commands. This is required because Airflow does not have any convenient operators to run DBT core commands. And scheduling DBT cloud jobs is not supported in the FREE plan of DBT.</p>
<p>In short, it is a workaround to orchestrate DBT with Cloud Composer.</p>
</blockquote>
<h1 id="heading-what-do-i-need-to-run-this-pipeline">What do I need to run this pipeline?</h1>
<h2 id="heading-operating-system">Operating System</h2>
<p>Prefer <strong>Linux</strong> for this data pipeline. This is because Terraform throws EOF errors on Windows unexpectedly, causing inconvenience.</p>
<p>You can use Google VM in case you're having a Windows machine.</p>
<h2 id="heading-tools">Tools</h2>
<ul>
<li><p><strong>Python</strong></p>
</li>
<li><p><strong>Terraform</strong></p>
</li>
<li><p><strong>Git</strong></p>
</li>
</ul>
<p>Refer to the following blog for installation instructions on Python (Anaconda) and Terraform:</p>
<div class="embed-wrapper"><div class="embed-loading"><div class="loadingRow"></div><div class="loadingRow"></div></div><a class="embed-card" href="https://itsadityagupta.hashnode.dev/setting-up-the-development-environment-on-google-virtual-machine">https://itsadityagupta.hashnode.dev/setting-up-the-development-environment-on-google-virtual-machine</a></div>
<p> </p>
<h2 id="heading-gcp-resources">GCP Resources</h2>
<ul>
<li><p>A GCP Project</p>
</li>
<li><p>A service account</p>
</li>
<li><p>GCloud CLI</p>
</li>
</ul>
<h3 id="heading-setting-up-the-service-account">Setting up the service account</h3>
<p>You can refer to the following blog for general instructions on creating a service account and managing keys:</p>
<div class="embed-wrapper"><div class="embed-loading"><div class="loadingRow"></div><div class="loadingRow"></div></div><a class="embed-card" href="https://itsadityagupta.hashnode.dev/setting-up-a-gcp-service-account">https://itsadityagupta.hashnode.dev/setting-up-a-gcp-service-account</a></div>
<p> </p>
<p>But, here are the roles that are required for this pipeline:</p>
<ul>
<li><p>Cloud Run</p>
</li>
<li><p>Cloud Run Service Agent</p>
</li>
<li><p>Composer Worker</p>
</li>
<li><p>Compute Admin</p>
</li>
<li><p>Dataproc Administrator</p>
</li>
<li><p>Environment and Storage Object Administrator</p>
</li>
<li><p>Secret Manager Secret Accessor</p>
</li>
<li><p>Storage Admin</p>
</li>
<li><p>Storage Object Admin</p>
</li>
<li><p>Storage Transfer Admin</p>
</li>
</ul>
<p>Additionally, you'll need to give one more access to this service account:</p>
<ul>
<li><p>In your GCP console, go to IAM &gt; Service Accounts and select your service account.</p>
</li>
<li><p>Go to Permissions &gt; Grant Access</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680951837379/97f2ebdf-2d5f-461c-bd95-5bb5de0bafef.png" alt class="image--center mx-auto" /></p>
<ul>
<li><p>You need to add your default compute service account as a new principal. It follows the format <code>{</code><a target="_blank" href="mailto:your-project-number%7D-compute@developer.gserviceaccount.com"><code>your-project-number}-compute@developer.gserviceaccount.com</code></a> and you can locate your project number in the project settings. Once you have identified your project number, simply add the default compute service account as a new principal and grant it the <code>Cloud Composer v2 API Service Agent Extension</code> role.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680952080592/915eaeae-c844-4ba0-8831-40d5c090bb9f.png" alt class="image--center mx-auto" /></p>
<p>  `</p>
</li>
</ul>
</li>
</ul>
<blockquote>
<p><strong>Note:</strong> You should have an environment variable named <code>GOOGLE_APPLICATION_CREDENTIALS</code> which points to the location of the key file of the service account. This is necessary for Terraform to manage resources on your behalf.</p>
<p>Command: <code>export GOOGLE_APPLICATION_CREDENTIALS=/path/to/keyfile.json</code></p>
</blockquote>
<h3 id="heading-setting-up-the-gcloud-cli">Setting up the GCloud CLI</h3>
<ul>
<li><p>Download the installer from <a target="_blank" href="https://cloud.google.com/sdk/docs/downloads-interactive#windows">here</a> and install the CLI app.</p>
</li>
<li><p>Run the following command (assuming that you have the <code>GOOGLE_APPLICATION_CREDENTIALS</code> environment variable pointing to your service account key file):</p>
</li>
</ul>
<pre><code class="lang-bash">gcloud auth activate-service-account --key-file <span class="hljs-variable">$GOOGLE_APPLICATION_CREDENTIALS</span>
</code></pre>
<p>If you're on Windows, you can provide the path to the key file instead of the environment variable.</p>
<h1 id="heading-setup-your-pipeline">Setup your pipeline</h1>
<blockquote>
<p>Tip: Throughout the process, enable any Google APIs that you're prompted to.</p>
</blockquote>
<h2 id="heading-step-0-get-the-code">Step 0: Get the code</h2>
<ul>
<li><p>Fork the <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere">repository</a> (This is important!)</p>
</li>
<li><p>Clone the forked repo.</p>
</li>
</ul>
<pre><code class="lang-bash">git <span class="hljs-built_in">clone</span> &lt;forked-repo-git-url&gt;
<span class="hljs-built_in">cd</span> yelp-gcp-infrastructure/terraform
</code></pre>
<h2 id="heading-step-1-modify-the-dbt-env-variable">Step 1: Modify the DBT env variable</h2>
<p>In the <code>dbt/Dockerfile</code>, modify the value of the <code>GCP_PROJECT</code> environment variable with the value of your project id. (Specifically, <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere/blob/7d56be4d5d2df90909f9bb5389aa9872b3c3a9b4/dbt/Dockerfile#L6">this</a> line)</p>
<p>Commit and push the changes to your repository.</p>
<h2 id="heading-step-2-save-the-sa-key-file-in-secret-manager">Step 2: Save the SA key file in Secret Manager</h2>
<p>DBT will require the service account key file to connect to BigQuery and execute transformations. This key file can be stored as a secret in the Secret Manager and accessed inside DBT's <code>profile.yml</code> file from Cloud Run.</p>
<ul>
<li><p>Go to Secret Manager &gt; Create Secret. (You may need to enable the Secret Manager API)</p>
</li>
<li><p>Give <code>dbt-service-keyfile</code> as the name of the secret. (Name should exactly match this!)</p>
</li>
<li><p>Click on Browse and upload the service account key file as the Secret Value.</p>
</li>
<li><p>Click Create.</p>
</li>
</ul>
<p>Here's the secret overview</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680960607367/42c804a6-5457-4c41-8a44-23750e4f2994.png" alt class="image--center mx-auto" /></p>
<p>:</p>
<h2 id="heading-step-3-setup-cloud-run-and-cloud-build-for-dbt">Step 3: Setup Cloud Run and Cloud Build for DBT</h2>
<ul>
<li><p>Go to Cloud Run &gt; Create a Service.</p>
</li>
<li><p>Select <code>Continuously deploy new revisions from a source repository</code> &gt; Setup with Cloud Build.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680957392471/e33ddaab-36bb-4cb6-bdc9-65c1c79dc644.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Click on "Manage connected repositories", sign in to your GitHub account and install the Build Agent on the forked repo only. Then click on Next.</p>
<p>  <img src="https://miro.medium.com/v2/resize:fit:575/1*VXl-9zNHlnsYopajDBf8jA.png" alt /></p>
</li>
<li><p>Select "Dockerfile" as the build type and provide the file path as <code>/dbt/Dockerfile</code>. Click Save.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680957717426/e886ec94-252c-4f3b-8fec-9c22259acafd.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Give your Cloud Run service a name. Pick a region. This region should be the same as that of your BigQuery dataset (to be configured in the further steps). And then select "Require Authentication".</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680958579346/e828e2f9-a425-401a-8062-c25c735a551f.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Expand the "<strong>Container, Networking, Security</strong>" section. Inside the <strong>Container</strong> section, choose Maximum requests per container to be 1.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680960990542/50355e42-c825-4edb-8726-4da0182cf2f3.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Under the Secrets section, select "Reference a Secret". Select the secret you created in the previous step. Reference method as "mounted as volume", and mount path as <code>secrets</code>.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680961171244/9c8a1b84-8085-49bb-aa2e-d8acbe2631a6.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Now, under the <strong>Security</strong> section, select your service account and click Create.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680961263407/692bc44a-0096-45b5-b52d-cec3f7c67eeb.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Your Cloud Run service is ready. Go inside your service and you'll find an endpoint for this service. Copy this as you'll need it in future steps.</p>
</li>
</ul>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1680961483349/db81d52d-3a88-4853-9928-0283a8f858f5.png" alt class="image--center mx-auto" /></p>
<p>Now you have set up your DBT code to run.</p>
<blockquote>
<p>A drawback of this workaround is that you can't view the data lineage or documentation. If you want to view that, you can always set this up using the DBT cloud as described in the course video:</p>
</blockquote>
<div class="embed-wrapper"><div class="embed-loading"><div class="loadingRow"></div><div class="loadingRow"></div></div><a class="embed-card" href="https://www.youtube.com/watch?v=rjf6yZNGX8I&amp;list=PL3MmuxUbc_hJed7dXYoJw8DoCuVHhGEQb&amp;index=37">https://www.youtube.com/watch?v=rjf6yZNGX8I&amp;list=PL3MmuxUbc_hJed7dXYoJw8DoCuVHhGEQb&amp;index=37</a></div>
<p> </p>
<h3 id="heading-optional-understanding-dbt-transformations">(Optional) Understanding DBT Transformations</h3>
<p>Let me walk you through the use of DBT in this pipeline. It is responsible for:</p>
<ul>
<li><p>Partitioning <code>stg_businesses</code> table on the <code>is_open</code> column. This will help others in querying this data on the basis of open or closed businesses, as per the requirement.</p>
</li>
<li><p>Creating different data models to answer business questions that will be used to create the final dashboard.</p>
</li>
</ul>
<p>You can go through the data model <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere/tree/main/dbt/models">code</a> to understand how exactly they look.</p>
<h2 id="heading-step-4-build-your-infrastructure-using-terraform">Step 4: Build your infrastructure using Terraform</h2>
<p>Go to <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere/blob/main/terraform/terraform.tfvars"><code>terraform/terraform.tfvars</code></a> file and modify the fields marked with <strong>"TODO"</strong>, with appropriate values.</p>
<p>As of now, all the resources are created in the <code>asia-south1</code> region except for Cloud Composer which is created in <code>us-central1</code> region (I suggest not changing the region for Cloud Composer). <em>For the rest of the resources, assign the</em> <strong><em>same</em></strong> <em>region if you want to change it.</em></p>
<p>The GCS buckets should have a globally unique name which is why you <strong>may</strong> have to rename them in the file.</p>
<p>The terraform variable <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere/blob/a79bc8d2713bd6a0374c9393ceedf20a34fa5d5b/terraform/terraform.tfvars#L9">"datalake_name"</a> will be the name of the GCS bucket for your pipeline which will contain all your data and spark jobs. You'll need it in the next step.</p>
<p>Also, make sure to change the service account for cloud composer (in that same file) to your service account's email.</p>
<p>After modifying the necessary fields, run the terraform command:</p>
<pre><code class="lang-bash">terraform apply
</code></pre>
<p>Enter "yes" when prompted and wait for completion.</p>
<p>Here's what Terraform is doing behind the scenes:</p>
<ul>
<li><p>Enables the required GCP APIs to work with different resources.</p>
</li>
<li><p>Creates the project's data lake with Spark ingestion scripts inside the <code>scripts</code> folder.</p>
</li>
<li><p>Creates BigQuery dataset and tables with defined schema.</p>
</li>
<li><p>Creates a Dataproc cluster.</p>
</li>
<li><p>Creates a Cloud Composer environment and uploads the Airflow DAG into it.</p>
</li>
<li><p>Once the resources are created, it generates output with a list of resources created and some metadata that is required in the later steps. (All the output values are defined in <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere/blob/main/terraform/outputs.tf">this</a> file.)</p>
</li>
</ul>
<h3 id="heading-a-note-on-cloud-composer">A note on Cloud Composer</h3>
<p>Cloud Composer generally takes around 20-30 mins for successful creation. But during the development, I observed that it might fail randomly due to timeout issues at the backend and throw errors stating permission issues. When it fails, it takes around 45-50 mins to throw the error. 😤</p>
<p>Now, if you have correctly set up your service account as mentioned earlier, you have no issues with permissions. In such cases, you can try running Terraform once again by changing the name and region of the composer environment. I've found <code>us-central1</code> to be the most reliable till now.</p>
<p>Or you can manually create Composer (Composer 2) in the console and give the name, region, and service account and click Create. Once the environment is created, go inside it and click on "Open DAGs Folder".</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681046614113/dc57eb4e-75ec-425d-b055-ad4d6f93ddd3.png" alt class="image--center mx-auto" /></p>
<p>This will open a GCS bucket where the composer stores all the Airflow DAGs. Click on "Upload files" and upload the <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere/blob/main/cloud_composer/ingest_data_dags.py"><code>cloud_composer/ingest_data_dags.py</code></a> file. This contains the DAG for this project.</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681046765632/941f8371-774c-4b08-a6e2-39c8854bfb9e.png" alt class="image--center mx-auto" /></p>
<p>Once the DAG is uploaded, you can move on to the next steps.</p>
<h2 id="heading-step-6-upload-airflow-variables-using-airflow-ui">Step 6: Upload Airflow variables using Airflow UI</h2>
<p>I've used Airflow variables to pass dynamic values to the DAG. These values are related to infrastructure like the BigQuery table names, Dataproc cluster names, the path of the spark jobs etc.</p>
<p>You can get these values from the terraform output. Run the following commands (<strong><em>inside the terraform folder</em></strong>) to get these variables in a JSON file to easily upload them to Airflow.</p>
<pre><code class="lang-bash">terraform output -json &gt; ../tf_output.json
<span class="hljs-built_in">cd</span> ..
python utils/get_variables.py --input tf_output.json --output  airflow_variables.json
</code></pre>
<p>You will get <code>airflow_variables.json</code> file that you can upload to the Airflow UI using the following steps:</p>
<ul>
<li><p>In the composer environment, click on "<strong>Open Airflow UI</strong>". You will be redirected to an Airflow dashboard.</p>
</li>
<li><p>Go to Admin &gt; Variables.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681047323685/e0dbdd5d-9450-4896-be7b-282bcb359bbf.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Click on "Choose File". Select the airflow_variables.json file and click on "Import Variables".</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681047432557/286a0f0b-ef7f-477e-a7cf-f96e930d198f.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>This will upload 16 variables to Airflow.</p>
</li>
<li><p>Similarly, select the <a target="_blank" href="https://github.com/Aditya-Gupta1/yelposphere/blob/main/data_paths_airflow_variables.json"><code>data_paths_airflow_variables.json</code></a> file already present in the repo and import variables from this file as well.</p>
</li>
<li><p>This uploads 5 more variables to Airflow.</p>
</li>
</ul>
<h2 id="heading-step-7-create-a-cloud-run-connection-in-airflow">Step 7: Create a Cloud Run connection in Airflow</h2>
<p>As I mentioned in the overview section, Airflow needs to send HTTP requests to Cloud Run. So you need to create an Airflow HTTP connection for it to connect to Cloud Run's endpoint. Follow the steps to do so:</p>
<ul>
<li><p>In the Airflow UI, under the Admin tab, click on Connections.</p>
</li>
<li><p>Click on the <strong>+</strong> Icon.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681047964319/79dc2ef6-7ae3-4b5f-b04e-636c79f25494.png" alt class="image--center mx-auto" /></p>
</li>
<li><p>Select connection_id as "cloud_run", connection type as "HTTP" and host as your Cloud Run service endpoint. Click on Save.</p>
<p>  <img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681048095167/39de3ecd-a220-493e-9e64-a9329bc71048.png" alt class="image--center mx-auto" /></p>
</li>
</ul>
<p>At this point, you can refresh your Airflow UI until there are no import errors shown at the top of Airflow UI. (It may take a few seconds for errors to resolve)</p>
<p>Before:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681048205256/1e01c5f6-d567-4550-8bc4-600444115a01.png" alt class="image--center mx-auto" /></p>
<p>After:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681048238294/fae15a68-b641-40eb-befa-95b6452592e9.png" alt class="image--center mx-auto" /></p>
<p>This means that your DAG is imported successfully to Airflow and now we just need to run it.</p>
<h2 id="heading-step-8-run-the-airflow-dag">Step 8: Run the Airflow DAG</h2>
<p>Finally, you get to run your DAG and visualize the progress. Click on the <code>process_yelp_data</code> DAG followed by the play button and then "<strong>Trigger DAG</strong>".</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681048751531/4f39bbfe-5ed8-4327-be3d-bd94c61f327f.png" alt class="image--center mx-auto" /></p>
<p>This will start the DAG as you can see on the UI:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681048848423/700b01f8-891b-4b5f-96dc-4e387c4b87be.png" alt class="image--center mx-auto" /></p>
<p>You can also see the DAG under the "Graph" tab:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681561446729/62da7cdd-a03d-4c12-acd6-f3a51040bd37.png" alt class="image--center mx-auto" /></p>
<p>Here's what's happening:</p>
<ul>
<li><p>It gets the data from a public bucket into the data lake.</p>
</li>
<li><p>It checks if the files are present in the location given by airflow variables, which is the data lake URL.</p>
</li>
<li><p>If yes, it will submit a spark job to the Dataproc cluster to load that particular file into BigQuery.</p>
</li>
<li><p>Once the spark job succeeds, the JSON files are deleted.</p>
</li>
<li><p>And finally, a GET request is sent to the Cloud Run endpoint to execute DBT transformations and tests.</p>
</li>
</ul>
<p>You can see all the DBT models created under the <code>yelp_dbt</code> dataset in BigQuery.</p>
<blockquote>
<p>Note that a task may fail, but that is probably because of the less memory in Airflow. In this case, you just have to wait until it retries itself.</p>
</blockquote>
<p>And here it gets completed:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681049357832/47a981c9-74e1-4a05-b3d8-b0dd2283411f.png" alt class="image--center mx-auto" /></p>
<p><img src="https://media.tenor.com/eIV3XiQyw4AAAAAC/you-did-it-willy-wonka-and-the-chocolate-factory.gif" alt="You Did It GIFs | Tenor" class="image--center mx-auto" /></p>
<h2 id="heading-step-9-optional-create-a-dashboard-with-google-data-studio">Step 9 (Optional): Create a dashboard with Google Data Studio</h2>
<p>I have used Google Data Studio to create a dashboard on top of the DBT models. You can either create your dashboard or simply view my dashboard here:</p>
<div class="embed-wrapper"><div class="embed-loading"><div class="loadingRow"></div><div class="loadingRow"></div></div><a class="embed-card" href="https://lookerstudio.google.com/reporting/03290c4d-e20a-4e61-9978-f4e0ae893ff5">https://lookerstudio.google.com/reporting/03290c4d-e20a-4e61-9978-f4e0ae893ff5</a></div>
<p> </p>
<p>Here's a screenshot of my dashboard in case you're having any trouble viewing it:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1681049724843/e06b9ea4-6c70-450d-ada6-e8680a03df1e.png" alt class="image--center mx-auto" /></p>
<p>Pretty cool, isn't it? 😉</p>
<p><img src="https://media2.giphy.com/media/IbUUbU4xUDJWcgGMGP/giphy.gif" alt="Yeah-i-know GIFs - Get the best GIF on GIPHY" class="image--center mx-auto" /></p>
<p>PS: I'll leave out the steps to create a dashboard on google data studio as I believe it is very convenient and self-explanatory since it is a drag-and-drop tool.</p>
<h1 id="heading-clean-up-your-gcp-resources">Clean up your GCP Resources!</h1>
<p>Well, this is important because:</p>
<p><img src="https://y.yarn.co/5dd375ae-7f47-4555-a9ef-a0a7305cdc18_text.gif" alt="YARN | It costs money. | Moonstruck (1987) | Video gifs by quotes |  5dd375ae | 紗" class="image--center mx-auto" /></p>
<p>And if you don't clean it, then ...</p>
<p><img src="https://media0.giphy.com/media/xAe3BXJVr9tJscgei5/200w.gif?cid=6c09b952gubsg4c6fxuqqengghnw9arr5mmkd163us6ncnc3&amp;rid=200w.gif&amp;ct=g" alt="Hey-that-costs-money GIFs - Get the best GIF on GIPHY" class="image--center mx-auto" /></p>
<p>So better clean it up as soon as you're done checking out the project. Here are the steps to do it:</p>
<ul>
<li><p>Inside the <code>terraform</code> folder, run the <code>terraform destroy</code> command and enter yes when prompted. It'll take a few minutes.</p>
</li>
<li><p>Once done, check your GCS buckets and delete any remaining buckets.</p>
</li>
</ul>
<p>(Generally, Composer's GCS bucket doesn't get deleted with Terraform destroy and has to be manually deleted.)</p>
<p>Then go to <strong>Cloud Build, Cloud Run, Artifact Registry and Secret Manager</strong> to delete everything present there.</p>
<p>That's it.</p>
<h1 id="heading-now-what">Now What?</h1>
<p>Since now you got to build the data pipeline (and destroy it 😂), here are a few things that I think can be improved:</p>
<ul>
<li><p>Better code structure for Terraform.</p>
</li>
<li><p>More data transformations on DBT.</p>
</li>
<li><p>Adding more data quality tests on DBT models.</p>
</li>
<li><p>Research about any easier way to orchestrate DBT with Airflow (although I did a LOT of research and asked a lot of different communities about this)</p>
</li>
<li><p>CI/CD Pipeline</p>
</li>
</ul>
<h1 id="heading-conclusion">Conclusion</h1>
<p>This was my capstone project for the Data Engineering Zoomcamp.</p>
<p>I hope you like it. If you have any questions about the project, face any problems while following along or have a suggestion for me, feel free to comment or drop me a DM on <a target="_blank" href="https://twitter.com/itsadityagupta">Twitter</a> or <a target="_blank" href="https://www.linkedin.com/in/itsadityagupta/">Linkedin</a>.</p>
<p><img src="https://news-legacy-vendors.abc-prod.net.au/scribble/2971157/5356cc15-7d8f-4309-8330-89dd39bca58f.gif" alt="COVID updates: Victoria to reach 70 per cent first doses target today,  restrictions ease in 12 NSW regions - ABC News" class="image--center mx-auto" /></p>
<div class="hn-embed-widget" id="linkedin-follow"></div></br><p style="color:#757CF9;">This article is published w/ <a target="_blank" href="https://scattr.io?ref=dev">Scattr  ↗️</a></p>