Difficulty

Expert

Reward

900 XP · 🏛️ Data Platform Architect

Prerequisites

Heterogeneous Sources · Data Modeling · Lakehouse

Primary Stack

Databricks · PySpark · Snowflake

Target

Unified Enterprise Lakehouse

Development Task

DEV-009

Multi-Source Enterprise Data Platform

Architect and build a unified enterprise data platform that integrates four disparate operational sources (Customer DB, Sales Files, Product REST API, and Store Reference Data) into a conformed, trusted medallion lakehouse with cross-source entity resolution, freshness SLA tracking, and financial reconciliation in Snowflake.

Project Brief

Understand the challenges of heterogeneous enterprise data integration and master data unification.

Business Scenario

A rapidly scaling global enterprise manages business operations across multiple legacy and cloud-native systems. Customer profiles reside in an operational PostgreSQL database, daily sales transactions arrive as batch CSV/JSON files in Cloud Storage, product catalog metadata is retrieved via an external partner REST API, and store location reference data is managed in enterprise ERP tables.

Because these systems operate in silos with different update frequencies, mismatched natural keys, varying data quality standards, and delivery schedules, business analysts report conflicting customer counts, orphan transactions with missing product descriptions, and mismatched revenue numbers between store and online channels.

Development Objective

Architect and implement a unified, multi-source enterprise data platform in Databricks and PySpark. The platform must ingest all four disparate sources, standardize schemas into Bronze storage, execute entity resolution and cross-source joins in Silver, enforce strict data quality assertions and error isolation, track source data freshness SLAs, and publish a conformed, query-optimized Gold enterprise dimensional warehouse to Snowflake.

Expected Outcome

Heterogeneous Ingestion

Seamlessly ingest JDBC relational tables, cloud storage files, REST API endpoints, and reference tables.

Source Failure Isolation

Ensure a failure or delay in the Product API does not halt sales transaction processing or corrupt existing entities.

Cross-Source Entity Resolution

Reconcile disparate customer IDs, product SKUs, and store codes across all originating source feeds.

Freshness SLA Tracking

Monitor and emit telemetry on source arrival times, ingestion lag, and data staleness thresholds.

Unified Enterprise Data Model

Deliver conformed Customer, Product, Store, and Sales Fact tables adhering to enterprise dimensional standards.

Snowflake Enterprise Serving

Synchronize unified enterprise dimensions, facts, and executive reporting views to Snowflake.

Developer Focus

The defining challenge of an enterprise platform architect is managing heterogeneity and failure isolation. Your platform must demonstrate that downstream reporting remains reliable and consistent even when upstream sources experience schema drift, late arrivals, or temporary API outages.

Source System

Inspect the four source systems, ingestion cadences, schema specifications, and integration contracts.

Four Originating Enterprise Sources

The enterprise platform ingests from four distinct source systems with differing protocols and refresh intervals:

Source A — Customer DB

PostgreSQL JDBC / Batch Extract (Daily)

Source B — Sales Files

Cloud Storage CSV / Parquet (Hourly)

Source C — Product API

External REST API (Paginated JSON / 6h)

Source D — Store Reference

ERP Master Data Tables (Weekly/Daily)

Primary Keys

Natural Source IDs mapped to Enterprise Surrogate Keys

Serving Target

Unified Snowflake Enterprise Warehouse

Source Schema Entities & Contracts

Source A: customer_master*

RELATIONAL DB

cust_id, full_name, email, phone, address_line, city, state, country, loyalty_tier, updated_at.

Source B: sales_transactions*

STORAGE FILES

txn_id, store_code, customer_ref, sku_id, txn_timestamp, qty, unit_price, discount, total_net.

Source C: product_catalog*

REST API (JSON)

product_sku, title, category_name, sub_category, brand_name, wholesale_cost, msrp, is_active.

Source D: store_reference*

ERP MASTER

store_id, store_name, region_code, territory, store_format, square_ft, open_date, status.

Known Cross-Source Edge Cases

Mismatched Identifier Formats

Customer DB uses integer IDs (10492) while Sales Files use prefixed strings ('C-10492').

Late-Arriving Product Metadata

