Olist Analytics

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 MERGE

Reprocess 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.