← All work

02 · In production

Retail Analytics Lakehouse

CRM, orders and three ad platforms in one governed lakehouse — with zero load on the production database.

Period
Feb 2025 – Mar 2026
Role
Data Engineer · Siyuan Digital Intelligence
Stack
Databricks · dbt · Fivetran · AWS · Power BI · Python
CRM & order tables
40+
rows a month via CDC
~100K
ad platforms unified
3

The problem

The marketing team wanted to know which regions’ ad spend actually brings customers back. The answer was spread across a CRM, an order management system (OMS) and three ad platforms — and the only way to get order data was to query the production database directly.

What I built

An end-to-end lakehouse on Databricks with two independent ingestion paths:

  • CRM and orders: the source database was first split into a write primary and a WAL-based read replica. Fivetran CDC connects only to the replica, so syncing never touches production.
  • Ad platforms: Python jobs pull Amazon Selling Partner, Google Ads and Meta Ads data through their REST APIs.

Inside Databricks, dbt models follow a strict Medallion contract: Bronze keeps the source shape, Silver holds every business rule, and Gold exposes a star schema for Power BI.

Key design decisions

  1. WAL read replica — CDC reads only the replica, so production carries zero sync load.
  2. Logic written once — every business rule lives in Silver’s int_* models; repurchase flags surface as fct_orders.is_repurchase.
  3. dim_geography bridge — maps order regions and ad-targeting regions onto one key.
  4. ROI as a measure — computed in Power BI, so it aggregates correctly over any time window.
Architecture — retail lakehouse: two ingestion paths into a dbt Medallion model on Databricks, feeding two Power BI models.

CRM and order data reach Databricks through a WAL read replica and Fivetran CDC; three ad platforms come in through Python REST jobs. In Silver, raw tables become customers, orders, order items and daily ad spend; customers and orders combine into customer orders and then repurchase flags, and ad spend plus the geo reference becomes regional ad attribution. Gold holds the facts (orders, order items, ad spend) and dimensions (date, customers, products, channel, geography), which feed the Repurchase × Discount and Regional Ad Effectiveness models.

The impact

  • About 40 CRM and order tables plus 3 ad APIs in one governed analytics platform.
  • About 100K rows a month synced through CDC with zero load on the production database.
  • Two Power BI models in production: Regional Ad Effectiveness, and Repurchase × Discount Correlation.

Key decisions

  • Business logic lives in one place. Deduplication, status rules and repurchase flags are defined once in Silver and inherited by every mart.
  • A bridge for regions. Order regions and ad-targeting regions share no keys, so both map onto a canonical dim_geography.
  • ROI as a measure, not a column. Computing ROI in Power BI keeps it correct for any time window.