Task 2.1: Design and implement loading patterns

• AA pipeline with two Lookup activities, a Copy activity and a stored procedure that saves the watermark

  Incorrect

  That pattern works, but it's the watermark table and logic the team doesn't want to maintain.

• BA Copy job in incremental copy mode with ModifiedAt as the incremental column

  Correct

  Copy job tracks the state of the last successful run itself and copies only rows with a higher ModifiedAt.

• CA mirrored database for the Azure SQL database

  Incorrect

  Mirroring replicates into its own mirrored database item, not into the existing lakehouse table.

• DA Copy job in full copy mode that ignores the ModifiedAt column

  Incorrect

  Full copy moves all 400 GB on every run instead of only the changed rows.

In incremental copy, a Copy job tracks and manages the state of the last successful run itself and copies only rows whose incremental column, here ModifiedAt, is higher than any value it copied before; a failed run resumes from the end of the last successful one. The pipeline pattern with Lookup activities, a Copy activity and a stored procedure is the do-it-yourself alternative.

• AUpdate C-104's current row: set RecValidTo to the load date and RecIsCurrent to 0

  Correct

  The current version is expired by closing its validity period and clearing the current flag.

• BInsert a new C-104 row with a new surrogate key, Segment = Wholesale and RecIsCurrent = 1

  Correct

  Type 2 adds a new version whose validity starts where the prior version ended.

• CUpdate Segment to Wholesale in C-104's current row and leave RecValidTo and RecIsCurrent as they are

  Incorrect

  Overwriting the attribute in place is type 1 behavior and loses the history.

• DDelete C-104's existing row and insert the new values with its old surrogate key

  Incorrect

  Deleting the row breaks the history that facts still reference.

• EAdd a PreviousSegment column and store Retail in it

  Incorrect

  A column for the prior value is type 3, which keeps only limited history.

For SCD type 2 changes, the load expires the current version (end date set to the processing date, current flag set to false) and inserts a new version with a new surrogate key, a start date equal to the prior version's end date and the current flag set to true. Type 1 overwrites attributes, and type 3 keeps a limited prior value in extra columns.

• AKeep INT IDENTITY(1000, 1); the warehouse honors the seed and increment as in SQL Server

  Incorrect

  Custom seed and increment values aren't supported, and IDENTITY requires the bigint type.

• BUse BIGINT IDENTITY and the keys will be sequential as long as loads run one at a time

  Incorrect

  Even sequential ingestion tasks can receive key ranges that aren't contiguous.

• CUse ROW_NUMBER() in each load to number the new rows from 1000 upward

  Incorrect

  Numbering each batch from a fixed start can repeat keys from earlier loads.

• DUse BIGINT IDENTITY and accept unique keys with possible gaps and out-of-order ranges

  Correct

  IDENTITY needs BIGINT, takes no seed or increment, and doesn't guarantee order or gap-free values.

Fabric Data Warehouse supports IDENTITY on BIGINT columns as surrogate keys. You can't specify a starting value or increment; values are positive and unique for the table's lifetime, but because the engine is distributed, gaps can appear and ranges from different ingestion tasks might not be sequential.

• AThe sales fact table first, then the dimensions that it references

  Incorrect

  Facts loaded first would miss members added to the sources since the last run.

• BGeography, then customer and vendor, then the sales fact table

  Correct

  Dimensions go first, in dependency order, so every fact lookup finds its members.

• CCustomer and vendor, then geography, then the sales fact table

  Incorrect

  Geography feeds the customer and vendor dimensions, so it has to be processed before them.

• DAll four tables in parallel, then a check that fixes missing keys

  Incorrect

  Parallel loading lets fact rows look up dimension members that aren't loaded yet.

A dimensional model ETL process optionally loads staging tables, then processes dimension tables, then fact tables, and finally runs post-processing such as refreshing semantic models. Dimensions with dependencies, such as an outrigger geography dimension used by customer and vendor dimensions, are processed in dependency order.

• AHold the stays in staging until the member's details arrive

  Incorrect

  The stays must be loaded tonight, so delaying them fails the requirement.

• BStore the stays with the surrogate key of the shared Unknown member

  Incorrect

  Unknown fits keys you can't trust; here the key is valid and should get its own member.

• CInsert an inferred member with the natural key and set its inferred flag

  Correct

  When the natural key is trusted, the fact load creates an inferred member and marks it for later completion.

• DLoad the stays with a NULL member key and fill it in once the details arrive

  Incorrect

  Fact rows need a valid dimension key when they're loaded, not a gap to patch later.