Sales transactions may arrive referencing new SKU items not yet indexed in the Product REST API.

Out-of-Sync Update Frequencies

Sales files arrive hourly while customer and store references refresh once daily or weekly.

Partial Pipeline Upstream Outages

If the external Product REST API experiences a 503 outage, sales and customer ingestion must proceed unaffected.

Architecture Standard

Every source ingestion pipeline must be decoupled. Never run all four source extracts in a single brittle, monolithic script where one network timeout crashes the entire platform.

Expected Architecture

Unified lakehouse architecture: decoupled Bronze staging, Silver entity resolution, and Gold data mart.

Multi-Source Integration Flow

The architecture standardizes heterogeneous feeds into raw Bronze storage, performs entity resolution and cross-source joins in Silver, and publishes a unified Star Schema to Snowflake.

Heterogeneous Ingestion

JDBC / Cloud Files / Requests

Ingest Customer DB, Sales Files, Product API, and Store Reference independently.

↓

Bronze Layer (Isolated)

Delta Lake (Append-Only)

Store raw untransformed snapshots with source metadata: BRONZE_CUSTOMERS, BRONZE_SALES, BRONZE_PRODUCTS, BRONZE_STORES.

↓

Silver Entity Resolution

PySpark / Delta Lake

Standardize schemas, map natural IDs to unified identifiers, resolve late-arriving dimensions, and quarantine bad records.

↓

Gold Enterprise Data Mart

PySpark / Dimensional Model

Assemble conformed DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE, and FACT_ENTERPRISE_SALES tables.

↓

Snowflake Enterprise Serving

Snowflake

Publish validated dimensional tables, clustering keys, and curated semantic views for executive BI.

Architecture Expectations

Fault-Isolated Ingestion Modules

Each source pipeline runs as an independent task; failures in one source do not block or corrupt other sources.

Default Conformed Keys (-1 Unknown)

Transactions referencing missing products or customers resolve to default unknown (-1) surrogate keys without dropping.

Source Freshness Telemetry

Track max(event_timestamp) and ingestion_timestamp for each source to detect stale or missing upstream deliveries.

Strict Financial Reconciliation

Total revenue in the unified enterprise sales fact table must equal the sum of trusted sales input files exactly.

Architecture Principle

In a multi-source platform, entity resolution must be centralized in Silver. Do not allow individual source ingestion jobs to execute cross-source joins before raw data has been safely landed in Bronze.

Development Requirements

Implement ingestion modules, entity mapping logic, dimensional modeling, and Snowflake synchronization.

Developer Responsibilities

The implementation must address the following 15 engineering requirements across multi-source ingestion, entity resolution, data quality, and dimensional delivery.

Source A (Customer DB) Ingestion

Ingest customer master records from relational database.

Required Checklist

✓Implement JDBC or batch reader capturing customer_master records.

✓Land raw records in Bronze BRONZE_CUSTOMER_RAW with extraction timestamps.

✓Handle incremental extracts based on updated_at watermark.

Expected Outcome

Reliable customer master Bronze feed.

Source B (Sales Files) Ingestion

Ingest hourly transaction batch files from cloud storage.

Required Checklist

✓Implement cloud file reader ingesting CSV/Parquet transaction batches.

✓Capture file metadata: file_path, file_modification_time, batch_id.

✓Land raw transactions in Bronze BRONZE_SALES_RAW.

Expected Outcome

Continuous hourly transaction capture.

Source C (Product REST API) Ingestion

Ingest product catalog metadata via external REST API.

Required Checklist

✓Implement Python HTTP client with pagination and token authentication.

✓Persist raw JSON response payloads in Bronze BRONZE_PRODUCT_RAW.

✓Handle transient HTTP 429/5xx errors with exponential backoff.

Expected Outcome

Reliable product catalog ingestion.

Source D (Store Reference) Ingestion

Ingest store location reference tables.

Required Checklist

✓Ingest store master data into Bronze BRONZE_STORE_RAW.

✓Capture store geography, format, and operational status attributes.

Expected Outcome

Standardized store master Bronze feed.

Schema Normalization & Cleansing

Standardize column naming and data types across all Bronze feeds.

Required Checklist

