A couple of years ago I complained about the physical wall traditional servers represent and how complex it is to maintain an on-premise Hadoop cluster. Well, the market has handed down its sentence: proprietary hardware is dead. Everyone is moving to the cloud.

Over the past year, many are in the midst of a full migration. Some use Amazon Redshift and others, Google BigQuery. Even though both are sold as "Cloud Data Warehouses", their technical philosophies are polar opposites.

Redshift: Managed power

Redshift was the first to democratize the columnar MPP (Massively Parallel Processing) model in the cloud. It's based on an old fork of PostgreSQL (ParAccel).

With Redshift, you are still a database administrator. You have to spin up a cluster, choose how many nodes you want (for example, four dc2.large nodes) and you pay for that cluster whether it's on or not. You have to manage distribution keys (DISTKEY) so the data is distributed well across the nodes and run VACUUM operations regularly to defragment the disk.

Data ingestion is extremely powerful if you do it from S3 using the native command:

-- Hyper-optimized bulk ingestion in Redshift from an S3 bucket
COPY fact_ventas 
FROM 's3://mi-bucket-corporativo/datos-ventas/' 
CREDENTIALS 'aws_access_key_id=AKI...;aws_secret_access_key=xyz...' 
FORMAT AS PARQUET;

BigQuery: "Serverless" alien technology

Then I tried BigQuery (based on Google's Dremel paper), and my DBA brain short-circuited. In BigQuery there are no servers. You don't pick nodes, you don't configure RAM, there are no indexes. You just have a web text box where you paste an SQL query and hit play.

Under the hood, Google dynamically allocates thousands of CPUs on its optical network to sweep your table (which can have Petabytes) and returns the result in seconds. The pricing model isn't based on hardware, it's based on the volume of data queried (about 5 dollars per Terabyte read).

-- A simple SELECT in BigQuery with nested data types (Arrays)
SELECT 
  fecha,
  usuario.id,
  ARRAY_LENGTH(usuario.compras) AS numero_compras
FROM `mi-proyecto.dataset_ventas.fact_eventos_web`
WHERE fecha >= '2017-01-01'

Reflection: The Data Engineer's new role

Working with BigQuery has made me realize how our job is changing. With Redshift I'm still doing technical tuning, but in BigQuery the infrastructure is completely opaque.

This brings a new danger: the direct economic cost of bad code. Before, a poorly fired SELECT * FROM giant_table just slowed down the server and pissed off your coworkers. Today, running that same query in BigQuery without using partitions can cost the company 100 dollars in a single click. The data engineer's job is no longer patching Linux servers, but optimizing table architecture so as not to bankrupt the company at the end of the month.