dbt Analytics Engineering (AWS Redshift)
Completedbt analytics engineering project on AWS Redshift Serverless, built on the Olist Brazilian e-commerce dataset (100K orders, 2016–2018). 18 models across three layers (staging, intermediate, marts), fully implemented and tested: 94 tests, 3 custom macros, 1 SCD Type 2 snapshot, and a star schema for self-service analytics.
Technologies
Problem
Raw source data needs structured transformation into analytics-ready dimensional models to enable BI teams and analysts to work independently without touching raw tables.
Approach
ELT pattern with dbt on Redshift: staging layer (source-conformed views) → intermediate layer (business logic) → marts layer (dimensional models for core and marketing schemas).
Result
18 dbt models fully built and tested: 7 staging views, 4 intermediate views, 6 mart tables (dim_customers, dim_products, dim_sellers, dim_dates, fct_orders, fct_order_items) + 1 marketing mart (customer lifetime value). 94 tests executed — 93 pass, 1 warn (known voucher data quality issue in source data). 1 seed (71 rows of PT→EN category translations), 1 SCD Type 2 snapshot (sellers), 3 custom macros, full dbt documentation generated.
Learnings
dbt 2.0 breaks generic test syntax — all arguments (values, to, field) must be nested under an arguments: key. Redshift reserves raw as a keyword; schema references must be quoted. Redshift has no DISTINCT ON — ROW_NUMBER() OVER (PARTITION BY ...) is the correct substitute. LISTAGG and COUNT(DISTINCT) cannot be combined in Redshift and require separate CTEs. dbt docs generate was removed in 2.0 — dbt compile --write-catalog with a local HTTP server replaces it.
Relevance
Demonstrates analytics engineering, dimensional modelling, and modern SQL transformation workflows — a core competency for data-oriented roles.