Vertical slice

GCP analytics engineering

Test the keys before trusting the fact table.

Built a BigQuery and dbt warehouse slice that preserves raw data, parses it defensively, and tests whether source identifiers can support a fact table.

Engineering decisions

My role: I built the cloud infrastructure and warehouse slice described in this project account.
Result & status: Completed a tested warehouse vertical slice with a documented key decision and fact model.

Raw loading was manual. Automated ingestion and business-facing marts are future work, not part of the implemented result.

The problem

Can the available business identifiers uniquely describe each fact record?

Raw first

Source values remain visible before parsing changes their types.

Defensive staging

Conversion rules make blanks and malformed values explicit.

Tested identity

The fact key follows source-data analysis rather than an assumed natural key.

Approach

Preserve raw values first.

Provisioned Google Cloud resources with Terraform, manually loaded an airline source file into BigQuery, preserved raw fields as strings, and used dbt for staging and a fact model.

Engineering

Test candidate keys before modeling.

Staging handles blanks, dates, numbers, and indicators explicitly. Notebook analysis and dbt tests challenged candidate natural keys; the fact model uses a surrogate key after those identifiers proved insufficient.

Validation

Check rows, parsing, and identity.

The project account describes notebook checks and dbt tests for row preservation, conversions, required values, and candidate-key behavior. The source files are not currently linked publicly, so this case provides a design account rather than an inspectable implementation.

Tools & methods

Terraform · BigQuery · dbt · Python · Google Cloud