Tooba Arshad

Posted on Oct 23

Architectural Deep-Dive: Enterprise Cross-Border E-Commerce Fraud & FinOps Engine on Microsoft Fabric

#azure #python #sql #datascience

Modern international e-commerce platforms handle massive streams of real-time clickstream data. Processing this data effectively requires solving two primary operational and architectural bottlenecks:

High-Velocity Fraud Attacks: Detecting bot networks or malicious actors triggering geographical events across multiple countries within milliseconds.
Multi-Currency FinOps Consolidation: Standardizing localized transactional currencies (INR, EUR, GBP) to a unified currency (USD) for real-time executive analytics and FinOps cost tracking.

To tackle these challenges at cloud scale, I architected and deployed an end-to-end Medallion Lakehouse Architecture using Microsoft Fabric, Azure Data Lake Storage Gen2 (ADLS Gen2), PySpark, and T-SQL.

🏗️ End-to-End System Architecture

Below is the high-level system architecture illustrating data flow from ingestion to serving:

Technology Stack & Services:

Storage & Virtualization: ADLS Gen2, Microsoft Fabric OneLake, OneLake Shortcuts, Azure Key Vault
Silver Processing Layer: Microsoft Fabric Synapse Notebooks (PySpark)
Gold Analytical Serving: Fabric SQL Analytics Endpoint, T-SQL Window Functions
Orchestration & Governance: Fabric Data Factory Pipelines, SHA-256 PII Masking, Z-Ordering, Power BI (Direct Lake)

💡 Engineering Highlights & Implementation Deep-Dive

1. Zero-Copy Ingestion via OneLake Shortcuts

Traditional ETL pipelines suffer from redundant data duplication and elevated cloud storage costs when moving raw data from external data lakes to analytics engines.

The Solution: I leveraged OneLake Shortcuts to directly map raw clickstream files sitting in ADLS Gen2 into the Fabric Bronze Lakehouse.
Impact: Zero data movement, zero duplication cost, and instantaneous schema-on-read access.

2. Silver Layer Transformation & Security (PySpark)

Raw clickstream payloads contain heterogeneous schema definitions, unmasked user PII, and non-standard timestamps. Using PySpark in Fabric Synapse Notebooks, I executed:

UTC Normalization: Converted local timestamps across diverse international time zones into standard UTC format.
FinOps Currency Standardizer: Dynamically calculated conversions for EUR, GBP, and INR into unified USD figures using transactional lookups.
GDPR Compliance (SHA-256 Hashing): Anonymized sensitive fields (email IDs, IP addresses, user credentials) using cryptographic SHA-256 hashing functions prior to Gold layer promotion.

# Sample PySpark SHA-256 PII Masking & UTC Normalization
from pyspark.sql.functions import sha2, col, to_utc_timestamp

silver_df = raw_df \
    .withColumn("masked_user_id", sha2(col("raw_user_id").cast("string"), 256)) \
    .withColumn("event_timestamp_utc", to_utc_timestamp(col("event_timestamp"), "Asia/Kolkata")) \
    .drop("raw_user_id")

3. Real-Time Velocity Fraud Engine (T-SQL)

Threat Vector: Fraudulent botnets often utilize VPN proxies or stolen session credentials to execute high-frequency transactions across different geographical nodes.
Analytical Logic: Built an analytical T-SQL view on the Fabric SQL Analytics Endpoint using the LAG() analytic function over a partitioned window (PARTITION BY user_id ORDER BY event_timestamp_utc).
Detection Threshold: If a user_id records sequential events in two distinct country_code values within a 10-second threshold, the transaction is flagged immediately in the vw_velocity_fraud_alerts view.

-- T-SQL Velocity Fraud Window Function

CREATE VIEW vw_velocity_fraud_alerts AS
WITH RankedEvents AS (
    SELECT 
        user_id,
        country_code,
        event_timestamp_utc,
        LAG(country_code) OVER (PARTITION BY user_id ORDER BY event_timestamp_utc) AS prev_country,
        LAG(event_timestamp_utc) OVER (PARTITION BY user_id ORDER BY event_timestamp_utc) AS prev_timestamp
    FROM gold_clickstream_analytics
)
SELECT 
    user_id,
    country_code,
    prev_country,
    DATEDIFF(SECOND, prev_timestamp, event_timestamp_utc) AS time_difference_seconds
FROM RankedEvents
WHERE country_code <> prev_country 
  AND DATEDIFF(SECOND, prev_timestamp, event_timestamp_utc) <= 10;

4. Storage & Performance Optimization (Z-Ordering)

The Bottleneck: Querying billions of rows in the Gold Lakehouse layer can lead to high latency if table layouts suffer from small file problems or lack spatial clustering.
Data Co-location: Applied Z-Ordering (ZORDER BY user_id, country_code) on Delta Lake tables to organize physical data files.
Maintenance Automation: Scheduled automated OPTIMIZE and VACUUM maintenance tasks via Data Factory to manage Delta transaction log retention and merge fragmented parquet files.

5. Production Orchestration

Execution Schedule: Automated end-to-end execution every 3 hours using Microsoft Fabric Data Factory.
Monitoring & Reliability: Integrated failure notification channels and run history audit logging for continuous monitoring.

📁 Source Code & Repository

Repository Access: The full repository containing synthetic data generators, PySpark notebooks, SQL DDL/DML scripts, and pipeline orchestration manifests is publicly available on GitHub.
GitHub Link: 🔗 https://github.com/Tooba-Analytics/ecommerce-clickstream-pipeline-fabric

🎯 Key Takeaways & Conclusion

Architecture Impact: By implementing modern Lakehouse patterns on Microsoft Fabric, this platform successfully bridges real-time security engineering (Velocity Fraud Detection) with executive financial reporting (FinOps).
Community Engagement: I welcome any questions, architecture feedback, or discussion points from the data engineering community! Feel free to drop a comment below or connect with me on LinkedIn. 🚀

DEV Community