Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 33 lessons
  1. Start here: cloud and data basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. The three languages: SQL, PySpark and KQL side by side
    6. 6. Getting data in: the map of Fabric tools
  2. Store, ingest and transform batch data (exam area 2, 30-35%)

    1. 7. Choosing a data store and a transformation tool
    2. 8. Shortcuts and mirroring: use data without copying it
    3. 9. Ingest data with pipelines and Copy job
    4. 10. Transform with PySpark and SQL: join, denormalize, aggregate
    5. 11. Messy data: duplicates, missing values and late arrivals
    6. 12. Full and incremental loads, and loading a star schema
  3. Orchestrate and stream (exam areas 1 and 2)

    1. 13. Pipeline or notebook? Choosing how to orchestrate
    2. 14. Schedules and event-based triggers
    3. 15. Parameters and dynamic expressions
    4. 16. Streaming in Fabric: Eventstreams, Spark streaming and KQL
    5. 17. KQL and windowing functions for real-time data
  4. Set up and secure a Fabric solution (exam area 1, 30-35%)

    1. 18. Workspace settings: Spark, domains, OneLake and data workflows
    2. 19. Version control with Git and database projects
    3. 20. Deployment pipelines: development, test and production
    4. 21. Who can see what: workspace roles and item permissions
    5. 22. Fine-grained security: rows, columns, objects, files and masking
    6. 23. Governance: sensitivity labels and endorsement
  5. Monitor, fix and optimize (exam area 3, 30-35%)

    1. 24. Monitoring hub, alerts and Activator
    2. 25. Fixing errors in pipelines, dataflows and notebooks
    3. 26. Fixing errors in Eventhouse, Eventstream and T-SQL
    4. 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
    5. 28. Optimize Spark, pipelines and warehouses
    6. 29. Optimize eventstreams, eventhouses and queries
  6. Exam practice and final review

    1. 30. Exam day: question types, timing and how to think
    2. 31. Mock exam 1
    3. 32. Mock exam 2
    4. 33. Final review: every decision on one page
See the leaderboard

16 min

Fixing errors in Eventhouse, Eventstream and T-SQL

Diagnose real-time and SQL errors and know where each one is reported.

By the end of this lesson you can

  • Find a failing Eventstream source or destination with Data insights and Runtime logs
  • Read .show ingestion failures and fix an ingestion mapping in an Eventhouse
  • Fix unsupported T-SQL, blocking and update conflicts in a Warehouse
  • Use query insights to find the slow or failed Warehouse query

From earlier lessons · 1 of 2

A Copy activity in a pipeline fails at 2 a.m. Where do you read the exact error message first?

Pick one answer.

From earlier lessons · 2 of 2

A notebook cell fails because an upstream file renamed a column. Which kind of error is this?

Pick one answer.

Monday, 8 a.m. at Northwind Rides. The live map of scooters stopped updating at 3 a.m. The battery column in the trip table is suddenly empty. And the finance team's nightly update of the payments table failed with a strange "snapshot isolation" error. Three items, three different places to look.

[4]

Each item reports errors in its own place

Exam depth: know it cold.

For a real-time or SQL failure, your first job is to know where the error is written down. Each item keeps its own record.

ItemWhere errors showWhat you use.
EventstreamLower pane of the editor, per nodeData insights and Runtime logs tabs.
EventhouseSystem commands in the KQL database.show ingestion failures.
WarehouseError message, system viewsQuery insights (queryinsights schema) and DMVs.

A DMV (dynamic management view) is a system view that shows what is running right now.

[249][250][257]

Eventstream: Data insights and Runtime logs

Exam depth: know it cold.

An Eventstream has two monitoring views. Select a source or destination node on the canvas, then open a tab in the lower pane.

  • Data insights: metric charts for that node. Look at Input events and Output events (are events flowing?), Backlogged input events (is it falling behind?) and the error counts.
  • Runtime logs: detailed messages from the Eventstream engine at three severity levels: warning, error and information. Filter by keyword or severity, and select Refresh for the latest.

Which tabs appear depends on the node type. For an Eventhouse destination, the docs list both tabs for the Event processing before ingestion mode.

[249]

