SNOWFLAKE + dbt CLOUD CASE STUDY
Olist Analytics
A production-oriented analytics engineering pipeline built on Snowflake and dbt Cloud using the Brazilian Olist e-commerce dataset.
Tools used
- SQL
- Snowflake
- dbt Cloud
- GitHub Actions
01 / PROBLEM
Build an e-commerce pipeline that behaves like a production analytics system.
The source contains nine related Olist datasets with one-to-many relationships across orders, items, payments, reviews, products, sellers and customers. The modelling challenge is not just cleaning the data. It is preserving the correct grain while producing reliable dimensional marts.
The project also adds production concerns that are often missing from portfolio work: separate development and production compute, ingestion-driven incrementality, pull-request CI, service authentication, controlled consumer access and scheduled deployment.
02 / PRODUCTION VALIDATION
Hardened pipeline deployed and validated
Post-deployment checks confirm the expected fact grains and ingestion watermarks.
03 / ARCHITECTURE
Raw sources flow through layered transformations into dimensional marts.
RAW.OLIST
Nine public source tables with persistent _LOADED_AT ingestion metadata.
Standardise
Explicit columns, cleaning, naming and ingestion metadata propagation.
Protect grain
Reusable enrichment and order-level aggregation before downstream joins.
Dimensional model
Three dimensions plus order and order-item transactional facts.
04 / KEY DECISIONS
Production behaviour is designed into the modelling workflow.
Keep two transactional fact grains
fct_orders provides one row per order. fct_order_items provides one row per (order_id, order_item_id). Keeping both facts prevents item-level joins from multiplying order-level measures while still connecting product and seller dimensions to transactions.
Use ingestion metadata, not purchase date
Persistent _LOADED_AT timestamps are propagated from RAW through staging and intermediate models into record_loaded_at. Both facts use these watermarks to drive Snowflake MERGE processing.
RAW._LOADED_AT
↓
staging._loaded_at
↓
intermediate.record_loaded_at
↓
incremental MERGEReprocess the current ingestion batch safely
The incremental predicate uses `>=` against the current maximum watermark. Rows from the same ingestion batch can be seen again, while the fact-table merge keys prevent duplicate records.
Separate DEV and PROD compute
Development uses DEV_WH, while production uses DBT_PROD_WH. Production models also land in dedicated staging, intermediate and mart schemas rather than sharing one general-purpose schema.
Production runs through a service identity
DBT_USER and DBT_ROLE use RSA key-pair authentication for production dbt jobs. A separate ANALYTICS_READER_ROLE gives consumers read-only access to production marts without transformation privileges.
Do not add clustering by default
Clustering is treated as an evidence-driven warehouse optimisation. The project keeps correctness and grain explicit first, then leaves physical optimisation to observed query patterns and cost.
05 / ENVIRONMENTS
Development and production are isolated by identity, compute and schema.
DEV
DEV_USER / DEV_ROLE
↓
DEV_WH
↓
DEV.DBT_DEV_STAGING
DEV.DBT_DEV_INTERMEDIATE
DEV.DBT_DEV_MARTSPROD
DBT_USER / DBT_ROLE
↓
DBT_PROD_WH
↓
PROD.ANALYTICS_STAGING
PROD.ANALYTICS_INTERMEDIATE
PROD.ANALYTICS_MARTS06 / QUALITY AND DELIVERY
Tests, pull-request checks and deployment controls protect the pipeline.
Data quality
- Primary keys and composite grains are tested.
- Relationships and accepted categorical values are validated.
- Commercial values are checked for invalid negative values.
- Known timestamp and reconciliation anomalies are surfaced as warnings instead of being hidden.
- Production validation confirms no duplicate order or order-item grains.
Delivery workflow
- Changes are developed through feature branches.
- Pull-request CI checks dbt parsing and graph integrity before merge.
- Changes are validated in DEV before production deployment.
- dbt Cloud runs the production build on a 12-hour schedule.
- Post-deployment SQL validates the production marts after release.
The source is static, so ingestion is not yet automated.
The watermark mechanism is implemented, but the public Olist dataset is a historical snapshot. Automated RAW ingestion is therefore the next logical extension. A future version could add object-storage or Snowpipe ingestion, warehouse-backed Slim CI, snapshots, semantic definitions, BI dashboards and cost monitoring.