Full data environment with Google cloud

Build an automated and governed data pipeline on Google Cloud Platform (GCP)

In a data-driven world, the ability to integrate, manage, and visualize information from multiple sources is crucial for strategic decision-making. This article presents a comprehensive, automated, and governed solution that leverages the serverless and managed tools of Google Cloud Platform (GCP).

Scenario

We want to regularly extract data (sales, customers) from external APIs, store it, transform it, ensure its quality and security, and finally make it accessible to decision-makers through interactive dashboards.

Proposed workflow

Cloud Functions / Cloud Run → Cloud Scheduler → Cloud Storage → Cloud Functions → BigQuery → Dataplex → Looker → Looker Studio

Step-by-step decomposition

Step 1: Data Extraction with Cloud Functions / Cloud Run

• Cloud Functions :
  • For simple to moderate event-driven tasks (languages Python, Node.js, Go).
  • Make API calls, manage authentication (Secret Manager), pagination, and errors, then store the data in Cloud Storage (GCS).
• Cloud Run :
  • Ideal for complex logic or requiring Docker containers.
  • Expose an HTTP endpoint triggered by Cloud Scheduler, extracts the data, and sends it to GCS.
• Security: Store API keys and secrets in Secret Manager. Ensure limited IAM permissions.
  
  

Step 2: Orchestration with Cloud Scheduler

• Objective: Automate the periodic triggering of data extraction.
• Operation: Set up tasks (cron jobs) triggering Cloud Functions or Cloud Run via HTTP at specific intervals.
  
  

Step 3: Storing raw data with Cloud Storage

• Objective: Durable and reliable storage of extracted data.
• Recommended organization: Clearly structure the buckets (e.g., by source, type of data, date).
• Recommended formats: JSON, Avro, Parquet for efficiency and flexibility.
• Lifecycle: Set up automated management (archiving or deletion after a specified period).

Step 4: Automated loading into BigQuery with Cloud Functions

• Objective: Automatically load data from GCS into BigQuery.
• Operation: A Cloud Function automatically responds to new files uploaded to GCS and initiates a load into BigQuery tables (staging tables).
• Alternative: Publish in Pub/Sub for more decoupling.

Step 5: Data Transformation in BigQuery

• Objective: Transform and optimize data for analysis.
• ETL Process (Extract, Transform, Load):
  • Initial loading (staging).
  • Powerful SQL transformation directly in BigQuery (cleaning, joining, aggregation, application of business logic).
  • Storage of transformed data in optimized analytical tables (partitioned, clustered).

Step 6: Governance with Dataplex

• Objective: Centralize the management, discovery, and governance of data.
• Key features:
  • Organization in Lakes, Zones, and Assets (GCS, BigQuery).
  • Automated and enrichable data catalog.
  • Proactive data quality management.
  • Automatic data lineage.
  • Centralized IAM security.

Step 7: Semantic Modeling with Looker

• Objective: Create a layer of governance for business metrics.
• LookML Approach:
  • Define unique views, dimensions, measures, and joins.
  • Ensure a consistent definition of metrics across the entire company.
  • Allow analysts to easily explore data without writing SQL.
    
    

Step 8: Visualization with Looker Studio

• Objective: Present insights clearly through interactive dashboards.
• Connection via Looker: Use the Looker connector in Looker Studio to take advantage of the governed and centralized metrics defined by LookML.
• Advantages: Intuitive, shareable, and interactive visualizations.

Benefits of the proposed approach

• Serverless and scalable: No need for manual infrastructure management.
• Complete automation: Reduction of manual tasks through Cloud Scheduler and Cloud events.
• Centralized governance: Increased control and visibility with Dataplex and Looker.
• Integrated security: Secure management of secrets and controlled access via IAM.
• Single source of truth: Consistency of data definitions and metrics through BigQuery and Looker.
• Integrated ecosystem: Fluidity and ease of integration within GCP.

Points of caution and alternatives​

• Complex orchestration: For advanced needs, consider Cloud Composer (Airflow).
• Monitoring: Integrate Cloud Logging and Cloud Monitoring to track and diagnose.
• Cost Management: Monitor the costs associated with storage, queries, and serverless executions.
  

Conclusion

This modern GCP pipeline combines automation, governance, and performance to transform raw data into actionable and secure insights, effectively democratizing access to strategic information.