Fiaz AnjumData · BI · Transformation
Let's Connect

Data Architecture

From source systems to business decisions

A reference architecture for a modern enterprise analytics platform. It is the same pattern I implemented on ClickHouse with Airbyte, dbt and Apache Superset. Select any stage to see what happens there, why it matters, and an illustrative example. All code below is synthetic and simplified.

Interactive explorer

Eight stages of a modern analytics platform

Select a stage, or use the arrow keys, to see what happens there, why it matters, and an illustrative example.

Stage 1 of 8

Source Systems

Transactional systems where business events happen: ERP (ERPNext/GERP, SAP, Sage 300), operational systems, POS, CRM and core databases such as Oracle, SQL Server and PostgreSQL.

Why it matters

Analytics is only as good as its understanding of the source. Profiling tables, keys, update patterns and ownership up front prevents most downstream rework.

  • ERPNext / GERP
  • SAP ERP
  • Oracle
  • SQL Server
  • PostgreSQL
  • REST APIs
SQL · illustrative example
-- Profile a source table before designing ingestion (illustrative)
SELECT
  COUNT(*)                         AS row_count,
  COUNT(DISTINCT name)             AS distinct_keys,
  MIN(modified)                    AS first_change,
  MAX(modified)                    AS last_change,
  SUM(docstatus = 2)               AS cancelled_docs
FROM `tabPurchase Invoice`;

Stage 2 of 8

Data Ingestion & CDC

Data is moved from sources into the platform. Change Data Capture (CDC) reads the database change log and replicates only inserts, updates and deletes, so large tables never need a full reload.

Why it matters

CDC keeps the warehouse fresh with minimal load on production systems and preserves history for audits and late-arriving corrections.

  • Airbyte (CDC)
  • Azure Data Factory
  • SSIS
  • Python
YAML · illustrative example
# Illustrative Airbyte connection settings
source: erpnext_mariadb
destination: clickhouse_bronze
replication:
  method: CDC            # binlog-based change capture
  sync_mode: incremental_append_deduped
  schedule: every 1 hour
streams:
  - tabPurchase Invoice
  - tabPurchase Invoice Item
  - tabStock Ledger Entry
  - tabSales Invoice

Stage 3 of 8

Bronze Layer: Raw

An immutable, source-shaped landing zone. Records arrive exactly as extracted, with ingestion metadata and CDC operation flags.

Why it matters

Keeping raw history makes every downstream number reproducible and allows models to be rebuilt without returning to the source.

  • ClickHouse
  • ReplacingMergeTree
  • Airbyte metadata
