Difficulty

Intermediate

Estimated Effort

2–3 Days

Prerequisites

PySpark · SQL

Primary Stack

Databricks

Target

Snowflake

Development Task

DEV-001

Build Customer Lakehouse Pipeline

Build a production-oriented customer data pipeline from source ingestion through Bronze, Silver, and Gold layers, making trusted customer data available for enterprise analytics.

Project Brief

Understand the business problem before designing the technical solution.

Business Requirement

A company has introduced a new Customer Management System. Customer information is generated by the source system and delivered to cloud storage for downstream processing.

The data engineering team needs a new production pipeline that ingests the customer data, validates and transforms it, and publishes a trusted customer dataset for analytics and reporting.

Your Responsibility

You are responsible for designing and developing the complete data pipeline from source ingestion through the final analytics-ready customer dataset.

Source System

Understand the source contract before designing the pipeline.

Source Overview

Customer data originates from the Customer Management System. The source system produces customer records that are delivered to the data platform for downstream processing and analytics.

Delivery Pattern

Customer data is delivered as a daily batch feed. Each delivery represents customer information received from the source system for processing by the data engineering platform.

Input Format

Format

CSV

Frequency

Daily

Data Type

Customer Master

Processing

Batch

Customer Dataset

The primary source dataset is a customer master feed containing customer identity, contact, registration, and lifecycle information.

Column	Data Type	Business Meaning
`customer_id`	STRING	Unique identifier assigned to the customer.
`customer_name`	STRING	Full name of the customer.
`email`	STRING	Primary email address associated with the customer.
`phone`	STRING	Customer contact number.
`country`	STRING	Country associated with the customer.
`registration_date`	DATE	Date on which the customer registered.
`status`	STRING	Current lifecycle status of the customer.

Known Source Constraints

Duplicate Customer IDs

The source may contain multiple records with the same customer_id.

Missing Values

Some customer attributes may be missing in the source data.

Invalid Email Values

Email fields may contain malformed or unusable values.

Invalid Dates

Registration dates may contain values that cannot be interpreted as valid dates.

Unexpected Status Values

The source may contain lifecycle status values outside the expected business domain.

Engineering Note

The source contract defines what the pipeline receives. It does not prescribe how you should implement ingestion, cleansing, deduplication, validation, or storage. Those are engineering decisions that you must make as part of this development task.

Expected Architecture

Design the solution using a layered lakehouse architecture.

Target Data Flow

The customer pipeline should follow a layered architecture where raw source data is preserved, business transformations are applied in controlled stages, and the final dataset is prepared for analytics consumption.

SOURCECustomer Management System

Origin of the customer data.

↓

LANDINGCloud Storage

Incoming customer files are received before pipeline processing.

↓

BRONZERaw Customer Data

Persist the source data with minimal transformation while preserving source fidelity.

↓

SILVERValidated Customer Data

Clean, standardize, validate, and prepare customer records for downstream consumption.

↓

GOLDAnalytics-Ready Customer Data

Publish a trusted customer dataset designed for business analytics.

↓

TARGETSnowflake

Make the curated customer dataset available to downstream consumers.

Layer Responsibilities

Bronze

Preserve

Maintain the source representation of customer data so that the original information can be traced and reprocessed when required.

Silver

Refine

Apply cleansing, standardization, validation, and record-level business rules to produce trusted customer records.

Gold

Serve

Produce a business-ready customer dataset optimized for downstream analytics and reporting.

Architectural Expectations

Layer Separation

Keep ingestion, cleansing, and business-serving responsibilities separated across appropriate layers.

Traceability

The final customer record should be traceable back through the processing layers to the source data.

Reprocessability

The design should allow appropriate layers to be reprocessed without unnecessarily rebuilding the entire pipeline.

Data Quality

Quality validation should occur before customer records are considered trusted for downstream consumption.

Scalability

The architecture should be able to handle increasing customer volumes without requiring a fundamental redesign.

Maintainability

Pipeline responsibilities should be organized so that future changes can be implemented without creating unnecessary coupling.

Engineering Decision

The architecture defines the expected responsibilities of each layer. The implementation approach, transformation strategy, partitioning approach, naming conventions, and processing logic are engineering decisions that must be determined by you.

Development Requirements

The solution must address the following engineering responsibilities.

Developer Objective

Build the customer data pipeline described in the business requirement and architecture sections. The implementation should process the incoming customer dataset through the required data layers and produce a trusted analytics-ready customer dataset.

