OLTP vs OLAP in Modern BI Projects: How I Turned Raw E-Commerce Transactions into Business Insight with dbt, BigQuery, and Looker Studio

Most explanations of OLTP vs OLAP sound clean in theory and unhelpful in practice.

You have probably seen some version of this:

• OLTP is for transactions
• OLAP is for analysis

That is technically correct. It is also incomplete.

In real business intelligence projects, the difference is much more tangible. It is the difference between a system that records orders and one that helps leadership understand customer retention, delivery performance, funnel conversion, and repeat-purchase behavior.

I was reminded of that while building an e-commerce BI project on the Olist dataset using dbt, BigQuery, and Looker Studio. The project started with raw operational tables and ended with analytical marts for:

• customer funnel analysis
• cohort retention
• repeat purchase behavior
• driver analysis

That journey is exactly where the difference between OLTP and OLAP in modern BI projects becomes real.

Why this matters in the real world

One of the fastest ways to build a weak dashboard is to connect a BI tool directly to raw transactional tables and hope good business insights will appear on their own.

They usually do not.

Operational data is designed to capture business events efficiently. Analytical data is designed to answer business questions clearly. Those are not the same job.

If a stakeholder asks:

• What share of customers make a second purchase?
• Which cohorts retain better over time?
• Does delivery speed affect repeat behavior?
• Where do we lose customers after their first order?

You are no longer dealing with a transactional problem. You are dealing with an analytical one.

That is why understanding OLTP vs OLAP is not just database theory. It is a practical skill for BI analysts, analytics engineers, and anyone building dashboards that executives will actually use.

What OLTP looked like in this project

At the beginning of the project, I worked with raw e-commerce entities that behave much more like an OLTP-style structure:

• orders
• customers
• order items
• order payments
• order reviews
• products
• sellers

In dbt, that raw layer became a set of staging models such as:

• stg_orders.sql
• stg_customers.sql
• stg_order_items.sql
• stg_order_payments.sql
• stg_order_reviews.sql
• stg_products.sql
• stg_sellers.sql

This is still close to the operational side of the business. The staging layer cleans, renames, and standardizes data, but it does not yet turn it into business intelligence.

For example, in my project:

• stg_orders.sql standardizes timestamps like purchased_at, approved_at, delivered_at, and estimated_delivery_at
• stg_customers.sql Keeps core customer fields such as customer_unique_id, city, and state
• stg_products.sql translates product category names into cleaner, English-friendly business labels
• stg_order_reviews.sql preserves order-level feedback and review timestamps

That work is essential, but it is still not OLAP. It is just making the raw records trustworthy and usable.

And that is an important distinction.

OLTP-style data captures what happened. It does not automatically explain what it means.

What OLTP is optimized for

Operational systems are built for speed, integrity, and event recording.

They are great at answering questions like:

• Did this order happen?
• Which customer placed it?
• What items were in it?
• What payment was recorded?
• Was a review submitted?

That is what transactional systems do best. They handle row-level events quickly and reliably.

But they are not designed to answer broader questions such as:

• How many customers became repeat buyers?
• How does first-order delivery quality relate to retention?
• Which acquisition months created better long-term cohorts?
• Which product categories contribute more to repeat behavior?

Those are analytical questions, which means they require OLAP-style modeling.

Where OLAP starts

The project began to adopt an analytical approach in the intermediate dbt models.

That transition showed up most clearly in files like:

• int_customer_orders.sql
• int_first_orders.sql
• int_order_delivery.sql
• int_order_revenue.sql
• int_order_profit_by_category.sql

This is where the data stopped being just a collection of transactions and started becoming a representation of customer behavior over time.

1. Customer order sequencing turns transactions into lifecycle data

In int_customer_orders.sql, I joined staged orders with staged customers and added two fields that immediately changed the value of the dataset:

• order_rank
• total_orders

That seems small, but it is a major shift.

Now the model can answer questions like:

• Is this the first order for the customer?
• Is this customer a repeat buyer?
• How many total purchases has this customer made?

That logic is not naturally available in OLTP-style raw tables. It has to be modeled.

This is one of the clearest signs that you have moved into OLAP territory. You are no longer storing events. You are organizing them into behavior.

2. First-order models make retention analysis possible

In int_first_orders.sql, I isolated each customer’s first purchase and preserved important first-order attributes such as:

• first order date
• first order status
• first order approval timing
• first-order delivery timing
• city and state
• total order count for that customer

That model matters because so much lifecycle analysis starts with the first purchase experience.

A raw transaction table does not tell you whether a customer’s first interaction with the business led to loyalty. A well-designed OLAP model can.

3. Delivery metrics turn timestamps into business signals

In int_order_delivery.sql, I transformed raw timestamps into metrics like:

• hours to approve
• hours to deliver
• days to deliver
• delivery speed buckets

This is a classic OLAP move.

Operational tables store timestamps. Analytical models interpret them.

That interpretation is where BI value appears. Once you translate timestamps into delivery performance buckets, you can start asking smarter questions:

• Do slower deliveries correlate with lower repeat behavior?
• Which delivery ranges perform best?
• Are customers with faster fulfillment more likely to return?

Those are business questions, not transaction-storage questions.

4. Revenue logic makes order data decision-ready

In int_order_revenue.sql, I rolled item-level rows into order-level revenue measures such as:

• total items
• total price
• total freight
• total revenue

That is another important OLAP step.

