A closer look to Google Cloud Platform

Google Cloud Platform

Google has one of the important cloud out there!

If you understand these, you need to know that there are similar alternatives in other clouds.

Just with the services having a different names: https://console.cloud.google.com/

Data Analytics with GCP

• Bigtable: While Bigtable is a distributed NoSQL database good for very high volumes of data, it’s not a relational database and doesn’t offer the same level of transactional consistency as Cloud Spanner.

• Cloud SQL: Cloud SQL is a relational database service, but it’s not globally distributed in the same way as Cloud Spanner, and it might not be the best choice for applications requiring very high scalability and availability across multiple regions.

BigQuery: BigQuery is a data warehouse designed for analytics and business intelligence, not for transactional workloads or CRUD operations. It’s used for analyzing large datasets, not for serving real-time application requests.

Google DataForm

You’re right, Google Dataform is indeed a data engineering tool. Here’s a short explanation:

Google Dataform:

• Purpose:
  • It’s a service for developing, testing, and deploying data transformations within Google Cloud’s BigQuery.
  • It helps you manage complex SQL-based data pipelines.
  • It enables version control, collaboration, and automated testing of your data transformations.
• Key Features:
  • SQLX: A SQL extension for defining data transformations.
  • Dependency management: Automatically handles the order of execution of your SQL queries.
  • Testing: Allows you to write tests to ensure the accuracy of your data.
  • Version control: Integrates with Git for collaborative development.
  • Scheduling: Allows for the scheduling of data pipeline executions.
• Use Cases:
  • Building data warehouses and data marts.
  • Performing data transformations for analytics and reporting.
  • Automating data pipelines.

/* my_table.sqlx */

{{
  let threshold = 100;
}}

SELECT
  *
FROM
  my_data
WHERE
  value > {{ threshold }};

Open Source Equivalents:

Finding a single, perfect open-source equivalent to Dataform is tricky because Dataform combines several functionalities.

However, here are some key open-source tools that provide similar capabilities:

• dbt (data build tool):
  • This is the closest open-source equivalent.
  • It focuses on data transformations within data warehouses (like BigQuery, Snowflake, and Redshift).
  • It uses SQL and Jinja templating for defining transformations.
  • It also supports testing and documentation.
• Apache Airflow:
  • This is a workflow orchestration tool that can be used to build and manage complex data pipelines.
  • It allows you to define dependencies between tasks and schedule their execution.
  • While not focused exclusivly on SQL transformation, it is very good at orchestrating those transformations.
• SQLMesh:
  • SQLMesh is an open-source data transformation framework that enables collaborative development and simplified operation of data pipelines.
  • It handles incremental data transformations, data quality testing, and environment promotions.
• Prefect:
  • Prefect is a modern workflow orchestration tool designed to make it easy to build, run, and monitor data pipelines. It has a focus on dynamic workflows.

In essence, Dataform is a managed service that simplifies the process of building and managing data transformations in BigQuery. dbt is the strongest open source competitor, and airflow is the strongest open source competitor for the orchestration portion of dataform.

GCS

Google Cloud Storage

The correct sequence of the data storage hierarchy is:

Tables -> File storage -> Block storage

Tables: At the highest level, you have tables in a database. These tables organize data in a structured format with rows and columns.

File storage: Tables are often stored as files on a file system. These files can be organized in various ways depending on the database system.

Block storage: File systems themselves are built on top of block storage. Block storage is the most basic level, where data is stored in raw blocks on physical storage devices (hard drives, SSDs, etc.).

The good thing about GCS is that you can query the content of its files via JHub and Pyspark:

#most likely they will be avro (row based, ~OLTP)
df = spark.read.format("avro").load(
    "gs://cs-odh-data/raw/type=datawarehouse-avrodata/environment=prod/deployment=abcd/table=awsometabletoexplore"
)

Pretty similar to inspecting s3 buckets with python.

Big Query

Simply speaking, BQ is just SQL.

But its actually much more than an interface to write your SQL queries.

Big Query Tricks

Given a table, you should always do cool filters while reading it:

