flattener

A Python utility for flattening complex nested data structures generated by the Connect for Cancer Prevention study questionnaires.

Overview

The flattener converts Connect study data - stored as nested BigQuery tables - into flat, analyst-friendly tables. It exports source tables to Parquet in GCS, uses DuckDB to flatten nested structures, and loads the results back into BigQuery. It also generates a user profile history table directly in BigQuery.

The tool is deployed as a Cloud Run service exposing a Flask API. The pipeline is orchestrated as an Airflow DAG in the flattener-orchestrator repository.

Pipeline

The Airflow DAG calls the following endpoints in order:

1. GET /heartbeat -- verify the service is healthy
2. POST /refresh_firestore -- (optional) trigger a Firestore backup
3. POST /table_to_parquet -- export a BigQuery table to Parquet in GCS
4. POST /convert_parquet -- decode BLOB columns to STRUCT (boxes table only)
5. POST /flatten_parquet -- flatten nested Parquet into a wide Parquet file via DuckDB
6. POST /parquet_to_table -- load the flattened Parquet back into BigQuery
7. POST /generate_profile_history -- generate the user profile history table in BigQuery

Steps 3-6 run in parallel across all selected tables. Step 7 runs once after all tables are loaded.

How It Works

Struct Traversal

The tool reads the schema of a Parquet file and recursively traverses it to identify all leaf fields. For each field, it:

1. Builds the correct hierarchical path (e.g., "parent"."child"."grandchild")
2. Creates an alias using underscores (e.g., parent_child_grandchild)
3. Casts the value to STRING in the generated SELECT expression

Firestore Value Wrappers

Some fields use a Firestore value wrapper pattern: a STRUCT with string, integer, and provided sub-fields. These are collapsed into a single STRING column using a CASE expression that checks the provided field.

Array Field Expansion

For fields of type VARCHAR[], the tool:

1. Queries the data for all distinct integer-castable values in the array
2. For each value, creates a binary indicator column (1 if present, NULL if not)
3. Names these columns using the pattern original_field_D_value

For example, an array field containing values ["104430631", "353358909"] would expand to:

• original_field_D_104430631 = 1
• original_field_D_353358909 = 1

Boxes Table

The boxes table stores specimen bag data in BLOB columns that are first converted to STRUCTs, then flattened into a UNION ALL of unnested tube rows with parent-level fields projected alongside.

User Profile History

The d_569151507 field on the participants table is an array of profile change deltas. The /generate_profile_history endpoint reads directly from BigQuery, unnests this array, and applies carry-forward logic using LAST_VALUE(IGNORE NULLS) window functions to produce a complete historical snapshot table.

Column Naming Conventions

• Nested fields: parent_child_grandchild
• Expanded array values: parent_array_field_D_value
• Firestore wrappers: the string/integer/provided suffixes are stripped

Deployment

The service is deployed via Cloud Build (cloudbuild.yaml) as a Cloud Run service with a GCS volume mount for DuckDB temporary files.