Lab Type

Master Capstone Lab

Difficulty

Expert / Master Hands-On

Reward

1,000 XP · 🏆 Hands-On Data Engineer

Core Stack

PySpark · Delta Lake · Snowflake

Deliverable

Full Multi-Source Lakehouse Platform

🏆 Master Hands-On Capstone

DEV-015

Build an End-to-End Data Engineering Platform

The ultimate hands-on test of data engineering mastery. Given five disparate enterprise datasets (customers, products, stores, POS transactions, and customer clickstream events), build the complete end-to-end production platform from scratch: multi-source ingestion, strict schemas, quarantine routing, Silver conformed layers, Gold dimensional star schemas, incremental merge logic, unit tests, and telemetry.

Ingest 5 Sources

Bronze & Quarantine

Silver Conformed

Gold Star Schema

Testing & Certification

Mission & Architectural Blueprint

Review the complete pipeline flow from raw multi-source ingestion to Snowflake dimensional serving.

The Master Engineering Challenge

Unlike theoretical architecture prompts, DEV-015 requires you to write, test, and execute actual production code. You are given five real starter datasets spanning structured relational files (customers, products, stores), transactional batches (sales.csv), and semi-structured clickstream JSON streams (customer_events.json).

Your mission is to construct an enterprise-grade platform fulfilling all 10 core engineering disciplines: from schema definition and quarantine isolation to conformed dimensional modeling, performance tuning, and financial auditability.

1. Multi-Format Ingestion

Seamlessly ingest CSV files and nested JSON event streams using explicit StructTypes.

2. Automated Quarantine

Route corrupted prices, negative quantities, and unparseable rows to bad-record Delta tables.

3. Conformed Star Schema

Build DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE, and FACT_ENTERPRISE_SALES in Gold.

4. Clickstream Attribution

Join clickstream add-to-cart events with completed sales transactions to compute conversion rates.

Starter Datasets & Inputs

Inspect all five enterprise datasets provided for this master challenge. Switch tabs to preview and copy each file.

📄 customers.csv

customer_id,first_name,last_name,email,phone,city,state,signup_date
C-1001,Aarav,Sharma,aarav.sharma@example.com,+91-9876543210,Mumbai,MH,2023-01-15
C-1002,Diya,Patel,diya.patel@example.com,9876543211,Ahmedabad,GJ,2023-02-20
C-1003,Rohan,Gupta,rohan.gupta@example.com,+91-9876543212,Bengaluru,KA,2023-03-10
C-1004,Ananya,Iyer,,9876543213,Chennai,TN,2023-04-05
C-1005,Kabir,Mehta,kabir.mehta@example.com,+91-9876543214,Delhi,DL,2023-05-12
C-1002,Diya,Patel,diya.patel@example.com,9876543211,Ahmedabad,GJ,2023-02-20
C-1006,Pooja,Verma,pooja.verma@example.com,INVALID_PHONE,Pune,MH,2023-06-18
C-1007,Vikram,Singh,vikram.singh@domain_missing,9876543216,Jaipur,RJ,2023-07-22
C-1008,   Neha   ,Deshmukh,neha.d@example.com,+91-9876543217,Nagpur,MH,2023-08-30
C-1009,Aditya,Reddy,aditya.reddy@example.com,9876543218,Hyderabad,TS,2023-09-14

💡 Dataset Contract: Customer master feed with duplicates (C-1002), null email (C-1004), and untrimmed names.

The 10 Hands-On Engineering Objectives

Implement all 10 modules to complete the master capstone platform.

Objective 1: Multi-Source Ingestion

Load all 5 datasets (customers, products, stores, sales, clickstream events).

▸Implement PySpark readers for CSV files with header=True and multiLine=True for JSON.

▸Save raw snapshots to Bronze Delta tables with _ingest_ts and _source_file audit columns.

Objective 2: Schema Enforcement

Define strict StructTypes for all 5 entities without inferSchema.

▸Map financial columns to DecimalType(10,2) and quantities to IntegerType().

▸Parse nested JSON event metadata (device, ip) into strongly-typed StructFields.

Objective 3: Automated Quarantine Framework

Separate clean records from corrupt records into dedicated Delta tables.

▸Filter out negative quantities, negative prices, and unparseable dates.

▸Append rejected records to quarantine_records with reject_reason and original_payload.

Objective 4: Silver Conformed Layer

Deduplicate primary keys and standardize strings across entities.