You are expected to make the technical decisions required to implement the solution while following the architectural and business requirements defined in this task.

Source Ingestion

Establish a reliable process for reading customer data from the configured landing location.

Required

✓Read customer files from the configured source location.

✓Handle the expected input format correctly.

✓Ensure the ingestion process can identify the data being processed.

✓Avoid silently ignoring unreadable or invalid input files.

Expected Outcome

Customer source data is successfully available for Bronze processing.

Bronze Layer

Create the raw customer data layer while preserving the source information required for traceability.

Required

✓Persist the incoming customer records in the Bronze layer.

✓Preserve source-level information required for troubleshooting and reprocessing.

✓Apply only the transformations necessary for reliable storage.

✓Ensure the Bronze layer can support downstream processing.

Expected Outcome

A reliable raw customer dataset is available for subsequent transformations.

Silver Transformation

Transform raw customer records into clean and standardized customer data.

Required

✓Apply appropriate data type conversions.

✓Standardize customer attributes where required.

✓Handle missing or invalid values according to defined business rules.

✓Identify and handle duplicate customer records.

✓Validate important customer attributes before promoting records downstream.

Expected Outcome

A trusted and standardized customer dataset is available in Silver.

Gold Dataset

Create an analytics-ready customer dataset for downstream consumers.

Required

✓Select the attributes required by downstream consumers.

✓Apply the required business-level transformations.

✓Ensure the resulting dataset has a clear and stable structure.

✓Produce a dataset suitable for analytics and reporting use cases.

Expected Outcome

A business-ready customer dataset is available for consumption.

Data Quality

Ensure customer records meet the minimum quality expectations before reaching the trusted layer.

Required

✓Validate customer_id completeness.

✓Validate email values according to an appropriate business rule.

✓Validate registration_date values.

✓Validate customer status values against the expected domain.

✓Detect duplicate customer identifiers.

✓Define how invalid records should be handled.

Expected Outcome

Data quality issues are detected and handled explicitly rather than silently entering the trusted dataset.

Error Handling

Make pipeline failures visible and operationally manageable.

Required

✓Handle expected processing failures explicitly.

✓Prevent silent data loss.

✓Ensure pipeline failures provide enough information for troubleshooting.

✓Separate rejected data from successfully processed data where appropriate.

Expected Outcome

Pipeline failures and rejected records can be identified and investigated.

Processing Logging

Provide sufficient operational visibility into each pipeline execution.

Required

✓Capture pipeline execution status.

✓Capture processing start and completion information.

✓Capture useful record-level or batch-level processing metrics.

✓Make failures identifiable through the available logging mechanism.

Expected Outcome

An operator can understand what happened during a pipeline execution without inspecting the entire codebase.

Incremental Processing

Design the pipeline so that repeated executions do not unnecessarily reprocess the complete historical dataset.

Required

✓Consider how previously processed customer data will be identified.

✓Prevent unnecessary duplicate processing.

✓Ensure new source data can be incorporated safely.

✓Consider how the solution behaves when a previous execution is retried.

Expected Outcome

The pipeline has a defined approach for processing new data while maintaining data consistency.

Snowflake Publishing

Publish the curated customer dataset to the downstream analytical platform.

Required

✓Create or use the appropriate target structure in Snowflake.

✓Publish the Gold customer dataset to the target.

✓Maintain an appropriate mapping between the curated dataset and the target structure.

✓Ensure failed publishing does not result in an unverified successful pipeline status.

Expected Outcome

The trusted customer dataset is available in Snowflake for downstream consumption.

Production Readiness

Ensure the solution is structured as an operational data engineering pipeline rather than a one-time script.

Required

✓Use a maintainable project structure.

✓Separate configuration from processing logic where appropriate.

✓Avoid unnecessary hard-coded environment-specific values.

✓Design the pipeline so that future source or business changes can be accommodated.

✓Ensure the solution can be tested and deployed consistently.

Expected Outcome

The completed pipeline is suitable for progression toward a production deployment.

Developer Freedom

Multiple technical approaches may satisfy these requirements. You are expected to evaluate the problem and select an appropriate implementation strategy rather than following a predefined solution.

Testing Requirements

Prove that the pipeline produces correct, reliable, and repeatable results.

Testing Objective

The pipeline must be tested before it can be considered complete. Testing should verify both the expected processing path and the behavior of the pipeline when data or execution conditions are not ideal.

Schema Validation

Verify that incoming data conforms to the expected customer dataset structure.

Verify