✓Normalize column names into snake_case standard across all sources.

✓Cast all dates and timestamps to UTC TIMESTAMP and monetary amounts to DECIMAL(12,2).

✓Standardize state, country, and currency codes to ISO standards.

Expected Outcome

Consistent, strongly-typed Silver baseline tables.

Cross-Source Entity Identifier Resolution

Map disparate natural keys into unified enterprise identifiers.

Required Checklist

✓Build mapping logic resolving integer cust_id and string 'C-XXXX' into unified enterprise customer keys.

✓Normalize product SKU strings (trim whitespace, remove hyphens) across sales and catalog feeds.

Expected Outcome

Harmonized entity keys across disparate sources.

Late-Arriving Dimension Handling

Prevent dropped transactions when dimensions arrive late.

Required Checklist

✓Map missing customer, product, or store lookups to default -1 (Unknown) surrogate keys in Silver.

✓Log late-arriving dimension occurrences to telemetry table for reconciliation.

✓Update fact surrogate keys when dimension records arrive in subsequent batches.

Expected Outcome

Zero dropped sales transactions due to late dimension feeds.

Conformed Dimension Modeling

Build enterprise conformed dimensions.

Required Checklist

✓Build DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE, and DIM_DATE in Silver/Gold.

✓Implement surrogate key generation and SCD Type 1 & Type 2 historical attribute tracking.

Expected Outcome

Conformed, reusable dimension tables.

Enterprise Fact Table Construction

Assemble the centralized FACT_ENTERPRISE_SALES table.

Required Checklist

✓Join sales transactions with conformed dimension surrogate keys.

✓Calculate fully additive metrics: gross_revenue, discount_amount, net_revenue, units_sold.

✓Include line-item detail and transaction-level metadata.

Expected Outcome

Unified enterprise sales fact table.

Source Freshness & SLA Tracking

Monitor data arrival latency across all four source systems.

Required Checklist

✓Calculate freshness lag: current_timestamp() - max(source_event_timestamp) for each source.

✓Log freshness metrics to operational telemetry table.

✓Trigger alert flags when any source exceeds freshness SLA (e.g., Sales > 2 hours, API > 12 hours).

Expected Outcome

Full visibility into data freshness and pipeline lag.

Cross-Source Financial Reconciliation

Guarantee 100% financial consistency across the enterprise.

Required Checklist

✓Automate reconciliation comparing sum(net_revenue) in FACT_ENTERPRISE_SALES against raw sales files.

✓Assert zero dropped dollars across all stores, channels, and date partitions.

Expected Outcome

Verifiable financial accuracy for accounting audits.

Dead-Letter Quarantine Framework

Isolate malformed or corrupted records across all source feeds.

Required Checklist

✓Route invalid records to source-specific quarantine Delta tables with rejection reasons.

✓Ensure valid records in the same batch proceed to Silver and Gold.

Expected Outcome

Zero silent data loss across all four sources.

Snowflake Enterprise Serving Deployment

Publish unified dimensional models and views to Snowflake.

Required Checklist

✓Deploy physical DDL for DIM_*, FACT_*, and executive presentation views in Snowflake.

✓Implement idempotent staging and merge scripts loading Gold tables to Snowflake.

Expected Outcome

Synchronized Snowflake warehouse accessible by BI dashboards.

End-to-End Data Lineage Tracking

Track record provenance from source to Snowflake.

Required Checklist

✓Attach source_system, source_file_or_endpoint, and ingestion_batch_id metadata to all Silver and Gold rows.

Expected Outcome

Complete auditability and compliance lineage.

Configuration & Secrets Management

Externalize all connection parameters and credentials.

Required Checklist

✓Maintain YAML configs for dev, staging, and prod environments.

✓Inject database credentials, API keys, and storage paths via secret scopes.

Expected Outcome

Secure, configuration-driven multi-environment deployments.

Architecture Freedom

You are responsible for designing the exact surrogate key hashing algorithm, dimensional table partitioning, and Snowflake clustering keys. Document your design choices and justify their scalability.

Testing Requirements

Demonstrate multi-source ingestion, failure isolation, identifier mapping, freshness tracking, and reconciliation.

