Fiaz AnjumBuilding Intelligent Data Platforms. Enabling Business Transformation.

# 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

-- 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;

-- 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

-- 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)

// 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 )

-- 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

-- 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;