□Required columns are present.

□Expected data types are handled correctly.

□Unexpected or incompatible source structures are detected.

Data Quality Validation

Verify that customer records are evaluated against the defined quality expectations.

Verify

□Null or missing customer identifiers are detected.

□Invalid email values are identified.

□Invalid registration dates are handled.

□Unexpected status values are detected.

Duplicate Handling

Verify that duplicate customer records do not incorrectly create multiple trusted customer records.

Verify

□Duplicate customer identifiers are detected.

□The chosen duplicate-handling strategy behaves consistently.

□The final trusted dataset maintains the expected customer uniqueness.

Transformation Validation

Verify that transformations produce the expected Silver and Gold data.

Verify

□Data type conversions produce expected results.

□Standardization rules are applied consistently.

□Business transformations produce expected values.

□The Gold dataset contains the required attributes.

Record Count Reconciliation

Verify that record movement between pipeline stages can be explained.

Verify

□Input record counts are captured.

□Processed and rejected records can be identified.

□Unexpected record loss can be detected.

□Output counts can be reconciled with processing results.

Incremental Processing

Verify that repeated executions behave correctly when new and previously processed data are present.

Verify

□New source records are processed.

□Previously processed records are not unnecessarily duplicated.

□A repeated execution produces a consistent result.

□Retry behavior does not corrupt the trusted dataset.

Failure Handling

Verify that expected pipeline failures are handled and made visible.

Verify

□Invalid input conditions are handled appropriately.

□Processing failures result in an identifiable failure state.

□Errors provide sufficient information for investigation.

□The pipeline does not report success when a required processing stage fails.

Target Validation

Verify that the curated customer dataset is correctly published to Snowflake.

Verify

□The target structure matches the expected Gold dataset.

□Expected records are available in the target.

□Data types and important attributes are preserved.

□Publishing failures are detectable.

Reprocessing Test

Verify that the pipeline can safely recover from a previous execution or processing failure.

Verify

□A failed or interrupted execution can be retried.

□Retrying does not unnecessarily duplicate trusted records.

□Previously successful processing remains consistent.

□The resulting dataset remains trustworthy after recovery.

End-to-End Validation

Verify the complete pipeline from source input through the Snowflake target.

Verify

□Source data is successfully ingested.

□Bronze data is created.

□Silver transformations are completed.

□Gold data is produced.

□The final dataset is available in Snowflake.

□Processing metrics and execution status are available.

Testing Principle

A successful test is not simply evidence that the pipeline runs. The developer should be able to demonstrate that the pipeline produces the expected data, handles invalid conditions correctly, and behaves safely when execution is repeated or interrupted.

Acceptance Criteria

The development task is complete only when the solution satisfies the following conditions.

Definition of Done

The solution should be considered complete only when the pipeline has been implemented, tested, and demonstrated to satisfy the business, data, architectural, and operational expectations defined in this task.

Source Ingestion Works

The pipeline successfully reads the expected customer source data and processes valid input without silent data loss.

□

Bronze Layer Is Available

The incoming customer data is persisted in the Bronze layer with sufficient source information to support traceability and reprocessing.

□

Silver Data Is Trusted

Customer records are appropriately cleansed, standardized, validated, and handled according to the defined data quality expectations.

□

Gold Dataset Is Analytics Ready

The Gold dataset contains the required customer attributes and represents a stable, business-ready structure for downstream consumption.

□

Data Quality Rules Are Enforced

Invalid, incomplete, duplicate, or unexpected customer records are detected and handled according to the implemented business rules.

□

Pipeline Is Retry Safe

Repeating a pipeline execution does not unnecessarily create duplicate trusted records or corrupt previously processed data.

□

Failures Are Visible

Pipeline failures and rejected records can be identified and investigated using the available error handling and logging mechanisms.

□

Snowflake Target Is Populated

The expected curated customer dataset is successfully published to the Snowflake target and can be consumed downstream.

□

Testing Evidence Exists

The developer provides sufficient evidence that the required schema, data quality, transformation, incremental, failure, and end-to-end scenarios were tested.

□

Solution Is Maintainable

The implementation is organized, configurable, understandable, and structured so that reasonable future changes can be introduced without unnecessary redesign.

□

Final Completion Check

Would you be comfortable deploying this pipeline?

If the answer is no, identify the missing requirement, testing evidence, operational safeguard, or data quality control before considering the development task complete.

Submission Expectation

The completed solution should include the implementation, relevant configuration, test evidence, and concise documentation explaining the major engineering decisions made during development.

