Task 2.3: Transform data

• ATreat a region change as SCD type 1: overwrite the region on the existing row

  Incorrect

  Overwriting makes past sales roll up to the new region, as if the salesperson had always been there.

• BTreat a name correction as SCD type 2 so that both spellings stay available

  Incorrect

  A new version would keep the misspelled name on historic rows, which the requirement rules out.

• CTreat a region change as SCD type 2: expire the current row and insert a new version

  Correct

  A type 2 version keeps old sales on the old row, so history stays under the region at the time of the sale.

• DTreat a region change as SCD type 3 with a PreviousRegion column on the current row

  Incorrect

  Type 3 keeps limited history on one row, so older sales can't be linked to the region at the time of each sale.

• ETreat a name correction as SCD type 1: overwrite the name on the existing rows

  Correct

  Type 1 overwrites the attribute because there's no need to keep the misspelling, and it's the type used to correct errors.

SCD type 1 overwrites the existing dimension member and is used for most changing attributes and for correcting errors; it doesn't preserve history. SCD type 2 inserts a new time-based version, which needs a surrogate key, and is reserved for attributes that must keep historical perspective, such as a salesperson's region. A dimension can support both SCD type 1 and type 2 changes. Type 3 tracks limited history with attributes on the same row.

• ASwitch RiskBand to SCD type 1 so that each customer keeps a single row

  Incorrect

  Overwriting removes the risk band that applied at the time of each sale, which analysts still need.

• BSwitch RiskBand to SCD type 3 with a PreviousRiskBand column on the current row

  Incorrect

  Type 3 keeps only limited history, so sales older than the last change lose their band.

• CKeep SCD type 2 and add a label attribute that names each version of the customer

  Incorrect

  A label makes versions readable but doesn't reduce their number, which is the problem.

• DStore RiskBand as its own dimension key in the sales fact table instead

  Correct

  When a changing attribute produces too many versions, it may be better stored in the fact table as a dimension key.

SCD type 2 inserts a new versioned row for each change and should be reserved for attributes that must preserve history. Too many SCD type 2 changes make a dimension hard to comprehend, and frequent changes can indicate that the attribute might be better stored in the fact table, as a dimension key, than versioned in the dimension. Type 3 keeps limited history with attributes on the same row, and a label attribute describes each member and version.

• AA bridge table with one row per salesperson and region pair, holding both surrogate keys

  Correct

  The bridge stores the many-to-many relationship, with a one-to-many relationship from each dimension to it.

• BA comma-separated RegionList column on DimSalesperson

  Incorrect

  A list in one column can't be related to DimSalesRegion, so slicing by region doesn't work.

• COne DimSalesperson row per salesperson and region, sharing the salesperson's surrogate key

  Incorrect

  Duplicate keys break the one side of the relationship to the fact table.

• DA RegionKey column on DimSalesperson that stores the salesperson's primary region

  Incorrect

  It keeps one region per salesperson, so the other regions they cover are lost.

When a dimension attribute must store multiple values, you design a multivalued dimension with a bridge table, which stores a many-to-many relationship between entities. With a salesperson dimension and a sales region dimension, the bridge holds a row for each salesperson and sales region relationship: physically there's a one-to-many relationship from each dimension to the bridge, and logically a many-to-many relationship between salespeople and regions.

• AQuantity int

  Incorrect

  int is a supported exact numeric type for warehouse tables.

• BOrderedAt datetime

  Correct

  datetime isn't supported for warehouse tables; use datetime2, whose precision is limited to 6 digits for fractions of seconds.

• CAmount decimal(18, 2)

  Incorrect

  decimal is supported, and it's the alternative recommended for money columns.

• DShipDate date

  Incorrect

  date is supported for warehouse tables.

• ECustomerName nvarchar(200)

  Correct

  nchar and nvarchar aren't supported; use char and varchar, which store Unicode in a UTF-8 collation.

