The problem
Raw operational data is rarely ready for analysis. Orders, customers, payments, and reviews arrive in separate tables with inconsistent keys and no guarantees about quality. A single clever query might answer one question today, but it does not give a team something they can rely on tomorrow.
I used the public Olist dataset of more than 100,000 Brazilian e-commerce orders to build the kind of analytics engineering stack a company would actually run in production, where the goal is a reliable, repeatable pipeline that other people can trust and extend.
The approach
I modelled the data with dbt in three layers. Staging cleans and standardises each source. Intermediate joins and reshapes them. Marts expose a Kimball star schema, with a central fct_orders table and dim_customers and dim_products around it. Fifteen models in total. Everything lives in BigQuery in the Berlin region, which keeps the data inside the EU.
Making the numbers trustworthy
A model is only useful if people believe the numbers behind it. I wrote 94 automated tests covering not-null and unique constraints, accepted values, referential integrity, and a few custom checks specific to this data. GitHub Actions runs every test on every pull request, so a change that quietly breaks the data cannot be merged by accident.
I also documented the design decisions in the repository, the star schema rationale, the layer separation, and the materialisation strategy, so the reasoning is written down rather than locked in my head.
The live Looker Studio dashboard connects straight to the marts layer and covers revenue trends, delivery performance, geography, and top product categories, so the output is something a non-technical stakeholder can read at a glance.
What I would do differently
I would add incremental models so the pipeline only processes new orders instead of rebuilding everything each run. I would layer in freshness and volume tests to catch upstream gaps early. And I would add a semantic layer so business users can self-serve common metrics without writing SQL.
Stack: dbt Core 1.11, BigQuery (europe-west10), SQL, Python, GitHub Actions, Looker Studio. Kimball star schema, three-layer model, tested and deployed through continuous integration.