Bigtable schema design in GCP - Mini Project: Build & Apply

Bigtable Schema Design for User Activity Tracking

📖 Scenario: You are building a system to track user activities on a website. Each user can perform multiple actions like login, logout, and page views. You want to store this data efficiently in Google Cloud Bigtable.

🎯 Goal: Create a Bigtable schema design that organizes user activity data by user ID and timestamp, with columns for action type and details.

📋 What You'll Learn

Create a table schema with a row key combining user ID and timestamp

Define column families for 'activity' data

Include columns for 'action' and 'details' under the 'activity' column family

Use a timestamp format that allows sorting by time

💡 Why This Matters

🌍 Real World

Bigtable is used in real-world applications to store large amounts of time-series or user activity data efficiently.

💼 Career

Understanding Bigtable schema design is important for roles in cloud data engineering and backend development working with scalable databases.

1

Create the initial table schema

Create a Bigtable table schema with a row key named row_key that combines user_id and timestamp as a string. Define a column family called activity.

GCP

# Define the table schema with row_key and activity column family
# Your code here

Need a hint?

Use a dictionary named table_schema with keys row_key and column_families.

2

Add columns for action and details

Add columns action and details under the activity column family in the table_schema dictionary.

GCP

table_schema = {
    'row_key': 'user_id#timestamp',
    'column_families': ['activity'],
    # Add columns 'action' and 'details' under 'activity'
    'columns': {
        'activity': []
    }
}
# Your code here

Need a hint?

Use a nested dictionary for columns with activity as key and list of columns as value.

3

Define the timestamp format for sorting

Add a variable timestamp_format and set it to the string 'YYYYMMDDHHMMSS' to represent the timestamp format used in the row_key for sorting by time.

GCP

table_schema = {
    'row_key': 'user_id#timestamp',
    'column_families': ['activity'],
    'columns': {
        'activity': ['action', 'details']
    }
}

# Define timestamp format for sorting
# Your code here

Need a hint?

Use a string variable named timestamp_format with the exact value 'YYYYMMDDHHMMSS'.

4

Complete the Bigtable schema design

Add a final key description to the table_schema dictionary with the value 'User activity tracking schema with row key as user_id and timestamp'.

GCP

table_schema = {
    'row_key': 'user_id#timestamp',
    'column_families': ['activity'],
    'columns': {
        'activity': ['action', 'details']
    },
    # Add description key here
}

timestamp_format = 'YYYYMMDDHHMMSS'
# Your code here

Need a hint?

Add the description key with the exact string value to table_schema.