Azure Data Factory is the core cloud-based data integration service. The scenario describes a classic ETL/ELT challenge with disparate sources (SQL Server, Data Lake) in different formats. Before any analytics or machine learning can happen, the data must be ingested, transformed, and unified into a single, reliable pipeline. Data Factory is specifically designed for this orchestration and movement of data, making it the foundational first step.

Azure Data Factory is Azure’s primary data orchestration service. Its pipeline canvas allows you to visually create a sequence of activities, including a dedicated ‘Databricks Notebook’ activity. You can easily set dependencies so one notebook runs after another succeeds, and ADF has built-in fault handling and alerting mechanisms. While the other options could be forced to work, ADF provides a managed, scalable, and native way to orchestrate such data workflows.

In large-scale data warehousing scenarios typical of Synapse, using hash comparisons (e.g., using HASHBYTES() on the concatenated business key columns) is a performant and reliable method for detecting changes. Comparing a single hash value is much more efficient than comparing each column individually in a join condition. The LEFT JOIN/IS NULL pattern is the standard way to find new rows (inserts). This combination is a classic and optimized pattern for SCD Type 2 ETL logic.

The medallion architecture’s Bronze layer is meant to retain immutable, raw data. Deleting data violates this principle. The correct approach is to perform an optimization operation *within* the Bronze layer. You can use a `OPTIMIZE` command (in Spark/Databricks) or a similar compaction job that reads the many small Delta files and rewrites them into fewer, larger files. This improves read performance for downstream processes that consume from Bronze, while preserving all the original data.

The ForEach activity in ADF has a ‘Batch count’ property that controls how many iterations are executed in parallel. By default, it is set to 1 (sequential). Increasing this value (e.g., to 10) allows the loop to process multiple items concurrently, which can drastically reduce total run time for independent tasks. It’s important to set this to a reasonable number that doesn’t cause throttling or resource contention on the target systems.

Row-Level Security (RLS) is the feature designed for this exact purpose. It allows you to create security policies that filter rows at the database level based on user characteristics (like their AAD group membership or a function like `USER_NAME()`). When a user from the ‘Europe’ group queries the `Sales` table, the RLS predicate automatically adds a `WHERE Region = ‘Europe’` clause, ensuring they only see their regional data. Dynamic Data Masking obscures data, while TDE and column encryption protect data at rest.

Data skew occurs when one or a few values of the join key are extremely common, causing a disproportionate amount of data to be sent to a single partition or worker node. This creates a performance bottleneck. The warning suggests this is detected. Mitigations include using a ‘skewed key’ option in the join transformation (if available) to handle the common values separately, or applying techniques like salting to break up the skewed keys into more evenly distributed values.

A significant operational advantage of the T-SQL COPY command is its simplicity. With PolyBase, you must first create an External Data Source, an External File Format, and an External Table before you can load data with `CREATE TABLE AS SELECT` or `INSERT…SELECT`. The COPY statement encapsulates all this metadata, allowing you to load data directly from a storage location with a single, more intuitive command, specifying the file format and options inline.

This describes the classic watermark-based incremental load pattern. It is robust and widely applicable. The pipeline stores the high-watermark (the last loaded max date) in a control table, retrieves it at the start of the run, and uses it to filter the source query (`WHERE LastModifiedDate > @Watermark`). This ensures only delta changes are moved, which is efficient for large tables. While option 4 (CDC) is ideal, it requires specific source system support and connectors, whereas the watermark pattern works with virtually any source that has a reliable date/time column.

Azure Data Factory is the core cloud-based data integration service. The scenario describes a classic ETL/ELT challenge with disparate sources (SQL Server, Data Lake) in different formats. Before any analytics or machine learning can happen, the data must be ingested, transformed, and unified into a single, reliable pipeline. Data Factory is specifically designed for this orchestration and movement of data, making it the foundational first step.0