SELECT * FROM `projectID-on-gcp.dataset_ID.table_ID`
--WHERE TIMESTAMP_TRUNC(ts, HOUR) = TIMESTAMP("2024-04-01T12:00:00") 
--LIMIT 100

And you can also script them:

--This refers to the previous hour
Select TIMESTAMP_SUB(TIMESTAMP_TRUNC('2024-04-01T12:00:00', HOUR), INTERVAL 1 HOUR)

When in production, with dataform, you might do it with:

SELECT * FROM `projectID-on-gcp.dataset_ID.table_ID` as abc
WHERE TIMESTAMP_TRUNC(abc.ts, HOUR) = TIMESTAMP_SUB(TIMESTAMP_TRUNC('${dataform.projectConfig.vars.executionDate}', HOUR), INTERVAL 1 HOUR)

Check the Schema of any table with:

SELECT column_name, data_type
FROM `projectID-on-gcp.dataset_ID.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'table_ID'

Once you know it, you can do some data profiling on the field distribution:

SELECT DISTINCT location_id
FROM `projectID-on-gcp.dataset_ID.table_ID`
WHERE location_id IS NOT NULL
LIMIT 3;

See how SQL on BQ compares with PySpark:

SELECT
  CASE
    WHEN customer_speed_tier > 500 THEN '500+'
    WHEN customer_speed_tier > 200 THEN '200-500'
    WHEN customer_speed_tier > 100 THEN '100-200'
    WHEN customer_speed_tier > 50 THEN '50-100'
    WHEN customer_speed_tier >= 0 THEN '0-50'
    ELSE 'Other'
  END AS speed_tier_group,
  COUNT(DISTINCT mac_address) AS count_of_mac_addresses
FROM
  `projectID.datasetID.yourtableID`
GROUP BY
  speed_tier_group
ORDER BY
  count_of_mac_addresses DESC;

from pyspark.sql.functions import countDistinct, when

speed_tier_df = ss.read.format('delta') \
  .load(f'{hdfs_node}/delta/yourtable')

grouped_df = speed_tier_df.withColumn(
    'speed_tier_group',
    when(speed_tier_df.customer_speed_tier > 500, '500+')
    .when(speed_tier_df.customer_speed_tier > 200, '200-500')
    .when(speed_tier_df.customer_speed_tier > 100, '100-200')
    .when(speed_tier_df.customer_speed_tier > 50, '50-100')
    .when(speed_tier_df.customer_speed_tier >= 0, '0-50')
    .otherwise('Other')
) \
.groupBy('speed_tier_group') \
.agg(
    countDistinct('mac_address').alias('distinct_mac')
) \
.sort('distinct_mac', ascending=False)

grouped_df.show(10)

If you have a local DB…you can do cool tricks with GenAI to extract insights:

Chat with Data

Other LangChain use cases

Data Chat Repository

Source Code for DB Chat with Langchain

Big Table

Bigtable is highly consistent for multi-row updates: TRUE.

Bigtable has lesser administrative overhead: TRUE. As a fully managed NoSQL database service, Bigtable handles infrastructure management, including scaling, replication, and failover, reducing administrative overhead.

Bigtable is highly scalable: TRUE. Bigtable is designed to scale massively to handle petabytes of data and massive read/write throughput, making it suitable for demanding applications.

Google Cloud SQL

Allows the end user to enable automatic backup of data: TRUE. Cloud SQL provides the functionality for users to configure and enable automatic backups of their databases.

Cloud SQL enables bug fixing to improve performance: TRUE. Google, as the provider of Cloud SQL, continuously works on bug fixes and performance improvements for the underlying infrastructure and software.

Enable high availability fail over replica: TRUE. Cloud SQL supports high availability configurations with failover replicas to minimize downtime in case of an instance failure.

Cloud Spanner

Consistent Schema: Cloud Spanner enforces a consistent schema across all of its distributed nodes. This means that all reads will see the same schema, and you won’t encounter inconsistencies where different parts of your database have different structures. Schema changes are carefully managed to ensure consistency.

Automatic Replication: Cloud Spanner automatically replicates your data across multiple zones (and optionally, multiple regions) for high availability and fault tolerance. You don’t have to manually configure replication. Spanner handles the distribution and synchronization of data behind the scenes. This ensures that your database remains available even if some zones experience outages.

Transaction Processing: Cloud Spanner is specifically designed for high-volume transaction processing, making it suitable for applications with demanding transactional workloads like those in the retail industry.

Distributed Relational Database: Cloud Spanner is a globally distributed, scalable, and strongly consistent database. It combines the benefits of a relational database (structure, ACID properties) with the scalability of a distributed system.

CRUD Operations: Cloud Spanner excels at handling CRUD (Create, Read, Update, Delete) operations, which are fundamental to most application’s data management needs.

Compute Engine

If you are into linux, CLI and so on, probably this is the service you have heard of.

Sometime ago, I used GCP CE free tier to tinker with their VMs.

• https://jalcocert.github.io/Linux/docs/linux__cloud/cloud/#gcp

Conclusions

If you are into DSC, you might be interested to explore Vertex AI:

• Model Garden, Vertex AI - https://cloud.google.com/model-garden?hl=es

Other Clouds vs GCP

GCP CE vs SBCs

Before going into the cloud, I got familiar with linux thanks to a raspberry Pi 4.

But there are other single board computers out there, so far I tried:

1. Raspberry Pi 4 2/4GB
2. Orange pi 5 8GB

Orange vs Raspberry Pi 4

Battle of SBCs Benchmarks

If you are wondering if you need a Pi or a MiniPC, you can have a look to this.

Most SBCs (not all) are ARM based CPUs and we will be seeing more and more ARM adoption on cloud providers.

GCP CE vs Mini PCs

Not so long ago I was lucky to try the following mini PCs:

1. BMax B4
2. Asrock x300
3. Firebat AK2 Plus

Asrock x300

Building a MiniPC as Server

BMax B4 Review

Small Factor x86 MiniPC

Mini PCs tend to have x86 CPU arch.

They tend to behave better if you plan to reproduce high resolution video, but in general, are less efficient as per compute power / Watt.

Green vs Brown Field

In IT, “Greenfield” and “Brownfield” describe two fundamental approaches to developing, implementing, or migrating systems:

1. Greenfield Migration (or Development):

   • Concept: This approach is like building something on a completely empty, fresh plot of land (a “green field”). You start from scratch, with no existing infrastructure, legacy systems, code, or data to contend with.
   • Characteristics:
     • Clean Slate: You have the freedom to design and implement the system using the latest technologies, best practices, and architectures.
     • No Legacy Debt: You’re not burdened by old code, outdated integrations, or technical debt from previous systems.
     • Opportunity for Innovation: Allows for significant re-engineering of processes and workflows.
   • Pros: Maximum flexibility, optimized for modern needs, potentially higher long-term efficiency, can adopt cutting-edge solutions.
   • Cons: Higher initial cost, longer implementation time, significant effort for data migration (if existing data needs to be brought over), greater need for change management as users adapt to a completely new system.
   • When to use: For completely new ventures, when legacy systems are highly fragmented, obsolete, or too complex to adapt, or when a complete overhaul and process re-engineering are desired.

2. Brownfield Migration (or Development):

   • Concept: This approach is like renovating or developing on a piece of land that already has existing structures or infrastructure (a “brown field”). You work with, or build upon, what’s already there.
   • Characteristics:
     • Leverages Existing Assets: You retain existing data, configurations, customizations, and often, established business processes.
     • Incremental Change: It’s more of an upgrade or conversion rather than a complete rebuild.
     • Preserves Investment: You aim to preserve existing investments in the legacy system.
   • Pros: Lower initial cost, faster implementation time (often), less disruptive to ongoing business operations, familiar environment for users, easier integration with existing systems.
   • Cons: Carries over legacy debt and inefficiencies, limited opportunity for fundamental process improvement, may not fully leverage the potential of new technologies, harder to innovate or achieve radical simplification.
   • When to use: When you need to upgrade or adapt an existing system, when historical data and processes are critical to maintain, or when budget and time constraints favor a less disruptive approach.

In essence:

• Greenfield = Build new.
• Brownfield = Adapt/Upgrade existing.