Task 2.2: Ingest and transform batch data

• AA Fabric warehouse

  Incorrect

  Warehouse foreign keys can only be created NOT ENFORCED, so orphan rows wouldn't be rejected.

• BSQL database in Fabric

  Correct

  It's built for operational OLTP workloads, with the Azure SQL Database engine and enforced relational integrity.

• CA lakehouse with Delta tables

  Incorrect

  Lakehouses suit data engineering and big data, not high-concurrency transactional row writes.

• DAn eventhouse with a KQL database

  Incorrect

  Eventhouses serve streaming and time-series analytics, not transactional apps.

Microsoft's data store guide points operational, transactional (OLTP) and normalized workloads to SQL database in Fabric, which uses the same engine as Azure SQL Database, supports transaction isolation levels and enforced foreign keys, and exposes a SQL analytics endpoint for Spark and Direct Lake reporting. Fabric warehouses support primary and foreign keys only as NOT ENFORCED.

• AA Fabric warehouse, with engineers writing through T-SQL

  Incorrect

  A warehouse fits T-SQL authors; these engineers build and change tables in PySpark.

• BA lakehouse, with analysts reading through its SQL analytics endpoint

  Correct

  Engineers build the tables with Spark, and the lakehouse's SQL analytics endpoint serves the T-SQL readers.

• CSQL database in Fabric, with the JSON flattened into normalized tables

  Incorrect

  SQL database targets operational OLTP workloads, not multi-terabyte data engineering.

• DAn eventhouse, with the files ingested into KQL tables for the analysts

  Incorrect

  Eventhouses suit streaming and time-series data queried with KQL.

The data store guide matches a lakehouse to data engineering teams that build with Spark, and every lakehouse comes with a SQL analytics endpoint that lets T-SQL users query its Delta tables without a second copy. The analysts here only read, so they don't need a warehouse of their own.

• ACreate the shortcut directly under the Tables folder, not in a subfolder

  Correct

  Shortcuts in Tables can be created only at the top level, where table discovery happens.

• BCreate the shortcut under Files/delta and let the lakehouse discover it

  Incorrect

  Table discovery doesn't happen in the Files folder.

• CCreate the shortcut in Tables/sales to group it with related tables

  Incorrect

  OneLake doesn't support shortcuts in subdirectories of the Tables folder.

• DConvert the Delta table to CSV before creating the shortcut

  Incorrect

  Delta is what makes the target recognizable as a table.

• EGive the shortcut a name without spaces

  Correct

  OneLake doesn't recognize a shortcut whose name contains a space as a Delta table.

In a lakehouse, shortcuts in the Tables folder can be created only at the top level; when the target is in a supported table format such as Delta, the lakehouse synchronizes its metadata and recognizes it as a table, readable by Spark SQL and the SQL analytics endpoint. Delta doesn't allow spaces in table names, and the Files folder doesn't perform table discovery.

• AA Dataflow Gen2 for each table, with the warehouse as its data destination

  Incorrect

  Dataflows aren't the set-based T-SQL logic the team works in.

• BA Spark notebook that builds the dimensions and facts and writes them into the warehouse

  Incorrect

  The team works only in T-SQL, and a notebook adds a Spark engine for logic the warehouse can run itself.

• CA shortcut in the warehouse that points to each cleaned lakehouse table

  Incorrect

  Warehouses don't hold shortcuts, and a pointer doesn't load dimension or fact tables.

• DWarehouse stored procedures that run INSERT...SELECT and CTAS

  Correct

  T-SQL ingestion reads tables in the same workspace and loads the model in set-based statements.

T-SQL ingestion with INSERT...SELECT, SELECT INTO and CREATE TABLE AS SELECT reads tables in other items of the same workspace, such as lakehouse tables, and loads warehouse tables in set-based operations, which Microsoft describes as likely the most efficient way to load dimensional model tables. Wrapping the statements in stored procedures keeps the load in the team's own language.

• AThrough a data gateway running in the company's own network

  Correct

  A data gateway lets mirroring reach sources behind a firewall or private endpoint.

• BBy opening public access to the company network while the snapshot runs

  Incorrect

  Mirroring replicates continuously, so the connection is needed all the time.

• CBy granting the workspace identity the db_owner role in the database

  Incorrect

  A database role grants permissions; it doesn't open a network path.

• DBy creating an ADLS Gen2 shortcut to the database's storage

  Incorrect

  Shortcuts point to storage paths, not to a SQL database's tables.

