Fabric Data Warehouse best practices for medallion architectures
Part three of a series on medallion architecture with Fabric Data Warehouse. Good medallion architecture is mostly operational discipline. In part one of this series, we chose the pattern, and in part two, we filled in the Bronze, Silver, and Gold layers. Now comes the part that usually determines whether the architecture holds up in production: the operating rules. Many medallion architectures look great on paper but become difficult to maintain as data volumes, business requirements, and consumers grow. A medallion pipeline is easy to explain and easy to demo. It is more challenging to keep clean over time. The challenges usually start small: row-by-row loads, report-specific logic in the wrong place, transformations that cannot be safely rerun, or Gold tables that slowly become another staging layer. Why this matters Most medallion problems are not caused by the names Bronze, Silver, and Gold. They happen because the pipeline stops behaving like a pipeline. Bronze starts cleaning. Silver starts serving dashboards. Gold starts compensating for upstream data quality. Before long, nobody knows where a rule belongs, and every change feels risky. The goal of best practices is not to add ceremony. The goal is to make the pipeline predictable: predictable loads, repeatable transformations, trusted outputs, and clear places to look when something breaks. Best practice 1: Batch the writes Fabric DW is built for set-based work. Treat ingestion and transformations as batches, not as a stream of tiny row-by-row operations. In Bronze, that usually means using COPY INTO, Fabric Pipelines, or other bulk-loading patterns to land data in raw tables. If you are loading from files, aim for fewer well-sized files instead of many tiny ones. When practical, files in the 100 MB to 1 GB range are a healthier starting point than a long tail of small files. In Silver and Gold, the same idea applies: prefer set-based T-SQL transformations, CTAS, INSERT...SELECT, and MERGE patterns over procedural row-at-a-time logic. Do this well Load Bronze in batches, and avoid trickle inserts when the source can be staged first. Add ingestion metadata, such as source file name and load timestamp, so every batch is traceable. Keep operational logging lightweight. If you need very high-write audit events, do not turn the warehouse into a single-row logging engine. Let Bronze preserve the batch; let Silver decide what is valid. Rule of thumb: if a load pattern creates a large number of tiny writes, fix the load pattern before tuning the query. Best practice 2: Make Silver rerunnable Silver is where the pipeline earns trust. That means Silver transformations need to be repeatable, testable, and safe to rerun. If an upstream source reloads, or a cleansing rule changes, you should know how to rebuild the affected Silver tables without guessing which reports need to be patched. This is where idempotent design matters: a transformation should produce the same result when run again against the same inputs. In Fabric DW, use CTAS when you want to materialize a clean table from a query, INSERT...SELECT for controlled incremental loads, and MERGE when late-arriving or changed data needs to update existing Silver rows. Do this well Design transformations so they can run again without duplicating or corrupting data. Use staging tables when the logic is complex. A few clear steps are easier to operate than one unreadable query. Put quality gates in Silver: required fields, valid formats, duplicate handling, and reason codes for rejected records. Choose precise data types and lengths. Silver is the right place to turn loose source data into reliable analytical data. Rule of thumb: if a report needs to clean the data again, Silver did not finish its job. Best practice 3: Shape Gold for consumption Gold is not just “the final table.” Gold is the business-facing serving layer. It should be modeled around how people ask questions, not around how the source systems store data. For some workloads, that means a star schema with fact and dimension tables. For others, it means a data mart, a wide reporting table, or a pre-aggregated summary. The pattern matters less than the principle: Gold should make the common analytical path simple, fast, and trustworthy. This is also where you should be careful not to let Gold become a junk drawer. If Gold is full of one-off fixes, report-specific exceptions, and raw technical fields, the layer is doing too much. Do this well Model the grain explicitly. A fact table without a clear grain becomes hard to explain and harder to debug. Pre-aggregate where the business repeatedly asks the same question. Hide technical fields that helped the pipeline but do not help the consumer. Keep Gold dependent on Silver by default. Direct Gold-to-Bronze dependencies should be rare and deliberate. Rule of thumb: Gold should answer the business question quickly without making the report author rediscover the pipeline. Best practice 4: Use Fabric DW defaults, but do not fight the engine Fabric DW gives you a SQL warehouse over Delta data in OneLake. That means you get transactional behavior, optimized storage patterns, and a managed engine that handles many physical decisions for you. The practical advice: do not bring every habit from traditional data warehousing with you. You do not need to micromanage distribution or indexing the same way you would in older platforms. Focus first on healthy data layout, set-based transformations, good table design, and predictable query patterns. At the same time, do not ignore the basics. Query performance still benefits from clean data types, useful statistics, well-shaped Gold tables, and avoiding unnecessary scans. Do this well Keep V-Order and platform optimizations on unless you have a measured reason to change them. Use the performance guidance for Fabric Data Warehouse before inventing custom tuning patterns. Check query behavior when a Gold table becomes critical to many reports. Treat advanced exceptions as exceptions. Most teams should start with the defaults and tune only when evidence says to tune. Rule of thumb: tune from evidence, not from habit. Best practice 5: Monitor by layer A medallion pipeline should be observable at each layer. If a dashboard is wrong or slow, you should be able to tell whether the issue started in Bronze ingestion, Silver transformation, or Gold serving. In Fabric DW, use Query Insights and the warehouse monitoring views to understand query behavior, expensive operations, and refresh patterns. Pair that with pipeline-level monitoring so you can see not only whether a job failed, but where the failure happened. Measure the pipeline in terms the team can act on: Bronze load duration, Silver transformation duration, rejected-record counts, Gold refresh duration, and Gold query performance. Do this well Track load and refresh duration by layer. Log the number of records received, accepted, rejected, and published. Watch critical Gold queries after refresh, especially the ones that feed executive dashboards or widely used semantic models. Keep operational alerts tied to business impact. A failed Gold refresh matters differently from a delayed Bronze load. Rule of thumb: if you cannot tell which layer failed, your monitoring is not layer-aware enough. Before moving a medallion pipeline into production, use the following checklist to verify that each layer is operating as intended. The best-practice checklist Area Bronze Silver Gold Write pattern Batch ingest with metadata Set-based transformations Scheduled refreshes Quality rule Preserve what arrived Validate, conform, and flag Expose trusted fields only Performance focus Avoid tiny writes Keep logic rerunnable Shape for common queries Takeaway Part two of this series was about one job per layer. Part three is about operating each job like it matters. Batch the writes. Make Silver rerunnable. Shape Gold for consumption. Use Fabric DW’s managed engine instead of fighting it. Monitor the pipeline by layer so failures are easy to locate and fixes happen in the right place. If you follow those rules, your medallion architecture becomes less fragile over time, not more. This post is part of our Medallion Architecture on Fabric Data Warehouse series: Choosing your medallion pattern in Fabric Data Warehouse Building the Bronze → Silver → Gold layers Fabric DW best practices for medallion architectures Securing and governing your layers Performance tuning your medallion pipeline Ready to go deeper? Explore the Microsoft Fabric Data Warehouse performance guidelines and ingestion guidance, then stay tuned for Part four of this series, where we’ll cover securing and governing your layers.
Streamlining orchestration with smarter pipelines and deeper monitoring
Spend less time configuring and troubleshooting pipelines—and more time delivering insights. The latest Microsoft Fabric Data Factory updates introduce smarter authoring, deeper observability, AI-powered operations, and new pipeline capabilities that make building and managing data workflows faster, easier, and more scalable.
AI-assisted Synapse Spark and pipeline migration to Microsoft Fabric from the command line (Preview)
Many organizations running Apache Spark workloads on Azure Synapse Analytics are planning their move to Microsoft Fabric for a more unified analytics experience. This preview introduces an AI-assisted migration skill that helps move Synapse Spark artifacts to Fabric from the command line, using REST APIs and guided steps throughout the process. The Synapse Migration skill manages the end-to-end migration of Spark pools, lake databases, notebooks, and Spark job definitions (SJDs) into their Fabric equivalents. You provide a Synapse workspace URL and a target Fabric workspace, and the skill helps resolve dependencies, refactor code where needed, and generate a detailed migration report. A companion Pipeline Migration skill can also move the Synapse Data Factory pipelines that orchestrate those notebooks, so both Spark artifacts and their workflows land in Fabric. The migration is idempotent by design, so it is safe to re-run if interrupted. It automatically detects migration blockers, such as C#/.NET notebooks and GPU pools with no Fabric equivalent, and flags them clearly so you can plan around those gaps. Migration phases The Synapse migration skill orchestrates the migration flow, guiding users through the migration of all items in a predefined sequence while managing internal dependencies between those items. Phase 0: Spark pools → Fabric environments — Mirrors pool configuration; upgrades runtime to Spark 3.5 (Runtime 1.3) Phase 1: Lake database → Lakehouse — Migrates database schemas and tables into Fabric Lakehouses Phase 1b: Storage path shortcuts — Scans code for abfss:// paths and creates OneLake shortcuts Phase 2: Notebooks → Fabric notebooks — Migrates notebooks with automatic code refactoring for Fabric compatibility Phase 3: SJD → Fabric Spark job definitions — Transfers job definitions with full configuration preservation Final: Validate and report — Cross-validates all migrated items, flags blockers, and generates a summary Two migration strategies Different teams have different priorities. Some need to move fast and refactor later; others want artifacts that are ready to run in Fabric on day one. The skill supports both approaches. Lift-and-shift vs. migrate-and-modernize Category Lift-and-shift Migrate-and-modernize Approach Copy artifacts as-is with minimal changes Refactor code and bindings during migration User input Source + target workspace only — all decisions auto-resolved Guided decisions at each phase Code changes None — notebooks and SJDs migrated verbatim mssparkutils → notebookutils, TokenLibrary → notebookutils.credentials, path rewrites, linked service removal Spark version Upgraded to Runtime 1.3 (Spark 3.5) Same upgrade Lakehouse/Env binding Auto-bound with sensible defaults User chooses at each decision point Phase 1b (shortcuts) Skipped Scans code for abfss:// paths, creates OneLake shortcuts Best for Fast migration; team refactors post-cutover Clean migration; items ready to run in Fabric immediately Lift-and-shift automatically resolves all decision points with sensible defaults: it creates environments, consolidates databases into a single Lakehouse, converts tables to Delta format, auto-binds everything, and skips code refactoring. Teams that need more control can choose migrate-and-modernize and make guided decisions at every step. Migrating your Synapse pipelines In Synapse, notebooks often rely on pipelines for workflow orchestration. Pipelines schedule notebooks, chain activities together, add control flow, and pass parameters between steps. The Pipeline Migration skill moves that orchestration layer into Fabric Data Factory pipelines and pairs with the Spark migration by rewiring notebook activities to the Fabric notebooks created by the Synapse Migration skill. Like the Spark migration skill, the Pipeline Migration skill runs from the command line over REST APIs. It starts with a read-only assessment that identifies scope and blockers before creating anything in Fabric. It then handles key platform differences automatically: Synapse Fabric What the skill does Linked services Connections Recreates each linked service as a Fabric connection and rewires activity references to it Datasets Inlined properties Folds dataset definitions directly into activity typeProperties as Fabric has no separate Dataset item Global parameters Variable library Converts workspace global parameters into a variable library, referenced as @pipeline().libraryVariables.<name> SynapseNotebook activity TridentNotebook activity Repoints notebook activities at their migrated Fabric notebooks by GUID Validation activity GetMetadata + IfCondition Rewrites the unsupported activity into an equivalent Fabric pattern The skill flags what it can’t carry over so you can plan around those gaps. Triggers are left out by design; you can recreate schedules in Fabric once the pipelines are in place. Guided decision points in migrate-and-modernize When you choose the modernize path, the skill presents options at each migration phase so you can control how artifacts map to their Fabric equivalents. Decision point Options Spark pool → environment Create a custom environment that mirrors pool configuration, or use a Fabric starter pool Lake database mapping Mode A: All databases → schemas in one Lakehouse | Mode B: Each database → its own Lakehouse | Mode C: Hybrid per-database assignment Non-Delta tables Convert to Delta format or keep as-is in Files/ with shortcut Storage path shortcuts Create OneLake shortcuts for external ADLS containers or keep original abfss:// paths Notebook Lakehouse binding Use existing Lakehouse or create a new one Notebook environment binding Auto-bind using pool mappings or manually assign a different environment Code refactoring Apply automatic refactoring or skip and refactor post-cutover The Synapse Migration and Pipeline Migration skills give teams a guided way to move Spark from Synapse to Fabric, with support for both quick lift-and-shift migrations and more controlled modernization paths. The Synapse migration skill and Pipeline migration skill are open-source and part of the skills-for-fabric toolkit on GitHub. To get started, install the skill and authenticate with az login. The skill works with GitHub Copilot, VS Code, Claude, and other AI coding assistants. Review the skill.md files for setup details. Explore the following documentation for more information: Skills for Fabric overview — What skills are, how they differ from MCP servers, supported AI tools, and agent specializations Install Skills for Fabric — Prerequisites, installation steps for Copilot CLI/Claude Code/other tools, bundles, example prompts, updates, and troubleshooting. Available Skills for Fabric by workload — Full catalog of all 23 skills across 11 workload categories. Questions, issues, or feature requests? Open an issue on GitHub — contributions and feedback are welcome.
Secure Azure and Fabric Event Flows Across Workspaces with Outbound Access Protection (Preview)
Lock down cross-workspace event flows in Fabric: new Outbound Access Protection lets you control which workspaces can consume Azure and Fabric events—blocking unauthorized access by default until you explicitly allow the Real-Time Events connector.