SQL · illustrative example
-- ClickHouse Bronze table for CDC records (illustrative)
CREATE TABLE bronze.erp_purchase_invoice
(
  name              String,
  supplier          String,
  posting_date      Date,
  grand_total       Decimal(18, 2),
  docstatus         UInt8,
  modified          DateTime64(3),
  _ab_cdc_deleted   Nullable(DateTime64(3)),
  _ingested_at      DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(modified)
ORDER BY name;

Stage 4 of 8

Silver Layer: Cleansed & Conformed

dbt staging models clean, type-cast, deduplicate and standardize data. They apply business rules and conform keys across systems and sites.

Why it matters

One clean, conformed version of each entity, such as supplier, item, warehouse or customer, means every gold model starts from the same truth.

  • dbt
  • SQL
  • ClickHouse
SQL · illustrative example
-- models/silver/stg_purchase_invoice.sql (illustrative dbt model)
SELECT
  name                                   AS invoice_id,
  upper(trim(supplier))                  AS supplier_key,
  posting_date,
  toDecimal64(grand_total, 2)            AS invoice_amount
FROM {{ source('bronze', 'erp_purchase_invoice') }} FINAL
WHERE docstatus = 1                       -- submitted only
  AND _ab_cdc_deleted IS NULL

Stage 5 of 8

Gold Layer: Business-Ready

Subject-area models such as finance, procurement, inventory and sales, shaped as facts and dimensions with agreed business definitions.

Why it matters

This layer is what dashboards query. Putting the logic here, once, ends the problem of different reports disagreeing.

  • dbt
  • Dimensional modeling
  • ClickHouse
SQL · illustrative example
-- models/gold/fct_procurement_savings.sql (illustrative)
-- Saving = (Previous-Year Rate − Current-Year Rate) × Current-Year Qty
SELECT
  item_code,
  supplier_key,
  cy.qty                                  AS cy_qty,
  py.avg_rate                             AS py_rate,
  cy.avg_rate                             AS cy_rate,
  (py.avg_rate - cy.avg_rate) * cy.qty    AS saving_amount
FROM {{ ref('int_rates_current_year') }} AS cy
LEFT JOIN {{ ref('int_rates_previous_year') }} AS py
  USING (item_code, supplier_key)

Stage 6 of 8

Semantic Layer

Reusable metrics, dimensions and relationships defined once and shared by every dashboard: Superset datasets and metrics, or Power BI semantic models and DAX measures.

Why it matters

Business users explore with trusted measures, not raw columns. Changing a definition happens in one place.

  • Superset datasets
  • Power BI semantic models
  • DAX
DAX · illustrative example
// Illustrative Power BI measure
Saving % =
VAR PrevCost = SUMX ( Purchases, Purchases[CY Qty] * Purchases[PY Rate] )
VAR CurrCost = SUMX ( Purchases, Purchases[CY Qty] * Purchases[CY Rate] )
RETURN
    DIVIDE ( PrevCost - CurrCost, PrevCost )

Stage 7 of 8

BI & Analytics

Dashboards, paginated reports and self-service exploration in Apache Superset, Power BI, SSRS and Wyn Enterprise, secured with RLS/RBAC.

Why it matters

Clear visual design, fast queries and role-appropriate access are what turn a data platform into daily decision support.

  • Apache Superset
  • Power BI
  • SSRS
  • Wyn Enterprise
  • RLS / RBAC
SQL · illustrative example
-- Superset row-level security filter clause (illustrative)
-- Applied to the 'Plant Managers – Multan' role
plant_code = 'MUL'

-- Power BI RLS rule equivalent (DAX)
-- [Region] = USERPRINCIPALNAME() lookup via a security table

Stage 8 of 8

Business Decisions

Procurement negotiations, inventory replenishment, sales targeting, branch performance reviews and policy decisions, all backed by consistent and timely data.

Why it matters

This is the point of the platform. Usage analytics close the loop by showing which dashboards are used and by whom.

  • Usage analytics
  • KPI reviews
  • Training & adoption
SQL · illustrative example
-- Dashboard adoption from Superset metadata (illustrative)
SELECT d.dashboard_title,
       count(DISTINCT l.user_id) AS active_users,
       count(*)                  AS views_30d
FROM logs l
JOIN dashboards d ON d.id = l.dashboard_id
WHERE l.dttm >= now() - INTERVAL 30 DAY
GROUP BY d.dashboard_title
ORDER BY views_30d DESC;

Reference implementation

An open-source stack in practice

The pattern as implemented with ERPNext, Airbyte, ClickHouse, dbt, SQL, Python and Apache Superset.

  1. Sources

    • ERPNext
    • Operational DBs
    • APIs

  2. Ingestion

    • Airbyte CDC
    • Python

  3. Bronze

    • ClickHouse raw

  4. Silver

    • dbt staging
    • Tests

  5. Gold

    • dbt marts
    • SQL

  6. Semantic

    • Superset datasets
    • Metrics

  7. BI

    • Superset
    • RBAC

  8. Decisions

    • Usage analytics
Conceptual architecture: Sources → Ingestion → Bronze → Silver → Gold → Semantic → BI → Decisions. Simplified; no confidential system details.

Core concepts

What every enterprise platform must get right

  • Data Ingestion

    Batch or CDC extraction from ERP, operational and cloud sources, scheduled and monitored, with minimal impact on production systems.

  • Change Data Capture

    Log-based replication of inserts, updates and deletes keeps the warehouse current and preserves change history.

  • Transformation

    Version-controlled dbt and SQL models move data from raw to cleansed to business-ready, with dependencies managed automatically.

  • Data Quality

    Uniqueness, not-null, accepted-value and reconciliation tests run with every build, so issues are caught before they reach a dashboard.

  • Data Modeling

    Conformed dimensions and subject-area facts give every report the same definitions of supplier, item, customer and period.

  • Data Serving

    Analytical engines such as ClickHouse and SQL Server, plus pre-aggregation and query tuning, keep dashboards fast at scale.

  • Reporting & Visualization

    Dashboards and paginated reports designed around decisions, with consistent KPI cards and drill paths.

  • Governance & Security

    RLS/RBAC, access reviews, usage monitoring and documentation make analytics both secure and auditable.

Business & Project Collaboration

Have a Data, Analytics or Digital Transformation Project?

Looking to modernize your data infrastructure, build an enterprise data warehouse, automate business reporting, or implement intelligent business analytics?

Let's discuss your requirements and explore how I can help turn your business challenges into practical technology solutions.

Discuss Your Project Send a Project Inquiry Connect on LinkedIn

Engagements I can discuss

  • Enterprise Data Engineering Projects
  • Data Warehouse & Lakehouse Implementation
  • Power BI & Superset Development
  • Business Intelligence Consulting
  • ERP Data Integration
  • Reporting Automation
  • Digital Transformation Initiatives
  • Analytics Architecture & Strategy
  • Technical Consulting & Collaboration

Inquiries are handled personally and confidentially. I take on engagements as an independent professional, not as a registered consulting firm.