Problem
Fragmented operations and feedback
Orders, items, payments, products, customers, sellers, delivery timestamps, and Portuguese reviews describe different parts of the same purchase.
RAW TO RELIABLE / ENGINEERING CASE STUDY / OLIST
Olist Supply Chain & AI Reviews Intelligence
An e-commerce analytics project connecting Snowflake and dbt models, Cortex-enriched Portuguese reviews, Power BI reporting, and a separate Neo4j graph investigation.
Historical public-data project · warehouse models, recorded BI output, and graph investigation. The source shows separate local and cloud workflows.
Project stateCompleted
Focus areas
- Local Prefect flow
- Order-item modeling
- Portuguese review enrichment
- Seller sentiment analysis
- Graph investigation
Technology stack
01 / Project Overview
Project Overview
One purchase, several grains, two analytical perspectives.
Engineering challenge
Connect grains without losing meaning
Align order-level and item-level records, preserve customer ratings, and attach AI-inferred sentiment to the review text that produced it.
Approach
Model first, enrich in the warehouse
Snowflake SQL loads CSVs from an S3 stage. dbt standardizes sources, invokes Cortex for eligible reviews, and builds fact and dimension models.
Output
Two views of the same domain
A recorded Power BI dashboard explores categories, sentiment, sellers, and geography. A separate Snowflake-to-Neo4j bridge supports relationship exploration.
- Raw source definitions
- 09
- Staging SQL models
- 10
- Mart SQL models
- 05
Code inventory from the audited repository. The dataset is attributed to Kaggle’s historical Brazilian Olist e-commerce data; these counts describe model definitions, not loaded rows or production scale.
02 / System Architecture
System Architecture
Warehouse processing and local orchestration are separate paths in the supplied implementation.
OLIST / VERIFIED DATA PATHS / FIG. 01
- Source-defined movement / dependency
- Documented / manual relationship
- Orchestration / control
Warehouse path
01 / Source
Olist CSV datasets
Nine source datasets
- Manual
02 / Storage
AWS S3 raw landing
CSV objects / manual boundary
- COPY
03 / Warehouse
Snowflake RAW_DATA
External stage / COPY INTO
- source()
04 / Transformation
dbt staging
Ten staging models
Cortex enrichmentstg_reviews_enriched - ref()
05 / Modeling
dbt marts
One fact / four dimensions
Graph investigation / direct warehouse bridge
01 / Source
Snowflake reviews
Staging + raw review records
- SELECT
02 / Transfer
Python graph bridge
Join on review_id
- MERGE
03 / Graph
Neo4j
Order → HAS_REVIEW → Review
Local orchestration / separate control path
01 / Control
Prefect flow
Local task orchestration
- Control
02 / Simulation
ingest.py
Simulated success / no load
- Then
03 / Invocation
dbt run
Configured cwd: dbt_olist
Local CSV loader / independent of the Prefect simulation
01 / Source
Local CSV files
data/raw
- Read
02 / Ingestion
Python CSV loader
ingest_raw_data.py
- to_sql
03 / Local database
PostgreSQL
Docker Compose service
Recorded analytics output / documented relationship
01 / Analytical models
dbt fact + dimensions
Published SQL models
- Recorded
02 / Recorded output
Power BI
Dashboard screenshot
Warehouse boundary
Snowflake SQL defines S3 file loading through an external stage and COPY INTO. No uploader is committed. Cortex runs inside stg_reviews_enriched before dim_reviews_enriched; it is a dbt dependency, not a separately orchestrated service.
Graph bridge scope
The bridge reads staging and raw reviews directly, filters non-null sentiment, and MERGEs Order–HAS_REVIEW–Review with ai_sentiment_score. The broader Customer–Review–Product–Seller investigation model is documented separately below.
Local execution boundary
Prefect retries its ingestion task once and checks subprocess exit codes. That task calls a simulation, not the PostgreSQL loader. Its dbt_olist working directory also differs from the public dbt/ configuration and root models/ layout; these paths do not establish a successful replay.
Recorded BI output
The Power BI screenshot records an analytical output. Its precise connection and semantic-model definitions are absent, so the dashed link marks a documented relationship rather than a verified runtime integration.
03 / Data Modeling
Data Modeling
The central grain is one order line item, identified by order_id and item_sequence.
The fact left joins staged orders onto staged items. It retains customer, product, and seller identifiers alongside item price, freight, their sum, order status, and purchase and delivery timestamps. Customer identity is preserved separately in customer_unique_id.
OLIST / MODEL RELATIONSHIPS / FIG. 02
Fact / Order line item
fct_order_items
order_id + item_sequencecustomer_id · product_id · seller_iditem_price · item_freighttotal_item_valueorder_status · purchase/delivery timestamps
customer_id
dim_customers
Order-linked customer attributes
customer_unique_idzip_code_prefix · city · state
product_id
dim_products
Item-linked product attributes
category_name_pt / enweight_g · dimensions
seller_id
dim_sellers
Item-linked seller attributes
zip_code_prefixcity · state
Potential analytical join / order_id
Cardinality must be validated before joining reviews to item measures.
Review grain / cardinality unproven
dim_reviews_enriched
Review-record output joined to enrichment on review_id; not aggregated to one row per order.
review_id · order_idoriginal_star_rating · ai_sentiment_score
Preserve grain across joins
Orders can contain multiple items. dim_reviews_enriched keeps review records and left joins enrichment on review_id; it does not aggregate reviews to one row per order. Multiple matching review rows joined on order_id would duplicate item facts and inflate summed measures. Even a single review repeats across an order’s items when counting joined reviews. No uniqueness or relationship tests prove these cardinalities.
Separate stored fields from derived metrics
The fact calculates total_item_value, but not delivery latency or an SLA breach flag. Customer and seller dimensions retain ZIP, city, and state; they do not join the geolocation model.
(i.price + i.freight_value) AS total_item_value
FROM order_items AS i
LEFT JOIN orders AS o
ON i.order_id = o.order_id04 / AI Review Enrichment
AI Review Enrichment
Portuguese review text becomes a queryable attribute inside Snowflake.
A customer’s star rating and the sentiment inferred from their written feedback answer different questions. The materialized stg_reviews_enriched model calls SNOWFLAKE.CORTEX.COMPLETE with llama3-8b for eligible comments and asks for a score from 1 to 10. dim_reviews_enriched left joins this result onto the original review records.
- 01 / FilterEligible review text
Non-null, trimmed length greater than three, and no listed placeholder strings.
- 02 / InferCortex response
The raw response is retained in ai_sentiment_raw for inspection.
- 03 / ParseNumeric sentiment
REGEXP_SUBSTR extracts digits; TRY_TO_NUMBER safely converts the result.
- 04 / ModelReview dimension
Original star rating and AI sentiment remain separate fields.
ai_sentiment_raw, -- Debugging column: Keep the raw output so we can see exactly what the LLM said
TRY_TO_NUMBER(REGEXP_SUBSTR(ai_sentiment_raw, '\\d+')) AS sentiment_scoreA non-numeric extraction converts to NULL. There is no enforced 1–10 range, sentiment calibration, or accuracy evaluation in the source. This is numeric extraction for analytics, not a validated normalized sentiment model. The separate Snowflake Cortex SQL file is an incomplete excerpt; the full dbt staging model is the evidence used here.
05 / Data Quality & Reliability
Data Quality & Reliability
Safeguards are described at the point where they act, with their limits visible.
Failure mode
Ingestion subprocess fails
Engineering decision
Prefect task retries once; non-zero return codes raise an exception
Reliability benefit / boundary
The flow surfaces execution failures. A successful stub exit is not proof of a completed ingestion.
Failure mode
LLM output includes prose or no number
Engineering decision
Keep the raw response, extract digits with REGEXP_SUBSTR, then use TRY_TO_NUMBER
Reliability benefit / boundary
Non-numeric extraction becomes NULL instead of a hard cast failure. The model does not enforce the requested 1–10 range.
Failure mode
Blank or placeholder review text
Engineering decision
Require non-null comments, trimmed length > 3, and exclude N/A, NA, NONE, NULL
Reliability benefit / boundary
Filters ineligible text before inference; filtered records remain available through the review dimension’s left join.
Failure mode
Repeated graph loads
Engineering decision
MERGE Order and Review identifiers and the HAS_REVIEW relationship
Reliability benefit / boundary
Reuses matching graph entities instead of creating a new pair for every matching record.
Failure mode
Unmapped product category
Engineering decision
Left join category translations and COALESCE to Unknown
Reliability benefit / boundary
Retains the product when an English category translation is absent.
Load completion needs reconciliation
Snowflake COPY uses ON_ERROR = CONTINUE. A load can finish while skipping invalid records, so success alone does not establish complete ingestion. No recorded COPY results substantiate a success percentage.
Tests are an evidence gap
The README describes unique and not_null tests, but the public source includes no such test definitions or dbt run results. stg_order_reviews casts order_id to INTEGER although the raw identifier is VARCHAR. That mismatch needs correction and validation before relying on a fresh build.
06 / Business Analytics & Power BI
Business Analytics & Power BI
The recorded Power BI output connects product categories, seller review volumes, sentiment, and geography.
Original Power BI project screenshot. Filters, totals, and charts describe this recorded view; the underlying BI measures were not independently reproduced in this audit.
Source: public Olist repository / dashboard_images. Full image preserved without cropping.- Avg Review Score 6.31 / documented as sentiment
- The README calls 6.31 historical average sentiment. The Cortex prompt requests 1–10, and the review dimension stores original stars separately from AI sentiment. The dashboard card’s exact measure binding remains unverified because its DAX definition is not published.
- Total Revenue / currency unresolved
- The screenshot’s dollar sign does not establish USD. Source SQL casts price, freight_value, and payment_value and calculates price + freight; no currency conversion or unit is defined. The publisher description does not specify the monetary unit, so BRL or USD labeling cannot be confirmed from the available definitions.
Read comparisons in context
The image shows category sentiment comparisons, a time trend, a seller watchlist with average sentiment and review counts, and a Brazil heatmap. These views support exploratory questions about customer feedback and seller concentration.
Interpret a recorded view
The displayed totals belong to this screenshot. The public repository has no PBIX, DAX definitions, or reproducible result export to independently validate those aggregates. Delivery-delay causality, offboarding impact, and business savings are not established by this image.
07 / Neo4j Graph Investigation
Neo4j Graph Investigation
Graph queries make an entity path visible, while the loader and investigation schema have different scopes.
The Python bridge reads enriched staging reviews joined to raw reviews on review_id. It MERGEs Order and Review nodes, attaches HAS_REVIEW, and stores ai_sentiment_score. The investigation query separately follows Customer → Review → Product → Seller, using WROTE, REVIEWS, and SOLD_BY relationships.
- Customer → Review
- WROTE / reviewer activity
- Review → Product
- REVIEWS / reviewed product
- Product → Seller
- SOLD_BY / seller concentration
The broader relationship loader is not committed. The Cypher reads sentiment_score, while the Python bridge writes ai_sentiment_score; connecting those properties also requires work. This path is a query-defined investigation model, not a claim that the bridge constructs the full graph.
Recorded investigation topology: one customer, six reviews, one product, and one seller. Demonstration-style labels do not verify real fraud or establish provenance from the historical dataset.
Source: public Olist repository / dashboard_images. The broader investigation schema is distinct from the committed Order–Review bridge.MATCH (c:Customer)-[:WROTE]->(r:Review)-[:REVIEWS]->(p:Product)-[:SOLD_BY]->(s:Seller)
WHERE r.sentiment_score >= 9.0
WITH c, count(DISTINCT s) AS distinct_sellers, count(r) AS total_perfect_reviews
// Core Logic: The customer left more than 5 perfect reviews, but ALL of them are exclusively for ONE single seller.
WHERE total_perfect_reviews > 5 AND distinct_sellers = 1
RETURN c.customer_id, total_perfect_reviews
ORDER BY total_perfect_reviews DESCA heuristic for investigation
The query filters sentiment_score ≥ 9, counts matched paths per customer, and requires more than five matches to one distinct seller. count(r) counts matched rows, not distinct review IDs; duplicated paths can affect the threshold.
A demonstration, not a fraud verdict
The screenshot returns FRAUD_BOT_MASTER and shows six reviews, one product, one seller, and one customer. These demonstration-style identifiers do not establish that the cluster occurred in the original Olist data. The topology illustrates a potential coordinated-review pattern, not confirmed fraud.
08 / Engineering Decisions & Troubleshooting
Engineering Decisions & Troubleshooting
Specific troubleshooting patterns, with implementation evidence separated from project notes.
Source + documentation
Keep Jinja documentation out of compiled SQL
- Observed problem
- The README records compilation errors from Jinja expressions inside SQL comments.
- Root cause
- Jinja processes template expressions before the warehouse interprets SQL comments.
- Engineering solution
- Use native {# … #} comments for template-related notes. The fact, customer, and seller models demonstrate this pattern.
- Validation / result
- Native Jinja comments appear in the fact, customer, and seller models, separating template notes from compiled SQL.
Source evidence
Source + documentation
Treat sentiment as an inferred, nullable attribute
- Observed problem
- The README describes conversational model responses breaking direct numeric casts.
- Root cause
- A prompt requesting a number does not constrain a generative response to valid numeric output.
- Engineering solution
- Retain ai_sentiment_raw; extract digits and convert with TRY_TO_NUMBER in the materialized staging table.
- Validation / result
- The staging model keeps the raw response for inspection and produces a nullable sentiment_score when extraction or numeric conversion fails.
Source evidence
Documented decision
Give geographic labels explicit context
- Observed problem
- The README records Brazilian state acronyms being interpreted as US states in Power BI.
- Root cause
- A state abbreviation alone is ambiguous without country context.
- Engineering solution
- The documented correction uses a Country → State hierarchy in the semantic model.
- Validation / result
- The recorded dashboard shows Brazilian geography. The Country → State correction is documented in the README.
Source evidence
Documented decision
Read seller sentiment alongside review volume
- Observed problem
- The README records sellers with one review distorting a ranking based on average score alone.
- Root cause
- An average alone gives a one-review seller the same ranking basis as a seller with hundreds of reviews.
- Engineering solution
- The documented reporting change adds Total Reviews as a secondary criterion.
- Validation / result
- The recorded watchlist displays average sentiment beside Total Reviews, making the sample size visible when comparing sellers.
Source evidence
09 / Evidence & Project Boundaries
Evidence & Project Boundaries
The page is grounded in public code and recorded project visuals.
What the audit supports
Raw-table DDL and loading SQL; dbt source definitions and model dependencies; Cortex filtering and parsing; a local Prefect flow; a PostgreSQL Compose service; graph-bridge logic; the investigation query; and two recorded screenshots.
What remains unverified
Fresh execution of the complete pipeline, passed schema tests, actual inference volume, ingestion success rate, seller-risk percentages, logistics sentiment differences, and measured business impact. No production usage by Olist is claimed.
Evidence limits for the engineering decisions: original compilation failures and rerun logs are not published. Cortex output range, accuracy, and failure-rate improvements were not evaluated here. No Power BI semantic model verifies the geographic hierarchy or seller-ranking calculation; no statistical weighting or Bayesian smoothing implementation is claimed.
Source reviewed at revision 35333e0. Runtime services were not replayed during the website audit. README folder names differ from the checked-in layout; evidence links point to actual files at the reviewed revision.