Required Test Scenarios

Validate the enterprise platform against the following 13 test scenarios:

Multi-Source Concurrent Ingestion

Scenario

Execute simultaneous ingestion across Customer DB, Sales Files, Product API, and Store Reference.

Expected Result

All four Bronze tables populate successfully with matching record counts and extraction timestamps.

Product API Outage Simulation

Scenario

Simulate HTTP 503 error on the Product REST API while ingesting sales transactions.

Expected Result

Sales transaction pipeline completes normally; missing product metadata resolves to -1 without crashing.

Customer Identifier Cross-Mapping

Scenario

Process sales transactions containing integer customer IDs (10492) and string IDs ('C-10492').

Expected Result

Both resolve to the same underlying DIM_CUSTOMER surrogate key.

Late-Arriving Product Dimension

Scenario

Sales arrive for a new SKU; product API record arrives in a subsequent extraction batch.

Expected Result

Initial fact row sets product_key = -1; subsequent dimension refresh updates fact with correct product_key.

Source Freshness SLA Breach Detection

Scenario

Simulate sales file ingestion delay exceeding 3 hours.

Expected Result

Freshness monitoring query detects SLA violation, flags alert, and records telemetry lag.

Malformed CSV Row Quarantine

Scenario

Inject corrupted lines with mismatched column counts into sales input files.

Expected Result

Corrupt rows are diverted to quarantine table; valid sales rows in the file load successfully.

Financial Revenue Reconciliation

Scenario

Compare sum(net_revenue) in FACT_ENTERPRISE_SALES against sum(total_net) across all raw sales files.

Expected Result

Variance equals exactly 0.00 across all date partitions and store channels.

Duplicate Transaction File Delivery

Scenario

Deliver identical sales batch files twice to cloud storage.

Expected Result

Idempotent Silver/Gold MERGE logic prevents duplicate fact records from inflating metrics.

SCD Type 2 Customer Attribute Update

Scenario

Update customer loyalty tier in Customer DB and re-run dimension pipeline.

Expected Result

Previous customer row is expired (is_current=false); new row is inserted with new surrogate key.

Snowflake Referential Integrity Check

Scenario

Query Snowflake FACT_ENTERPRISE_SALES for orphan foreign keys.

Expected Result

100% of foreign keys resolve to valid dimension surrogate keys or default unknown (-1) rows.

Executive View Performance Test

Scenario

Execute complex 4-way join analytical queries on Snowflake presentation views.

Expected Result

Query completes in sub-second time utilizing Snowflake partition pruning and clustering.

End-to-End Pipeline Rerun / Idempotency

Scenario

Execute the entire multi-source pipeline end-to-end twice for the same date window.

Expected Result

Target tables maintain identical row counts, surrogate keys, and financial metrics.

Data Lineage Auditability Test

Scenario

Trace a random fact row in Snowflake back to its originating sales file and Bronze row.

Expected Result

source_file, batch_id, and extraction_timestamp correctly link to the raw input record.

Testing Principle

An enterprise platform is validated by its ability to handle partial outages gracefully. Proving that an API outage does not corrupt sales facts or crash customer processing is essential.

Acceptance Criteria

Verify that the multi-source enterprise platform fulfills all production Definition of Done criteria.

Definition of Done

The enterprise platform is accepted when all 10 criteria are met:

All 4 Heterogeneous Sources Ingested

Customer DB, Sales Files, Product API, and Store Reference feeds ingest reliably into isolated Bronze tables.

Source Failure Isolation Proven

Failures or delays in any single source feed do not crash or corrupt other pipeline components.

Entity Resolution & Mapping Operational

Disparate natural keys are harmonized into unified enterprise entity identifiers.

Late-Arriving Dimensions Handled

Missing dimensions resolve cleanly to -1 default keys without dropping sales transactions.

Conformed Star Schema Deployed

DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE, and FACT_ENTERPRISE_SALES are deployed to Snowflake.

Financial Reconciliation Proven

Total revenue in the enterprise fact table reconciles 100% with raw sales transaction files.

Freshness SLA Tracking Active

Source arrival lag is monitored continuously with automated breach alerting.