▸Deduplicate customers by customer_id and sales by txn_id.

▸Trim whitespace from names and standardize state/region codes to uppercase.

Objective 5: Gold Dimensional Star Schema

Build production Star Schema tables in Gold.

▸Create DIM_CUSTOMER, DIM_PRODUCT, and DIM_STORE with surrogate integer keys.

▸Create FACT_ENTERPRISE_SALES containing net_revenue, discount_amount, and units_sold.

▸Map missing customers or products to default -1 (Unknown) surrogate keys.

Objective 6: Clickstream Conversion Attribution

Correlate clickstream ADD_TO_CART events with completed transactions.

▸Calculate conversion funnel: VIEW -> ADD_TO_CART -> PURCHASE per product category.

▸Identify abandoned carts where an ADD_TO_CART event had no matching purchase within 2 hours.

Objective 7: Incremental Processing Logic

Implement idempotent Delta MERGE logic for new arriving transaction batches.

▸Ensure running the same sales batch twice does NOT double revenue or insert duplicate rows.

▸Use MERGE INTO silver_sales USING new_batch ON silver_sales.txn_id = new_batch.txn_id.

Objective 8: Performance Optimization

Apply at least two significant performance optimizations.

▸Apply broadcast(dim_store) and broadcast(dim_product) during fact enrichment.

▸Enable Delta Liquid Clustering or Z-ORDER BY (store_id, txn_date) on FACT_ENTERPRISE_SALES.

Objective 9: Operational Telemetry & Monitoring

Emit execution metrics and SLA tracking to an audit Delta table.

▸Log batch_id, rows_ingested, rows_quarantined, rows_loaded_to_gold, and execution_duration_sec.

▸Compute data freshness lag: current_timestamp() - max(txn_timestamp).

Objective 10: Production Architecture Runbook

Document the complete architecture, data dictionary, and operational runbook.

▸Write README covering entity relationships, quarantine rules, and emergency backfill SOPs.

Expected Output & Gold Volumes

Verify your completed platform yields these exact record counts.

Silver Clean Customers

8 Customers

Deduplicated & trimmed

Silver Clean Products

9 Products

PRD-006 quarantined

Silver Clean Sales

9 Sales Rows

Deduplicated & valid

Total Quarantined

5 Records

Across sales & products

Validation Checks & Master Audit Queries

Execute these validation queries to certify your capstone implementation.

SQL Test 1 — Financial Reconciliation (Variance must be exactly 0.00)

SELECT 
    ROUND(SUM(net_amount), 2) AS silver_net_revenue,
    (SELECT ROUND(SUM(net_revenue), 2) FROM gold_fact_sales) AS gold_net_revenue,
    ROUND(SUM(net_amount) - (SELECT SUM(net_revenue) FROM gold_fact_sales), 2) AS variance
FROM silver_sales;

SQL Test 2 — Referential Integrity & Default Unknown Keys

-- Ensure no orphan foreign keys exist; unmatched records must map to -1
SELECT COUNT(*) AS orphan_keys 
FROM gold_fact_sales 
WHERE customer_key NOT IN (SELECT customer_key FROM gold_dim_customer)
   OR product_key NOT IN (SELECT product_key FROM gold_dim_product)
   OR store_key NOT IN (SELECT store_key FROM gold_dim_store);

Evidence To Submit

Complete portfolio package required for master certification.

☑

1. Complete Platform Source Code

Modularized repository containing ingestion/, silver/, gold/, and tests/ modules.

☑

2. Quarantine Telemetry Audit

Screenshot or log showing records diverted to quarantine with descriptive error tags.

☑

3. Gold Star Schema DDL & Sample Query

SQL query demonstrating multi-way join between FACT_ENTERPRISE_SALES and all three dimensions.

☑

4. Incremental Re-run Proof

Evidence showing identical row counts and metrics after re-running the sales pipeline twice.

☑

5. Financial Reconciliation Result

Output proving zero financial variance between Silver clean sales and Gold analytical facts.

☑

6. Operational Architecture Guide (README.md)

Complete documentation of data models, state machines, and disaster recovery procedures.

Master Capstone Certification

Complete DEV-015 to Earn 1,000 XP & Hands-On Data Engineer Badge

The pinnacle of the Hands-On Engineering Lab Series. Build the complete multi-source platform, pass all validation queries, and submit your production portfolio.

🏆

1,000 XP

Hands-On Data Engineer