- Client case study · Data integration / ELT
- Client anonymised with their approval
DTC + manufacturing: one trusted data layer.
A growing direct-to-consumer manufacturer could not reliably reconcile customer, order, production and inventory information across its operational systems.
The engagement at a glance
What Quorlytic built.
Quorlytic consolidated the manufacturer's e-commerce, on-premise ERP and CRM records into one tested data layer on BigQuery — managed incremental and CDC ingestion, layered dbt Cloud models and governed Looker metrics — so sales, product identity, margin and inventory are reported from the same approved definitions.
Business friction
Different product IDs, delayed reports and conflicting margin calculations.
Engineering response
Automated ELT, identity reconciliation and tested, layered dbt models.
Decision enabled
A consistent view of acquisition, sales, margin and inventory.
Architecture
The architecture, and the decisions that hold it up.
Transformation plane: dbt Cloud orchestrates 7 staging + 1 identity integration + 5 curated models, with tests and lineage before semantic publishing.
Design decisions that mattered
Source contracts
Define API pagination, incremental keys, CDC boundaries, retries and source ownership.
Product identity
Maintain an audited SKU-to-ERP mapping with review rules for ambiguous matches.
Metric authority
Approve sales recognition, margin logic and inventory rules with business owners.
Delivery
A focused two-week delivery sequence.
The first release was delivered in two weeks, in five work packages, each closing on evidence the business could review.
- Week 1 / Start
Discover + align
Map source systems, confirm access and security boundaries, inventory fields and refresh requirements. Agree initial dashboard wireframes, KPI ownership, source-to-target mappings and measurable acceptance checks.
Exit evidence: Signed decision brief; mapping register; dashboard wireframes.
- Weeks 1–2
Ingest + model
Provision isolated environments and secure connections; configure API pulls and ERP CDC; build dbt staging models, the product identity integration model and five curated outputs. Configure Looker semantic dimensions and measures.
Exit evidence: Working connectors; versioned dbt project; semantic definitions.
- Week 2 / QA
Validate + reconcile
Run freshness, uniqueness, nullability and relationship tests. Compare order counts, quantities, returns and margin logic with source owners; inspect unmatched SKUs and perform business UAT.
Exit evidence: Test report; discrepancy register; business sign-off.
- Week 2 / Live
Release + observe
Promote approved code, secure production credentials and schedules, execute smoke tests, verify refresh behavior and confirm alert routing and rollback procedure.
Exit evidence: Production pipeline; monitoring evidence; rollback point.
- Week 2 / Close
Enable + transfer
Train dashboard users and platform owners. Deliver lineage diagrams, metric dictionary, access map, known limitations, incident runbook and ownership handover.
Exit evidence: Runbooks; training records; final acceptance checklist.
Delivery depended on timely source credentials, available data owners, stable access paths and prompt UAT decisions.
Data models
From messy inputs to usable models.
Thirteen structured dbt models in three layers: staging that preserves raw meaning, one identity bridge, and curated outputs the business reads from.
- 07
Staging models
Type, normalize and preserve raw meaning.
- 01
Integration model
Resolve identity across systems.
- 05
Curated models
Publish governed business outputs.
Layer-by-layer engineering
Staging / source-aware
Normalize timestamps and currency fields; retain source keys and ingestion metadata; type order, line-item, customer, opportunity, product and inventory records; detect duplicates and malformed payloads.
Integration / identity bridge
The int__product_unified model joins commerce SKUs with ERP manufacturing identifiers, keeping match confidence, unmatched exceptions and approved overrides.
Warehouse / decision-ready
A small, governed set of dimensions and facts, including sales_fct and margin_summary, with reconciliation lineage from each dashboard measure to its upstream source.
Semantic layer / trusted metrics
Approved measures exposed through Looker with business names, descriptions, filters, date grains and viewer permissions, so dashboard teams cannot publish conflicting definitions.
Operational controls
Reliable by design — not just at launch.
Governance, QA and incident ownership formed part of the deliverable, rather than being added after dashboards went live.
Zone isolation
Separate staging, integration and curated datasets; grant least-privilege access.
Safe changes
Distinct dbt development and production targets, reviews and rollback procedures.
Observability
Freshness checks, error alerts, run histories, retry policy and on-call ownership.
Semantic security
Only authorized curated aggregates appear in stakeholder dashboards.
Acceptance checkpoints
- All three sources ingest through approved secure routes; provenance is traceable.
- Critical modeled keys have documented uniqueness, relationship and null tests.
- Commerce-versus-ERP SKU exceptions are either resolved or visible to an owner.
- Approved KPI calculations reconcile to business-accepted reference extracts.
- Failure alerts, refresh schedules, permissions and rollback are demonstrated.
Risks and responses
Risks managed during delivery.
API drift
Version and monitor schemas; alert owners before dependent models break.
SKU mismatches
Record fuzzy or manual mapping decisions and reconcile exceptions during UAT.
KPI disagreement
Approve formulas, inclusion rules and edge cases before publishing.
Refresh disruption
Automate retries and alarms; document backfills and recovery responsibilities.
Questions
Questions about this engagement.
What did Quorlytic build for the direct-to-consumer manufacturer?
One tested data layer on BigQuery that consolidates e-commerce, on-premise ERP and CRM records. Managed ingestion loads the sources incrementally, dbt Cloud runs staging, identity-integration and curated models with tests and lineage, and Looker publishes governed commercial KPIs.
How long did the first release take?
Two weeks, in five work packages: discover and align, ingest and model, validate and reconcile, release and observe, and enable and transfer. Each closed on evidence the business could review.
How was product identity resolved across commerce and ERP?
An integration model joins commerce SKUs to ERP manufacturing identifiers through an audited mapping, keeping match confidence, unmatched exceptions and approved overrides visible to an owner rather than silently merging records.
Which technologies were used?
Managed incremental and CDC ingestion into BigQuery raw and typed zones, dbt Cloud for staging, identity-integration and curated models, and Looker for governed KPIs and views. The sources were an e-commerce API, an ERP on PostgreSQL and a CRM REST API.
Your next data decision
Have a data foundation to build?
Tell us the decision you need to support. We will shape the engineering brief around it.