Dead-Letter Quarantine Operational

Malformed payloads and corrupt rows across all sources are isolated with rejection telemetry.

All 13 Test Scenarios Pass

Complete test suite passes with documented execution logs and reconciliation outputs.

Comprehensive Architecture Guide Published

Documentation contains entity-relationship diagrams, mapping rules, and operational runbooks.

Acceptance Rule

Approval requires reconciliation query outputs proving that total revenue across all stores in Snowflake matches raw input files with zero variance.

Developer Deliverables

Submit all source ingestion modules, entity mapping logic, DDL scripts, and reconciliation evidence.

Required Deliverables

The submission must include the following 11 artifacts:

Four Decoupled Source Ingestion Modules

PySpark / Python modules for Customer DB, Sales Files, Product API, and Store Reference.

Bronze Staging Persistence Logic

Append-only writer logging raw payloads with source metadata and batch tracking.

Cross-Source Entity Resolution Module

Logic mapping disparate customer, product, and store identifiers to unified keys.

Conformed Dimension Pipelines

PySpark modules building DIM_CUSTOMER (SCD 1/2), DIM_PRODUCT, and DIM_STORE.

Enterprise Fact Loading Pipeline

Module performing surrogate key lookups, metric calculations, and loading FACT_ENTERPRISE_SALES.

Freshness SLA Monitoring Service

Telemetry script computing source arrival lag and logging SLA compliance metrics.

Snowflake DDL & Serving Scripts

Production SQL DDL defining Snowflake dimensional tables, clustering keys, and presentation views.

Financial Reconciliation Suite

Automated SQL / PySpark scripts verifying 100% financial consistency between sources and target fact.

Automated Test Suite

Complete test suite covering all 13 required multi-source verification scenarios.

Configuration Files

YAML configuration files for dev, staging, and production multi-source environments.

Enterprise Architecture Guide & Data Dictionary

README containing entity models, source mapping dictionaries, and incident runbooks.

Submission Principle

Ensure your test harness allows another engineer to simulate all four source feeds locally and verify the reconciliation output.

Engineering Constraints

Adhere to architectural, operational, and security boundaries for enterprise data platforms.

Required Boundaries

The solution must strictly comply with the following 10 constraints:

Fault-Isolated Ingestion

Source ingestion tasks must remain decoupled; failure in one source must not crash other source pipelines.

Zero Silent Loss on Corrupt Records

Malformed payloads from any source must be routed to quarantine storage with rejection diagnostics.

No Orphan Foreign Keys

Unmatched natural keys must resolve to default unknown (-1) surrogate keys rather than producing dangling foreign keys.

Idempotent Pipeline Reruns

Re-running any source batch or the entire pipeline must yield identical target state without row duplication.

No Hard-Coded Credentials

Database passwords, API tokens, and cloud keys must be injected via secure secret scopes.

Standardized Naming Conventions

All normalized columns and database tables must adhere to enterprise snake_case conventions.

Immutable Bronze Raw Storage

Raw Bronze files and tables must remain append-only and never be mutated in place.

Observable Freshness Telemetry

All source pipelines must log extraction timestamps, row counts, and latency lag metrics.

Scalable Distributed Transformations

Entity resolution and dimensional joins must be implemented using distributed PySpark APIs.

Production Code Quality

Code must be modularized into reusable classes/functions with proper type annotations and docstrings.

Constraint Notice

Hard-coding source mappings in monolithic scripts or dropping transactions due to missing product lookups violates enterprise architecture standards.

Suggested Project Structure

Recommended repository layout for production multi-source data platforms.

Recommended Project Layout

Structure your repository to separate source ingestion, entity mapping, dimension/fact transformations, sinks, and tests:

