High-volume B2B2C photography platform
One order model across two product lines, inside a live photography lakehouse
A high-volume photography platform needed more of its live data in its Databricks lakehouse, and reports that matched the source. We extended its change data capture pipeline, fixed how deletes reached reporting and merged both product lines into one order model.
Data & AnalyticsLakehouse reporting pipelinesModernized a live product
- Worked in
- The platform's Databricks, AWS and Terraform repos
- First month
- Ten core tables added and verified in the silver layer
- Order model
- One fact table across both product lines
- Reports
- Metabase moved to the updated reporting catalog
The challenge
Order, gallery and marketing data sat in live production databases for two product lines, and the reporting layer needed more tables, correct current state and one shared order model.
What we did
Working in the platform's own repositories and Databricks catalogs: new replication tasks, incremental loading, a fix to change data capture, and gold models joining both product lines.
The outcome
Reporting tables moved to the catalog Metabase reads, silver now reflects deletes correctly, and a unified order fact table merges both product lines, checked against Metabase.
The starting point
A high-volume B2B2C photography platform that runs online galleries and ordering for photography businesses and their customers, across two product lines. It has its own engineering team, data leadership and a Databricks lakehouse on AWS.
What had to change
Reporting needed order, cart, campaign and gallery tables that were not yet in the lakehouse, and the second product line's data was outside the shared model. Some reporting tables also did not match the source databases.
What could not break
- Production databases keep serving customers as they are
- New tables arrive through the client's own replication and Terraform repositories
- Changes are proven in the development catalog before production
- Production access follows the client's own permission process
- Reporting tables must reflect the true current state of each source
The calls we made
We learned the pipeline as it was, then extended and fixed it inside the platform's own repositories and catalogs.
- 01
Extend the pipeline already there
We read the existing raw to bronze and bronze to silver notebooks first, then added each table through the client's own replication tasks and notebooks, so its team kept one pipeline to run.
Ruled outA parallel pipeline the platform's team would have to run separately.
- 02
Incremental loads keyed on the source
For each table we identified primary keys and sort fields for change data capture, then built incremental loading into the silver layer instead of full reloads.
Ruled outFull reloads of every table on each run.
- 03
Silver has to match the source
When counts did not match, we traced them to the replication layer and fixed the change data capture step's delete handling and ranking, so silver shows the true current state.
Ruled outPatching the reports to hide the mismatch.
- 04
One order model for both product lines
Order data from the two product lines was merged into one gold fact table under one schema, then checked against the numbers Metabase already showed.
Ruled outA separate order report for each product line.
How the system fits together
What we built
Replication for new source tables
Change data capture tasks for order, cart, campaign, gallery and price sheet tables, then marketing tables and the second product line's orders, members and stores, all as pull requests in the client's Terraform repository.
Incremental silver layer
Incremental loading into silver, a fix to how the change data capture step handles deletes and ranks updates, and checks of counts against the source databases.
Reporting catalog for Metabase
Reporting tables moved into the client's reporting catalog, with Metabase pointed at the updated data.
Gold models across both product lines
A unified order fact table that merges both product lines under one schema, tuned for daily runs, and a rebuilt client gallery metrics model in SQL.
How it works
Capture
Replication tasks pick up inserts, updates and deletes from the live databases without changing them.
Land
Raw rows land in the bronze layer of the client's lakehouse.
Reconcile
Incremental jobs keep silver at the true current state of each source table.
Model
Gold jobs merge both product lines into one order fact table and gallery metrics.
Report
Metabase reads the reporting catalog and the gold models.
A closer look
What changed
The reporting layer gained the order, cart, campaign, gallery and marketing tables it was missing, and silver now reflects deletes and updates correctly. Metabase reads the updated reporting catalog, and a unified order fact table brings both product lines under one schema.
Under the hood
- Datics role
- Engineering delivered by Datics
- Sources
- Live MySQL production databases for both product lines, read through AWS DMS change data capture.
- Processing
- Delta Lake bronze, silver and gold layers in Databricks, with Spark notebooks and scheduled workflows.
- Consumers
- Metabase reports on the reporting catalog and the gold order and gallery models.
- Boundary
- Everything ran in the platform's own AWS and Databricks environment, through its Terraform repository and permission process.
Cloud and infrastructure
Also in the stack
How it shipped
Learn the pipeline
Sync meetings with the client's team, a read of the existing notebooks, and parity between the development and production replication setup.
Add and verify tables
Ten core tables added and verified in the development silver layer, with incremental loading built alongside.
Fix and widen
The change data capture fix, count mismatches traced to replication, marketing tables and the second product line's tables brought in.
Model for reporting
Tables moved to the reporting catalog for Metabase, then the unified order fact table and gallery metrics models in gold.
Through their repositories and reviews
New replication tasks went in as pull requests to the platform's shared Terraform repository, reviewed and merged by its own engineers.
Their catalogs, their access rules
All work ran in the platform's own AWS account and Databricks catalogs, on accounts it issued, with development tested before production.
Written reports each month
Every month closed with a written work report: tables added, fixes made and pull requests opened.
What this unlocked
With both product lines in one order model, reporting and any future forecasting or AI features start from the same numbers.
Related work
Have a product with the same kind of constraints?
Tell us what already exists and what you want it to do. We will tell you what it takes.