An eventstream in Live view with the Eventhouse destination node selected. The Runtime logs tab in the lower pane shows the message Send Events: Kusto output failure with a count of 200.
Microsoft Learn, CC BY 4.0

[249]

The three error counts in Data insights each point at a different cause.

  • Deserialization errors: input events the engine can't read. The incoming format doesn't match what the source expects, such as broken JSON.
  • Data conversion errors: output events that can't be converted to the destination's schema, such as text sent to a number column.
  • Runtime errors: any other error while processing events.

Rising Backlogged input events with no errors means the stream is slow, not broken. Lesson 29 covers making it faster.

[249]

Match

Match each Eventstream symptom to what it tells you

Pick an item on the left, then its match on the right.

Eventhouse: .show ingestion failures

Exam depth: know it cold.

Ingestion is loading data into a KQL database table. When it fails, the record goes to a system list. You read it with the management command .show ingestion failures. A management command starts with a dot and acts on the database, not on table rows.

Failures are kept for 14 days. The columns that matter:

  • Table and FailedOn: which table, and when (UTC).
  • Details and ErrorCode: the root cause in words and as a code.
  • FailureKind: Permanent (fix the data or setup) or Transient (a retry may work).

Streaming ingestion has its own command, .show streamingingestion failures.

[250][251]

kusto
.show ingestion failures
| where FailedOn > ago(1d) and Table == "Trips"
| project FailedOn, Table, FailureKind, ErrorCode, Details
| order by FailedOn desc
Yesterday's failures for one table, newest first. Pipe the command into where and project like a normal query.

[250]

Ingestion mapping errors: the silent kind

Exam depth: know it cold.

An ingestion mapping is the rule that says which source field fills which table column. Without one, KQL maps by position for CSV, and by name for JSON and Parquet. Name matching is case-sensitive.

A JSON mapping lists a column and a path, such as $.battery. If the partner renames the field, the path finds nothing.

The docs warn what a mismatch does: it can ingest empty or incorrect data. Rows still arrive, so nothing looks broken. You also can't change an existing column's type in Get data. Mapping a field to a column of another type can leave empty columns.

[252][253][254]

[252]

You are a data engineer at Northwind Rides. Scooter events land as JSON in an Eventhouse table. Since Sunday, new rows arrive but the BatteryLevel column is empty. The vendor renamed the field from battery to batteryPct. You need the column filled again with the least effort. What should you do?

Pick one answer.

[252][253]

The pipeline says success. The column says nothing. Check the mapping. Picture: Whiteboard doodle in black and red marker: a cartoon data engineer stands proudly next to a big green checkmark labeled "SUCCESS", while beh

Warehouse T-SQL: unsupported features

Exam depth: know it cold.

A Fabric Warehouse runs most, but not all, of SQL Server's T-SQL. Code copied from SQL Server often fails for this reason. These aren't supported:

  • Triggers, synonyms and materialized views
  • Recursive queries and SELECT ... FOR XML
  • CREATE USER and SET ROWCOUNT
  • Manually created multi-column statistics

ALTER TABLE is limited. You can add nullable columns, drop columns and add NOT ENFORCED key constraints. Rename a column with sp_rename.

Changing the isolation level has no effect. Snapshot isolation always applies: each transaction sees the data as it was when the transaction started.

Writes (INSERT, UPDATE, DELETE) work only in a Warehouse, never through a Lakehouse's SQL analytics endpoint.

[93][255]

Blocking and update conflicts

Exam depth: know it cold.

A Warehouse locks the whole table for any write, not single rows. That leads to two problems.

Update conflicts. Two transactions UPDATE, DELETE or MERGE the same table at once. The first to commit wins. The other is rolled back with error 24556 or 24706. The message starts "Snapshot isolation transaction aborted due to update conflict". Fix it by retrying the failed one, and by not running those writes on one table at the same time.

Blocking. A query waits because another session holds a lock, often an open transaction nobody committed. Find it with sys.dm_exec_requests, where blocking_session_id is not 0. Ask the owner to commit or roll back. A workspace Admin can run KILL, which rolls back that session's work.

[255][256]