Fabric Data Warehouse supports a subset of T-SQL data types for tables. bit, smallint, int, bigint, decimal and numeric, float and real, date, time and datetime2, char, varchar, varbinary and uniqueidentifier are supported. Types such as money, datetime and smalldatetime, datetimeoffset, nchar and nvarchar, text, tinyint and xml aren't, with alternatives such as decimal, datetime2, varchar and smallint.

• AChange the workspace's default Data Warehouse collation to case insensitive

  Incorrect

  A workspace-level change affects only warehouses created afterwards, not the existing one.

• BCreate a new warehouse with the case-insensitive collation and move the tables there

  Correct

  A warehouse's collation is fixed at creation, so ignoring case everywhere needs a new warehouse created with the CI collation.

• CALTER DATABASE to switch the warehouse to Latin1_General_100_CI_AS_KS_WS_SC_UTF8

  Incorrect

  Once a warehouse or SQL analytics endpoint is created, its collation can't be changed.

• DWrap every compared column in UPPER() in the nightly queries

  Incorrect

  It would work, but every query would have to be rewritten, which the team wants to avoid.

New warehouses and SQL analytics endpoints use the workspace's Data Warehouse default collation, which by default is the case-sensitive Latin1_General_100_BIN2_UTF8. The case-insensitive alternative is Latin1_General_100_CI_AS_KS_WS_SC_UTF8. Changing the workspace setting doesn't affect existing items, and a warehouse's collation can't be changed after it's created; create a new warehouse with the collation you need, from the workspace setting or with the REST API.

• AA RelativeMonthOffset column in DimDate that the daily ETL refreshes

  Correct

  Relative offset attributes belong in the date dimension, and the ETL must run daily to update them based on the current date.

• BA DAX calculated column on the model's Date table that uses TODAY()

  Incorrect

  Direct Lake on SQL doesn't support calculated columns that reference Direct Lake tables, so the column would have to be prepared upstream.

• CA RelativeMonthOffset column computed once when DimDate is generated

  Incorrect

  Offsets are relative to today, so values computed once go stale; the ETL has to update them every day.

• DA RelativeMonthOffset column on each fact row, set when the row loads

  Incorrect

  A value stored at load time goes stale for older rows, and period attributes belong in the date dimension, not in the facts.

A date dimension can include relative offset attributes, such as RelativeMonthOffset, to support relative date filtering: current periods use 0, previous periods -1, -2 and so on. When the dimension includes them, the ETL process must run daily to update the offset values based on the current date. For a Direct Lake model, include every column needed for filtering and grouping as a dimension attribute, because calculated columns that reference Direct Lake tables aren't supported in Direct Lake on SQL models.

• AAn executive dashboard needs revenue aggregated by month and product category

  Correct

  An aggregation that changes the data's grain or business meaning is an independent purpose for another table.

• BThree Fabric engines read the same order lines

  Incorrect

  Several engines reading the data isn't a reason by itself; one shared table should serve compatible consumers.

• CA partner extract may hold only five years of rows and must leave out two confidential columns

  Correct

  Different retention and security requirements are reasons the shared table can't serve that use.

• DThe Direct Lake model reads the table while Spark jobs write it

  Incorrect

  Readers and writers on different engines share Delta tables; that alone doesn't call for a copy.

• EData scientists prefer to query the table from notebooks rather than through SQL

  Incorrect

  A preference for another engine over the same data doesn't justify duplicating it.

Fabric guidance is to use one shared Delta table whenever consumers' requirements are compatible, and not to create another physical table solely because multiple engines read the data. Another table is justified when it has an independent purpose, such as a transformation or aggregation that changes the grain or business meaning, different security, retention or quality requirements, or a latency the shared table can't meet.

• AThe escape character in the source format, set to N/A

  Incorrect

  The escape character escapes quotes inside quoted values; it doesn't decide which text counts as a missing value.

• BA string type for Quantity in the destination mapping

  Incorrect

  The rows would load, but N/A would stay as text instead of arriving as NULL in a numeric column.