When a dimension lookup fails during a fact load, the row must still be inserted with a valid key. If the natural key is valid, the fact load can insert an inferred member with unknown attribute values and set its inferred flag, so the row is recognized as incomplete when the member's details arrive. The special Unknown member suits lookups that point to integrity problems.

• AA periodic snapshot fact table, loaded at the end of every day

  Correct

  Periodic snapshots record measurements at set intervals, such as daily stock balances.

• BA transaction fact table with one row per stock movement

  Incorrect

  Storing every movement costs a lot and doesn't serve the analysis the team needs.

• CAn accumulating snapshot fact table

  Incorrect

  An accumulating snapshot doesn't record a balance taken at the end of each day.

• DA factless fact table that records which products moved each day

  Incorrect

  A factless table records events without measures, so it can't hold balances.

A periodic snapshot fact table stores measurements at predefined intervals and suits trend analysis; its measures are semi-additive. An inventory table loaded daily with each product's end-of-day balance is the classic example, and it can replace a transaction fact table when recording every movement doesn't serve the analysis.

• AA Spark structured streaming notebook that writes to a lakehouse, plus a shortcut from the eventhouse

  Incorrect

  That needs streaming code the team doesn't want to run.

• BA pipeline Copy activity that runs every minute from Event Hubs to a lakehouse

  Incorrect

  A scheduled copy isn't a continuous stream and doesn't give KQL access within seconds.

• CAn eventstream with a lakehouse destination only, queried from KQL through the SQL endpoint

  Incorrect

  KQL runs against an eventhouse; the lakehouse's SQL analytics endpoint uses T-SQL.

• DAn eventstream with the Event Hubs source and two destinations, an eventhouse and a lakehouse

  Correct

  One eventstream can route the same stream to an eventhouse for KQL and a lakehouse as Delta tables.

Eventstreams capture events from sources such as Azure Event Hubs, optionally transform them, and route them to multiple destinations without code. An eventhouse destination makes events queryable with KQL, and a lakehouse destination converts them to Delta tables for Spark and SQL.

• AOnly rows changed in the source in the last 24 hours

  Incorrect

  There's no earlier state yet, so nothing limits the first run.

• BNothing until a watermark value is entered by hand

  Incorrect

  The first run doesn't wait for a starting value; it copies everything.

• CAll of the source data

  Correct

  The first run copies everything; later runs copy only new or changed data.

• DOnly the table schema

  Incorrect

  Data is copied on the first run, not only the schema.

In incremental copy mode, a Copy job's first run copies all data from the source; each later run moves only data that's new or changed since the last run.

• AIt's the surrogate key that fact tables should reference instead

  Incorrect

  Facts reference the generated surrogate key, Salesperson_SK.

• BIt's the natural key that relates rows to the source system

  Correct

  A natural key is the key stored in the source system, sometimes called a business key.

• CIt's a foreign key to another dimension

  Incorrect

  Foreign keys point to other dimension tables; EmployeeID comes from the source.

• DIt's an audit attribute that records which system loaded the row

  Incorrect

  Audit attributes track how rows were created or changed.

A dimension table typically has a surrogate key generated in the warehouse and one or more natural keys from the source system, which the ETL process uses to match source rows to dimension rows. Natural keys are sometimes called business keys.

• AThe default stream

  Incorrect

  The default stream carries the events as they arrive from the sources.

• BA lakehouse destination

  Incorrect

  A lakehouse destination stores the events in a table; it doesn't publish a stream that other teams can discover in Real-Time hub.

• CAn Activator destination

  Incorrect

  Activator acts on conditions; it doesn't republish the stream.

• DA new derived stream

  Correct

  A derived stream holds the transformed events, can feed multiple destinations and appears in Real-Time hub.

After you add operations such as Filter or Manage fields, you can output them to a derived stream. The derived stream represents the transformed events, can be routed to multiple destinations in Fabric, and is visible in Real-Time hub.

• AA transaction fact table

  Correct

  Transaction facts store business events at the lowest grain, known when inserted.

• BA periodic snapshot fact table

  Incorrect

  A periodic snapshot doesn't store one row per order line.

• CAn accumulating snapshot fact table

  Incorrect

  It doesn't describe order lines whose measures are final when they're inserted.

• DA junk dimension

  Incorrect

  A junk dimension is a dimension table, not a fact table.

A transaction fact table stores business events or transactions, usually at the lowest grain with additive measures; its data is fully known when inserted. Every sales order line is the standard example.