A full-length practice case study. Nine questions on regex validation, temporal clause selection, randomized encryption, window functions and grouping sets.
Twelve minutes for nine case-study questions is too fast, and one of your two mistakes was "did not see the correct one of two options". That's the signature of rushing.
Your overall exam pace is proven — 45 minutes for 50 questions. That means you have time to spend, and the case study is precisely where to spend it, because you cannot go back to it. Every other question on the exam is revisitable; these nine are not.
Target 20–22 minutes this time, deliberately. If you finish early, re-read the two options you were torn between rather than moving on. Speed is a resource you've already banked — the case study is where you cash it in.
Caldera Energy is a United Kingdom domestic energy supplier with 2.6 million customers. It operates a smart-meter network, a field engineering workforce of 1,400 staff organised into eight regions, and a customer call centre.
Caldera Energy runs its data platform on Azure SQL Database. It is modernising meter-data retention, opening a partner API for price-comparison websites, and deploying an AI assistant for field engineers.
Caldera Energy plans to replace its nightly meter-data purge, enforce meter serial validation at the database, formalise its deployment pipeline, launch the partner API, and deploy the engineer assistant.
MeteringDB contains Meter.Reading (3.8 billion rows, growing by approximately 220 million rows per month), Meter.Device, and dbo.Customer.
OpsDB contains dbo.Tariff, dbo.EngineerJob, and dbo.MaintenanceReport (400,000 reports averaging 2,500 words each).
A nightly job deletes meter readings older than 84 months in batches. It runs for seven hours, blocks the ingestion process, and generates very large volumes of transaction log.
Meter serial numbers are currently validated only in the mobile application used by engineers. Approximately 0.4% of rows in Meter.Device contain malformed serials that entered through a legacy bulk import.
A developer recently added a nonclustered index to Meter.Reading to support a new report. It was created with ON [PRIMARY].
The billing engine is a .NET service in Azure Container Apps.
The partner API will be consumed by third-party comparison websites. No server-side application code will be written for it.
The engineer assistant has not yet been built.
dbo.Customer contains BankAccountNumber and ContactPhone. The bank account number is used only for direct-debit file generation. It is never used in a WHERE clause, a JOIN, or a GROUP BY. The contact telephone number is not considered sensitive under Caldera's data classification and is not encrypted.
An Azure AI Foundry resource named foundry-caldera hosts text-embedding-3-small and gpt-4.1-mini deployments.
BankAccountNumber must never be readable in plaintext by database administrators.ContactPhone on screen. This is a usability measure and is not a compliance control.ReadingDate must read only the relevant months of data.GB-4471902-K). Invalid values must be rejected at insert.CREATE TABLE Meter.Reading (
ReadingID BIGINT IDENTITY(1,1) NOT NULL,
MeterID INT NOT NULL,
ReadingDate DATETIME2(0) NOT NULL,
ReadingValue DECIMAL(12,3) NOT NULL,
CONSTRAINT PK_Reading PRIMARY KEY CLUSTERED (ReadingID, ReadingDate)
);
-- added last week by a developer
CREATE NONCLUSTERED INDEX IX_Reading_Meter
ON Meter.Reading (MeterID) INCLUDE (ReadingValue)
ON [PRIMARY];CREATE TABLE Meter.Device (
MeterID INT PRIMARY KEY,
SerialCode VARCHAR(20) NOT NULL, -- e.g. 'GB-4471902-K'
RegionID INT NOT NULL,
InstalledOn DATE NOT NULL
);RANGE LEFT on ReadingDate with end-of-month boundaries; scheme mapping each partition to its own filegroup.RANGE RIGHT on ReadingDate with first-of-month boundaries; scheme mapping all partitions to [PRIMARY].RANGE RIGHT on ReadingID; scheme mapping all partitions to [PRIMARY].ReadingDate.B1T1T8
First-of-month boundaries require RANGE RIGHT so the first instant of a month lands with that month. Partitioning on the date column is what delivers elimination for T1. ALL TO ([PRIMARY]) satisfies T8 — and is the only legal option on Azure SQL Database anyway.
A fails twice: end-of-month boundaries on a datetime2 column send anything after midnight on the last day into the next month, and multiple filegroups are impossible here. C partitions on the wrong column, so date filters get no elimination. D doesn't change the cost of deleting hundreds of millions of rows.
Note the PK is already (ReadingID, ReadingDate) in the exhibit — someone anticipated the unique-index rule. That's one collision this study doesn't set for you; question 2 sets a different one.
RANGE RIGHT.IX_Reading_Meter was created ON [PRIMARY] rather than on the partition scheme, so it is not aligned.Meter.Reading are out of date.B1 — and the evidence is in the Existing environment tab, not the requirements.
SWITCH requires every index to be aligned — built on the same partition scheme with the same partitioning column. The developer's index sits on a single filegroup, so it isn't aligned, and one non-aligned index blocks the whole operation.
ON [PRIMARY]". Case studies hide the deciding fact in the environment narrative and expect you to connect it to a requirement three tabs away.A is a genuine SWITCH prerequisite and therefore a strong distractor — but the exhibit shows the PK already includes ReadingDate. B is correct configuration, not a fault.
CHECK (SerialCode LIKE '[A-Z][A-Z]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[A-Z]')CHECK (REGEXP_LIKE(SerialCode, '^[A-Z]{2}-[0-9]{7}-[A-Z]$'))AFTER INSERT trigger that calls REGEXP_LIKE and rolls back on failure.T2 — "must be rejected at insert" is the deciding phrase.
A CHECK constraint with REGEXP_LIKE is declarative, evaluated at write time, and expresses the pattern precisely. REGEXP_LIKE is a predicate, so a CHECK constraint is exactly where it belongs.
A is the interesting distractor — that LIKE pattern would work, and before SQL Server 2025 it was the only option. But it's unreadable, doesn't scale to any real pattern, and when regex is available it's the expected answer. If an exam offers both, take regex. C works but is heavier and fires after the write. D reports rather than rejects.
WITH NOCHECK. And REGEXP_LIKE needs compatibility level 170.
FOR SYSTEM_TIME AS OF · (b) FOR SYSTEM_TIME FROM … TO …FOR SYSTEM_TIME ALL · (b) FOR SYSTEM_TIME BETWEEN … AND …FOR SYSTEM_TIME AS OF · (b) FOR SYSTEM_TIME CONTAINED IN (…, …)T3 — two questions, two clauses.
(a) "as it stood on any past date" is a point-in-time snapshot → AS OF.
(b) "versions that both began and were replaced within a quarter" → CONTAINED IN, the only clause that requires the row's entire lifespan to fall inside the window.
FROM…TO and BETWEEN…AND both mean "alive at any point during the window" — they'd include a tariff that started in 2024 and is still current. Only CONTAINED IN means born and died inside it, which is why a currently-active row can never match it.D can't reconstruct a point-in-time state without you building that logic yourself.
default()B3 — plus a sentence in the Existing environment tab that most people skim past.
"Never readable in plaintext by database administrators" → Always Encrypted, because the keys live outside the database. Then: "it is never used in a WHERE clause, a JOIN, or a GROUP BY" tells you there is no query requirement — so you take the stronger type, randomized.
C decrypts transparently for anyone who authenticates. D is a display control and defeatable by inference.
partial(0,"XXX-XXX-",4), and do not grant UNMASK to the agent roledbo.CustomerDENY SELECT on ContactPhone to the agent roleB4 — and two phrases make this clean.
"Not a compliance control" lowers the bar to exactly where DDM belongs. And the environment tab states the phone number is not encrypted — which is what makes masking technically possible at all.
BankAccountNumber is encrypted and simply unreadable by anyone without the key; ContactPhone is unencrypted and masked for presentation. That's how a real design satisfies both kinds of requirement.
C filters rows, not columns. D hides it entirely — agents need the last four digits.
RegionIDRegionID, plus block predicates AFTER INSERT and AFTER UPDATESELECT granted per roleCHECK constraint comparing RegionID to SESSION_CONTEXTB2 — one requirement, three verbs.
"See only jobs in their own region" → filter predicate. "Not create a job for another region" → AFTER INSERT. "Not reassign an existing job to another region" → AFTER UPDATE.
C is bypassable via the base table and unmanageable at eight regions. D can't reference session state reliably and doesn't control reads.
VECTOR_SEARCHVECTOR_DISTANCET4B6
A: "tariff, its region, and the current unit price in a single request" is GraphQL relationship traversal. And "no server-side application code will be written" (Existing environment → Applications) eliminates E.
C: two numbers decide it. 2,500 words per report forces chunking — a single vector for a long report is a blurred average. 400,000 reports under one second forces approximate search with a DiskANN index.
D is the near-miss: right idea, wrong on both counts — no chunking and an exhaustive scan that won't hit one second.
Dropdown 1 — to satisfy T5 (compare each reading to the immediately preceding reading for the same meter):
LAG(ReadingValue) OVER (PARTITION BY MeterID ORDER BY ReadingDate) ·
B. LEAD(...) · C. FIRST_VALUE(...) · D. MIN(...) OVER ()Dropdown 2 — to satisfy T6 (totals by region, by region and tariff, and a grand total, in one query):
GROUP BY CUBE (RegionID, TariffID) ·
B. GROUP BY ROLLUP (RegionID, TariffID) ·
C. GROUP BY RegionID, TariffID · D. NTILE(3) OVER (...)Dropdown 3 — to satisfy the first half of T7 (validate before merging to main):
dotnet build · B. dotnet publish ·
C. sqlpackage /Action:Import · D. sqlpackage /Action:ExportT5T6T7 — each dropdown scores separately, so partial credit is available. Never leave one on its default.
1. "Immediately preceding" → LAG. LEAD looks forward, which inverts the sign and shifts every row by one period.
2. Three levels requested: (Region, Tariff), (Region), and (). That's exactly ROLLUP — n+1 = 3 groupings, dropping columns from the right. CUBE would give 2ⁿ = 4, adding an unrequested "by tariff across all regions" level. C gives only the detail level with no subtotals.
3. Validation happens before the merge, against no database — dotnet build compiles the project into a dacpac and surfaces unresolved references offline. Publishing the dacpac (SqlPackage /Action:Publish) is the deployment half of T7, which happens after.
| If you missed… | Because | The habit to fix it |
|---|---|---|
| Q2 | The deciding fact was in Existing environment, not Requirements | Read the environment tab for what recently changed — that's where faults are planted |
| Q5 | You reached for deterministic out of habit | "Never used in WHERE/JOIN/GROUP BY" removes the query requirement → take the stronger type |
| Q7 | You answered the first verb only | Underline every "and" and "or" in a requirement — B2 had three obligations |
| Q8 | You missed the "no server-side code" line | Constraints hide under Applications, not just under Requirements |
| Q9 part 2 | CUBE looked safe because it includes everything | Count the levels the requirement lists. ROLLUP = n+1, CUBE = 2ⁿ |
| Q3 | You picked the LIKE version | Both work; when regex is available it's the intended answer |