sql
-- Which queries are waiting, and on which session?
SELECT session_id, status, blocking_session_id,
wait_type, total_elapsed_time, open_transaction_count
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
-- Last resort, Admin role only: ends the session and rolls back its work
KILL 101;
Find who is blocked and by whom. KILL only an idle session holding an open transaction.

[256]

You are a data engineer at Northwind Rides. Two pipelines run at 1 a.m. One updates refunds in dbo.Payments. The other updates different rows of dbo.Payments with late fees. One fails with error 24556, an update conflict. You need both to succeed every night. What should you do?

Pick one answer.

[255]

Query insights: find the slow or failed query

Exam depth: know it cold.

Query insights is a set of views in the queryinsights schema of every Warehouse and SQL analytics endpoint. It keeps 30 days of history for completed, failed and canceled queries. You need Contributor or higher.

  • queryinsights.exec_requests_history: one row per query, with status, duration, CPU and text.
  • queryinsights.long_running_queries: query shapes ranked by median run time.
  • queryinsights.frequently_run_queries: query shapes ranked by how often they run.

A finished query can take up to 15 minutes to appear. Tag important queries with OPTION (LABEL = '...') so you can find them later.

[257][258]

sql
SELECT last_run_command, median_total_elapsed_time_ms, number_of_runs
FROM queryinsights.long_running_queries
WHERE last_run_command LIKE '%NIGHTLY PAYMENTS%'
ORDER BY median_total_elapsed_time_ms DESC;
Find the slowest shapes of the labelled nightly load.

[257][258]

A checklist per item

Eventstream

  1. Select the failing node. Open Data insights: are input and output events flowing?
  2. Check the three error counts to name the cause.
  3. Open Runtime logs, filter to errors, read the message.

Eventhouse

  1. Run .show ingestion failures for the table. Read Details and FailureKind.
  2. Rows load but a column is empty? Compare the mapping with the source fields.

Warehouse

  1. Read the error. Is the feature supported, and is this a Warehouse, not an endpoint?
  2. Error 24556 or 24706? Separate the writes and retry.
  3. Query stuck? Check sys.dm_exec_requests for a blocking session.
  4. Query slow or failed earlier? Search queryinsights.

[249][250][256][257]

Exam-style practice

You are a data engineer at Northwind Rides. An Eventstream sends scooter events to a Lakehouse. The Lakehouse destination node shows a rising count of data conversion errors. Input events look normal. You need to find the cause. What is the most likely cause?

Pick one answer.

[249]

You are a data engineer at Northwind Rides. A batch load of partner CSV files into an Eventhouse table failed last night. You need to see why each file failed and whether a retry might work. What should you run?

Pick one answer.

[250]

You are a data engineer at Northwind Rides. You move a SQL Server script to a Fabric Warehouse. It creates a trigger that writes an audit row whenever a payment changes. The script fails. You need the audit rows with the least change to the design. What should you do?

Pick one answer.

[93]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 14

[93][249][250][252][255][256][257]

Worth keeping

  1. Eventstream errors live per node in Data insights and Runtime logs.
  2. Eventhouse ingestion errors live in .show ingestion failures, kept 14 days.
  3. A blank column with rows still loading points at the ingestion mapping.
  4. Warehouse writes lock whole tables: separate writes to one table and retry conflicts.
  5. Query insights keeps 30 days of Warehouse query history for finding slow or failed queries.

Northwind's errors are gone, but the trip table is still slow. It holds 200,000 tiny files and its storage keeps growing. Nothing is failing, so where do you start? Next lesson opens up the Delta table and cleans it.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (12)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [93]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
  3. [249]Microsoft Learn: Monitor the status and performance of an eventstream
  4. [250]Microsoft Learn: .show ingestion failures command
  5. [251]Microsoft Learn: .show streamingingestion failures command
  6. [252]Microsoft Learn: Ingestion mappings
  7. [253]Microsoft Learn: JSON mapping
  8. [254]Microsoft Learn: Get data from Eventstream
  9. [255]Microsoft Learn: Transactions in Fabric Data Warehouse
  10. [256]Microsoft Learn: Troubleshoot query blocking in Fabric Data Warehouse
  11. [257]Microsoft Learn: Query Insights in Fabric Data Warehouse
  12. [258]Microsoft Learn: Use query labels in Fabric Data Warehouse