Prepare and process data

DP-750 · 65 questions

• A city must land nightly permit CSV drops for batch analytics and also capture live counter submissions from Event Hubs. How should extraction be designed?
• A county archives cold historical 311 tickets for rare compliance reads while keeping active silver tickets ready for ACID merges. Which format choice fits?
• Transit partners require an open table format for a shared lake while city pipelines remain Azure Databricks–native. What should the data engineer evaluate?
• A water utility stores high-volume IoT readings and needs prune-friendly filters for leak investigations by date and facility. What modeling choice should they make?
• A housing authority must keep auditable history when apartment eligibility status changes over time. Which slowly changing dimension approach should they use?
• A 311 analytics team needs gold tables that support daily SLA dashboards. Which grain should they choose?
• Courts need to record how ordinance fee schedules change over time so prior assessments remain explainable. What should they implement?
• An open-data team maintains a large citations table with evolving filter patterns and wants to avoid managing many static partitions. What should they adopt?
• Finance analysts repeatedly filter a payments fact table by citizen-id and period. How should they improve file locality for those filters?
• A public-health clinic must fulfill targeted resident erasure requests on large Delta fact tables without rewriting entire files. What should they enable?
• A library wants curated catalog facts under Unity Catalog lifecycle control and raw vendor MARC landings left in an external path. How should tables be chosen?
• Emergency management must land raw CAD JSON then promote typed operational events for analytics. Which ingest and modeling flow fits?
• A permits office must orchestrate extracts from on-prem SQL into ADLS before Databricks refine. Which tool should they select for that hybrid orchestration?
• A city prefers managed ingest of a SaaS citizen-CRM export into Unity Catalog with less custom notebook glue. What should they choose?
• Analysts need to prototype permit transforms quickly, then promote stable logic into production pipelines. How should notebooks be used?
• Transit planning can wait for nightly ridership totals, but service alerts must refresh continuously. How should load methods be chosen?
• A 311 team must pull the prior day's closed tickets into bronze with managed batch ingest. Which approach fits?
• A water utility needs near-real-time valve telemetry landed into Unity Catalog. What should they configure?
• Parks engineers receive weekend CSV drops of reservation exports and need them in a Delta bronze table. What should they use?
• Emergency management must consume a continuous weather-alert topic into a live Delta sink from engineering notebooks. What pattern should they use?
• An open-data team wants to materialize a new parks table from a cleaned staging query in SQL. Which statement pattern should they use?
• A county must refresh a reference fee table from the latest validated extract while replacing prior contents cleanly. Which SQL approach fits?
• Finance receives new parking citation files and must load them into an existing Delta table with repeatable, idempotent semantics. What should they use?
• Housing needs silver case records to reflect inserts, updates, and deletes from the case-management OLTP system. Which ingest approach should they apply?
• Transit must continuously append AVL vehicle positions into a bronze Delta path. Which technology should they build on?
• City 311 needs live queue-depth metrics from event streams into Databricks. Which Azure messaging source should feed the pipeline?
• Public works wants an incremental load of streetlight outages managed as a pipeline with declarative table definitions. What should they create?
• A library volume receives new MARC file drops that should be ingested with schema inference as files arrive. What should they use inside declarative pipelines?
• A permits team re-runs fee file drops and must avoid double-counting when the same files land again. Which SQL ingest choice is best?
• County health runs Structured Streaming for clinic event feeds and must keep replays from duplicating ops metrics. What operational need should they recognize?
• Open-data permit status webhooks arrive through Azure Event Hubs as JSON. What should the silver path do next?
• School attendance CDC includes soft-deletes for withdrawn students. How should gold enrollment counts stay accurate?
• City hydrant inspections need an ongoing production ingest, not a one-time backfill. Which tool choice fits best?
• A 311 team must design cleansing rules for category codes. What should they do first?
• Courts engineers are typing fine amounts and hearing times for silver case tables. Which typing choice is appropriate?
• Housing CRM exports sometimes repeat the same household under new surrogate IDs. What should silver cleansing do?
• Public-health vaccination dates are sometimes missing in clinic extracts. How should the pipeline handle those nulls?
• Transit reliability gold needs mean delay by route after removing test bus IDs. Which transform sequence fits?
• Permits silver must enrich applications with parcel keys and combine multiple historical years. Which approach is correct?
• Finance wants parcel IDs that appear in both delinquency and lien datasets for targeted outreach. Which set operation fits?
• Open-data must publish a flat CSV citation extract for the portal. What transform is appropriate?
• Emergency management needs resource-type counts by incident as columns on a command dashboard. What should they do?
• Library nightly feeds include new acquisitions and updates to existing catalog items. Which load pattern should silver use?
• Parks captures reservation events for bronze and needs a current-state silver table. How should load styles differ by layer?
• After cash-office audits, county finance must land corrected fee rows in a quarantined adjustments table. Which pattern fits?
• Many 311 tickets arrive with null category codes but usable free-text descriptions. How should cleansing resolve those nulls?
• Water utility IoT readings arrive as strings and sometimes include non-numeric spikes. What should the transform do?
• Courts must publish a public calendar without sealed case IDs. Which set approach is correct?
• Before building permit SLA gold metrics, the city wants to understand turnaround-day outliers. What should engineers do?
• Housing pipelines treat householdid as a required key. How should nullability be enforced?
• Transit reference data must keep each vehicle on at most one active depot assignment. What quality check fits?
• Public-health clinic streams include body-temperature readings that can be impossible. What should quality controls do?
• Finance fee amount columns must remain DECIMAL through the load. Which quality practice is correct?
• A permits vendor CSV sometimes adds unexpected columns. What should bronze ingest do?
• Open-data citation feeds add optional fields over time. How should schema drift be managed?
• A city's 311 Lakeflow Spark Declarative Pipeline must drop tickets whose priority is not in {P1, P2, P3} into quarantine. What should the team configure?
• Emergency management wants high null GPS rates on CAD events to warn operators without failing the entire streaming pipeline. Which expectation behavior fits?
• A library must validate ISBN-like identifiers' format before merging rows into the catalog silver table. What quality step should they add?
• A county clinic queue dashboard breaks when wait-time metrics arrive as negative numbers. How should quality constraints treat those values?
• A water utility's IoT firmware adds new telemetry fields mid-season. How should the lakehouse team handle schema drift?
• Courts require both casenumber and filingdate to be non-null before a row may enter gold calendars. What should the quality gate enforce?
• A city streaming micro-batch must not land duplicate ticketid values inside the same window. How should uniqueness be enforced?
• Housing wants bad enum values quarantined differently from orphan parcel IDs that fail referential checks. How should quality be layered?
• Parks must document which expectations are fatal for public open-data drops versus those that only warn on internal silver. What principle applies?
• Transit sees repeated failed expectations for AVL vendor null spikes in the pipeline UI. What should engineers do next?