DEV-009-multi-source-enterprise-platform/
│
├── README.md
│
├── config/
│   ├── dev.yaml
│   ├── staging.yaml
│   └── prod.yaml
│
├── src/
│   ├── ingestion/
│   │   ├── ingest_customer_db.py   # PostgreSQL JDBC extractor
│   │   ├── ingest_sales_files.py    # Cloud Storage batch file reader
│   │   ├── ingest_product_api.py    # REST API client & pagination
│   │   └── ingest_store_ref.py      # ERP store master extractor
│   │
│   ├── bronze/
│   │   └── bronze_writer.py         # Append-only raw landing & metadata
│   │
│   ├── silver/
│   │   ├── normalize.py             # Schema normalization & typing
│   │   ├── entity_resolution.py     # Cross-source key mapping
│   │   ├── late_dimensions.py       # Unknown key (-1) mapper
│   │   └── quarantine.py            # Multi-source bad-record handler
│   │
│   ├── gold/
│   │   ├── dim_customer.py          # Customer dimension (SCD 1/2)
│   │   ├── dim_product.py           # Product hierarchy dimension
│   │   ├── dim_store.py             # Store geography dimension
│   │   └── fact_enterprise_sales.py # Central enterprise sales fact
│   │
│   ├── monitoring/
│   │   ├── freshness_monitor.py     # Source arrival lag & SLA tracker
│   │   └── reconciliation.py        # Financial variance checker
│   │
│   └── serving/
│       └── snowflake_loader.py      # Staging & merge loader for Snowflake
│
├── ddl/
│   ├── 01_dimensions.sql
│   ├── 02_facts.sql
│   └── 03_presentation_views.sql
│
├── tests/
│   ├── test_source_isolation.py
│   ├── test_entity_resolution.py
│   ├── test_late_dimensions.py
│   ├── test_freshness_sla.py
│   └── test_financial_reconciliation.py
│
└── docs/
    ├── enterprise_data_model.md     # Logical & physical ER diagrams
    └── source_mapping_dictionary.md # Source-to-target attribute mappings

Module Responsibilities

src/ingestion/

Decoupled extractors for Customer DB, Sales Files, Product API, and Store Reference.

src/silver/

Contains schema normalization, cross-source entity mapping, late dimension handling, and quarantine.

src/gold/

Builds conformed dimensions (Customer, Product, Store) and the central enterprise sales fact table.

src/monitoring/

Automated freshness lag monitoring, SLA breach alerting, and financial reconciliation scripts.

ddl/

Production Snowflake SQL DDL defining tables, primary/foreign keys, clustering, and semantic views.

tests/

Comprehensive test suite covering source isolation, entity resolution, freshness, and reconciliation.

docs/

Enterprise dimensional data model, source-to-target mapping dictionaries, and operational runbooks.

Design Rationale

Separating individual source extractors into dedicated modules ensures upstream API schema changes or network timeouts can be patched without impacting other source ingestion pipelines.

Submission Checklist

Final engineering quality checklist before submitting DEV-009.

Final Review Checklist

Verify every checklist item before submitting your enterprise platform:

✓

Four Heterogeneous Sources Ingested

Customer DB, Sales Files, Product API, and Store Reference feeds land reliably in Bronze.

✓

Source Failure Isolation Verified

Simulated outages on Product API or Customer DB do not halt sales transaction processing.

✓

Cross-Source Entity Mapping Active

Disparate natural customer, product, and store identifiers map to unified enterprise keys.

✓

Late-Arriving Dimensions Handled

Transactions with missing dimensions resolve to -1 default keys without dropping records.

✓

Conformed Star Schema Deployed

DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE, and FACT_ENTERPRISE_SALES are fully implemented.

✓

Financial Reconciliation Proven

Total revenue in FACT_ENTERPRISE_SALES matches raw sales files with exactly 0.00 variance.

✓

Freshness SLA Monitoring Operational

Ingestion lag is computed and tracked continuously with automated SLA breach alerts.

✓

Snowflake Staging & Sync Verified

Curated dimensional tables and presentation views are deployed to Snowflake.

✓

All 13 Test Scenarios Pass

Complete test suite passes with documented execution logs and reconciliation outputs.

✓

Source Mapping Dictionary Published

docs/source_mapping_dictionary.md documents every source-to-target field mapping.

✓

Code Quality & Security Compliance

Modules are typed, documented, and free of hard-coded credentials or monolithic scripts.

Ready for Review

Submit DEV-009 only after the four source extractors, entity resolution, conformed star schema, financial reconciliation, Snowflake sync, and freshness monitoring have been thoroughly validated.