• CMultiline rows in the source format, turned off

  Incorrect

  That setting is about rows that span several lines and read performance, not about how null values are written in the file.

• DThe Null value setting of the source format, as N/A

  Correct

  Null value specifies the string that represents null in the file; its default is an empty string.

For delimited text in a pipeline, the source format settings describe how to read the file: column and row delimiters, the quote and escape characters, whether the first row is a header, whether rows can span several lines, and the Null value, the string representation of a null value, which is an empty string by default. Setting it to the vendor's marker makes those cells arrive as NULL, so a numeric destination column can take them.

• AThe endpoint converts the values to varchar(36) strings, which don't match

  Incorrect

  No such conversion happens: with no matching Delta Parquet type, the values are stored as binary, which the endpoint can't read.

• BCross-database joins aren't supported between a warehouse and an endpoint

  Incorrect

  Cross-database queries between a warehouse and an endpoint are supported; the failure comes from this column's type.

• CIt's stored as binary in Delta Parquet and can't be read on the endpoint

  Correct

  Delta Parquet has no matching type, so the endpoint sees binary values and joins on the column fail.

• DThe two items use different collations

  Incorrect

  Collation affects character comparisons, not a uniqueidentifier column's storage.

The uniqueidentifier type has no matching type in Delta Parquet, so it's stored as a binary type. Warehouse supports storing and reading uniqueidentifier columns, but the values can't be read on the SQL analytics endpoint, and reading them in a lakehouse shows a binary representation. Features such as cross-joins between Warehouse and the SQL analytics endpoint on such a column don't work as expected.

• Anvarchar(max)

  Incorrect

  The endpoint maps Delta strings to varchar with a UTF-8 collation, not to nvarchar.

• Bvarchar(4000)

  Incorrect

  varchar(4*n) is the mapping for VARCHAR(n) columns shorter than 2,000 characters; STRING columns get varchar(8000).

• Cvarchar(max)

  Incorrect

  varchar(max) is used for STRING in endpoints of mirrored items, not of a lakehouse.

• Dvarchar(8000)

  Correct

  STRING maps to varchar(8000) in the SQL analytics endpoint of a lakehouse, with a UTF-8 BIN2 collation.

The SQL analytics endpoint generates its table columns from the Delta types. STRING and VARCHAR(n) with n of 2000 or more become varchar(8000) in the SQL analytics endpoint of a lakehouse and varchar(max) in the endpoint of mirrored items, while shorter VARCHAR(n) columns become varchar(4*n), all with the Latin1_General_100_BIN2_UTF8 collation.

• ACAST(Amount AS decimal(18, 2))

  Incorrect

  CAST raises an error when a value can't be converted, so the query fails.

• BTRY_CAST(Amount AS decimal(18, 2))

  Correct

  TRY_CAST returns the converted value when it succeeds and NULL when the conversion fails.

• CCONVERT(decimal(18, 2), Amount)

  Incorrect

  CONVERT also raises an error on values that can't be converted.

• DISNULL(Amount, 0)

  Incorrect

  ISNULL replaces NULLs; it doesn't convert text or handle 'n/a'.

TRY_CAST tries to convert a value to the specified data type and returns it if the cast succeeds; if an error occurs, it returns NULL. A conversion that's explicitly not permitted still fails with an error. TRY_CAST is available in the Fabric warehouse and the SQL analytics endpoint, which makes it useful for cleansing values that might not convert.

• A16 MB

  Correct

  The limit for storage in varchar(max) is currently 16 MB in Fabric Data Warehouse.

• B8 KB

  Incorrect

  8,000 characters is the limit of varchar(8000), not varchar(max).

• C2 GB

  Incorrect

  The documented limit for varchar(max) in Fabric Data Warehouse is 16 MB, far below 2 GB.

• D1 MB

  Incorrect

  The documented limit is 16 MB.

Fabric Data Warehouse supports varchar and varbinary, and the limit for storage in varchar(max) and varbinary(max) is currently 16 MB. There's no json type for tables; use varchar instead.