If an Azure SQL Database isn't publicly accessible and doesn't allow Azure services to connect, you can set up a virtual network data gateway or an on-premises data gateway to mirror it; the gateway connects through a private endpoint or a trusted private network. Mirroring then replicates the data into Delta tables in OneLake continuously.

• AROW_NUMBER partitioned by OrderID, newest first, keeping row 1

  Correct

  Numbering each order's versions newest first and keeping 1 returns one latest row per order.

• BSELECT DISTINCT on all columns of the staging table

  Incorrect

  Versions differ in UpdatedAt and other columns, so DISTINCT keeps them all.

• CGROUP BY OrderID with MAX() on every column

  Incorrect

  MAX per column can mix values from different versions into one row.

• DRANK() OVER (PARTITION BY OrderID ORDER BY OrderID), keeping rows ranked 1

  Incorrect

  Ordering by the partition key ranks every version 1, so nothing is removed.

ROW_NUMBER numbers the rows within each partition in the order you specify. Partitioning by the business key and ordering by the change timestamp descending, then keeping row 1, returns exactly the latest version of each order; DISTINCT and per-column MAX don't reliably keep whole, single versions.

• A| make-series T=avg(Temp) default=double(null) on Timestamp step 1h by DeviceId | extend T=series_fill_forward(T)

  Incorrect

  Forward fill repeats the last known value instead of interpolating between neighbors.

• B| make-series T=avg(Temp) on Timestamp step 1h by DeviceId | extend T=series_fill_linear(T)

  Incorrect

  By default series_fill_linear fills null placeholders, and without a null default the empty hours don't hold any.

• C| make-series T=avg(Temp) default=double(null) on Timestamp step 1h by DeviceId | extend T=series_fill_linear(T)

  Correct

  A null default marks the empty hours as missing, and series_fill_linear interpolates them.

• D| make-series T=avg(Temp) default=double(null) on Timestamp step 1h by DeviceId

  Incorrect

  The empty hours stay null, so the chart leaves them out instead of estimating them.

make-series builds one array per device with an element for every hour and puts its default value in hours that have no rows. Interpolation functions such as series_fill_linear() treat null as the missing value, so the series needs default=double(null) before series_fill_linear() replaces each missing element with a value interpolated from its neighbors; series_fill_forward() copies the previous value instead.

• AWHERE COUNT(*) > 10

  Incorrect

  WHERE filters rows before grouping and can't contain aggregates.

• BORDER BY COUNT(*) DESC

  Incorrect

  ORDER BY sorts results; it doesn't filter them.

• CHAVING COUNT(*) > 10

  Correct

  HAVING filters groups after aggregation.

• DSELECT TOP (10)

  Incorrect

  TOP limits how many rows return; it doesn't test each customer's order count.

HAVING specifies a search condition for a group or an aggregate and is typically used with GROUP BY; WHERE filters individual rows before they're grouped and can't reference aggregate functions.

• ALH_Core.dbo.Customers in the FROM clause

  Correct

  Cross-database queries use three-part names for items in the same workspace.

• BOPENROWSET(BULK 'LH_Core/Customers')

  Incorrect

  OPENROWSET doesn't reference a lakehouse table by its name.

• Cexternal_table('LH_Core.Customers')

  Incorrect

  external_table() is a KQL function.

• D[LH_Core].[Customers] through a linked server

  Incorrect

  Linked servers aren't how Fabric items reference each other.

Fabric Data Warehouse supports cross-database queries: from a warehouse you can reference other warehouses, lakehouse SQL analytics endpoints and mirrored databases in the same workspace by using three-part names such as database.schema.table.

• AThey're deleted together with the shortcut

  Incorrect

  Deleting a shortcut leaves its target unchanged.

• BNothing; only the shortcut object is removed

  Correct

  Shortcuts don't support cascading deletes.

• CThey're moved to the lakehouse's Files folder

  Incorrect

  Deleting a shortcut moves nothing.

• DThey become read-only until the shortcut is recreated

  Incorrect

  The target's permissions aren't changed.

Shortcuts behave like symbolic links that are independent of their targets. Deleting a shortcut removes only the shortcut object; the target data stays unchanged.

• AA Fabric lakehouse

  Correct

  Lakehouses hold unstructured, semi-structured and structured data for data engineering and ML.

• BA Fabric warehouse

  Incorrect

  A warehouse holds structured tables, not image and audio files.

• CSQL database in Fabric

  Incorrect

  SQL database in Fabric isn't a store for millions of image and audio files.

• DAn eventhouse

  Incorrect

  An eventhouse isn't a store for millions of image and audio files.

The data store guide recommends a lakehouse for big data and machine learning with unstructured, semi-structured or structured data.