Developer Deliverables

The completed task should be submitted with the following implementation and evidence.

Required Deliverables

The implementation is not considered complete until the required engineering artifacts and supporting evidence are available for review.

Pipeline Implementation

Provide the completed implementation covering the required ingestion, Bronze, Silver, Gold, and Snowflake processing flow.

Configuration

Provide the configuration required to execute the pipeline across the intended environment without unnecessarily hard-coding environment-specific values.

Data Quality Implementation

Provide the implemented validation and handling logic for the customer data quality requirements defined in the task.

Test Implementation

Provide the tests required to validate schema handling, transformations, duplicate handling, incremental processing, failures, and end-to-end execution.

Test Evidence

Provide evidence demonstrating that the required test scenarios were executed and produced the expected results.

Execution Evidence

Provide sufficient execution information to demonstrate successful processing from source ingestion through the Snowflake target.

Technical Documentation

Provide concise documentation explaining the solution, important engineering decisions, configuration requirements, and execution approach.

Known Limitations

Document any known limitations, assumptions, or areas that would require additional work before broader production adoption.

Submission Principle

A completed implementation without supporting test and execution evidence should not be treated as a complete development submission.

Engineering Constraints

Implementation freedom is encouraged, but the following engineering boundaries must be respected.

Non-Negotiable Engineering Practices

The implementation approach is intentionally left to the developer. However, the solution should follow the engineering constraints below to ensure that the resulting pipeline is reliable, maintainable, and suitable for operational use.

Do Not Hard-Code Credentials

Credentials, secrets, tokens, and sensitive connection information must not be embedded directly in source code or notebooks.

Avoid Unnecessary Hard-Coding

Environment-specific paths, dates, table names, and other configuration values should be externalized where appropriate rather than scattered throughout the implementation.

Do Not Silently Drop Data

Records that fail validation or processing must have an identifiable handling strategy. Data should not disappear without an explainable reason.

Preserve Traceability

The solution should provide enough information to understand where processed records originated and how they moved through the pipeline.

Design for Reprocessing

The pipeline should consider retries, repeated execution, and recovery from failed processing without unnecessarily corrupting or duplicating trusted data.

Separate Processing Responsibilities

Keep ingestion, transformation, validation, publishing, and supporting logic reasonably separated so that individual responsibilities can be understood and maintained.

Do Not Hide Failures

A required pipeline stage must not be treated as successful when it has actually failed. Errors should be surfaced through the appropriate execution and logging mechanisms.

Keep Business Rules Understandable

Business transformations and validation rules should be implemented in a way that another engineer can understand, review, and modify.

Avoid Unnecessary Complexity

Choose an implementation that solves the stated problem without introducing unnecessary frameworks, components, or processing steps.

Do Not Optimize Without Evidence

Performance optimizations should be based on an identifiable processing characteristic, measurement, or reasonable engineering justification rather than unnecessary complexity.

Engineering Judgment

These constraints define the engineering boundaries of the task, not a predefined implementation. Different technical solutions may be acceptable when they satisfy the business requirements, testing expectations, and engineering standards.

Prerequisites

Recommended knowledge and skills before attempting this development task.

Expected Knowledge

This task is designed for a developer who is already comfortable with the fundamentals below. The task focuses on applying those skills to a complete data engineering problem rather than teaching each technology from the beginning.

PySpark Fundamentals

Comfortable with DataFrames, transformations, actions, joins, aggregations, and common Spark processing patterns.

Spark SQL

Able to work with SQL-based transformations, filtering, aggregations, and analytical data operations.

Lakehouse Concepts

Understand the purpose of Bronze, Silver, and Gold data layers and the role of Delta-based processing.

ETL / ELT Concepts

Understand ingestion, transformation, validation, data movement, and downstream publishing.

Data Quality Fundamentals

Understand common validation concepts such as null checks, duplicate detection, data type validation, and business-rule validation.

Basic Snowflake SQL

Able to understand target tables, schemas, loading concepts, and basic SQL operations in Snowflake.

Git Fundamentals

Understand basic source-control concepts such as commits, branches, and maintaining changes in a development repository.

Important

You do not need to know the exact solution before starting this task. The purpose of the Development Arena is to apply your existing engineering knowledge and make appropriate technical decisions while solving the problem.

Engineering Principle

Build the solution, don't just follow the solution.

This task is designed to simulate real development work. Requirements define what the system must achieve; the engineering solution is yours to design.