Microsoft Fabric · Lakehouse and Data Quality
Validation-First Lakehouse Ingestion with Microsoft Fabric
A controlled file-ingestion workflow that validates trusted office CSV files before loading approved records into a curated Fabric Lakehouse table.
The project focuses on preventing wrongly named, unsupported and schema-incompatible files from reaching the trusted dataset while preserving files through archive and quarantine paths.
Project Overview
- Project type
- Hands-on data engineering implementation
- Primary platform
- Microsoft Fabric
- Source format
- CSV files
- Source location
- Fabric Lakehouse landing folder
- Orchestration
- Fabric Data Factory pipeline
- Validation
- Fabric Notebook with PySpark-based schema validation
- Target
- Curated Fabric Lakehouse table
- Loading behaviour
- Upsert using property_id
- Invalid-data handling
- Quarantine folders
- Operational controls
- Archive-before-delete, quarantine-before-delete and pipeline notifications
- Implementation evidence
- Pipeline JSON, validation playbook, SQL checks and masked Fabric screenshots
- Primary focus
- Validation, trusted data promotion and controlled file lifecycle
Problem to Solve
Engineering problem
File-based ingestion becomes unreliable when every file arriving in a landing folder is treated as a valid input. Incorrectly named CSV files, non-CSV files and schema-incompatible files can enter trusted datasets unless validation and routing controls are applied before loading.
- Trusted office files needed to be distinguished from unexpected files.
- Filename validation alone was not enough to confirm schema compatibility.
- Schema-drift files needed to be isolated instead of loaded into the curated table.
- Unsupported file types needed controlled handling.
- Sensitive source fields needed to be excluded from the trusted target.
- Landing files should not be deleted before successful archive or quarantine processing.
- Valid files should continue through the approved path without allowing invalid files to contaminate the curated table.
Constraints
- Input files arrive through a shared landing location.
- The trusted filename convention is office_*.csv.
- The curated table should preserve approved records using property_id.
- Invalid files must remain available for review.
- Cleanup must depend on successful archive or quarantine completion.
Architecture Overview
The solution establishes trust in stages. Fabric Data Factory discovers landing files, separates candidate files from unsupported inputs, invokes a Fabric Notebook to validate schema compatibility and promotes only approved records into the curated Lakehouse table. Archive, quarantine and cleanup activities manage the file lifecycle after processing.
- 01
Landing Zone
Incoming files arrive in the project landing directory for discovery and processing.
- 02
File Discovery
The pipeline reads the landing directory and retrieves the available file list.
- 03
File Classification
Files are classified into trusted office CSV candidates, wrongly named CSV files and non-CSV files.
- 04
Schema Validation
A Fabric Notebook validates the expected file structure and identifies schema-compatible and schema-drift inputs.
- 05
Curated Lakehouse Load
Approved columns from validated files are loaded into the curated Lakehouse table using an upsert based on property_id.
- 06
Quarantine and Archive
Schema-drift and unsupported files are moved to quarantine, while valid processed files are moved to archive.
- 07
Cleanup and Notification
Landing files are deleted only after the required archive or quarantine operation succeeds, and execution results are sent through pipeline notifications.
Key principle: Validation-first ingestion allows only trusted and schema-compatible data to reach the curated Lakehouse table while unsupported inputs are isolated through controlled quarantine and file-lifecycle handling.
Validation-First Microsoft Fabric Lakehouse Ingestion
End-to-End Data Flow
Files Arrive in the Landing Folder
Incoming files are placed in the project landing location for pipeline processing.
- Input
- CSV and other incoming files
- Output
- Landing directory contents
Discover Landing Files
The pipeline reads the landing directory and retrieves the available files for classification.
Identify Trusted Office CSV Files
Files matching the office_*.csv convention are treated as candidate files for schema validation.
- Output
- Trusted filename candidates
Separate Unexpected and Unsupported Files
Wrongly named CSV files and non-CSV files are identified separately so they cannot proceed through the trusted loading path.
- Output
- Files requiring quarantine handling
Validate Candidate File Schema
The validation notebook checks candidate files against the expected schema and separates schema-compatible files from schema-drift files.
Load Approved Records into the Lakehouse
Approved columns from validated files are loaded into the curated Lakehouse table. Existing records are updated and new records are inserted using property_id as the upsert key.
- Output
- Curated Lakehouse table
Archive or Quarantine Processed Files
Successfully processed valid files move to archive. Schema-drift, wrongly named CSV and non-CSV files move to the appropriate quarantine location.
- Decision
- Valid and loaded → Archive; schema drift → Schema quarantine; unexpected CSV → Invalid-name quarantine; unsupported format → Non-CSV quarantine.
Clean the Landing Area
A landing file is deleted only after its required archive or quarantine operation completes successfully.
- Operational purpose
- Reduce the risk of file loss during partial failure.
Send Pipeline Notification
The pipeline sends execution-status information through the configured Microsoft Teams notification flow.
Technical Implementation
Pipeline Orchestration
Fabric Data Factory coordinates discovery, filtering, notebook validation, curated loading and file-lifecycle operations.
- Activity dependencies control execution order.
- Candidate and problem files follow separate processing branches.
- Cleanup activities depend on successful archive or quarantine handling.
- The curated load runs only for approved files.
Trusted File Contract
The office_*.csv naming convention acts as the first validation layer.
- Matching files become validation candidates.
- Wrongly named CSV files are separated.
- Non-CSV files are routed away from the trusted path.
- Filename validation is not treated as proof of schema compatibility.
Notebook-Based Schema Validation
A Fabric Notebook reads candidate files and checks whether their structure matches the expected schema.
- Schema-compatible files continue to curated loading.
- Schema-drift files are identified before trusted promotion.
- Validation happens before the curated table is modified.
- Notebook output is used by the pipeline to determine routing.
Curated Lakehouse Loading
Validated records are loaded into the curated Lakehouse table using an upsert pattern.
- property_id is the business key used for matching.
- Existing records can be updated.
- New records can be inserted.
- insert_time is added as ingestion metadata.
- Only approved target columns are loaded.
Sensitive-Column Exclusion
The curated mapping excludes source fields that should not be promoted into the trusted target.
- Personal-contact fields are excluded.
- Internal-reference fields are excluded.
- This demonstrates deliberate target-column selection and basic sensitive data handling.
Controlled File Lifecycle
Processed files follow archive or quarantine paths before they are removed from the landing folder.
- Valid files are archived after successful loading.
- Schema-drift files are quarantined.
- Wrongly named CSV files are quarantined.
- Non-CSV files are quarantined.
- Delete activities depend on successful movement to the required destination.
Notifications
Pipeline status is sent through the configured Teams notification integration to provide operational visibility.
Implementation Evidence
Data Quality and Validation
Trusted Filename Pattern
- Purpose
- Identify files intended for office-data ingestion.
- Implementation
- Accept candidate files matching office_*.csv.
- Failure handling
- Route wrongly named CSV files to quarantine.
Supported File Type
- Purpose
- Prevent unsupported file formats from entering CSV processing.
- Implementation
- Separate .csv files from other file types.
- Failure handling
- Route non-CSV files to a dedicated quarantine location.
Schema Compatibility
- Purpose
- Confirm that candidate files contain the expected structure before loading.
- Implementation
- Validate the file schema using a Fabric Notebook and PySpark.
- Failure handling
- Route schema-drift files to quarantine.
Approved Column Mapping
- Purpose
- Control which source columns enter the trusted target.
- Implementation
- Load only approved columns into the curated table.
- Failure handling
- Exclude unsupported or sensitive source fields from the curated mapping.
Business-Key Loading
- Purpose
- Avoid creating a new current-state record when the same property already exists.
- Implementation
- Use property_id as the upsert match key.
- Failure handling
- The upsert behaviour is not presented as duplicate elimination beyond its supported match logic.
File-Lifecycle Dependency
- Purpose
- Prevent deletion before a file is preserved in archive or quarantine.
- Implementation
- Connect delete activities only after successful archive or quarantine completion.
- Failure handling
- Do not run the related delete step when the preceding preservation action fails.
Validation Evidence
Data Protection Considerations
Sensitive-column exclusion
Prevent selected internal or personal source fields from entering the trusted analytical target.
Behaviour: The curated mapping excludes personal-contact and internal-reference fields.
This is a target-data minimisation decision within the project, not a complete security, privacy or governance programme.
Monitoring and Failure Handling
Pipeline Notifications
Provide visibility into pipeline execution results.
Behaviour: The pipeline uses the configured Microsoft Teams notification integration to report run status.
Invalid-File Isolation
Prevent unexpected files from blocking or contaminating the trusted load path.
Behaviour: Wrongly named, unsupported and schema-drift files are routed to quarantine locations for review.
Dependency-Based Cleanup
Avoid deleting a file before it has been preserved.
Behaviour: Delete activities run only after the corresponding archive or quarantine activity succeeds.
Branch-Specific Processing
Keep valid-file processing separate from invalid-file handling.
Behaviour: Filtered file groups follow different execution branches based on filename, format and schema status.
Partial-Failure Visibility
Make it clear when an archive, quarantine or cleanup operation does not complete.
Behaviour: The dependent downstream action does not continue when the required upstream activity fails.
Monitoring Evidence
Rerun and Recovery Considerations
Upsert-Based Curated Loading
The property_id match key supports updating existing records and inserting new records rather than relying only on append behaviour.
Archive-Before-Delete
Valid source files are retained in archive before landing cleanup.
Quarantine-Before-Delete
Invalid files remain available in quarantine before their landing copies are removed.
Invalid-File Reinspection
Quarantined files can be reviewed and corrected outside the trusted processing path.
Partial-Failure Protection
Dependency conditions reduce the chance of deleting a source file when its archive or quarantine movement has failed.
These controls support safer rerun and recovery behaviour. The workflow is not described as fully idempotent because every partial-failure scenario has not been verified.
Recovery Evidence
Key Design Decisions
- 01
Validate Before Curated Loading
Filename matching alone cannot identify schema drift.
- 02
Use a Trusted Filename Contract
This provides a simple first classification layer before deeper validation.
Trade-off: Valid content with an incorrect filename is quarantined rather than loaded automatically.
- 03
Separate Unexpected File Types
Different invalid-file categories are easier to review and troubleshoot when they are not combined.
- 04
Upsert Using property_id
The target represents the current approved state of each property record.
- 05
Add insert_time Metadata
This provides basic traceability of when data entered the trusted target.
- 06
Exclude Sensitive Source Fields
The analytical target should contain only approved fields needed for the use case.
- 07
Quarantine Instead of Ignoring
Files remain available for investigation rather than being silently discarded.
- 08
Preserve Before Deleting
Archive-before-delete and quarantine-before-delete dependencies reduce the risk of losing source files during a partial failure.
Challenges and Implementation Considerations
Filename Validation Was Not Sufficient
A file can match office_*.csv while still having an incompatible schema.
Approach: Use the filename pattern only as candidate classification, followed by notebook-based schema validation.
Different Invalid Inputs Needed Different Handling
Schema drift, wrong filenames and unsupported formats represent different failure conditions.
Approach: Create separate filters and quarantine paths.
Protecting the Curated Table
Loading invalid or unexpected fields could reduce trust in the analytical dataset.
Approach: Validate structure and apply explicit approved-column mapping before load.
Avoiding File Loss During Cleanup
Deleting a file before archive or quarantine completion would remove the source needed for investigation or rerun.
Approach: Use activity dependencies so preservation succeeds before deletion.
Supporting Current-State Updates
Property records may arrive again with updated values.
Approach: Use property_id as the upsert key instead of append-only loading.
Processing Valid Files Independently
One missing or invalid office file should not automatically make other valid files unsuitable for processing.
Approach: Discover and classify each available file so approved files can follow the trusted branch while invalid inputs are isolated.
Outcome and Demonstrated Value
The completed workflow establishes a controlled path from file arrival to curated Lakehouse loading. Only files that pass filename, format and schema checks can reach the trusted table, while invalid inputs remain available through quarantine handling.
- Establishes a validation-first trust boundary before curated loading.
- Routes unsupported and schema-incompatible files to quarantine.
- Uses a business-key upsert and approved-column mapping for curated data.
- Preserves processed and rejected files before landing cleanup.
- Adds execution context through pipeline notifications and audit metadata.