product_reviews

source 2 PII columns

What customers said about products, and how strongly.

Definition

Raw customer × product reviews landed as CSV in GCS. One row per submitted review — rating, free-text body, helpful-vote count, and a verified-purchase flag arrive together before any cleanup. Staging coerces the CSV's String columns into typed equivalents and FK-validates against the e-commerce dimensions.

11 of 11

Column

Description

Tests · PII

review_id

UUID — primary key on the source side. Stable across re-syncs.

2 tests

customer_id

Reviewing customer. String on the wire (CSV); coerced to UInt64 in staging and validated against `stg_postgres__customers`.

customer +17 PII1 test

product_id

Reviewed product. String on the wire; coerced to UInt64 in staging and validated against `stg_postgres__products`.

1 test

rating

1..5 star rating. String on the wire; coerced to UInt8 in staging.

1 test

title

Free-text review headline. Nullable.

PII

body

Free-text review body. Nullable.

PII

language

ISO-2 language code of the review text — `en`, `es`, or `de`. Lower-cased in staging.

verified_purchase

String "true"/"false" — set to "true" when the reviewer has a matching non-cancelled order for the product. Coerced to UInt8 (0/1) in staging.

helpful_votes

Count of "helpful" thumbs-up on the review. String on the wire; coerced to UInt32 in staging.

review_ts

ISO-8601 UTC timestamp the review was submitted (e.g. `2026-04-30T12:34:56Z`). Parsed in staging into `review_at` + derived `review_day`.

source

Channel the review came from — `web`, `mobile`, or `import`. Renamed to `review_source` in staging to avoid colliding with the dbt `source()` function name.