Implementing Fabric Warehouse Using Data Factory
BimlFlex provides an intuitive process to implement Microsoft Fabric Warehouse using Data Factory (FDF) for cloud-based data warehousing solutions. This integration provides full support for structured, SQL-based analytics, expanding Fabric coverage alongside Lakehouse, Databricks, and Snowflake.
Architecture Overview
[SCREENSHOT NEEDED: bimlflex-diagram-fabric-warehouse-fdf-automation.png - Architecture diagram showing Data Factory orchestration with Fabric Warehouse, including source systems, blob storage landing, and Warehouse processing layers]
BimlFlex uses Data Factory copy commands to ingest and land (stage) source data in OneLake, Azure Blob Storage, or Data Lake Storage Gen2. BimlFlex provides logic to map the resulting files so that the generated code can load the data into Fabric Warehouse tables using T-SQL.
Fabric Warehouse Features
Microsoft Fabric Warehouse support in BimlFlex enables data warehouse patterns and automation with a focus on T-SQL-based analytics:
Metadata Import Support
Warehouse objects such as schemas, tables, and views can be imported directly from Microsoft Fabric Warehouse. This accelerates onboarding by eliminating manual mapping and allows existing structures to drive automation patterns.
To import metadata from a source:
- Navigate to the source connection in the Connections editor
- Click the Preview button to view available objects
- Select the tables you want to import (you can import thousands of tables at once)
- BimlFlex imports the metadata and makes it available for modeling
Data Vault Templates
Templates have been introduced for implementing Data Vault modeling within Fabric Warehouse, including hubs, links, satellites, and PIT/Bridge tables. This ensures consistent automation across both Warehouse and other supported targets.
Supported Data Vault constructs include:
- Hubs: Core business entities
- Links: Relationships between hubs
- Satellites: Descriptive attributes with history
- PIT Tables: Point-in-time (PIT) tables
- Bridge Tables: Denormalized query acceleration structures
Data Mart Templates
Fabric Warehouse-specific templates support the generation of dimensional models, including star schemas and reporting tables. These templates are optimized for Fabric's T-SQL-based query engine to deliver strong performance for analytics workloads.
Features include:
- Dimension table generation
- Fact table generation
- Star schema optimization
- Reporting view creation
Delete Detection Templates
Support for delete detection has been added to Fabric Warehouse, enabling automated handling of record removals in source data. This ensures warehouse tables remain synchronized and accurate without requiring manual intervention.
Medallion Architecture Support
While Fabric Lakehouse is typically used for the full medallion architecture (Bronze/Silver/Gold), Fabric Warehouse can serve as a destination for specific layers:
| Use Case | Description |
|---|---|
| Gold Layer Destination | Fabric Warehouse excels as a Gold layer target for T-SQL-based analytics and reporting |
| Silver Layer Option | Can host curated Data Vault or Normal Form models when T-SQL access is preferred |
| Hybrid Architecture | Combine with Fabric Lakehouse for Bronze/Silver layers, using Warehouse for Gold |
For comprehensive guidance on medallion architecture patterns, see the Delivering Lakehouse documentation.
Prerequisites
Before implementing Fabric Warehouse with Data Factory, ensure you have completed the following:
- Fabric Configuration: Complete the setup outlined in the Microsoft Fabric Configuration Overview
- Storage: Configure OneLake, blob storage, or Data Lake Storage Gen2 for landing, staging, archive, and error containers
- Connections: Create and configure the Fabric Warehouse connection in BimlFlex
Detailed prerequisites and configuration steps are provided in the Microsoft Fabric Configuration Overview section.
Configuring Fabric Warehouse in BimlFlex
Loading Sample Metadata
BimlFlex provides two pre-configured sample metadata sets that can be used with Fabric Warehouse:
| Sample | Description | Use Case |
|---|---|---|
| Fabric Data Vault | Pre-configured for Data Vault implementation | Building a silver layer with Hub, Link, and Satellite patterns |
| Fabric Datamart | Pre-configured for dimensional modeling | Building bronze-to-gold layer data marts |
To load a sample:
- Navigate to the BimlFlex Dashboard
- Select from the Load Sample Metadata dropdown
- Choose either Fabric Data Vault or Fabric Datamart
The sample metadata includes pre-configured projects, connections, and objects that demonstrate best practices for Fabric Warehouse implementations.
[SCREENSHOT NEEDED: fabric-warehouse-data-vault-fdf-sample.png - Sample metadata selection showing Fabric Data Vault and Fabric Datamart options]
For more information on lakehouse and data modeling implementations:
Connection Configuration
Configure your Fabric Warehouse connections from within the BimlFlex Connections editor:
Source System Connection:
- Enable Cloud option for the source system
- Configure Staging / Landing Environment for OneLake, Blob Storage, or Data Lake Storage Gen2 with Data Factory connections
[SCREENSHOT NEEDED: fabric-warehouse-connection-source.png - Connection settings for source system with cloud enabled]
Fabric Warehouse Connection:
- Set System Type to Fabric Warehouse
- Configure the Connection String appropriately for Fabric Warehouse
- Configure Integration Template to Data Factory Source -> Target
- Set External Location to the OneLake path or ABFSS path for data storage
- Set External Reference to the Fabric connection ID (every connection in Fabric has an internal ID that BimlFlex uses to reference it)
[SCREENSHOT NEEDED: fabric-warehouse-connection-settings.png - Connection settings showing Fabric Warehouse configuration]
The External Reference is required for all Fabric Warehouse connections. This ID can be found in the Fabric portal and enables BimlFlex to properly reference the connection when generating and deploying Data Factory pipelines.
Batch Configuration
Prior to building your solution, configure batches from the BimlFlex Batches editor to:
- Assign batches to different compute resources
- Configure scaling parameters
- Set execution priorities
[SCREENSHOT NEEDED: fabric-warehouse-batch-configuration.png - Batch configuration screen]
Generated Output
BimlFlex generates all necessary Fabric Warehouse artifacts automatically—you do not need to write stored procedures or pipeline code manually:
| Artifact Type | Description |
|---|---|
| Warehouse Tables | DDL scripts for creating all Warehouse table structures |
| Stored Procedures | T-SQL procedures for data transformation and loading logic |
| Views | Reporting and query views |
| Data Factory Pipelines | Complete pipeline orchestration including copy activities, stored procedure execution, and error handling |
[SCREENSHOT NEEDED: fabric-warehouse-generated-code.png - Generated Fabric Warehouse code output in BimlStudio]
Pipeline Features
Generated pipelines include sophisticated data movement logic:
- High watermark lookups for incremental loading
- Copy activities with proper connection settings
- Stored procedure execution for data processing
- Automatic file handling (archive/error movement)
- Error handling and retry logic
T-SQL Optimization
Because Fabric Warehouse uses a T-SQL-based query engine, BimlFlex generates optimized T-SQL code that leverages:
- Fabric Warehouse distribution strategies
- Efficient data loading patterns
- Query performance optimizations
Deployed Solution
Once deployed to Data Factory, the solution provides:
- Visual pipeline representation
- Monitoring and logging capabilities
- Error handling with automatic file archiving
[SCREENSHOT NEEDED: fabric-warehouse-fdf-pipeline.png - Data Factory pipeline visualization for Fabric Warehouse solution]
Monitoring and Management
After deployment, you can:
- Scale compute resources up or down
- View copy command completions and errors
- Suspend or resume solution execution
- Monitor execution status and performance
[SCREENSHOT NEEDED: fabric-warehouse-solution-monitoring.png - Data Factory monitoring showing completions and errors]
Files encountering errors are automatically moved to an error folder. On subsequent runs, these files will have already been processed and archived appropriately.
Comparison: Fabric Warehouse vs Fabric Lakehouse
Choose the appropriate Fabric component based on your workload requirements:
| Feature | Fabric Warehouse | Fabric Lakehouse |
|---|---|---|
| Query Engine | T-SQL | Spark SQL |
| Data Format | Managed tables | Delta Lake |
| Best For | Structured analytics, BI workloads | Flexible data engineering, ML workloads |
| Schema | Schema-on-write | Schema-on-read |
| Primary Users | SQL developers, BI analysts | Data engineers, data scientists |
| Processing | Stored procedures | Notebooks (Spark) |
Both can be used within the same BimlFlex solution, and you can mix Lakehouse and Warehouse targets as needed for your architecture.
Related Resources
BimlFlex Documentation
- Microsoft Fabric Configuration Overview
- Implementing Fabric Lakehouse
- Configuring a Landing Area
- Configuring Connections
External Resources
- Microsoft Fabric Documentation
- Fabric Warehouse Overview
- Fabric Warehouse T-SQL Reference
- Fabric Data Factory Documentation
- Fabric Data Factory Data Source Management
Video Resources
Refer to the video in the Microsoft Fabric Configuration Overview for a walkthrough of configuring BimlFlex for Microsoft Fabric, including Warehouse implementations.
Benefits of Using BimlFlex with Fabric Warehouse
BimlFlex provides significant advantages when building Fabric Warehouse solutions:
- No Code Required: Your team only needs to understand data modeling—BimlFlex generates all stored procedures and pipelines automatically
- Focus on Design: Concentrate on source-to-target mappings and transformations, not implementation details
- Automatic Updates: As Microsoft Fabric evolves, BimlFlex templates are updated to ensure optimal implementations
- Data Vault Accelerator: Full access to the Data Vault accelerator for modeling hubs, links, and satellites
- Transformation Support: Apply transformations directly in BimlFlex, including macros for reusable patterns
- Data Lineage: Complete data lineage visualization for any object in your solution
- Schema Documentation: Automatic schema diagrams and documentation generation