Ingest and transform data

DP-700 · 67 questions

• A national archives network loads the previous day's ticketing transactions from each branch's point-of-sale system into a Fabric Warehouse every night. The source system cannot reliably flag which rows changed, but the full table is small and inexpensive to pull each run. Which loading pattern best fits this scenario?
• An archives network's collection-management system stamps every catalogue record with a reliable LastModifiedUtc column whenever a record is re-described or corrected. The catalogue holds millions of records, but only a small fraction change on any given day. Which loading pattern is the best fit for refreshing the Lakehouse copy?
• Conservation sensors in a museum's rare-manuscripts vault emit humidity, light, and vibration readings continuously, and curators want alerts within seconds of an out-of-range reading. Which loading pattern fits this requirement?
• A data engineer is preparing cleansed loan-tracking data for a dimensional model that will report on objects loaned between museum branches. Each branch dimension row will be joined into the loan fact table, and branch names occasionally get renamed after reorganizations. Which practice should the engineer follow when preparing the branch dimension for loading?
• An archives team is ingesting scanned document images and their OCR text output for oral-history transcripts. The files vary in format, some processing will use PySpark notebooks, and there is no requirement for T-SQL querying against this data yet. Which Fabric data store should the team choose to land this data?
• A museum network's finance team needs to run complex T-SQL joins and aggregations across ticketing revenue and membership billing data, and several analysts are only comfortable writing SQL, not Spark code. Which Fabric data store best matches this need?
• Vibration and light sensors across a museum network's storage vaults emit millions of timestamped readings per day, and conservation staff need fast time-series queries filtering by branch, sensor type, and time window. Which Fabric data store is designed for this workload?
• A national archives network is designing a medallion architecture in its Lakehouse for digitised-collection metadata pulled nightly from each branch's collection-management system. What should the bronze layer contain?
• Continuing the archives network's medallion design, the team wants a layer where branch-specific field names are standardized, duplicate loan records across systems are removed, and data types are validated before anything is aggregated for reporting. Which layer is this?
• The archives network's leadership wants a single table showing total visitor counts per branch per month, ready to plug directly into a Power BI dashboard with no further joins or cleansing needed by the report author. Which medallion layer should produce this table?
• A regional branch of the archives network keeps its own digitised-object files in an existing Azure Data Lake Storage account that it cannot migrate. The central data engineering team wants to reference those files from the central Lakehouse without duplicating the storage or building a copy pipeline. Which OneLake capability should they use?
• The archives network's membership system runs on an operational Azure SQL Database, and the analytics team wants a near-real-time, continuously refreshed copy of its tables in OneLake without hand-building an ingestion pipeline. Which capability is designed for this?
• An archives network needs to combine an oral-history transcript dataset with heavy PySpark transformations that write large new derived tables, and the result must be physically stored inside the destination Lakehouse rather than left dependent on the original source location. Why would a pipeline copy be favored here over a OneLake shortcut?
• A data engineer sets up mirroring against the archives network's donor-management database so its tables appear in OneLake for analytics. Which statement correctly describes what mirroring provides once it is configured?
• A data engineer designing the archives network's nightly ingestion wants to isolate any failure in a branch's export format so a bad file cannot corrupt the conformed silver tables, and wants the ability to reprocess a failed run without re-extracting from the source branch system. Which practice addresses this?
• A catalogue record's artifact-condition classification is corrected from a data-entry typo. Analysts agree there is no reporting value in keeping a record of the incorrect classification once it's fixed, and the dimension row should simply reflect the current, correct value. Which slowly changing dimension approach fits this correction?
• An archive's catalogue record for a manuscript is re-described over time as scholarship advances: its subject classification changes twice over several years, and curators want reports to be able to show which classification was in effect at the time each past loan or exhibit occurred. Which slowly changing dimension approach supports this?
• A branch dimension tracks the administrative region a branch reports to. Leadership only ever needs to compare a branch's current region against its immediately prior region for a single transition review, and they explicitly do not need full multi-version history. Which slowly changing dimension design fits this narrow requirement most efficiently?
• A team preparing a dimensional model for inter-branch loan reporting has not yet decided what a single row in the loan fact table represents: some want one row per loan agreement, others want one row per day an object remains on loan. Which step should the team complete before building the fact table?
• A team implementing a Type 2 slowly changing dimension for museum branches needs the loan fact table to always join to the exact version of a branch's attributes that was current when each loan occurred. What must the fact table reference to make this work correctly?
• A gallery's visitor-flow sensors stream footfall counts continuously, and the reporting requirement is a rolling five-minute total per gallery, refreshed as new events arrive rather than recomputed from all history each time. Which loading pattern characteristic should the design center on?
• A loan-tracking fact row arrives referencing a newly acquired object that has not yet been fully catalogued, so no matching row exists in the object dimension. Rejecting the fact would delay reporting on the loan until cataloguing finishes days later. Which loading pattern handles this kind of late-arriving dimension gap?
• An archives network is choosing where to land newly digitised oral-history audio files alongside the semi-structured JSON metadata each file's OCR and transcription process produces, before any Spark transformation happens. Which factor should most directly drive the data store choice at this stage?
• A national archives network is standing up a home for newly digitised object metadata coming from a dozen branches. Each branch's export carries a slightly different set of fields, the volume will grow into the tens of millions of records, and the conservation team wants to run PySpark notebooks directly against the data alongside ad-hoc SQL queries. Which Fabric data store best fits this requirement?
• A museum's conservation team streams humidity, light, and vibration readings from gallery and vault sensors around the clock, generating hundreds of thousands of readings per hour. Analysts need to run fast time-series queries, like the average vault humidity over the last six hours, against this telemetry. Which Fabric data store is purpose-built for this workload?
• The archives network's finance office keeps ticketing revenue and membership-fee data that must support multi-table transactions, foreign-key style relationships between branches and transactions, and heavy T-SQL reporting for the annual audit. Which Fabric data store should host this data?
• A collections registrar with no coding background needs to clean up an inconsistent branch export of inter-branch loan records — trimming whitespace, renaming a couple of columns, and filtering out cancelled loans — before it lands in a Lakehouse table. Which tool is the best fit for this task?
• A data engineer needs to run a custom deduplication and text-normalization routine across millions of OCR-extracted oral-history transcript records, using a third-party Python NLP library and iterative logic that doesn't map cleanly onto point-and-click transformations. Which tool should the engineer use?
• An analyst already has gallery sensor readings landed in an Eventhouse and wants to quickly check, ad hoc, which vault exceeded a vibration threshold in the last hour before deciding whether to escalate. Which tool should the analyst reach for first?
• A regional branch keeps its own scanned-document archive in an Azure Data Lake Storage Gen2 account that predates the museum network's move to Fabric. The central data engineering team wants to query and transform that data through OneLake without physically copying it into a Lakehouse. What should they create?
• Two workspaces in the same Fabric tenant each host a Lakehouse. The exhibits team's workspace needs read access to the digitised-object Delta table that lives in the collections team's Lakehouse, without maintaining a second physical copy of that multi-terabyte table. What is the appropriate solution?
• The archives network's donor-relationship data lives in an operational Azure SQL Database used by the development office, and analysts want that data reflected in OneLake as near-real-time, read-only Delta tables without the team building and maintaining a custom extraction pipeline. Which Fabric capability directly fits this need?
• After enabling mirroring for the development office's Azure SQL Database, an analyst opens the mirrored tables in OneLake and tries to run an UPDATE statement to correct a donor's mailing address directly against the mirrored table. What should the analyst expect?
• Every night, the archives network needs to pull the previous day's visitor-flow CSV exports from each branch's on-premises file server into a Lakehouse's Files section before any transformation happens. Which Fabric orchestration capability is designed to move this raw data into Fabric?
• A data engineer is building a pipeline that must first copy freshly digitised object images into a Lakehouse, and then run a complex PySpark cleanup routine — including a third-party image-metadata library — against those files as the next orchestrated step. Which pipeline activity should invoke that PySpark cleanup step?
• A branch's ticketing point-of-sale system occasionally re-sends the same transaction record after a network retry, so a Lakehouse table built from a PySpark notebook ends up with exact duplicate rows sharing the same transaction ID. Which PySpark approach removes those duplicates before the data is written downstream?
• A nightly batch adds new inter-branch loan records to a Warehouse table, but some records represent updates to loans that were already loaded the previous night — for example, a loan's return date being filled in late. A straight INSERT would create duplicate rows for those loans. Which T-SQL approach correctly applies both new and updated records in a single operation?
• Conservation telemetry lands in an Eventhouse as raw sensor rows with separate temperature-in-Celsius and humidity-percentage fields. Before the data is queried by the visualization team, an engineer wants to compute and store a derived risk-score column on each row as it's processed. Which KQL capability is designed for adding a computed column like this?
• A reporting team wants a single, wide table that combines each digitised object's core catalogue record with its branch name, its current conservation status, and its most recent loan destination — all of which currently live in four separate normalized tables joined by keys. Repeated joins across these tables are slowing down the team's dashboards. What transformation should a data engineer apply to address this?
• A junior data engineer objects to denormalizing the object-catalogue reporting table, arguing that duplicating branch names and conservation-status text across millions of rows wastes storage and risks inconsistency if a branch is ever renamed. A senior engineer defends the design anyway. What is the strongest justification for choosing denormalization here despite that trade-off?
• A data engineer has a Lakehouse table of individual ticket scans and needs to produce, via a PySpark notebook, a daily total visitor count per branch for a dashboard. Which PySpark pattern correctly produces one row per branch per day with a summed count?
• An analyst needs a T-SQL query against a Warehouse table of inter-branch loan records that reports only the branches that have sent out more than 20 loans this year, along with each qualifying branch's total loan count. Which clause is required, in addition to GROUP BY, to filter on the aggregated count itself?
• A branch's handheld scanner occasionally uploads the same ticket-scan event twice after a connectivity drop, and a data engineer needs to remove these exact-duplicate rows from a Warehouse table using T-SQL rather than PySpark. Which T-SQL pattern correctly identifies and removes the duplicates while keeping exactly one copy of each?
• An oral-history ingestion job normally appends each interview transcript's metadata to a Lakehouse table the same day it's recorded, but a batch of transcripts from a rural branch arrives three weeks late because of a slow network connection, and some of those late records are corrections to transcripts that were already partially loaded earlier. Which approach correctly handles this late-arriving, partly-overlapping data?
• A dozen regional branches each export their collection-management catalogue on a different schedule and periodically add a new field — one branch recently started including a 'conditiongrade' column that no other branch has yet. A nightly load process ingesting these exports into a Lakehouse table needs to keep working without manual schema edits every time a branch changes its export. What should the ingestion approach do to handle this?
• The conservation team at a national museum and archives network wants to take a live stream of gallery humidity readings and fan it out to three destinations at once — a Lakehouse table for long-term archiving, a Data Activator for threshold alerts, and a Power BI report — applying only light filtering along the way, with no custom code. Which Fabric capability best fits this need?
• A data engineer is building an Eventstream that ingests turnstile tap events from every branch of a national museum network and needs to compute a rolling count of visitors per gallery every five minutes before landing the results in an Eventhouse. Which Eventstream capability should the engineer use to do this without leaving the no-code canvas?
• A PySpark notebook uses Spark structured streaming to compute a five-minute tumbling window count of oral-history recording-upload events from branch archives, but a network outage occasionally delays some upload-confirmation events by up to twenty minutes. Which technique should the engineer add to the streaming query so those delayed events can still be incorporated into the correct window without the state store growing indefinitely?
• Vibration-sensor readings from a museum's vault racking arrive at an Eventhouse table with an explicit event-time column, but network buffering means the rows are not always ingested in the same order the sensors generated them. An analyst writing a KQL query needs results grouped strictly by when each vibration actually occurred, not by when the row reached the Eventhouse. Which approach should the analyst take?
• An Eventstream aggregates turnstile-tap events into a visitor count for each museum branch, and the requirement is that every tap belongs to exactly one, non-overlapping ten-minute reporting bucket with no gaps and no double-counting across buckets. Which windowing function satisfies this requirement?
• A conservation dashboard needs to show a smoothed, frequently-updating view of a gallery's average lux exposure, refreshing every minute over a rolling five-minute span so curators can spot a creeping light-exposure trend without the choppiness of a strict five-minute tumbling average. Which windowing function fits this need?
• Security wants an immediate alert the instant three or more vault-vibration events occur within any three-minute span, evaluated continuously as each new reading arrives rather than only at fixed checkpoints. Which windowing function is designed for this kind of continuous, event-triggered evaluation?
• Analysts want to measure how long each visitor lingers inside a single gallery by grouping that visitor's motion-sensor pings together whenever consecutive pings are no more than two minutes apart, and starting a new group whenever a gap of more than two minutes occurs. Which windowing function models this directly?
• A building-management system emits temperature, humidity, and CO2 readings from a gallery's HVAC controller, and all three values for a given moment always carry the exact same timestamp because they are sampled together. An engineer wants to group and combine those three simultaneous readings into a single row per timestamp, without introducing any time-span logic. Which windowing function matches this need?
• A Spark structured streaming job aggregates loan-tracking scan events (an object moving between branches) into five-minute tumbling windows with a ten-minute watermark. A scan event with an event-time timestamp that is fifteen minutes older than the current watermark then arrives. What happens to that event?
• Donor oral-history upload-confirmation events sometimes arrive at an Eventstream out of the order in which they actually occurred, because branches upload over inconsistent network links. The downstream Eventhouse table needs to reflect correct chronological order for time-based analysis regardless of arrival order. What is the correct way to address this?
• Curators want to run ad hoc, exploratory queries over months of historical gallery-temperature telemetry — comparing trends across seasons, finding anomalies, and slicing by branch — using a query language built for fast time-series analytics at scale, rather than building a fixed routing topology. Which Fabric engine is purpose-built for this kind of analysis?
• An engineer needs to join a live stream of building-management HVAC events with a live stream of gallery-occupancy events, computing a rolling correlation between occupancy and temperature drift using custom stateful logic that goes beyond simple filtering or aggregation. Which tool should the engineer reach for?
• A Spark structured streaming job aggregates artifact-scan events into five-minute tumbling windows and writes results to a Lakehouse table using append output mode. The team notices that windows only appear in the output table well after they close, and never get corrected once written. Which streaming concept explains this behavior?
• A partner museum exposes its object-loan catalogue as a paginated JSON REST API rather than a database or file export, and the archives network wants to pull the full catalogue into a Lakehouse table every night, with authentication handled through a stored connection and pagination handled automatically, without anyone writing custom code. Which Fabric capability directly fits this need?
• A branch-transfer telemetry stream logs each time a crated artifact passes a loading-dock scanner during an inter-branch loan move. The reporting requirement is a strict, non-overlapping count of scans per fifteen-minute block for the whole day, where each scan counts toward one and only one block. Which windowing function is the correct fit?
• Facilities wants a congestion indicator for the main atrium that updates every two minutes but reflects the average visitor-flow rate over the trailing ten minutes, so a short burst at the entrance doesn't cause the indicator to spike and immediately drop back down. Which windowing function delivers this trailing, overlapping average?
• A conservation Data Activator needs to evaluate, the instant each new gallery-temperature reading arrives, whether the average temperature over the trailing four minutes has crossed a damage-risk threshold, so the alert can fire on the very reading that pushes it over rather than waiting for a scheduled check. Which windowing function is designed to re-evaluate on every incoming event like this?
• An archivist reviewing oral-history recording sessions wants each interview segment automatically grouped by continuous speech, where consecutive audio-activity events belong to the same segment as long as no silence gap longer than ninety seconds occurs, and a new silence gap longer than that starts a new segment. Which windowing function models this behavior?
• A gallery's environmental controller emits a single combined reading of temperature, humidity, and lux for a given instant, and all three values in that reading always share one identical timestamp because they come from the same sampling cycle. Reporting needs one output row per sampling instant that carries all three values together, with no time-span logic involved. Which windowing function is the direct match?
• A branch occasionally uploads inter-branch loan-scan events up to two hours late due to intermittent connectivity at remote sites, far longer than what a real-time Spark structured streaming watermark can reasonably tolerate without ballooning state. Which pattern should the team adopt to still capture these very late scans without keeping years of streaming state open?
• A facilities team wants building-management fire-suppression-system heartbeat events routed continuously to both an Eventhouse for historical KQL analysis and a Data Activator that pages on-call staff if a heartbeat is missing for more than two minutes, with light field renaming along the way and no team member available who can write Spark code. Which Fabric engine should this team choose?