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
At this point, an architecture diagram illustrates the end-to-end data flow for a Fabric Warehouse solution generated by BimlFlex. The diagram shows source systems on the left connecting to Azure Data Factory, which uses copy activities to land files in Azure Blob Storage or OneLake. From there, T-SQL stored procedures load data into Fabric Warehouse tables across the staging, Data Vault or Normal Form, and Data Mart layers. Refer to the Microsoft Fabric Configuration Overview for a video walkthrough of this architecture.
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.
At this point, the BimlFlex App displays the Dashboard with the Load Sample Metadata dropdown expanded. The dropdown lists available sample sets including Fabric Data Vault and Fabric Datamart. Navigate to Dashboard in the BimlFlex App and open the Load Sample Metadata dropdown to see these options alongside any other samples available for your configured environment.
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
At this point, the BimlFlex App displays the Connections editor for the source system connection. The form shows the Cloud toggle enabled, and the Staging / Landing Environment field is populated with a connection referencing OneLake, Blob Storage, or Data Lake Storage Gen2. Navigate to Connections in the BimlFlex App and select your source connection to review and configure these settings.
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)
At this point, the BimlFlex App displays the Connections editor for the Fabric Warehouse connection. The form shows System Type set to Fabric Warehouse, the Integration Template set to Data Factory Source -> Target, the External Location field populated with the OneLake or ABFSS path for data storage, and the External Reference field containing the Fabric connection ID. Navigate to Connections in the BimlFlex App and select your Fabric Warehouse connection to review these settings.
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
At this point, the BimlFlex App displays the Batches editor, showing the list of configured batches with columns for batch name, compute resource assignment, scaling parameters, and execution priority. Navigate to Batches in the BimlFlex App to create or update batches before triggering a build of your Fabric Warehouse solution.
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 |
At this point, BimlStudio displays the Build Output pane after a successful build of the Fabric Warehouse solution. The output lists the generated artifacts by category: Warehouse table DDL scripts, T-SQL stored procedures, reporting views, and Data Factory pipeline JSON files. The output pane also shows any warnings or informational messages produced during generation. Open your project in BimlStudio and trigger a build to see this output.
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
At this point, the Microsoft Fabric Data Factory interface displays the deployed pipeline in its canvas view. The pipeline shows a sequence of activities: a high watermark lookup activity, copy activities for landing source files into the storage layer, stored procedure execution activities for T-SQL-based data processing, and file archiving activities for post-load cleanup. Navigate to your Fabric workspace, open Data Factory, and select the deployed pipeline to view this canvas.
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
At this point, the Microsoft Fabric Data Factory Monitor view displays the pipeline run history for the deployed Warehouse solution. Each run shows its status (succeeded, failed, in progress), start time, duration, and the number of copy activity completions and errors. Selecting an individual run expands the activity-level detail, including bytes read/written per copy activity and stored procedure execution results. Navigate to your Fabric workspace, open Data Factory, and select Monitor to access this view.
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