In a transactional model, order items exist as separate detailed records. In an analytical model, you often need a single order-level revenue view to power KPIs like:

• average order value
• lifetime revenue
• revenue by repeat customer segment
• revenue by cohort or category

Without that abstraction, dashboards become cluttered, fragile, and harder to trust.

The marts are where OLAP becomes obvious

The strongest proof of OLAP in this project lives in the mart layer:

• mart_customer_funnel.sql
• mart_cohort_retention.sql
• mart_repeat_purchase.sql
• mart_driver_analysis.sql

These models are not built for operational workflows. They are built for decision-making.

That is the essence of OLAP in modern BI projects.

Funnel analysis: from transactions to business drop-offs

In mart_customer_funnel.sql, I modeled a customer lifecycle funnel with stages such as:

• total customers
• customers with a first order
• customers with delivered first orders
• customers with a second order
• customers with a third order
• customers with a fourth order

It also calculates rates like:

• first order rate
• delivery completion rate
• second purchase rate
• third purchase rate
• fourth purchase rate

This is exactly the kind of transformation that raw OLTP-style tables do not provide on their own.

A transactional system records that an order happened. An analytical funnel shows where the business is losing momentum.

That is a completely different outcome.

Instead of asking, “How many orders do we have?” the business can ask, “Where are we losing customers after the first transaction?” That is a much more strategic conversation.

Cohort retention: historical patterns over time

In mart_cohort_retention.sql, I grouped customers into cohorts based on the month of their first order, then tracked future purchasing activity with:

• cohort_month
• order_month
• month_offset
• retained customers
• cohort size
• retention rate

This is one of the clearest examples of OLAP vs OLTP.

An OLTP structure stores order events one row at a time. An OLAP model reorganizes those events into long-term behavioral patterns.

Now the business can ask:

• Which first-purchase months created stronger long-term customers?
• Is retention improving or declining across cohorts?
• Does retention fall sharply after month one?

That is the kind of insight leadership expects from modern business intelligence.

Repeat purchase analysis: turning customers into segments

In mart_repeat_purchase.sql, I summarized customer behavior into metrics like:

• total customers
• one-time customers
• repeat customers
• high-frequency customers
• repeat purchase rate
• average orders per customer
• average revenue per customer
• average days between purchases

This is where OLAP becomes especially valuable.

A transactional system can tell you that the order X happened. An analytical model can tell you that one-time customers dominate the base while repeat customers drive longer-term value.

That is not just reporting. That is a business interpretation.

Driver analysis: moving from descriptive to diagnostic analytics

In mart_driver_analysis.sql, I evaluated repeat behavior across dimensions such as:

• delivery speed bucket
• review score
• category
• state

This is one of my favorite parts of the project because it pushes the model beyond basic reporting.

Now the analysis can start answering “why” questions:

• Do faster deliveries appear to support repeat behavior?
• Are some categories naturally stronger for repeat purchases?
• Does customer experience, reflected in review score, align with retention patterns?
• Are there geographic differences worth investigating?

This is where modern BI projects start to feel more senior. They do not stop at reporting what happened. They create a framework for understanding what may be driving it.

The most practical way to explain OLTP vs OLAP

After working through this project, the clearest explanation I can give is this:

OLTP is about recording the business

It captures events accurately and efficiently:

• orders
• items
• payments
• reviews
• customers

OLAP is about understanding the business

It organizes those events into decision-ready structures:

• funnels
• cohorts
• repeat purchase logic
• delivery performance metrics
• driver analysis

In other words:

OLTP stores activity. OLAP creates insight.

That is the difference that matters in a BI project.

Why this matters for modern BI teams

One of the biggest mistakes in dashboard design is asking the reporting layer to do too much.

If you connect a BI tool directly to raw tables and try to calculate everything there, you often get:

• inconsistent definitions
• duplicated business logic
• slow dashboards
• confusing metrics
• poor trust from stakeholders

A better approach is what this project followed:

1. stage the raw data
2. create intermediate analytical logic
3. build mart models for business questions
4. expose trusted models to Looker Studio

That structure gives you a much more reliable foundation for business intelligence.

It also makes the project easier to explain to hiring managers and analytics leaders, because the architecture reflects how strong BI teams actually work.

Lessons from this project

A few takeaways stood out while building this:

First, raw data is not the same as analytics-ready data. Clean tables are useful, but they do not replace business modeling.

Second, customer behavior usually has to be engineered into the dataset. Order rank, total orders, first-order logic, and delivery buckets do not magically appear in transactional structures.

Third, the mart layer is where business meaning becomes visible. That is where metrics stop being technical and start being useful.

And finally, a strong BI project is not just about dashboards. It is about the semantic layer underneath them. That is where trust is built.

Final takeaway

The difference between OLTP and OLAP in modern BI projects lies in the shift from collecting facts to creating understanding.

In this e-commerce project, the OLTP-style raw tables captured the operational truth of the business: orders, products, payments, reviews, and customers.

But the OLAP layer turned that truth into something leadership could act on:

• funnel conversion
• cohort retention
• repeat purchase behavior
• delivery-driven retention insights
• customer lifecycle patterns

That is why raw data alone is never enough for modern BI.

Strong business intelligence happens when you transform transactions into decisions.

Call to action

If you enjoy practical content on business intelligence, dbt, BigQuery, analytics engineering, and dashboard design, connect with me on LinkedIn and explore more of my work on GitHub.

I share projects and ideas focused on turning data into clearer business decisions.