aws-dbt-project · architecture

Olist E-Commerce Analytics on Redshift Serverless

Raw Olist order data lands in S3, loads into Redshift via COPY, and moves through a three-layer dbt pipeline (staging → intermediate → marts) into a dimensional star schema consumed by Power BI.

End-to-end data flow S3 → Redshift → dbt → BI

SourceKaggle
olist_*.csv
7 CSV files
IngestionAWS
S3 · raw data bucket
Redshift COPY (IAM role)
raw schema 7 tables
Stagingdbt · views
stg_orders
stg_order_items
stg_order_payments
stg_order_reviews
stg_customers
stg_products
stg_sellers
Intermediatedbt · views
int_orders_enriched
int_order_items_enriched
int_customer_orders
int_product_category_translated
seed: category_translation 71 rows
Marts / coredbt · tables
dim_customers
dim_products
dim_sellers
dim_dates
fct_orders incremental
fct_order_items
snapshot: sellers_snapshot SCD2
Marts / marketingdbt · tables
mrt_customer_lifetime_value
ConsumptionBI
Power BI Desktop 4-page dashboard · Import mode
AWS infrastructure
dbt transformation

Dimensional model marts/core star schema

dim_dates1 row / calendar day
dim_products1 row / product SKU
dim_customers1 row / unique person
fct_orders1 row / order · incremental
fct_order_items1 row / order line
dim_sellers1 row / seller
mrt_customer_lifetime_value1 row / unique customer
fact table
marketing mart
dimension

Technology stack

LayerTechnology
Transformationdbt-core 2.0.0-alpha.1 · dbt-redshift 1.10.1
WarehouseAmazon Redshift Serverless (8 RPU base, eu-central-1)
Raw storageAmazon S3
Data loadingRedshift COPY from S3 via IAM role
dbt packagesdbt_utils · dbt_expectations (metaplane) · audit_helper
Docsdbt compile --write-catalog + Python http.server
BIPower BI Desktop, Import mode, 4-page dashboard