Beyond SELECT: Why T-SQL Still Shapes Modern Data Strategy

1
00:00:00,000 --> 00:00:03,080
Most organizations think Microsoft fabric made SQL optional.

2
00:00:03,080 --> 00:00:03,800
They are wrong.

3
00:00:03,800 --> 00:00:06,640
T-Sql didn't disappear, it just moved upstream

4
00:00:06,640 --> 00:00:08,080
into the part of your platform

5
00:00:08,080 --> 00:00:10,480
where cost-ordered findings and mysterious performance

6
00:00:10,480 --> 00:00:11,880
incidents are born.

7
00:00:11,880 --> 00:00:13,320
And the bill always arrives late.

8
00:00:13,320 --> 00:00:15,360
Every one of these failures shows up later

9
00:00:15,360 --> 00:00:17,480
as cloud-spent, audit findings,

10
00:00:17,480 --> 00:00:19,200
or teams losing trust in the data.

11
00:00:19,200 --> 00:00:21,280
In the next hour, you're going to use T-Sql

12
00:00:21,280 --> 00:00:23,280
the way enterprises actually need it.

13
00:00:23,280 --> 00:00:25,920
To enforce intent, truth, security boundaries,

14
00:00:25,920 --> 00:00:27,120
and predictable compute,

15
00:00:27,120 --> 00:00:30,160
so your modern stack doesn't drift into conditional chaos.

16
00:00:30,160 --> 00:00:31,800
The foundational misunderstanding,

17
00:00:31,800 --> 00:00:34,560
beyond select means beyond responsibility.

18
00:00:34,560 --> 00:00:36,800
Here's the foundational misunderstanding.

19
00:00:36,800 --> 00:00:39,920
People here, beyond select, and assume it means

20
00:00:39,920 --> 00:00:43,720
beyond SQL, or worse, beyond responsibility.

21
00:00:43,720 --> 00:00:45,280
They start treating the relational layer

22
00:00:45,280 --> 00:00:47,360
as an optional interface you can bypass

23
00:00:47,360 --> 00:00:50,840
with notebooks, pipelines, or whatever the lake gives us.

24
00:00:50,840 --> 00:00:52,800
And then they act surprised when the system

25
00:00:52,800 --> 00:00:54,760
behaves like a system with no contract.

26
00:00:54,760 --> 00:00:56,120
Because that's what you built.

27
00:00:56,120 --> 00:00:57,920
Beyond select doesn't mean fancy queries.

28
00:00:57,920 --> 00:00:59,360
It means the parts of T-Sql

29
00:00:59,360 --> 00:01:01,960
that define a relational system as a disciplined machine.

30
00:01:01,960 --> 00:01:05,320
Schema, constraints, transactions, permissions,

31
00:01:05,320 --> 00:01:07,760
stored procedures, and plan behavior.

32
00:01:07,760 --> 00:01:09,640
The things that make data deterministic

33
00:01:09,640 --> 00:01:11,080
instead of negotiable.

34
00:01:11,080 --> 00:01:13,440
Most teams use T-Sql like it's a reporting tool.

35
00:01:13,440 --> 00:01:15,200
It isn't. It is a contract language.

36
00:01:15,200 --> 00:01:16,880
A relational contract has two properties.

37
00:01:16,880 --> 00:01:18,800
One, predictable shape.

38
00:01:18,800 --> 00:01:21,200
Tables have declared columns and types,

39
00:01:21,200 --> 00:01:23,240
not whatever showed up today.

40
00:01:23,240 --> 00:01:24,720
Two, enforceable rules.

41
00:01:24,720 --> 00:01:26,840
The system can reject invalid states,

42
00:01:26,840 --> 00:01:29,840
not just display them with a warning in some downstream dashboard.

43
00:01:29,840 --> 00:01:31,120
If you remember nothing else, remember

44
00:01:31,120 --> 00:01:33,120
this modern platforms didn't remove the need

45
00:01:33,120 --> 00:01:34,320
for relational discipline.

46
00:01:34,320 --> 00:01:36,440
They amplified the consequences of not having it.

47
00:01:36,440 --> 00:01:38,920
Fabric, synapse, and the rest of the modern data stack

48
00:01:38,920 --> 00:01:40,080
are abstraction layers.

49
00:01:40,080 --> 00:01:42,040
They optimize for convenience and throughput.

50
00:01:42,040 --> 00:01:43,680
They do not optimize for intent.

51
00:01:43,680 --> 00:01:47,160
Over time, your intent erodes unless you encoded

52
00:01:47,160 --> 00:01:49,960
into the data layer in a way the system can't ignore.

53
00:01:49,960 --> 00:01:51,440
And that's where entropy shows up.

54
00:01:51,440 --> 00:01:53,760
Data entropy is the default trajectory.

55
00:01:53,760 --> 00:01:56,040
Increasing disorder, unpredictability,

56
00:01:56,040 --> 00:01:57,640
and disagreement about what's true.

57
00:01:57,640 --> 00:01:59,800
Research on data entropy describes the causes

58
00:01:59,800 --> 00:02:02,960
as inconsistent inputs, unstructured accumulation,

59
00:02:02,960 --> 00:02:05,600
customization, sprawl, and time-based decay.

60
00:02:05,600 --> 00:02:06,800
That's not academic poetry.

61
00:02:06,800 --> 00:02:08,240
That's a weekly incident pattern.

62
00:02:08,240 --> 00:02:09,600
Here's a concrete micro example

63
00:02:09,600 --> 00:02:12,400
because abstraction without pain is just a conference talk.

64
00:02:12,400 --> 00:02:15,680
A fabric warehouse ingest semi-curated data from a lake house.

65
00:02:15,680 --> 00:02:19,240
The lake side is schema on read, which sounds modern and flexible.

66
00:02:19,240 --> 00:02:22,000
But without an enforcement step, it becomes schema never,

67
00:02:22,000 --> 00:02:24,400
a column that used to be numeric arrives as a string,

68
00:02:24,400 --> 00:02:26,280
a date arrives in two formats,

69
00:02:26,280 --> 00:02:28,520
a key that used to be unique now has duplicates

70
00:02:28,520 --> 00:02:30,280
because a pipeline replayed a batch.

71
00:02:30,280 --> 00:02:31,040
Nothing fails.

72
00:02:31,040 --> 00:02:32,880
Everything loads.

73
00:02:32,880 --> 00:02:34,880
Then Power BI inherits the garbage.

74
00:02:34,880 --> 00:02:36,520
Someone fixes it with DAX.

75
00:02:36,520 --> 00:02:38,920
Another person fixes it in Power Query.

76
00:02:38,920 --> 00:02:41,200
A third person filters it in a semantic model.

77
00:02:41,200 --> 00:02:43,760
Now you have three truths and one data set.

78
00:02:43,760 --> 00:02:46,680
And the platform's only response is to keep executing.

79
00:02:46,680 --> 00:02:48,400
This is why the distinction matters.

80
00:02:48,400 --> 00:02:51,960
SQL engines do exactly what you ask, not what you mint.

81
00:02:51,960 --> 00:02:54,120
T-SQL is where you make meaning enforceable.

82
00:02:54,120 --> 00:02:56,640
With explicit schema constraints and validation gates,

83
00:02:56,640 --> 00:02:59,040
you can force the warehouse boundary to behave like a boundary.

84
00:02:59,040 --> 00:03:00,560
You can reject invalid rows.

85
00:03:00,560 --> 00:03:01,960
You can quarantine drift.

86
00:03:01,960 --> 00:03:04,600
You can stop bad data before it becomes a BI argument

87
00:03:04,600 --> 00:03:07,040
and an executive escalation.

88
00:03:07,040 --> 00:03:10,400
Modern platforms help by making it easy to connect everything

89
00:03:10,400 --> 00:03:11,840
to everything.

90
00:03:11,840 --> 00:03:14,160
Convenience tooling accelerates policy erosion.

91
00:03:14,160 --> 00:03:16,560
It reduces friction for doing the wrong thing at scale.

92
00:03:16,560 --> 00:03:17,960
Entropy loves low friction.

93
00:03:17,960 --> 00:03:20,240
And once entropy exists in your data layer,

94
00:03:20,240 --> 00:03:22,160
everything above it becomes probabilistic.

95
00:03:22,160 --> 00:03:24,280
Performance becomes probabilistic because the optimizer

96
00:03:24,280 --> 00:03:25,960
makes decisions based on statistics

97
00:03:25,960 --> 00:03:27,800
that no longer reflect reality.

98
00:03:27,800 --> 00:03:30,720
Security becomes probabilistic because teams lean on workspace

99
00:03:30,720 --> 00:03:33,440
rolls and assume that implies least privilege

100
00:03:33,440 --> 00:03:34,680
at the data layer.

101
00:03:34,680 --> 00:03:36,760
Truth becomes probabilistic because logic gets

102
00:03:36,760 --> 00:03:39,080
duplicated across pipelines, reports, and apps.

103
00:03:39,080 --> 00:03:42,400
So no, beyond select doesn't mean you graduated from SQL.

104
00:03:42,400 --> 00:03:44,920
It means you're now responsible for the relational contract

105
00:03:44,920 --> 00:03:47,440
you previously outsourced to a DBA.

106
00:03:47,440 --> 00:03:48,480
You no longer employ it.

107
00:03:48,480 --> 00:03:49,360
That sounds harsh.

108
00:03:49,360 --> 00:03:50,520
It is not optional.

109
00:03:50,520 --> 00:03:53,240
And the next step is understanding how SQL actually executes

110
00:03:53,240 --> 00:03:55,920
your intent because the code you wrote is not the order

111
00:03:55,920 --> 00:03:56,720
the engine runs.

112
00:03:56,720 --> 00:03:58,320
That's where the first trap lives.

113
00:03:58,320 --> 00:04:00,160
How SQL actually runs your query.

114
00:04:00,160 --> 00:04:01,880
Execution order is the trap.

115
00:04:01,880 --> 00:04:04,400
People write SQL in a comforting narrative order.

116
00:04:04,400 --> 00:04:06,640
Select what they want, from where they want it,

117
00:04:06,640 --> 00:04:08,800
and then they sprinkle in where, group by,

118
00:04:08,800 --> 00:04:10,440
order by, like seasoning.

119
00:04:10,440 --> 00:04:12,560
The engine does not share that sentimentality.

120
00:04:12,560 --> 00:04:15,480
SQL reads like English, but it executes like a compiler.

121
00:04:15,480 --> 00:04:17,520
And compilers don't care what you intended.

122
00:04:17,520 --> 00:04:19,440
They care what you actually specified,

123
00:04:19,440 --> 00:04:21,640
and in what phase of the pipeline that specification

124
00:04:21,640 --> 00:04:22,400
can be applied.

125
00:04:22,400 --> 00:04:24,360
Here's the execution order you need in your head

126
00:04:24,360 --> 00:04:27,120
because it explains 80% of why is this slow?

127
00:04:27,120 --> 00:04:29,400
And 80% of why did this return that?

128
00:04:29,400 --> 00:04:31,960
From happens first, always then where, then group by,

129
00:04:31,960 --> 00:04:33,480
then having, then select, including

130
00:04:33,480 --> 00:04:36,040
distinctive, then order by, then top coding, order,

131
00:04:36,040 --> 00:04:38,480
and execution order are not the same thing.

132
00:04:38,480 --> 00:04:40,880
That distinction matters because you are probably

133
00:04:40,880 --> 00:04:43,280
trying to optimize in the wrong place.

134
00:04:43,280 --> 00:04:44,320
The top makes it faster.

135
00:04:44,320 --> 00:04:45,960
Fallacy is the simplest example.

136
00:04:45,960 --> 00:04:49,120
People slap top 100 on a query, and expect it to stop early

137
00:04:49,120 --> 00:04:50,800
like a well-behaved loop.

138
00:04:50,800 --> 00:04:52,560
But top is a late stage operator.

139
00:04:52,560 --> 00:04:55,480
The engine still has to form the roasted that top is applied to.

140
00:04:55,480 --> 00:04:58,320
If that rouse it is expensive to build, top doesn't save you.

141
00:04:58,320 --> 00:05:00,920
It just hides the cost behind a smaller output.

142
00:05:00,920 --> 00:05:02,600
If you visualize the plan left to right,

143
00:05:02,600 --> 00:05:04,560
everything expensive happens before top.

144
00:05:04,560 --> 00:05:07,160
That's why filter early isn't a micro optimization.

145
00:05:07,160 --> 00:05:08,600
It's an architectural principle.

146
00:05:08,600 --> 00:05:10,880
Where shapes the workload before joins expanded,

147
00:05:10,880 --> 00:05:14,360
before aggregations group it, before sorts, reorder it.

148
00:05:14,360 --> 00:05:17,240
And the dirty truth is that joins are multipliers.

149
00:05:17,240 --> 00:05:19,240
A join doesn't just combine tables.

150
00:05:19,240 --> 00:05:22,960
It combines work, it amplifies I/O memory grants, and spill risk.

151
00:05:22,960 --> 00:05:24,480
And if you join first and filter later,

152
00:05:24,480 --> 00:05:26,720
you've built a bigger intermediate set than you ever needed,

153
00:05:26,720 --> 00:05:29,280
and then you pay to throw most of it away.

154
00:05:29,280 --> 00:05:31,280
This is also why people get burned by,

155
00:05:31,280 --> 00:05:33,280
but I filtered in the select.

156
00:05:33,280 --> 00:05:35,200
No, you projected columns in the select.

157
00:05:35,200 --> 00:05:36,400
That is not filtering rows.

158
00:05:36,400 --> 00:05:39,160
That's decoration after the expensive parts happened.

159
00:05:39,160 --> 00:05:41,520
The thing most people miss is that SQL has multiple kinds

160
00:05:41,520 --> 00:05:43,800
of filtering, and only some of them save you.

161
00:05:44,640 --> 00:05:46,840
Where filters rose before aggregation.

162
00:05:46,840 --> 00:05:49,440
That can reduce join inputs and reduce the number of rows

163
00:05:49,440 --> 00:05:51,600
that group or sort needs to touch.

164
00:05:51,600 --> 00:05:53,120
Having filters after aggregation,

165
00:05:53,120 --> 00:05:55,440
that means the engine still had to group first.

166
00:05:55,440 --> 00:05:57,320
Having can be correct and necessary,

167
00:05:57,320 --> 00:05:59,280
but it does not save upstream work.

168
00:05:59,280 --> 00:06:01,840
It discards results after the expensive grouping

169
00:06:01,840 --> 00:06:02,960
already happened.

170
00:06:02,960 --> 00:06:04,840
Distinct is another late stage filter.

171
00:06:04,840 --> 00:06:07,280
It removes duplicates after the engine produced them.

172
00:06:07,280 --> 00:06:09,240
If duplicates are produced by a join explosion,

173
00:06:09,240 --> 00:06:10,640
distinct isn't a fix.

174
00:06:10,640 --> 00:06:14,160
It's a tax order buy is usually the tax nobody budgets for.

175
00:06:14,160 --> 00:06:15,520
Sorting requires memory.

176
00:06:15,520 --> 00:06:17,880
When the engine can't sort in memory, it spills.

177
00:06:17,880 --> 00:06:20,200
And spills are not a little slower.

178
00:06:20,200 --> 00:06:21,400
There is structural admission.

179
00:06:21,400 --> 00:06:22,920
You asked for an expensive operation

180
00:06:22,920 --> 00:06:24,280
and the system had to page it out.

181
00:06:24,280 --> 00:06:27,160
Now, do not misunderstand this as right trick SQL.

182
00:06:27,160 --> 00:06:29,200
You don't optimize by gaming clause order.

183
00:06:29,200 --> 00:06:31,520
The optimizer can reorder and transform.

184
00:06:31,520 --> 00:06:33,800
You optimize by giving the engine predicates

185
00:06:33,800 --> 00:06:36,520
and join conditions that can actually be applied early.

186
00:06:36,520 --> 00:06:38,720
Sagaable filters, correct keys,

187
00:06:38,720 --> 00:06:41,360
and a schema that makes cardinality predictable.

188
00:06:41,360 --> 00:06:44,520
Execution order is the trap because it exposes a deeper point.

189
00:06:44,520 --> 00:06:46,000
Your query text is not the truth.

190
00:06:46,000 --> 00:06:47,000
The plan is the truth.

191
00:06:47,000 --> 00:06:48,720
The text is your request.

192
00:06:48,720 --> 00:06:51,080
The plan is the platform's interpretation of your request

193
00:06:51,080 --> 00:06:54,160
under cost constraints, statistics, and available indexes.

194
00:06:54,160 --> 00:06:56,840
And if you're trying to govern a fabric era platform,

195
00:06:56,840 --> 00:06:58,240
you don't govern with intentions.

196
00:06:58,240 --> 00:07:00,080
You govern with what the engine will actually do

197
00:07:00,080 --> 00:07:02,280
when someone runs this 2,000 times an hour.

198
00:07:02,280 --> 00:07:04,560
Once you understand execution order, you stop guessing.

199
00:07:04,560 --> 00:07:06,520
You start verifying.

200
00:07:06,520 --> 00:07:09,840
And that moves us to the only honest artifact in the whole process,

201
00:07:09,840 --> 00:07:11,560
the execution plan.

202
00:07:11,560 --> 00:07:13,560
Execution plans are a governance instrument,

203
00:07:13,560 --> 00:07:14,720
not a performance toy.

204
00:07:14,720 --> 00:07:17,200
Most teams treat execution plans like a performance

205
00:07:17,200 --> 00:07:20,040
troubleshooting tool you open when something's already on fire.

206
00:07:20,040 --> 00:07:22,280
That's backwards.

207
00:07:22,280 --> 00:07:24,880
An execution plan is a governance artifact.

208
00:07:24,880 --> 00:07:27,080
It's the only place where the platform admits

209
00:07:27,080 --> 00:07:28,760
what it's going to do to your data

210
00:07:28,760 --> 00:07:31,520
and your compute when this query runs under load.

211
00:07:31,520 --> 00:07:33,360
Not your intent, not your naming conventions,

212
00:07:33,360 --> 00:07:35,520
not your architectural diagram, the plan.

213
00:07:35,520 --> 00:07:38,280
And once you're in fabric or synapse a style consumption models,

214
00:07:38,280 --> 00:07:41,600
what it does, maps directly to cost, blast radius,

215
00:07:41,600 --> 00:07:43,280
and incident probability.

216
00:07:43,280 --> 00:07:45,000
A stable plan is predictable compute.

217
00:07:45,000 --> 00:07:48,440
An unstable plan is conditional chaos with a credit card attached.

218
00:07:48,440 --> 00:07:51,000
There are two categories of plan inspection that matter.

219
00:07:51,000 --> 00:07:54,760
The first is estimated plans, what the optimizer intends to do.

220
00:07:54,760 --> 00:07:57,800
The second is actual plans, what happened when the query ran

221
00:07:57,800 --> 00:08:00,680
with real row counts, real time, and real spills.

222
00:08:00,680 --> 00:08:03,080
The gap between the two is where failures live.

223
00:08:03,080 --> 00:08:06,280
If you remember nothing else here, estimated plans are a hypothesis.

224
00:08:06,280 --> 00:08:07,640
Actual plans are evidence.

225
00:08:07,640 --> 00:08:09,360
Now here's the uncomfortable truth.

226
00:08:09,360 --> 00:08:12,040
The optimizer isn't trying to make your query fast.

227
00:08:12,040 --> 00:08:14,240
It's trying to make it cheap according to a cost model

228
00:08:14,240 --> 00:08:15,640
built on statistics.

229
00:08:15,640 --> 00:08:17,440
If those statistics are still or skewed,

230
00:08:17,440 --> 00:08:20,080
the optimizer will confidently pick a plan that is wrong.

231
00:08:20,080 --> 00:08:22,240
Not maliciously, deterministically.

232
00:08:22,240 --> 00:08:25,520
So when you look at a plan, you're not hunting for one magic operator.

233
00:08:25,520 --> 00:08:27,240
You're reading a risk report.

234
00:08:27,240 --> 00:08:28,680
Start with the simplest red flag,

235
00:08:28,680 --> 00:08:30,560
scandros versus return rows.

236
00:08:30,560 --> 00:08:34,440
If you're returning 500 rows but scanning millions, you didn't write a query.

237
00:08:34,440 --> 00:08:35,640
You wrote an IO build.

238
00:08:35,640 --> 00:08:38,480
This is one of the most reliable indicators of wasted work.

239
00:08:38,480 --> 00:08:41,080
And it's highlighted in general optimization guidance.

240
00:08:41,080 --> 00:08:43,280
The bigger the gap, the more room you have to improve

241
00:08:43,280 --> 00:08:45,360
by filtering earlier, indexing properly

242
00:08:45,360 --> 00:08:47,800
or fixing non-saggable predicates.

243
00:08:47,800 --> 00:08:49,440
Next, full scans on large tables.

244
00:08:49,440 --> 00:08:50,840
A scan isn't always a bug.

245
00:08:50,840 --> 00:08:53,760
Sometimes a scan is cheaper than using a non-selective index.

246
00:08:53,760 --> 00:08:56,280
But in governance terms, a scan is a liability.

247
00:08:56,280 --> 00:08:57,760
It scales with table growth.

248
00:08:57,760 --> 00:08:59,960
If the table doubles, your cost profile doubles.

249
00:08:59,960 --> 00:09:01,440
That's not a performance problem.

250
00:09:01,440 --> 00:09:04,040
That's an architecture decision you fail to make explicit.

251
00:09:04,040 --> 00:09:05,600
Then look for expensive sorts.

252
00:09:05,600 --> 00:09:10,080
Order by and certain group by patterns for sorting and sorting forces memory.

253
00:09:10,080 --> 00:09:12,240
When memory isn't available, you get spills.

254
00:09:12,240 --> 00:09:16,040
Spills are the platform telling you it had to page out intermediate results to disk

255
00:09:16,040 --> 00:09:18,440
because your workload exceeded the memory grant.

256
00:09:18,440 --> 00:09:21,560
And yes, in distributed systems, this can become a multiplier.

257
00:09:21,560 --> 00:09:25,480
A spill plus concurrency is where it was fine yesterday comes from.

258
00:09:25,480 --> 00:09:27,520
Joins are the next category of plan risk

259
00:09:27,520 --> 00:09:29,640
because joins define plan shape.

260
00:09:29,640 --> 00:09:32,120
The join algorithm isn't a style preference.

261
00:09:32,120 --> 00:09:36,440
Anested loop that looks harmless at 1000 rows becomes catastrophic at 10 million.

262
00:09:36,440 --> 00:09:41,240
A hash join that behaves fine under steady distributions becomes a memory hog under skew.

263
00:09:41,240 --> 00:09:43,480
A merge join once sorted inputs.

264
00:09:43,480 --> 00:09:46,840
If it doesn't get them, you pay for sorts before you even join.

265
00:09:46,840 --> 00:09:49,080
The plan tells you which path you're betting on.

266
00:09:49,080 --> 00:09:51,800
Now why does the optimizer pick wrong in the real world?

267
00:09:51,800 --> 00:09:53,360
Three reasons show up constantly.

268
00:09:53,360 --> 00:09:56,960
Stale statistics, data skew, and parameter sensitivity.

269
00:09:56,960 --> 00:10:00,440
Stale stats make the optimizer hallucinate row counts.

270
00:10:00,440 --> 00:10:02,840
Skew makes average estimates useless.

271
00:10:02,840 --> 00:10:06,720
And parameter sensitivity means the first parameter value compiled becomes the lens

272
00:10:06,720 --> 00:10:10,400
through which every later execution is judged even when the data distribution changes.

273
00:10:10,400 --> 00:10:13,680
That's why execution plans are governance because they're cost forecasting.

274
00:10:13,680 --> 00:10:16,560
A plan you can't predict is a system you can't budget.

275
00:10:16,560 --> 00:10:19,120
It's also a system you can't reliably secure

276
00:10:19,120 --> 00:10:22,640
because performance incidents force temporary exceptions, bigger capacities,

277
00:10:22,640 --> 00:10:25,120
broader permissions, emergency bypasses.

278
00:10:25,120 --> 00:10:26,320
Those are entropy generators.

279
00:10:26,320 --> 00:10:28,680
So the practice isn't tune query sometimes.

280
00:10:28,680 --> 00:10:32,160
The practice is every enterprise critical query and procedure

281
00:10:32,160 --> 00:10:35,600
must have an acceptable plan shape, not just correct results.

282
00:10:35,600 --> 00:10:37,080
Plans are acceptance criteria.

283
00:10:37,080 --> 00:10:41,680
And once you adopt that mindset, you can see the first enterprise failure mode.

284
00:10:41,680 --> 00:10:45,040
Clearly in fabric entropy doesn't start in the warehouse.

285
00:10:45,040 --> 00:10:48,800
It starts upstream in the lake house inputs you chose not to constrain.

286
00:10:48,800 --> 00:10:52,640
Case one, fabric entropy when lake house inputs become warehouse liabilities.

287
00:10:52,640 --> 00:10:53,840
So let's make this real.

288
00:10:53,840 --> 00:10:56,000
A fabric warehouse pulls data from a lake house.

289
00:10:56,000 --> 00:11:00,480
The lake house is where semi curated data lens notebooks, spark jobs,

290
00:11:00,480 --> 00:11:05,680
ingestion pipelines, good enough CSVs and whatever the source system emitted this week.

291
00:11:05,680 --> 00:11:08,240
The warehouse is where the business expects truth.

292
00:11:08,240 --> 00:11:11,760
Repeatable reporting, stable semantic models and numbers that don't change

293
00:11:11,760 --> 00:11:13,600
because someone reprocessed a folder.

294
00:11:13,600 --> 00:11:16,240
Those two expectations collide.

295
00:11:16,240 --> 00:11:18,080
And the collision is predictable.

296
00:11:18,080 --> 00:11:19,680
The failure mode looks boring at first.

297
00:11:19,680 --> 00:11:21,200
The pipeline keeps running.

298
00:11:21,200 --> 00:11:23,440
Data keeps loading. Dashboards keep refreshing.

299
00:11:23,440 --> 00:11:24,560
Nobody gets paged.

300
00:11:24,560 --> 00:11:25,600
That's the trap.

301
00:11:25,600 --> 00:11:28,880
Because schema on read without enforcement turns into schema never.

302
00:11:28,880 --> 00:11:31,440
And then every downstream layer starts compensating.

303
00:11:31,440 --> 00:11:35,920
A column that used to be I&T arrives as in varcha because one source started sending nA.

304
00:11:35,920 --> 00:11:38,720
A data arrives as text because a connector changed.

305
00:11:38,720 --> 00:11:42,320
A key stops being unique because an incremental load replays a day.

306
00:11:42,320 --> 00:11:44,960
Nulls appear where nulls were previously impossible.

307
00:11:44,960 --> 00:11:47,840
The warehouse accepts it because you never told it not to.

308
00:11:47,840 --> 00:11:50,720
And then the bill arrives in the form of data quality work,

309
00:11:50,720 --> 00:11:53,680
which is always framed as analytics work because nobody wants to admit

310
00:11:53,680 --> 00:11:55,600
it's a system design omission.

311
00:11:55,600 --> 00:11:57,520
So power BI gets defensive logic.

312
00:11:57,520 --> 00:12:01,840
Coaless everywhere, passing functions everywhere, fixes in power query,

313
00:12:01,840 --> 00:12:04,480
and DAX measures that turn invalid states into,

314
00:12:04,480 --> 00:12:05,920
so the visual doesn't break.

315
00:12:05,920 --> 00:12:07,680
The result is not just slower refresh.

316
00:12:07,680 --> 00:12:09,200
The result is institutional drift.

317
00:12:09,200 --> 00:12:11,440
Now truth depends on which report you opened.

318
00:12:11,440 --> 00:12:12,480
This clicked for him.

319
00:12:12,480 --> 00:12:15,760
After watching teams argue over two numbers that were both correct

320
00:12:15,760 --> 00:12:20,240
because they were both compensating for different slices of the same upstream disorder.

321
00:12:20,240 --> 00:12:21,840
That's not a reporting problem.

322
00:12:21,840 --> 00:12:23,120
That's a contract problem.

323
00:12:23,120 --> 00:12:24,400
Here's the t-cycle intervention.

324
00:12:24,400 --> 00:12:27,520
First you treat the warehouse as a boundary, not a mirror.

325
00:12:27,520 --> 00:12:31,360
That means you land data into a staging shape that is explicit

326
00:12:31,360 --> 00:12:34,320
and then you promote into a curated shape that is enforceable.

327
00:12:34,320 --> 00:12:38,240
Explicit schema is the start, type columns, not will pass it later.

328
00:12:38,240 --> 00:12:40,400
And then constraints where they are appropriate,

329
00:12:40,400 --> 00:12:43,120
primary keys, when you actually mean uniqueness,

330
00:12:43,120 --> 00:12:44,880
check constraints for domain rules,

331
00:12:44,880 --> 00:12:46,960
and not null when the business rule is,

332
00:12:46,960 --> 00:12:48,400
this can't be missing.

333
00:12:48,400 --> 00:12:51,280
And yes, people will say constraints slow ingest.

334
00:12:51,280 --> 00:12:55,040
What they mean is constraints prevent loading garbage at full speed.

335
00:12:55,040 --> 00:12:56,320
Good, that's the point.

336
00:12:56,320 --> 00:12:58,880
Next you add a validation gate that is set based,

337
00:12:58,880 --> 00:13:00,480
not row by row heroics.

338
00:13:00,480 --> 00:13:01,680
In T-sql terms,

339
00:13:01,680 --> 00:13:03,840
a predictable pattern of queries that count

340
00:13:03,840 --> 00:13:06,960
and quarantine invalid rows before they enter the curated tables.

341
00:13:06,960 --> 00:13:10,080
If rows fail the gate, they go to a quarantine table with reason codes.

342
00:13:10,080 --> 00:13:12,080
The pipeline doesn't pretend the data is clean.

343
00:13:12,080 --> 00:13:13,680
It declares the failure explicitly.

344
00:13:13,680 --> 00:13:16,720
This is where execution plans and constraints do governance work,

345
00:13:16,720 --> 00:13:17,920
not dashboards.

346
00:13:17,920 --> 00:13:21,120
Because a dashboard can only describe chaos after it's already shipped.

347
00:13:21,120 --> 00:13:23,120
A constraint stops chaos at the boundary.

348
00:13:23,120 --> 00:13:26,320
A validation query makes the chaos measurable and actionable.

349
00:13:26,320 --> 00:13:28,080
And the payoff is larger than performance.

350
00:13:28,080 --> 00:13:30,080
It's cognitive load.

351
00:13:30,080 --> 00:13:31,760
When the warehouse enforces shape,

352
00:13:31,760 --> 00:13:34,480
the semantic model stops being a data repair shop.

353
00:13:34,480 --> 00:13:36,080
You remove defensive transforms,

354
00:13:36,080 --> 00:13:37,760
refresh becomes simpler.

355
00:13:37,760 --> 00:13:40,480
And the business stops building parallel definitions index

356
00:13:40,480 --> 00:13:41,840
just to make it work.

357
00:13:41,840 --> 00:13:44,720
Now do not take that as permission to slap constraints everywhere.

358
00:13:44,720 --> 00:13:45,600
This is design.

359
00:13:45,600 --> 00:13:47,760
The goal is to encode the rules that represent

360
00:13:47,760 --> 00:13:50,000
business invariants and audit expectations,

361
00:13:50,000 --> 00:13:51,440
not personal preferences.

362
00:13:51,440 --> 00:13:53,280
The other quiet win is operational.

363
00:13:53,280 --> 00:13:55,600
When you enforce schema and validate early,

364
00:13:55,600 --> 00:13:57,360
incidents become diagnosable.

365
00:13:57,360 --> 00:13:59,360
A pipeline fails with a known reason,

366
00:13:59,360 --> 00:14:01,840
not a mysterious downstream visual break.

367
00:14:01,840 --> 00:14:03,360
Data contracts become visible,

368
00:14:03,360 --> 00:14:05,840
which source produced invalid values when it started

369
00:14:05,840 --> 00:14:07,520
and how much it impacted the data sets.

370
00:14:07,520 --> 00:14:09,040
So you don't just reduce defects,

371
00:14:09,040 --> 00:14:12,160
you reduce ambiguity and ambiguity is what burns teams out.

372
00:14:12,160 --> 00:14:14,640
Now here's the deeper connection back to the theme.

373
00:14:14,640 --> 00:14:16,400
Fabric makes it easy to move data.

374
00:14:16,400 --> 00:14:18,320
It does not make it safe to move meaning.

375
00:14:18,320 --> 00:14:20,640
If you don't enforce meaning at the warehouse boundary,

376
00:14:20,640 --> 00:14:22,960
meaning leaks upward into every consumer tool,

377
00:14:22,960 --> 00:14:25,600
power BI, power apps, power automate,

378
00:14:25,600 --> 00:14:28,560
ad hoc SQL, quick fixes in notebooks.

379
00:14:28,560 --> 00:14:30,400
That's entropy acceleration.

380
00:14:30,400 --> 00:14:33,120
So case one isn't about being strict for the sake of being strict,

381
00:14:33,120 --> 00:14:34,560
it's about restoring determinism.

382
00:14:34,560 --> 00:14:36,320
The warehouse accepts only valid states,

383
00:14:36,320 --> 00:14:37,840
rejects invalid states,

384
00:14:37,840 --> 00:14:39,600
and keeps the blast radius contained.

385
00:14:39,600 --> 00:14:42,000
And once you do that, something else becomes visible.

386
00:14:42,000 --> 00:14:43,440
When data is clean and shaped,

387
00:14:43,440 --> 00:14:45,280
performance problems stop being random.

388
00:14:45,280 --> 00:14:46,960
Here they become planned problems.

389
00:14:46,960 --> 00:14:48,880
And plan problems in cloud billing models

390
00:14:48,880 --> 00:14:51,040
are usually hiding inside plan instability.

391
00:14:51,040 --> 00:14:53,040
Case two, parameter sniffing,

392
00:14:53,040 --> 00:14:54,960
your cloud builds favorite feature.

393
00:14:54,960 --> 00:14:56,800
Now we hit the failure mode that looks like

394
00:14:56,800 --> 00:14:58,480
the platform is flaky, but it isn't.

395
00:14:58,480 --> 00:15:00,400
It's deterministic and it's expensive.

396
00:15:00,400 --> 00:15:03,200
Parameter sniffing is your cloud builds favorite feature

397
00:15:03,200 --> 00:15:04,720
because it converts stable workloads

398
00:15:04,720 --> 00:15:07,920
into cost spikes without changing a single line of application code.

399
00:15:07,920 --> 00:15:12,160
This isn't a SQL quirk, it's a cost predictability failure.

400
00:15:12,160 --> 00:15:13,840
Here's the context that produces it.

401
00:15:13,840 --> 00:15:15,600
You have a high concurrency workload.

402
00:15:15,600 --> 00:15:18,240
Maybe it's a data serving layer for power BI queries,

403
00:15:18,240 --> 00:15:20,160
maybe it's an API feeding power apps,

404
00:15:20,160 --> 00:15:21,760
maybe it's an orchestration pipeline

405
00:15:21,760 --> 00:15:23,760
that calls stored procedures repeatedly.

406
00:15:23,760 --> 00:15:25,360
The pattern is always the same.

407
00:15:25,360 --> 00:15:27,280
One stored procedure, one predictable shape,

408
00:15:27,280 --> 00:15:29,280
and lots of executions per hour.

409
00:15:29,280 --> 00:15:31,280
And then the incident tickets arrive.

410
00:15:31,280 --> 00:15:32,720
Random slowdowns.

411
00:15:32,720 --> 00:15:34,000
It was fine yesterday.

412
00:15:34,000 --> 00:15:36,560
One run takes two seconds, the next takes two minutes.

413
00:15:36,560 --> 00:15:38,800
Compute spikes, capacity alarms,

414
00:15:38,800 --> 00:15:40,960
people blame fabric, people blame the gateway,

415
00:15:40,960 --> 00:15:43,680
people blame the network, nobody blames the plan, they should.

416
00:15:44,480 --> 00:15:46,080
Because what's happening is simple.

417
00:15:46,080 --> 00:15:49,040
SQL server style engines compile an execution plan

418
00:15:49,040 --> 00:15:51,920
using the parameter values they see at compile time,

419
00:15:51,920 --> 00:15:55,040
then cache and reuse that plan for later executions.

420
00:15:55,040 --> 00:15:57,440
If the first parameter values represent a selective case,

421
00:15:57,440 --> 00:16:00,240
the plan might favor index seeks and nested loops.

422
00:16:00,240 --> 00:16:02,240
If the first values represent a broad case,

423
00:16:02,240 --> 00:16:04,720
the plan might favor scans and hash joins.

424
00:16:04,720 --> 00:16:06,240
Both plans can be correct.

425
00:16:06,240 --> 00:16:09,360
Only one is survivable for the next parameter distribution.

426
00:16:09,360 --> 00:16:12,320
So the same procedure gets executed for customer IDLE 12

427
00:16:12,320 --> 00:16:14,880
and returns 50 rows and then for customer IDLE 5

428
00:16:14,880 --> 00:16:16,480
and returns 5 million rows.

429
00:16:16,480 --> 00:16:20,080
The plan chosen for 50 rows gets reused for the 5 million row case

430
00:16:20,080 --> 00:16:22,080
and the system doesn't fail politely.

431
00:16:22,080 --> 00:16:24,880
It burns CPU and memory until your capacity looks like

432
00:16:24,880 --> 00:16:26,800
a denial of service attack you're paying for,

433
00:16:26,800 --> 00:16:29,520
that is parameter sniffing in enterprise reality.

434
00:16:29,520 --> 00:16:31,280
The reason this matters in fabric error systems

435
00:16:31,280 --> 00:16:33,200
is that cost is no longer abstract.

436
00:16:33,200 --> 00:16:34,560
You aren't tuning for elegance,

437
00:16:34,560 --> 00:16:36,320
you're tuning for predictable spend

438
00:16:36,320 --> 00:16:38,000
and predictable incident surface.

439
00:16:38,000 --> 00:16:39,200
So what do you do with T-School?

440
00:16:39,200 --> 00:16:41,440
You have a few levers and none of them are magical.

441
00:16:41,440 --> 00:16:43,840
They are trade-offs you choose deliberately.

442
00:16:43,840 --> 00:16:47,200
One, you can force recompilation for this specific statement.

443
00:16:47,200 --> 00:16:49,680
Option, recompile, tells the engine,

444
00:16:49,680 --> 00:16:51,200
don't reuse a cached plan,

445
00:16:51,200 --> 00:16:54,000
compile a plan for the current parameter values.

446
00:16:54,000 --> 00:16:56,560
That can stabilize runtime per execution context

447
00:16:56,560 --> 00:16:59,280
but it shifts cost into compilation overhead.

448
00:16:59,280 --> 00:17:01,200
And if the procedure runs constantly,

449
00:17:01,200 --> 00:17:03,200
you can turn CPU into your new bottleneck.

450
00:17:03,200 --> 00:17:05,760
It fixes the symptom, it can create a new one.

451
00:17:05,760 --> 00:17:08,480
Two, you can change how the optimizer sees the parameter

452
00:17:08,480 --> 00:17:10,080
by using local variables.

453
00:17:10,080 --> 00:17:12,880
That tends to produce a more generalized plan

454
00:17:12,880 --> 00:17:14,480
because the optimizer can't assume

455
00:17:14,480 --> 00:17:17,120
a specific value distribution at compile time.

456
00:17:17,120 --> 00:17:19,680
The upside is fewer extreme plan regressions.

457
00:17:19,680 --> 00:17:22,560
The downside is you might lose the hyper optimise plan

458
00:17:22,560 --> 00:17:24,400
that was perfect for the common case.

459
00:17:24,400 --> 00:17:27,200
Your trading peak performance for consistency.

460
00:17:27,200 --> 00:17:31,280
Three, you can control parameterization more deliberately.

461
00:17:31,280 --> 00:17:33,680
Split procedures, branch logic for known

462
00:17:33,680 --> 00:17:35,760
small versus large distributions

463
00:17:35,760 --> 00:17:37,520
or use query patterns that avoid

464
00:17:37,520 --> 00:17:40,560
wildly different card analyses on the same code path.

465
00:17:40,560 --> 00:17:42,400
This is where people stop treating,

466
00:17:42,400 --> 00:17:44,240
stored procedures as a convenience wrapper

467
00:17:44,240 --> 00:17:46,160
and start treating them as an operating model.

468
00:17:46,160 --> 00:17:48,640
And yes, SQL Server 2022 introduces

469
00:17:48,640 --> 00:17:51,680
parameter-sensitive plan optimization in some contexts

470
00:17:51,680 --> 00:17:53,440
where multiple plans can be cached

471
00:17:53,440 --> 00:17:55,440
for a single parameterized query.

472
00:17:55,440 --> 00:17:57,440
That's the engine admitting the problem exists

473
00:17:57,440 --> 00:17:59,280
but you still don't outsource governance

474
00:17:59,280 --> 00:18:00,400
to a feature checkbox.

475
00:18:00,400 --> 00:18:02,720
You validate plan behavior under your workload.

476
00:18:02,720 --> 00:18:04,800
Here's the practical way to keep executives

477
00:18:04,800 --> 00:18:06,240
and architects aligned.

478
00:18:06,240 --> 00:18:09,040
Don't describe this as sometimes SQL gets confused.

479
00:18:09,040 --> 00:18:10,080
Describe it as,

480
00:18:10,080 --> 00:18:11,520
our runtime is probabilistic

481
00:18:11,520 --> 00:18:14,640
because the plan cache is using the wrong historical sample.

482
00:18:14,640 --> 00:18:16,160
Then you attach the real risk.

483
00:18:16,160 --> 00:18:18,640
Probabilistic runtime becomes probabilistic cost

484
00:18:18,640 --> 00:18:20,880
and probabilistic cost becomes emergency changes

485
00:18:20,880 --> 00:18:22,960
and emergency changes become security dead.

486
00:18:22,960 --> 00:18:23,760
That's the loop.

487
00:18:23,760 --> 00:18:26,320
The wind condition for this case isn't faster queries.

488
00:18:26,320 --> 00:18:27,440
It's stable queries.

489
00:18:27,440 --> 00:18:30,000
A stable plan shape means stable memory grants.

490
00:18:30,000 --> 00:18:32,640
Stable I/O, stable concurrency behaviour.

491
00:18:32,640 --> 00:18:34,400
Fewer spills, fewer lock escalations,

492
00:18:34,400 --> 00:18:36,480
fewer 2AM escalations where somebody suggests

493
00:18:36,480 --> 00:18:39,920
just scale up capacity as if that's a fix and not an admission.

494
00:18:39,920 --> 00:18:42,480
Once you enforce stability, the incident pattern changes.

495
00:18:42,480 --> 00:18:44,000
Instead of random slowness,

496
00:18:44,000 --> 00:18:46,080
you get deterministic bottlenecks you can address

497
00:18:46,080 --> 00:18:48,640
with indexing statistics or structural choices.

498
00:18:48,640 --> 00:18:49,680
And that's the point.

499
00:18:49,680 --> 00:18:52,240
T-Sql isn't here to make your query clever.

500
00:18:52,240 --> 00:18:55,040
It's here to make your system predictable under load.

501
00:18:55,040 --> 00:18:56,640
Now once performance is probabilistic,

502
00:18:56,640 --> 00:18:59,200
the next thing that becomes probabilistic is security

503
00:18:59,200 --> 00:19:01,280
because teams start granting access broadly

504
00:19:01,280 --> 00:19:03,440
to avoid breaking critical workloads.

505
00:19:03,440 --> 00:19:05,440
That's where the next failure mode lives.

506
00:19:05,440 --> 00:19:07,040
Security, dead in fabric.

507
00:19:07,040 --> 00:19:07,840
Always on.

508
00:19:07,840 --> 00:19:09,760
Security doesn't mean always correct.

509
00:19:09,760 --> 00:19:11,280
Now we need to talk about security

510
00:19:11,280 --> 00:19:14,080
because performance incidents don't just burn compute.

511
00:19:14,080 --> 00:19:16,880
They create panic and panic creates exceptions.

512
00:19:16,880 --> 00:19:18,880
Exceptions are entropy generators.

513
00:19:18,880 --> 00:19:21,760
Security, dead is how those exceptions become permanent.

514
00:19:21,760 --> 00:19:23,920
Most teams here, fabric security is always on

515
00:19:23,920 --> 00:19:26,400
and translate that into security is handled.

516
00:19:26,400 --> 00:19:29,120
That translation is how audits get interesting.

517
00:19:29,120 --> 00:19:31,040
The research is clear on the baseline.

518
00:19:31,040 --> 00:19:34,480
Fabric SQL databases use Microsoft Android ID authentication,

519
00:19:34,480 --> 00:19:36,160
not SQL authentication.

520
00:19:36,160 --> 00:19:38,880
Data is encrypted in transit with TLS 1.2

521
00:19:38,880 --> 00:19:41,440
and encrypted at rest with Microsoft managed keys.

522
00:19:41,440 --> 00:19:43,600
Those defaults matter because they erase

523
00:19:43,600 --> 00:19:45,840
entire categories of legacy failure.

524
00:19:45,840 --> 00:19:49,680
Weak SQL logins, forgotten certificates, stale patching.

525
00:19:49,680 --> 00:19:51,440
You do not have to earn those wins.

526
00:19:51,440 --> 00:19:52,960
The platform gives them to you,

527
00:19:52,960 --> 00:19:55,200
but that's not the security contract you actually need.

528
00:19:55,200 --> 00:19:56,880
Always on security is posture.

529
00:19:56,880 --> 00:19:58,320
It is not intent.

530
00:19:58,320 --> 00:20:01,360
Intent is who can do what? To which data, under which conditions,

531
00:20:01,360 --> 00:20:02,800
and how you prove it later?

532
00:20:02,800 --> 00:20:04,240
This is the uncomfortable truth.

533
00:20:04,240 --> 00:20:05,760
This isn't a fabric floor.

534
00:20:05,760 --> 00:20:07,680
It's an abstraction boundary problem.

535
00:20:07,680 --> 00:20:09,760
Fabric has a dual control model on one side.

536
00:20:09,760 --> 00:20:11,840
You have workspace roles and item permissions

537
00:20:11,840 --> 00:20:13,760
because fabric is a SAS platform

538
00:20:13,760 --> 00:20:15,840
and it needs collaboration primitives.

539
00:20:15,840 --> 00:20:18,000
On the other side, you have native T-School permissions

540
00:20:18,000 --> 00:20:19,920
and database roles because the data engine

541
00:20:19,920 --> 00:20:22,560
still speaks in schemers, objects and grants.

542
00:20:22,560 --> 00:20:23,920
And the failure pattern is simple.

543
00:20:23,920 --> 00:20:25,680
Teams stop at workspace roles.

544
00:20:25,680 --> 00:20:28,720
They treat contributor or member as if it translates

545
00:20:28,720 --> 00:20:30,720
into least privilege at the data layer.

546
00:20:30,720 --> 00:20:31,680
It doesn't.

547
00:20:31,680 --> 00:20:34,560
Workspace roles decide who can access or manage the item.

548
00:20:34,560 --> 00:20:36,640
They don't define a data contract inside the item.

549
00:20:36,640 --> 00:20:38,720
They do not encode which schemers are rightable,

550
00:20:38,720 --> 00:20:40,240
which tables are queryable,

551
00:20:40,240 --> 00:20:42,160
or which stored procedures are callable.

552
00:20:42,160 --> 00:20:44,640
Over time, the workspace becomes a convenient zone

553
00:20:44,640 --> 00:20:46,800
and the database becomes an open floor plan.

554
00:20:46,800 --> 00:20:48,960
That's how security becomes probabilistic.

555
00:20:48,960 --> 00:20:51,360
Because when a system has no internal boundaries,

556
00:20:51,360 --> 00:20:54,240
every new integration inherits broad access by default.

557
00:20:54,240 --> 00:20:57,120
A pipeline identity needs to load data so it gets elevated.

558
00:20:57,120 --> 00:20:59,200
A Power BI semantic model needs to refresh

559
00:20:59,200 --> 00:21:00,080
so it gets elevated.

560
00:21:00,080 --> 00:21:02,720
A Power app needs a view so it gets elevated.

561
00:21:02,720 --> 00:21:04,320
And nobody walks the permissions back

562
00:21:04,320 --> 00:21:06,000
because the system still works.

563
00:21:06,000 --> 00:21:08,640
And the next person doesn't want to be the one who breaks it.

564
00:21:08,640 --> 00:21:09,760
That is security dead.

565
00:21:09,760 --> 00:21:12,000
Permission drift, you can't justify anymore.

566
00:21:12,000 --> 00:21:14,000
Now add two platform realities from the research

567
00:21:14,000 --> 00:21:16,000
that you need to say out loud.

568
00:21:16,000 --> 00:21:17,760
First, fabric security model is evolving

569
00:21:17,760 --> 00:21:20,160
but some enterprise features are not fully there yet.

570
00:21:20,160 --> 00:21:22,240
Specifically, auditing and customer managed keys

571
00:21:22,240 --> 00:21:23,680
are not currently supported.

572
00:21:23,680 --> 00:21:25,440
If your compliance program expects database

573
00:21:25,440 --> 00:21:28,240
native auditing trails or customer managed encryption keys,

574
00:21:28,240 --> 00:21:29,760
you cannot hand wave that away.

575
00:21:29,760 --> 00:21:31,600
You either design compensating controls

576
00:21:31,600 --> 00:21:33,520
or you accept that your fabric SQL estate

577
00:21:33,520 --> 00:21:36,320
has a compliance boundary you must manage explicitly.

578
00:21:36,320 --> 00:21:38,880
Second, fabric integrates with Microsoft purview

579
00:21:38,880 --> 00:21:41,640
for labels and protection policies in preview context.

580
00:21:41,640 --> 00:21:42,920
That's powerful for governance,

581
00:21:42,920 --> 00:21:45,720
but it does not replace database layer intent.

582
00:21:45,720 --> 00:21:48,520
Labels classify policies constrained at a higher level.

583
00:21:48,520 --> 00:21:50,720
They do not magically design your schema boundaries

584
00:21:50,720 --> 00:21:52,160
or your execution rights.

585
00:21:52,160 --> 00:21:53,480
So where does T-SQL fit?

586
00:21:53,480 --> 00:21:56,120
T-SQL is where you create survivable least privilege.

587
00:21:56,120 --> 00:21:59,520
Not by training people by making the platform refuse unsafe actions.

588
00:21:59,520 --> 00:22:01,440
In architectural terms, you use the database

589
00:22:01,440 --> 00:22:03,360
to turn allowed into possible.

590
00:22:03,360 --> 00:22:06,200
You define schemas that separate curated from staging.

591
00:22:06,200 --> 00:22:08,880
You grant read access to what consumers need

592
00:22:08,880 --> 00:22:11,880
and write access only to what pipeline identities own.

593
00:22:11,880 --> 00:22:14,120
You stop treating broad workspace membership

594
00:22:14,120 --> 00:22:15,920
as a data access strategy.

595
00:22:15,920 --> 00:22:18,640
Because the audit question is never did you encrypt data.

596
00:22:18,640 --> 00:22:22,480
The audit question is who had access to sensitive tables and why

597
00:22:22,480 --> 00:22:23,920
and how do you prove it.

598
00:22:23,920 --> 00:22:25,640
Fabric will give you a secure baseline.

599
00:22:25,640 --> 00:22:27,400
It will not define your intent for you.

600
00:22:27,400 --> 00:22:30,080
And if you don't define it, the system defines it for you

601
00:22:30,080 --> 00:22:32,640
through accumulated shortcuts, broad grants,

602
00:22:32,640 --> 00:22:33,880
and forgotten exceptions.

603
00:22:33,880 --> 00:22:36,520
That's the part that passes audits or fails them.

604
00:22:36,520 --> 00:22:39,520
Case three, least privilege that survives reality.

605
00:22:39,520 --> 00:22:41,000
Case three is where security theory

606
00:22:41,000 --> 00:22:43,720
meets the thing that actually breaks it, integration.

607
00:22:43,720 --> 00:22:47,000
Not the attacker, not the zero day, the integration.

608
00:22:47,000 --> 00:22:49,240
In the Microsoft ecosystem, the most common path

609
00:22:49,240 --> 00:22:51,040
into your data layer isn't a hacker.

610
00:22:51,040 --> 00:22:54,280
It's a well-meaning product team wiring power platform

611
00:22:54,280 --> 00:22:57,120
into analytics because someone asked for real time

612
00:22:57,120 --> 00:22:59,520
and the service principle needed access yesterday.

613
00:22:59,520 --> 00:23:00,480
So the pattern shows up.

614
00:23:00,480 --> 00:23:03,880
Dataverse, power apps, power automate, maybe a custom API,

615
00:23:03,880 --> 00:23:05,600
all touching SQL services somewhere.

616
00:23:05,600 --> 00:23:07,520
Sometimes it's a direct SQL endpoint.

617
00:23:07,520 --> 00:23:09,560
Sometimes it's a warehouse feeding power BI,

618
00:23:09,560 --> 00:23:11,320
which then feeds embedded experiences.

619
00:23:11,320 --> 00:23:12,160
The details change.

620
00:23:12,160 --> 00:23:13,640
The failure mode does not.

621
00:23:13,640 --> 00:23:16,640
A service principle gets broad access because it's easier.

622
00:23:16,640 --> 00:23:19,840
A workspace role gets assigned because we just need it to work.

623
00:23:19,840 --> 00:23:22,840
A pipeline identity gets elevated because it kept failing

624
00:23:22,840 --> 00:23:24,920
and then nobody comes back to remove the permission

625
00:23:24,920 --> 00:23:27,280
because nobody wants to be the person who breaks production.

626
00:23:27,280 --> 00:23:28,520
That's not a people problem.

627
00:23:28,520 --> 00:23:30,120
That's a design omission.

628
00:23:30,120 --> 00:23:32,840
Least privilege only works when it survives reality

629
00:23:32,840 --> 00:23:34,440
and reality is messy.

630
00:23:34,440 --> 00:23:37,760
People leave, ownership shifts, requirements grow

631
00:23:37,760 --> 00:23:39,560
and every exception becomes permanent

632
00:23:39,560 --> 00:23:41,960
unless the system makes permanence painful.

633
00:23:41,960 --> 00:23:44,400
Here's the architectural move that changes the trajectory.

634
00:23:44,400 --> 00:23:46,440
Treat least privilege as schema design,

635
00:23:46,440 --> 00:23:47,720
not as a policy document.

636
00:23:47,720 --> 00:23:51,120
Start by separating the database into deliberate zones.

637
00:23:51,120 --> 00:23:54,120
At minimum, you want a staging zone, a curated zone

638
00:23:54,120 --> 00:23:55,560
and an access zone.

639
00:23:55,560 --> 00:23:58,760
Those zones don't have to map to fabrics, workspace concepts.

640
00:23:58,760 --> 00:24:00,160
They have to map to intent.

641
00:24:00,160 --> 00:24:01,920
Staging is where ingestion lands.

642
00:24:01,920 --> 00:24:03,160
It is allowed to be ugly.

643
00:24:03,160 --> 00:24:04,960
It is not allowed to be widely readable.

644
00:24:04,960 --> 00:24:06,320
Curated is where truth lives.

645
00:24:06,320 --> 00:24:07,200
It is constrained.

646
00:24:07,200 --> 00:24:08,160
It is stable.

647
00:24:08,160 --> 00:24:09,640
It is what you defend.

648
00:24:09,640 --> 00:24:12,520
Access is how consumers see curated data.

649
00:24:12,520 --> 00:24:15,840
Views, stored procedures and explicitly granted interfaces.

650
00:24:15,840 --> 00:24:18,760
This is where you make the easy path, also the safe path.

651
00:24:18,760 --> 00:24:21,400
Now, the permissions model becomes simple and simplicity

652
00:24:21,400 --> 00:24:22,240
is what survives.

653
00:24:22,240 --> 00:24:24,240
You grant right permissions only to the identities

654
00:24:24,240 --> 00:24:25,400
that load staging.

655
00:24:25,400 --> 00:24:28,520
You grant read permissions to curated only through controlled objects.

656
00:24:28,520 --> 00:24:30,280
And you deny everything else by default.

657
00:24:30,280 --> 00:24:32,280
Yes, deny by default, not because it's dramatic.

658
00:24:32,280 --> 00:24:34,280
Because default allow becomes defaults broad.

659
00:24:34,280 --> 00:24:37,080
The thing most organizations miss is that workspace,

660
00:24:37,080 --> 00:24:39,880
RBAC and database permissions are not substitutes.

661
00:24:39,880 --> 00:24:42,200
Workspace roles can get someone into the room.

662
00:24:42,200 --> 00:24:45,400
TScore permissions decide which draws they can open once they're inside.

663
00:24:45,400 --> 00:24:47,280
If you don't define the draws, the entire room

664
00:24:47,280 --> 00:24:48,760
becomes one shared draw.

665
00:24:48,760 --> 00:24:51,840
Now add the part that actually makes this work across power platform.

666
00:24:51,840 --> 00:24:53,520
Power platform loves abstraction.

667
00:24:53,520 --> 00:24:55,360
Dataverse abstracts the data model.

668
00:24:55,360 --> 00:24:57,400
Power apps abstracts access patterns.

669
00:24:57,400 --> 00:24:59,240
Connectors abstract authentication.

670
00:24:59,240 --> 00:25:02,040
And those abstractions are useful, but they leak at boundaries.

671
00:25:02,040 --> 00:25:03,440
Eventually, someone needs a view.

672
00:25:03,440 --> 00:25:05,120
Someone needs a data set refresh.

673
00:25:05,120 --> 00:25:06,840
Someone needs a pipeline to write back.

674
00:25:06,840 --> 00:25:07,800
Those are boundary crossing.

675
00:25:07,800 --> 00:25:09,720
So you design for boundary crossings.

676
00:25:09,720 --> 00:25:12,280
Create roles that represent real consumers.

677
00:25:12,280 --> 00:25:14,320
Reporting readers, automation writers,

678
00:25:14,320 --> 00:25:16,680
ingestion loaders and admin operators.

679
00:25:16,680 --> 00:25:19,800
Then map those roles to schemers and objects with explicit grounds.

680
00:25:19,800 --> 00:25:22,600
Do not grant broad table access when a view will do.

681
00:25:22,600 --> 00:25:24,720
A view is not just convenience, it's a contract.

682
00:25:24,720 --> 00:25:26,440
It constraints what columns are exposed.

683
00:25:26,440 --> 00:25:28,200
It constraints how joins occur.

684
00:25:28,200 --> 00:25:30,960
It constraints what the consumer can accidentally do.

685
00:25:30,960 --> 00:25:33,240
Stored procedures do the same for write paths.

686
00:25:33,240 --> 00:25:35,280
If an automation flow needs to submit records,

687
00:25:35,280 --> 00:25:38,000
don't give a table write, give it an executable procedure

688
00:25:38,000 --> 00:25:40,920
that validates inputs and writes into the one intended target.

689
00:25:40,920 --> 00:25:44,000
You've just converted ability into permissioned intent.

690
00:25:44,000 --> 00:25:46,960
Now, some people will ask about row level security

691
00:25:46,960 --> 00:25:48,920
and predicate based access patterns.

692
00:25:48,920 --> 00:25:50,200
The concept is valid.

693
00:25:50,200 --> 00:25:53,960
Restrict rows, not just tables, but the rule stays the same.

694
00:25:53,960 --> 00:25:56,200
Encode the boundary in something enforceable,

695
00:25:56,200 --> 00:25:58,080
not in app layer promises.

696
00:25:58,080 --> 00:26:01,320
If the requirement is this identity can only see these customers,

697
00:26:01,320 --> 00:26:03,360
then the database layer must be the place

698
00:26:03,360 --> 00:26:06,400
where that restriction is true even when the app changes.

699
00:26:06,400 --> 00:26:07,920
And here's the operational payoff

700
00:26:07,920 --> 00:26:09,760
that executives actually care about.

701
00:26:09,760 --> 00:26:12,960
Once designed, this model doesn't require heroics to maintain.

702
00:26:12,960 --> 00:26:15,600
Because the permissions become attached to roles,

703
00:26:15,600 --> 00:26:17,480
roles attached to schemas and consumers

704
00:26:17,480 --> 00:26:19,080
attached to controlled objects.

705
00:26:19,080 --> 00:26:21,200
When a team changes, you swap role membership.

706
00:26:21,200 --> 00:26:24,000
You don't rewrite the world when a new power app appears.

707
00:26:24,000 --> 00:26:27,520
You give it the reporting role, not the keys to the warehouse.

708
00:26:27,520 --> 00:26:29,800
And the audit conversation stops being theatrical.

709
00:26:29,800 --> 00:26:32,640
Instead of we think only these users had access,

710
00:26:32,640 --> 00:26:33,840
you can show the grounds.

711
00:26:33,840 --> 00:26:35,280
You can show the schema boundaries.

712
00:26:35,280 --> 00:26:37,640
You can show deny by default posture.

713
00:26:37,640 --> 00:26:41,160
You can show that the workspace role wasn't the final control.

714
00:26:41,160 --> 00:26:43,680
That is least privilege that survives reality.

715
00:26:43,680 --> 00:26:46,040
And once you have survivable least privilege,

716
00:26:46,040 --> 00:26:48,120
the next failure mode becomes obvious.

717
00:26:48,120 --> 00:26:50,080
Even with clean data and tight access,

718
00:26:50,080 --> 00:26:52,760
truth still drifts when business logic gets duplicated

719
00:26:52,760 --> 00:26:54,440
across layers.

720
00:26:54,440 --> 00:26:58,200
Case four, business logic drift the quiet killer of modern data.

721
00:26:58,200 --> 00:27:00,000
Case four is the one nobody budgets for.

722
00:27:00,000 --> 00:27:02,960
Because it doesn't show up as a failed pipeline or a security alert.

723
00:27:02,960 --> 00:27:05,440
It shows up as a slow loss of trust.

724
00:27:05,440 --> 00:27:08,960
Business logic drift is what happens when the rules exist in five places.

725
00:27:08,960 --> 00:27:12,000
Get changed in one and everyone keeps pretending they're still aligned.

726
00:27:12,000 --> 00:27:14,920
In the Microsoft ecosystem, drift is almost guaranteed

727
00:27:14,920 --> 00:27:17,160
because every layer is capable of helping.

728
00:27:17,160 --> 00:27:19,320
Power apps can validate and transform.

729
00:27:19,320 --> 00:27:21,560
Power automate can branch and enrich.

730
00:27:21,560 --> 00:27:23,240
Data flows can reshape.

731
00:27:23,240 --> 00:27:24,680
Notebooks can fix it.

732
00:27:24,680 --> 00:27:26,760
Power BI can redefine truth in measures.

733
00:27:26,760 --> 00:27:30,320
An ad hoc school will always exist because someone always needs an answer today.

734
00:27:30,320 --> 00:27:31,480
Each layer can be useful.

735
00:27:31,480 --> 00:27:33,520
Each layer is also an entropy generator.

736
00:27:33,520 --> 00:27:36,080
Here's the drift mechanic in enterprise reality.

737
00:27:36,080 --> 00:27:37,840
A business rule starts simple.

738
00:27:37,840 --> 00:27:40,560
Active customers are customers with a current contract.

739
00:27:40,560 --> 00:27:41,680
And then a report needs it.

740
00:27:41,680 --> 00:27:43,080
So someone implements it in DAX.

741
00:27:43,080 --> 00:27:43,920
Then an app needs it.

742
00:27:43,920 --> 00:27:46,080
So someone implements it in PowerFX.

743
00:27:46,080 --> 00:27:47,080
Then a pipeline needs it.

744
00:27:47,080 --> 00:27:49,160
So someone implements it in a transform step.

745
00:27:49,160 --> 00:27:51,960
Then a different team wants active to include trial contracts.

746
00:27:51,960 --> 00:27:54,840
So they change their version and now truth becomes negotiable.

747
00:27:54,840 --> 00:27:56,920
No one intended to create multiple definitions.

748
00:27:56,920 --> 00:27:58,960
They just optimized for local delivery.

749
00:27:58,960 --> 00:28:01,760
The system accepted that behavior because the data layer

750
00:28:01,760 --> 00:28:03,600
didn't enforce a single definition.

751
00:28:03,600 --> 00:28:05,200
And the consequences are predictable.

752
00:28:05,200 --> 00:28:06,760
First, audit pain.

753
00:28:06,760 --> 00:28:09,400
When an auditor asks, how do you calculate this KPI?

754
00:28:09,400 --> 00:28:12,400
The honest answer becomes, it depends which report you opened.

755
00:28:12,400 --> 00:28:13,240
That's not governance.

756
00:28:13,240 --> 00:28:14,040
That's theater.

757
00:28:14,040 --> 00:28:15,520
Second, operational drag.

758
00:28:15,520 --> 00:28:18,480
Every time a rule changes, you have to hunt it across layers.

759
00:28:18,480 --> 00:28:19,920
And you change the Power BI measure.

760
00:28:19,920 --> 00:28:22,160
But the power app still filters the old way.

761
00:28:22,160 --> 00:28:24,040
Or the pipeline still tags the old way.

762
00:28:24,040 --> 00:28:25,400
Now the data doesn't reconcile.

763
00:28:25,400 --> 00:28:28,440
And you spend time in meetings arguing about which definition is correct

764
00:28:28,440 --> 00:28:30,080
instead of fixing the system.

765
00:28:30,080 --> 00:28:31,960
Third, performance decay.

766
00:28:31,960 --> 00:28:35,560
Logic duplicated across tools is rarely implemented with cost discipline.

767
00:28:35,560 --> 00:28:37,960
You see expensive transformations happening in refresh,

768
00:28:37,960 --> 00:28:40,920
indirect query-like paths, in semantic models, and in app calls.

769
00:28:40,920 --> 00:28:42,280
The platform doesn't collapse.

770
00:28:42,280 --> 00:28:44,920
It just gets slower and more expensive quietly

771
00:28:44,920 --> 00:28:46,480
until it becomes normal.

772
00:28:46,480 --> 00:28:49,120
This clicked for him the first time a team's war two reports

773
00:28:49,120 --> 00:28:50,840
were reading the same data set and they were.

774
00:28:50,840 --> 00:28:52,720
But one had a temporary DAX correction

775
00:28:52,720 --> 00:28:55,160
that became the official truth for six months.

776
00:28:55,160 --> 00:28:56,280
Nobody documented it.

777
00:28:56,280 --> 00:28:57,320
Nobody owned it.

778
00:28:57,320 --> 00:28:58,520
Everyone relied on it.

779
00:28:58,520 --> 00:28:59,320
That's drift.

780
00:28:59,320 --> 00:29:00,640
The T-School intervention is boring.

781
00:29:00,640 --> 00:29:02,000
And that's why it works.

782
00:29:02,000 --> 00:29:04,480
You centralize business logic in the data layer.

783
00:29:04,480 --> 00:29:08,200
Views for read semantics, stored procedures for write semantics,

784
00:29:08,200 --> 00:29:10,480
and constraints where the rule is a true invariant.

785
00:29:10,480 --> 00:29:12,120
You encode the definition once and then

786
00:29:12,120 --> 00:29:14,120
you make every consumer depend on it.

787
00:29:14,120 --> 00:29:15,720
A view becomes a contract surface.

788
00:29:15,720 --> 00:29:17,680
And it says, this is what active customer means.

789
00:29:17,680 --> 00:29:19,160
This is what revenue includes.

790
00:29:19,160 --> 00:29:21,080
This is how we treat canceled orders.

791
00:29:21,080 --> 00:29:22,520
And because it's a database object,

792
00:29:22,520 --> 00:29:24,640
it's versionable, testable, and inspecable.

793
00:29:24,640 --> 00:29:25,800
You can review changes.

794
00:29:25,800 --> 00:29:27,040
You can see dependencies.

795
00:29:27,040 --> 00:29:28,920
You can see what breaks if you change it.

796
00:29:28,920 --> 00:29:30,800
And yes, this is where people get nervous because they've

797
00:29:30,800 --> 00:29:33,280
seen the database as a dumping ground.

798
00:29:33,280 --> 00:29:35,000
That's the trade-off you acknowledge up front.

799
00:29:35,000 --> 00:29:36,840
Central logic requires ownership.

800
00:29:36,840 --> 00:29:38,720
Without ownership, the database becomes

801
00:29:38,720 --> 00:29:41,320
a landfill of half finished views, conflicting naming,

802
00:29:41,320 --> 00:29:42,640
and procedural sprawl.

803
00:29:42,640 --> 00:29:45,520
But the answer to that isn't keep logic everywhere.

804
00:29:45,520 --> 00:29:46,520
The answer is governance.

805
00:29:46,520 --> 00:29:49,720
Define the contract surfaces deliberately, document intent,

806
00:29:49,720 --> 00:29:51,520
and restrict who can change them.

807
00:29:51,520 --> 00:29:53,120
Because the alternative isn't freedom.

808
00:29:53,120 --> 00:29:55,120
The alternative is entropy.

809
00:29:55,120 --> 00:29:56,920
Now, the practical payoff is immediate.

810
00:29:56,920 --> 00:29:58,240
When logic lives in one place, power

811
00:29:58,240 --> 00:30:00,800
be I become simpler, power apps become simpler.

812
00:30:00,800 --> 00:30:01,880
Pipelines become simpler.

813
00:30:01,880 --> 00:30:04,000
Your semantic model stops being a rewrite engine

814
00:30:04,000 --> 00:30:05,800
and goes back to being a consumption layer.

815
00:30:05,800 --> 00:30:07,960
You can still build tool-specific calculations,

816
00:30:07,960 --> 00:30:10,080
but the core definition stopped drifting.

817
00:30:10,080 --> 00:30:12,040
And here's the part most people miss.

818
00:30:12,040 --> 00:30:14,880
Centralizing logic doesn't just make the numbers consistent.

819
00:30:14,880 --> 00:30:16,480
It makes the system cheaper to change.

820
00:30:16,480 --> 00:30:19,880
A rule changes once, tests run once, consumers inherit once,

821
00:30:19,880 --> 00:30:21,800
you reduce the surface area of change,

822
00:30:21,800 --> 00:30:23,480
and the incident probability drops

823
00:30:23,480 --> 00:30:25,240
because you aren't changing five layers

824
00:30:25,240 --> 00:30:27,120
that can each fail differently.

825
00:30:27,120 --> 00:30:30,480
So if you're trying to build modern data systems that don't decay,

826
00:30:30,480 --> 00:30:32,960
you don't fight drift with documentation and training.

827
00:30:32,960 --> 00:30:35,800
You fight drift by forcing a single contract surface.

828
00:30:35,800 --> 00:30:38,960
And that contract surface is still annoyingly t-sockel.

829
00:30:38,960 --> 00:30:41,000
Now, once you centralize logic,

830
00:30:41,000 --> 00:30:44,160
you've created something valuable and valuable things get used,

831
00:30:44,160 --> 00:30:46,920
which means you now have to keep them fast under load.

832
00:30:46,920 --> 00:30:50,120
That's where indexing and statistics stop being optional.

833
00:30:50,120 --> 00:30:53,240
Indexing, the shortcut nobody teaches is the trade-off.

834
00:30:53,240 --> 00:30:56,200
Indexing is where a lot of teams finally admit the truth.

835
00:30:56,200 --> 00:30:57,960
They didn't have a performance problem.

836
00:30:57,960 --> 00:30:59,160
They had an access pass problem.

837
00:30:59,160 --> 00:31:00,640
A table is just stored data.

838
00:31:00,640 --> 00:31:03,160
It has no opinion about how you'll ask for it later.

839
00:31:03,160 --> 00:31:06,320
Without an index, the engine often has one honest option,

840
00:31:06,320 --> 00:31:08,400
scan until it finds what you ask for.

841
00:31:08,400 --> 00:31:09,600
That works when you're small.

842
00:31:09,600 --> 00:31:11,040
It also works when you're desperate.

843
00:31:11,040 --> 00:31:12,080
It stops working.

844
00:31:12,080 --> 00:31:14,160
The moment the system becomes popular,

845
00:31:14,160 --> 00:31:15,800
the index is a pre-built access path.

846
00:31:15,800 --> 00:31:17,920
It's the engine's way of not reading everything.

847
00:31:17,920 --> 00:31:19,640
You're giving the optimizer a structure

848
00:31:19,640 --> 00:31:22,000
it can use to locate rows without brute force.

849
00:31:22,000 --> 00:31:23,320
That's the shortcut part.

850
00:31:23,320 --> 00:31:25,840
The thing most people miss is the trade-off part.

851
00:31:25,840 --> 00:31:28,040
Indexes don't make data faster.

852
00:31:28,040 --> 00:31:29,640
They make certain questions cheaper

853
00:31:29,640 --> 00:31:31,760
by making other operations more expensive.

854
00:31:31,760 --> 00:31:34,400
Every index you add is an ongoing tax on rights.

855
00:31:34,400 --> 00:31:36,840
Inserts, updates, deletes, merges.

856
00:31:36,840 --> 00:31:38,760
The system must maintain the index structure.

857
00:31:38,760 --> 00:31:40,600
And in a fabric or synapse style environment,

858
00:31:40,600 --> 00:31:43,480
that tax turns into computer latency

859
00:31:43,480 --> 00:31:45,160
just as reliably as a scan does.

860
00:31:45,160 --> 00:31:47,600
It's just easier to ignore because it's distributed across

861
00:31:47,600 --> 00:31:48,440
every right.

862
00:31:48,440 --> 00:31:50,640
So you don't index because it feels responsible.

863
00:31:50,640 --> 00:31:52,440
You index because the execution plan proves

864
00:31:52,440 --> 00:31:54,440
you're paying a bigger bill without it.

865
00:31:54,440 --> 00:31:56,120
Here's how to decide what to index

866
00:31:56,120 --> 00:31:58,080
without turning your database into a museum

867
00:31:58,080 --> 00:31:59,960
of abandoned performance fixes.

868
00:31:59,960 --> 00:32:02,280
Start with the predicates that shape your row count

869
00:32:02,280 --> 00:32:03,640
columns in where?

870
00:32:03,640 --> 00:32:06,760
If users filter by customer it, date, status, region,

871
00:32:06,760 --> 00:32:08,560
whatever, those are the candidates.

872
00:32:08,560 --> 00:32:10,400
An index that aligns with common filters

873
00:32:10,400 --> 00:32:12,600
turns scan millions to return hundreds

874
00:32:12,600 --> 00:32:14,760
into seek a narrow range and stop.

875
00:32:14,760 --> 00:32:16,120
Next, join keys.

876
00:32:16,120 --> 00:32:17,800
Join our multipliers and join keys

877
00:32:17,800 --> 00:32:19,800
are where the multiplier gets weaponized.

878
00:32:19,800 --> 00:32:22,400
If you join fact sales to dim customer and customer key,

879
00:32:22,400 --> 00:32:25,560
and that key isn't indexed in a way the optimizer can exploit,

880
00:32:25,560 --> 00:32:27,920
you invite hash joins and big memory grants.

881
00:32:27,920 --> 00:32:29,080
Sometimes that's fine.

882
00:32:29,080 --> 00:32:31,200
Under concurrency it becomes expensive.

883
00:32:31,200 --> 00:32:33,440
Then group by and order by patterns.

884
00:32:33,440 --> 00:32:34,680
These often force sorts.

885
00:32:34,680 --> 00:32:36,160
A sort is a memory negotiation.

886
00:32:36,160 --> 00:32:38,800
If the engine can't sort in memory, it spills.

887
00:32:38,800 --> 00:32:40,880
Indexes can reduce or eliminate those sorts

888
00:32:40,880 --> 00:32:42,880
when the data is already in the needed order,

889
00:32:42,880 --> 00:32:44,840
or when the index can be used to stream the rows

890
00:32:44,840 --> 00:32:47,400
in a way that avoids a big intermediate sort.

891
00:32:47,400 --> 00:32:49,720
Now the warning that needs to be set out loud,

892
00:32:49,720 --> 00:32:52,000
you can't index your way out of ambiguity.

893
00:32:52,000 --> 00:32:53,880
If your predicates are non-sargable,

894
00:32:53,880 --> 00:32:57,400
functions on columns, implicit conversions, mismatched types,

895
00:32:57,400 --> 00:32:59,360
the engine can't use the index efficiently

896
00:32:59,360 --> 00:33:00,960
even if it exists.

897
00:33:00,960 --> 00:33:02,600
You didn't misconfigure anything.

898
00:33:02,600 --> 00:33:05,120
You designed a query the optimizer can't exploit.

899
00:33:05,120 --> 00:33:07,120
And indexing doesn't excuse bad data shape.

900
00:33:07,120 --> 00:33:08,960
If you have skewed distributions,

901
00:33:08,960 --> 00:33:10,640
the optimizer might still choose a scan

902
00:33:10,640 --> 00:33:12,280
because the index isn't selective enough.

903
00:33:12,280 --> 00:33:13,800
That's not the engine being stupid.

904
00:33:13,800 --> 00:33:15,040
That's the engine telling you,

905
00:33:15,040 --> 00:33:17,280
this index doesn't actually narrow the work.

906
00:33:17,280 --> 00:33:19,120
So the workflow is always the same.

907
00:33:19,120 --> 00:33:20,440
Write the query.

908
00:33:20,440 --> 00:33:21,680
Inspect the plan.

909
00:33:21,680 --> 00:33:22,960
Look for the wasted work.

910
00:33:22,960 --> 00:33:26,240
Scans, heavy sorts, big mismatches between scanned

911
00:33:26,240 --> 00:33:28,680
and returned rows only then decide whether an index

912
00:33:28,680 --> 00:33:30,480
meaningfully changes the plan shape.

913
00:33:30,480 --> 00:33:32,440
That's why indexing is evidence-driven governance,

914
00:33:32,440 --> 00:33:33,800
not a tuning hobby.

915
00:33:33,800 --> 00:33:36,680
And you also need to internalize a second uncomfortable truth.

916
00:33:36,680 --> 00:33:38,600
Some indexes are entropy generators.

917
00:33:38,600 --> 00:33:40,280
The classic pattern is index everything

918
00:33:40,280 --> 00:33:41,680
someone complained about.

919
00:33:41,680 --> 00:33:43,440
Over time you get dozens of indexes.

920
00:33:43,440 --> 00:33:45,520
Overlapping half-used, never removed.

921
00:33:45,520 --> 00:33:48,440
Rides slow down, maintenance complexity rises,

922
00:33:48,440 --> 00:33:50,960
and the optimizer has more choices to evaluate.

923
00:33:50,960 --> 00:33:54,280
You've created architectural erosion through helpful changes.

924
00:33:54,280 --> 00:33:56,360
The safe posture is a simple rule.

925
00:33:56,360 --> 00:33:59,000
Add indexes only when you can name the workload they support

926
00:33:59,000 --> 00:34:00,480
and show the plan improvement.

927
00:34:00,480 --> 00:34:02,800
If the workload disappears, the index disappears.

928
00:34:02,800 --> 00:34:04,480
That's how you keep the system from drifting

929
00:34:04,480 --> 00:34:06,160
into a performance junk drawer.

930
00:34:06,160 --> 00:34:09,720
And once you adopt that posture, indexing becomes less emotional.

931
00:34:09,720 --> 00:34:11,120
It becomes part of the contract.

932
00:34:11,120 --> 00:34:13,960
You're not optimizing for the query in front of you.

933
00:34:13,960 --> 00:34:16,000
You're optimizing for the behavior of a system

934
00:34:16,000 --> 00:34:18,680
underload across time as data grows.

935
00:34:18,680 --> 00:34:19,800
Because that's the actual job.

936
00:34:19,800 --> 00:34:23,200
Now when indexes aren't enough, when the plan still has to read too much,

937
00:34:23,200 --> 00:34:24,640
you're about to leave query tuning

938
00:34:24,640 --> 00:34:26,760
and enter storage level discipline.

939
00:34:26,760 --> 00:34:27,840
That's partitioning.

940
00:34:27,840 --> 00:34:31,080
Partitioning storage level discipline for billion-row reality.

941
00:34:31,080 --> 00:34:34,720
Partitioning exists because you can't optimize your way out of scale.

942
00:34:34,720 --> 00:34:36,840
At some point, the table is just too large.

943
00:34:36,840 --> 00:34:38,560
And every plan choice becomes a debate

944
00:34:38,560 --> 00:34:41,360
about how expensive the inevitable scan will be.

945
00:34:41,360 --> 00:34:44,000
This is the moment where people say we already indexed it.

946
00:34:44,000 --> 00:34:44,680
And they did.

947
00:34:44,680 --> 00:34:45,360
And it helped.

948
00:34:45,360 --> 00:34:46,840
But the query still takes too long

949
00:34:46,840 --> 00:34:48,680
or the workload falls over under concurrency

950
00:34:48,680 --> 00:34:51,960
because the engine still has to touch a massive amount of data

951
00:34:51,960 --> 00:34:53,560
to answer a simple question.

952
00:34:53,560 --> 00:34:54,840
That's what partitioning is for.

953
00:34:54,840 --> 00:34:56,400
Partitioning is not a performance trick.

954
00:34:56,400 --> 00:34:57,880
It is storage level discipline.

955
00:34:57,880 --> 00:35:01,880
You are telling the engine, this table is not one continuous ocean.

956
00:35:01,880 --> 00:35:03,360
It is a set of bounded segments

957
00:35:03,360 --> 00:35:06,040
and most queries should only swim in one segment.

958
00:35:06,040 --> 00:35:08,360
The most practical model is time-based partitioning

959
00:35:08,360 --> 00:35:11,320
because almost every enterprise dataset has a time axis,

960
00:35:11,320 --> 00:35:14,800
transaction date, event time, ingestion time, effective date.

961
00:35:14,800 --> 00:35:17,280
And most enterprise questions are time-bounded,

962
00:35:17,280 --> 00:35:19,160
whether people admitted or not.

963
00:35:19,160 --> 00:35:21,760
Last seven days, this month, this quarter,

964
00:35:21,760 --> 00:35:23,000
since the last release.

965
00:35:23,000 --> 00:35:25,040
So you partition by time and you make time

966
00:35:25,040 --> 00:35:27,280
a first class part of the physical design.

967
00:35:27,280 --> 00:35:29,760
The reason this works is simple partition elimination.

968
00:35:29,760 --> 00:35:32,240
If the predicate aligns with the partition key,

969
00:35:32,240 --> 00:35:34,320
the engine can ignore entire partitions.

970
00:35:34,320 --> 00:35:35,640
It doesn't scan faster.

971
00:35:35,640 --> 00:35:36,680
It scans less.

972
00:35:36,680 --> 00:35:39,720
And scanning less is the only tuning that actually scales.

973
00:35:39,720 --> 00:35:41,240
Now here's where most people mess up.

974
00:35:41,240 --> 00:35:42,760
They partition the table,

975
00:35:42,760 --> 00:35:45,280
but their queries don't filter on the partition key.

976
00:35:45,280 --> 00:35:48,080
Or worse, they wrap the partition key in a function,

977
00:35:48,080 --> 00:35:50,160
making the predicate non-sargable.

978
00:35:50,160 --> 00:35:53,160
Or they filter on a different date column that's close enough.

979
00:35:53,160 --> 00:35:55,320
The engine can't eliminate what you didn't target.

980
00:35:55,320 --> 00:35:57,880
Partitioning only pays off when your workload cooperates.

981
00:35:57,880 --> 00:36:00,200
That means you have to enforce an access pattern.

982
00:36:00,200 --> 00:36:02,000
The partition key must appear in predicates

983
00:36:02,000 --> 00:36:03,520
for the queries you care about.

984
00:36:03,520 --> 00:36:04,720
If you won't enforce that,

985
00:36:04,720 --> 00:36:06,840
partitioning becomes overhead with no benefit.

986
00:36:06,840 --> 00:36:10,360
This is also why partitioning is not just a database decision.

987
00:36:10,360 --> 00:36:11,720
It is an organizational decision.

988
00:36:11,720 --> 00:36:14,120
Once you partition the table, you've declared a policy.

989
00:36:14,120 --> 00:36:17,120
How data is physically organized, how it ages,

990
00:36:17,120 --> 00:36:18,720
how it gets archived,

991
00:36:18,720 --> 00:36:20,760
and how consumers are expected to ask questions.

992
00:36:20,760 --> 00:36:22,760
If a team refuses to filter by time,

993
00:36:22,760 --> 00:36:25,400
they are refusing to operate within the systems cost model.

994
00:36:25,400 --> 00:36:26,960
That distinction matters.

995
00:36:26,960 --> 00:36:29,160
Because partitioning has governance benefits

996
00:36:29,160 --> 00:36:30,760
that don't show up in a quick demo.

997
00:36:30,760 --> 00:36:32,600
First, predictable cost.

998
00:36:32,600 --> 00:36:35,280
If last seven days reliably hits one partition

999
00:36:35,280 --> 00:36:36,840
instead of the entire table,

1000
00:36:36,840 --> 00:36:39,680
you've turned a time bomb query into a bounded query.

1001
00:36:39,680 --> 00:36:41,280
Second, operational hygiene.

1002
00:36:41,280 --> 00:36:43,320
Partitions create natural lifecycle boundaries.

1003
00:36:43,320 --> 00:36:45,440
If you need to drop all data, archive it,

1004
00:36:45,440 --> 00:36:46,520
or rebuild indexes,

1005
00:36:46,520 --> 00:36:47,720
you can do that per partition

1006
00:36:47,720 --> 00:36:49,000
instead of treating the whole table

1007
00:36:49,000 --> 00:36:50,440
like a fragile artifact.

1008
00:36:50,440 --> 00:36:53,400
Third, performance stability under concurrency.

1009
00:36:53,400 --> 00:36:55,200
When many queries hit recent partitions,

1010
00:36:55,200 --> 00:36:57,720
you reduce contention on the entire storage footprint.

1011
00:36:57,720 --> 00:36:59,000
You localize the pressure,

1012
00:36:59,000 --> 00:37:00,840
but partitioning does not replace indexing.

1013
00:37:00,840 --> 00:37:01,760
It complements it,

1014
00:37:01,760 --> 00:37:03,880
partitioning reduces the size of the haystack.

1015
00:37:03,880 --> 00:37:06,960
Indexing helps you find the needle inside the smaller haystack.

1016
00:37:06,960 --> 00:37:08,760
If you skip either you get partial results,

1017
00:37:08,760 --> 00:37:10,080
either you prune partitions,

1018
00:37:10,080 --> 00:37:12,120
but still scan too much inside them,

1019
00:37:12,120 --> 00:37:13,560
or you have an index that's great,

1020
00:37:13,560 --> 00:37:15,080
but still spans an ocean,

1021
00:37:15,080 --> 00:37:16,520
you didn't need to traverse.

1022
00:37:16,520 --> 00:37:17,960
Now, the critical rule for partitioning

1023
00:37:17,960 --> 00:37:20,040
and enterprise systems is alignment.

1024
00:37:20,040 --> 00:37:23,640
Align the partition key with how the business asks questions.

1025
00:37:23,640 --> 00:37:25,640
Align the partition grain with how data arrives

1026
00:37:25,640 --> 00:37:28,000
and how it's retained daily, weekly, monthly.

1027
00:37:28,000 --> 00:37:29,240
Align the queries with the key

1028
00:37:29,240 --> 00:37:31,160
and enforce that alignment through code review

1029
00:37:31,160 --> 00:37:32,280
and through contract surfaces

1030
00:37:32,280 --> 00:37:35,080
like views that already include the time predicate pattern.

1031
00:37:35,080 --> 00:37:38,080
Because the moment you let consumers bypass that contract,

1032
00:37:38,080 --> 00:37:39,400
you're back to chaos.

1033
00:37:39,400 --> 00:37:41,160
One query touches one partition,

1034
00:37:41,160 --> 00:37:43,040
another touches all partitions,

1035
00:37:43,040 --> 00:37:46,120
and your capacity planning turns into superstition.

1036
00:37:46,120 --> 00:37:50,160
So partitioning is the point where T-SQL as control surface

1037
00:37:50,160 --> 00:37:52,720
stops being metaphor and becomes physical reality.

1038
00:37:52,720 --> 00:37:54,120
You are designing the shape of work

1039
00:37:54,120 --> 00:37:55,640
the platform is allowed to do.

1040
00:37:55,640 --> 00:37:57,280
And if partitioning still isn't enough,

1041
00:37:57,280 --> 00:37:58,840
if you have indexes, partitions,

1042
00:37:58,840 --> 00:38:00,640
and discipline predicates,

1043
00:38:00,640 --> 00:38:01,800
and the workload still fails,

1044
00:38:01,800 --> 00:38:03,280
then you're not tuning SQL anymore.

1045
00:38:03,280 --> 00:38:05,000
You're changing the system's shape.

1046
00:38:05,000 --> 00:38:08,080
Structural redesign when query tuning stops working.

1047
00:38:08,080 --> 00:38:09,720
Here's the line you eventually hit.

1048
00:38:09,720 --> 00:38:11,040
You tune the query,

1049
00:38:11,040 --> 00:38:12,640
you added the right indexes,

1050
00:38:12,640 --> 00:38:15,320
you aligned partitions, you updated statistics,

1051
00:38:15,320 --> 00:38:17,040
and the workload still fails.

1052
00:38:17,040 --> 00:38:19,560
At that point, the database isn't misbehaving,

1053
00:38:19,560 --> 00:38:21,000
it's obeying physics.

1054
00:38:21,000 --> 00:38:22,680
The trigger conditions are consistent,

1055
00:38:22,680 --> 00:38:24,320
the query plan is already reasonable.

1056
00:38:24,320 --> 00:38:25,880
You're not doing something obviously stupid

1057
00:38:25,880 --> 00:38:27,920
like scanning everything for no reason,

1058
00:38:27,920 --> 00:38:29,560
but the data volume is too large,

1059
00:38:29,560 --> 00:38:31,160
the concurrency is too high,

1060
00:38:31,160 --> 00:38:33,760
or the access pattern is fundamentally incompatible

1061
00:38:33,760 --> 00:38:35,000
with the structure you built.

1062
00:38:35,000 --> 00:38:37,400
This is where most teams spiral into conditional chaos,

1063
00:38:37,400 --> 00:38:40,240
more hints, more indexes, more retries, more capacity.

1064
00:38:40,240 --> 00:38:42,960
They keep tuning symptoms because redesign feels like failure.

1065
00:38:42,960 --> 00:38:43,840
It isn't.

1066
00:38:43,840 --> 00:38:46,560
Structural redesign is the moment you stop pretending

1067
00:38:46,560 --> 00:38:48,560
the access pattern is unknown.

1068
00:38:48,560 --> 00:38:50,960
Most data platforms drift because teams refuse

1069
00:38:50,960 --> 00:38:53,240
to declare what gets access together.

1070
00:38:53,240 --> 00:38:54,520
They want the freedom of a lake,

1071
00:38:54,520 --> 00:38:55,760
the governance of a warehouse,

1072
00:38:55,760 --> 00:38:57,360
and the cost profile of a demo.

1073
00:38:57,360 --> 00:38:59,600
The system will happily take that deal.

1074
00:38:59,600 --> 00:39:00,880
Your credit card will not,

1075
00:39:00,880 --> 00:39:01,920
so the principle is blunt,

1076
00:39:01,920 --> 00:39:04,000
store together what is access together.

1077
00:39:04,000 --> 00:39:06,720
That means you accept that physical design is policy.

1078
00:39:06,720 --> 00:39:08,560
If most queries always slice by time

1079
00:39:08,560 --> 00:39:10,800
and customer you design for time and customer.

1080
00:39:10,800 --> 00:39:12,840
If most queries need latest state,

1081
00:39:12,840 --> 00:39:14,240
you design for latest state.

1082
00:39:14,240 --> 00:39:16,520
If most consumers need pre-aggregated results,

1083
00:39:16,520 --> 00:39:18,400
you build a table or a materialized structure

1084
00:39:18,400 --> 00:39:19,320
that represents that.

1085
00:39:19,320 --> 00:39:22,280
You stop asking every query to be a research project.

1086
00:39:22,280 --> 00:39:24,520
Now this is where the normalization debate shows up,

1087
00:39:24,520 --> 00:39:27,000
usually with ideological heat and very little evidence.

1088
00:39:27,000 --> 00:39:28,640
Normalization is not virtue,

1089
00:39:28,640 --> 00:39:30,560
denormalization is not laziness.

1090
00:39:30,560 --> 00:39:32,360
They are trade-offs in a cost model.

1091
00:39:32,360 --> 00:39:34,520
Normalization reduces redundancy

1092
00:39:34,520 --> 00:39:37,480
and can enforce integrity through keys and relationships,

1093
00:39:37,480 --> 00:39:39,280
but it forces joints at rate time.

1094
00:39:39,280 --> 00:39:40,600
Under heavy analytics workloads,

1095
00:39:40,600 --> 00:39:42,320
those joints become multipliers.

1096
00:39:42,320 --> 00:39:45,720
Denormalization reduces joint cost by pre-combining data

1097
00:39:45,720 --> 00:39:47,320
that is frequently accessed together,

1098
00:39:47,320 --> 00:39:51,160
but it increases right cost and introduces the risk of drift

1099
00:39:51,160 --> 00:39:53,200
if you don't enforce update logic.

1100
00:39:53,200 --> 00:39:55,800
So the correct posture is not normalize everything

1101
00:39:55,800 --> 00:39:57,520
or denormalize everything.

1102
00:39:57,520 --> 00:40:00,680
The correct posture is normalize where you need integrity,

1103
00:40:00,680 --> 00:40:02,400
denormalize where you need performance

1104
00:40:02,400 --> 00:40:04,000
and enforce both with contracts.

1105
00:40:04,000 --> 00:40:05,920
This is why enterprise warehouses use patterns

1106
00:40:05,920 --> 00:40:08,760
like curated fact tables with denormalized attributes

1107
00:40:08,760 --> 00:40:09,840
for common dimensions.

1108
00:40:09,840 --> 00:40:12,080
It's not because someone hates third normal form.

1109
00:40:12,080 --> 00:40:14,200
It's because they understand the workload.

1110
00:40:14,200 --> 00:40:15,720
And in fabric error architectures,

1111
00:40:15,720 --> 00:40:17,560
you also need to be honest about

1112
00:40:17,560 --> 00:40:19,440
when you're not tuning SQL anymore.

1113
00:40:19,440 --> 00:40:21,960
Sometimes the fix isn't inside the relational engine.

1114
00:40:21,960 --> 00:40:24,560
Sometimes the fix is changing the execution model

1115
00:40:24,560 --> 00:40:27,000
using spark style processing for transformations

1116
00:40:27,000 --> 00:40:29,800
that are inherently large scale and batch oriented

1117
00:40:29,800 --> 00:40:32,520
or moving specific workloads into distributed compute

1118
00:40:32,520 --> 00:40:34,800
where parallelism is a first class property.

1119
00:40:34,800 --> 00:40:36,200
That's not a performance fix.

1120
00:40:36,200 --> 00:40:39,680
That's a system shape change, same data, different physics.

1121
00:40:39,680 --> 00:40:41,200
But here's the key discipline.

1122
00:40:41,200 --> 00:40:43,880
Don't let spark exists become an excuse

1123
00:40:43,880 --> 00:40:45,680
to avoid relational contracts.

1124
00:40:45,680 --> 00:40:48,520
If the output becomes a warehouse table that powers reporting,

1125
00:40:48,520 --> 00:40:50,520
it still needs enforceable shape constraints

1126
00:40:50,520 --> 00:40:53,240
where appropriate and governed access parts.

1127
00:40:53,240 --> 00:40:54,760
Otherwise, you've just moved entropy

1128
00:40:54,760 --> 00:40:56,760
from one compute engine to another.

1129
00:40:56,760 --> 00:40:58,360
This clicked for him during migrations

1130
00:40:58,360 --> 00:41:00,880
where teams replaced a slow SQL transformation

1131
00:41:00,880 --> 00:41:03,080
with a notebook, got a speed up,

1132
00:41:03,080 --> 00:41:04,560
and then watched defects multiply

1133
00:41:04,560 --> 00:41:08,280
because nobody enforced schema and validation at the boundary.

1134
00:41:08,280 --> 00:41:10,760
They optimized runtime and destroyed determinism.

1135
00:41:10,760 --> 00:41:11,760
That is not progressed.

1136
00:41:11,760 --> 00:41:13,880
So how do you know redesign is required?

1137
00:41:13,880 --> 00:41:15,560
If performance depends on heroics.

1138
00:41:15,560 --> 00:41:17,480
If costs scale non-linearly with growth,

1139
00:41:17,480 --> 00:41:20,320
if every incident ends with will add capacity for now,

1140
00:41:20,320 --> 00:41:22,200
if the same logical question produces

1141
00:41:22,200 --> 00:41:23,760
wildly different plan shapes depending

1142
00:41:23,760 --> 00:41:25,280
on which way the query got written.

1143
00:41:25,280 --> 00:41:27,880
Those are structural signals, not tuning signals.

1144
00:41:27,880 --> 00:41:29,040
And the moment you accept that

1145
00:41:29,040 --> 00:41:31,200
the redesign conversation becomes sane,

1146
00:41:31,200 --> 00:41:34,600
you start from access patterns and contracts, not from syntax.

1147
00:41:34,600 --> 00:41:36,400
You design storage to match usage.

1148
00:41:36,400 --> 00:41:38,040
You decide where to pre-compute.

1149
00:41:38,040 --> 00:41:39,280
You decide what to cache.

1150
00:41:39,280 --> 00:41:40,720
You decide what to materialize.

1151
00:41:40,720 --> 00:41:43,280
You decide what the system is allowed to do at query time

1152
00:41:43,280 --> 00:41:45,440
because query tuning is local optimization.

1153
00:41:45,440 --> 00:41:47,680
Structural redesign is governance.

1154
00:41:47,680 --> 00:41:50,800
Statistics plus plan cache, deterministic systems become

1155
00:41:50,800 --> 00:41:53,040
probabilistic when you stop feeding them truth.

1156
00:41:53,040 --> 00:41:55,960
After structural redesign, most teams expect stability

1157
00:41:55,960 --> 00:41:57,200
to be automatic.

1158
00:41:57,200 --> 00:41:59,720
They got the shape right, they partitioned, they indexed,

1159
00:41:59,720 --> 00:42:02,280
they tuned the query, and now they want to believe

1160
00:42:02,280 --> 00:42:04,240
the system will just stay that way.

1161
00:42:04,240 --> 00:42:07,000
It won't because the optimizer isn't a mind reader.

1162
00:42:07,000 --> 00:42:09,080
It's a cost model operating on two inputs.

1163
00:42:09,080 --> 00:42:12,200
Statistics and plan reuse, when those inputs drift,

1164
00:42:12,200 --> 00:42:15,400
your deterministic design becomes probabilistic runtime again.

1165
00:42:15,400 --> 00:42:18,000
Same schema, same query, different behavior.

1166
00:42:18,000 --> 00:42:20,120
That's not mystery, that's starvation.

1167
00:42:20,120 --> 00:42:22,560
Statistics are the optimizer's worldview.

1168
00:42:22,560 --> 00:42:24,720
They are not nice to have metadata.

1169
00:42:24,720 --> 00:42:27,040
They are the engine's estimate of how many rows exist,

1170
00:42:27,040 --> 00:42:28,640
how values are distributed,

1171
00:42:28,640 --> 00:42:30,720
and how selective a predicate will be.

1172
00:42:30,720 --> 00:42:33,120
That estimate drives everything, join choice,

1173
00:42:33,120 --> 00:42:36,400
join order, memory grants, parallelism decisions,

1174
00:42:36,400 --> 00:42:40,480
and whether an index is used at all, no stats, no truth.

1175
00:42:40,480 --> 00:42:42,720
And the first way this breaks an enterprise is painfully

1176
00:42:42,720 --> 00:42:43,560
predictable.

1177
00:42:43,560 --> 00:42:46,520
The data changes, but the optimizer keeps believing

1178
00:42:46,520 --> 00:42:49,440
the old story, a table that used to be evenly distributed

1179
00:42:49,440 --> 00:42:51,960
becomes skewed because one region goes viral,

1180
00:42:51,960 --> 00:42:54,600
one product explodes, one tenant becomes dominant,

1181
00:42:54,600 --> 00:42:57,840
or a backfill inserts six months of history overnight.

1182
00:42:57,840 --> 00:42:59,240
The query text didn't change.

1183
00:42:59,240 --> 00:43:00,200
The data did.

1184
00:43:00,200 --> 00:43:01,560
If the statistics don't reflect that,

1185
00:43:01,560 --> 00:43:04,680
the engine will choose a plan for a world that no longer exists.

1186
00:43:04,680 --> 00:43:07,080
That's how you get plan regressions with no deployment.

1187
00:43:07,080 --> 00:43:09,280
Now, layer in the second component, the plan cache.

1188
00:43:09,280 --> 00:43:10,520
Plan reuse is a feature.

1189
00:43:10,520 --> 00:43:13,360
It saves compilation cost, which is real CPU and latency,

1190
00:43:13,360 --> 00:43:14,640
especially at scale.

1191
00:43:14,640 --> 00:43:16,400
But plan reuse is also the mechanism

1192
00:43:16,400 --> 00:43:19,240
that turns one historical sample into a policy decision

1193
00:43:19,240 --> 00:43:21,040
for the next thousand executions.

1194
00:43:21,040 --> 00:43:23,160
That's why parameter sensitivity exists,

1195
00:43:23,160 --> 00:43:25,520
and it's why even fixed queries can drift back

1196
00:43:25,520 --> 00:43:27,560
into instability when the workload changes.

1197
00:43:27,560 --> 00:43:30,120
The plan cache is where yesterday's assumptions

1198
00:43:30,120 --> 00:43:32,000
become today's incidents.

1199
00:43:32,000 --> 00:43:33,440
Here's the enterprise pattern.

1200
00:43:33,440 --> 00:43:36,440
A workload runs fine for months, then a seasonal shift hits

1201
00:43:36,440 --> 00:43:39,720
or a new integration goes live or a data load pattern changes.

1202
00:43:39,720 --> 00:43:41,280
The cache plan still exists.

1203
00:43:41,280 --> 00:43:43,520
It still gets reused, and now it's wrong in a way

1204
00:43:43,520 --> 00:43:45,320
that only shows up under concurrency.

1205
00:43:45,320 --> 00:43:47,000
The system doesn't fail loudly.

1206
00:43:47,000 --> 00:43:49,520
It just starts consuming more CPU per request.

1207
00:43:49,520 --> 00:43:52,720
Their memory grounds grow, then spills show up, then cures form,

1208
00:43:52,720 --> 00:43:55,000
then someone scales capacity and calls it solved.

1209
00:43:55,000 --> 00:43:55,840
That's not solved.

1210
00:43:55,840 --> 00:43:56,760
That's a tax.

1211
00:43:56,760 --> 00:44:00,160
So governance here is not watch dashboards and hope.

1212
00:44:00,160 --> 00:44:02,360
Governance is feeding the optimizer truth

1213
00:44:02,360 --> 00:44:05,480
and verifying that plan reuse still matches reality.

1214
00:44:05,480 --> 00:44:07,520
This is where monitoring stops being optional.

1215
00:44:07,520 --> 00:44:09,360
You need visibility into what's running,

1216
00:44:09,360 --> 00:44:11,360
what's expensive, and what's changing.

1217
00:44:11,360 --> 00:44:13,560
Dynamic management views and query store concepts

1218
00:44:13,560 --> 00:44:15,360
exist for exactly this reason.

1219
00:44:15,360 --> 00:44:19,000
To identify, frequently executed, expensive queries,

1220
00:44:19,000 --> 00:44:20,560
track plan changes over time,

1221
00:44:20,560 --> 00:44:22,400
and correlate regressions with deployments,

1222
00:44:22,400 --> 00:44:24,560
data shifts, or configuration changes.

1223
00:44:24,560 --> 00:44:26,400
You're not looking for one slow query.

1224
00:44:26,400 --> 00:44:28,720
You're looking for the small set of queries

1225
00:44:28,720 --> 00:44:31,600
that dominate cumulative cost because they run constantly

1226
00:44:31,600 --> 00:44:33,240
and the plans that keep flipping

1227
00:44:33,240 --> 00:44:35,960
because the optimizer can't form a stable decision

1228
00:44:35,960 --> 00:44:37,360
with the information it has.

1229
00:44:37,360 --> 00:44:40,040
And yes, the fix can be as boring as updating statistics.

1230
00:44:40,040 --> 00:44:41,280
And that's why the section matters.

1231
00:44:41,280 --> 00:44:42,640
Teams love heroic tuning.

1232
00:44:42,640 --> 00:44:43,800
They hate maintenance.

1233
00:44:43,800 --> 00:44:45,560
But if your statistics are stale,

1234
00:44:45,560 --> 00:44:47,400
every other decision is compromised.

1235
00:44:47,400 --> 00:44:49,120
If your plan cache is polluted

1236
00:44:49,120 --> 00:44:51,320
with plans built on outdated distributions,

1237
00:44:51,320 --> 00:44:53,000
you'll spend money debugging ghosts.

1238
00:44:53,000 --> 00:44:54,520
So the operational rule is simple.

1239
00:44:54,520 --> 00:44:57,360
Treat statistics updates as policy not as cleanup.

1240
00:44:57,360 --> 00:44:58,640
You don't wait for an incident.

1241
00:44:58,640 --> 00:45:01,160
You define maintenance that keeps the optimizer's worldview

1242
00:45:01,160 --> 00:45:02,640
aligned with the data's reality,

1243
00:45:02,640 --> 00:45:04,680
especially after heavy loads, backfills,

1244
00:45:04,680 --> 00:45:06,480
or major distribution shifts.

1245
00:45:06,480 --> 00:45:08,880
Then you validate with plans and runtime metrics

1246
00:45:08,880 --> 00:45:11,040
that the system state deterministic.

1247
00:45:11,040 --> 00:45:13,480
Because the platform will always drift toward entropy.

1248
00:45:13,480 --> 00:45:15,400
Your job is to keep feeding it truth.

1249
00:45:15,400 --> 00:45:18,240
AI plus co-pilot school, it writes queries, not contracts.

1250
00:45:18,240 --> 00:45:20,520
Now we have to talk about the newest entropy accelerator,

1251
00:45:20,520 --> 00:45:21,720
AI generated school.

1252
00:45:21,720 --> 00:45:23,720
Co-pilot can write a query in five seconds

1253
00:45:23,720 --> 00:45:26,040
that would take a junior analyst an hour.

1254
00:45:26,040 --> 00:45:27,160
That feels like progress.

1255
00:45:27,160 --> 00:45:28,840
It is not automatically progress.

1256
00:45:28,840 --> 00:45:30,400
Because AI optimizes for one thing,

1257
00:45:30,400 --> 00:45:32,440
it returns results that look plausible.

1258
00:45:32,440 --> 00:45:34,880
It does not optimize for determinism under load,

1259
00:45:34,880 --> 00:45:37,560
long-term maintainability, or governance intent.

1260
00:45:37,560 --> 00:45:39,720
AI accelerates mistakes at scale.

1261
00:45:39,720 --> 00:45:41,800
And the uncomfortable part is that most teams

1262
00:45:41,800 --> 00:45:43,840
won't notice until the bill, the incident,

1263
00:45:43,840 --> 00:45:46,200
or the order derives, a bad query doesn't always fail.

1264
00:45:46,200 --> 00:45:48,800
It just quietly consumes more CPU than it should,

1265
00:45:48,800 --> 00:45:50,840
or returns subtly incorrect joins,

1266
00:45:50,840 --> 00:45:54,040
or introduces a new definition of truth that nobody reviewed.

1267
00:45:54,040 --> 00:45:56,920
So the rule for AI in enterprise data systems is simple.

1268
00:45:56,920 --> 00:45:58,760
AI can draft queries.

1269
00:45:58,760 --> 00:46:00,720
Humans must enforce contracts.

1270
00:46:00,720 --> 00:46:03,160
A contract means at least three things in this context,

1271
00:46:03,160 --> 00:46:04,400
shape, access, and cost.

1272
00:46:04,400 --> 00:46:07,000
Shape means the query respects explicit schemas,

1273
00:46:07,000 --> 00:46:09,680
correct data types, and defined business rules.

1274
00:46:09,680 --> 00:46:11,160
If a column is supposed to be numeric,

1275
00:46:11,160 --> 00:46:13,640
you don't handle the dirty strings in the report.

1276
00:46:13,640 --> 00:46:15,760
You fix the pipeline or quarantine the rows.

1277
00:46:15,760 --> 00:46:18,000
If a relationship is supposed to be one to many,

1278
00:46:18,000 --> 00:46:19,960
you don't let an accidental joint duplicate rows

1279
00:46:19,960 --> 00:46:21,440
and call it more granular.

1280
00:46:21,440 --> 00:46:23,400
Access means the query only touches

1281
00:46:23,400 --> 00:46:25,280
what the caller is allowed to see.

1282
00:46:25,280 --> 00:46:27,400
AI will happily select from a base table

1283
00:46:27,400 --> 00:46:28,280
because it exists.

1284
00:46:28,280 --> 00:46:30,880
It doesn't know that the base table contains sensitive columns,

1285
00:46:30,880 --> 00:46:33,720
and the consumer is supposed to use a masked view.

1286
00:46:33,720 --> 00:46:34,760
That's not malicious.

1287
00:46:34,760 --> 00:46:36,080
It's ignorant.

1288
00:46:36,080 --> 00:46:38,560
Cost means the query has an acceptable plan shape.

1289
00:46:38,560 --> 00:46:39,760
And this is where the governance minds

1290
00:46:39,760 --> 00:46:43,240
that you already built becomes the filter for AI output.

1291
00:46:43,240 --> 00:46:46,160
The most common AI failure mode is non-sargable predicates.

1292
00:46:46,160 --> 00:46:49,480
AI loves writing helpful expressions in ware clauses,

1293
00:46:49,480 --> 00:46:51,280
wrapping date columns and functions,

1294
00:46:51,280 --> 00:46:54,600
doing implicit conversions or comparing unlike types.

1295
00:46:54,600 --> 00:46:57,240
The query still returns the right rows, so everyone ships it.

1296
00:46:57,240 --> 00:46:59,640
But the optimizer can't use indexes effectively.

1297
00:46:59,640 --> 00:47:01,800
Suddenly you have scans, sorts, and spills

1298
00:47:01,800 --> 00:47:03,240
that scale with data growth.

1299
00:47:03,240 --> 00:47:05,440
Another AI favorite is unnecessary joints.

1300
00:47:05,440 --> 00:47:07,400
The model sees table names that look relevant

1301
00:47:07,400 --> 00:47:08,640
and glues them together.

1302
00:47:08,640 --> 00:47:09,960
The result set looks rich.

1303
00:47:09,960 --> 00:47:11,280
It's also multiplied.

1304
00:47:11,280 --> 00:47:13,560
One accidental joint to a non-unique dimension

1305
00:47:13,560 --> 00:47:16,080
can inflate row counts by orders of magnitude,

1306
00:47:16,080 --> 00:47:19,200
and then every aggregate becomes wrong unless you detect it.

1307
00:47:19,200 --> 00:47:21,400
And of course, there's the classic select star.

1308
00:47:21,400 --> 00:47:22,440
It's convenient.

1309
00:47:22,440 --> 00:47:24,360
It's also a silent contract violation.

1310
00:47:24,360 --> 00:47:26,600
Your exporting columns, you didn't intend to export,

1311
00:47:26,600 --> 00:47:28,800
including future columns you haven't even added yet.

1312
00:47:28,800 --> 00:47:31,080
That's how we didn't know that field was exposed

1313
00:47:31,080 --> 00:47:32,480
becomes an audit conversation.

1314
00:47:32,480 --> 00:47:35,120
So how do you use AI without letting it turn your warehouse

1315
00:47:35,120 --> 00:47:35,920
into a casino?

1316
00:47:35,920 --> 00:47:37,400
You said acceptance criteria.

1317
00:47:37,400 --> 00:47:38,320
Not does it work.

1318
00:47:38,320 --> 00:47:39,920
Does it have an acceptable plan?

1319
00:47:39,920 --> 00:47:41,680
Use the plan as the gate.

1320
00:47:41,680 --> 00:47:44,600
Estimated or actual, depending on environment and risk.

1321
00:47:44,600 --> 00:47:46,120
But the principle stays the same.

1322
00:47:46,120 --> 00:47:47,800
The plan is the truth source.

1323
00:47:47,800 --> 00:47:50,440
Here are the rejection signals you enforce immediately.

1324
00:47:50,440 --> 00:47:52,560
First, scan rows versus return rows.

1325
00:47:52,560 --> 00:47:54,480
If the gap is massive, you reject it,

1326
00:47:54,480 --> 00:47:57,160
not because you love tuning, because you refuse to approve

1327
00:47:57,160 --> 00:47:59,480
an IO bill disguised as a query.

1328
00:47:59,480 --> 00:48:03,080
Second, full scans on large tables with no bounded predicate.

1329
00:48:03,080 --> 00:48:05,640
If the query doesn't constrain time, tenant, customer,

1330
00:48:05,640 --> 00:48:07,680
or partition key, you reject it.

1331
00:48:07,680 --> 00:48:09,440
That query is a future incident.

1332
00:48:09,440 --> 00:48:11,280
Third, expensive sorts and spills.

1333
00:48:11,280 --> 00:48:13,520
If the plan shows large sorts memory pressure

1334
00:48:13,520 --> 00:48:15,680
or spill warnings, you reject it

1335
00:48:15,680 --> 00:48:18,040
until the query shape and indexes are justified.

1336
00:48:18,040 --> 00:48:20,760
Under concurrency, that becomes capacity pain.

1337
00:48:20,760 --> 00:48:23,560
Fourth, joins that change cardinality unexpectedly.

1338
00:48:23,560 --> 00:48:25,200
If the joint keys aren't explicit,

1339
00:48:25,200 --> 00:48:28,320
if uniqueness assumptions aren't proven, you reject it.

1340
00:48:28,320 --> 00:48:31,120
You don't allow, maybe it's fine, joins in production.

1341
00:48:31,120 --> 00:48:32,360
Now the safe usage boundary.

1342
00:48:32,360 --> 00:48:34,640
Let AI write the first draft, then you rewrite it

1343
00:48:34,640 --> 00:48:36,000
into a contract surface.

1344
00:48:36,000 --> 00:48:39,240
A view, a stored procedure, a validated transformation

1345
00:48:39,240 --> 00:48:41,440
step with explicit columns, explicit predicates,

1346
00:48:41,440 --> 00:48:43,600
and permissions that match the consumer.

1347
00:48:43,600 --> 00:48:46,160
And you store the plan behavior as part of the review.

1348
00:48:46,160 --> 00:48:48,920
This is the acceptable plan shape for this workload.

1349
00:48:48,920 --> 00:48:51,600
That's the only way this scales, because if AI can create

1350
00:48:51,600 --> 00:48:54,600
SQL instantly, your system can accumulate bad SQL

1351
00:48:54,600 --> 00:48:55,480
instantly.

1352
00:48:55,480 --> 00:48:58,280
And once that happens, the platform becomes probabilistic,

1353
00:48:58,280 --> 00:49:01,120
cost, performance, and truth all drift.

1354
00:49:01,120 --> 00:49:03,640
So the promise of AI isn't that it replaces T-School.

1355
00:49:03,640 --> 00:49:05,960
The promise is that it forces you to treat T-School

1356
00:49:05,960 --> 00:49:08,880
like governance, not like typing C.

1357
00:49:08,880 --> 00:49:11,800
The operating model, T-Sql as control surface.

1358
00:49:11,800 --> 00:49:13,880
Here's the operating model, and it's the only one

1359
00:49:13,880 --> 00:49:16,120
that survives contact with a real enterprise.

1360
00:49:16,120 --> 00:49:18,440
The problem is never that you can't query data.

1361
00:49:18,440 --> 00:49:20,800
The problem is that modern data platforms make it easy

1362
00:49:20,800 --> 00:49:24,000
to ship ambiguity, ambiguous schemers, ambiguous definitions,

1363
00:49:24,000 --> 00:49:26,680
ambiguous permissions, and ambiguous cost behavior.

1364
00:49:26,680 --> 00:49:28,440
And ambiguity doesn't stay contained.

1365
00:49:28,440 --> 00:49:31,280
It leaks outward into reports, apps, pipelines,

1366
00:49:31,280 --> 00:49:33,760
and eventually into incident tickets and audit findings.

1367
00:49:33,760 --> 00:49:35,240
The reason the current approach fails

1368
00:49:35,240 --> 00:49:37,880
is because most teams treat SQL like a retrieval tool

1369
00:49:37,880 --> 00:49:39,760
and governance like a separate discipline.

1370
00:49:39,760 --> 00:49:42,600
They build modern stacks that move data quickly,

1371
00:49:42,600 --> 00:49:44,600
but they don't enforce intent at the boundary

1372
00:49:44,600 --> 00:49:45,400
where it matters.

1373
00:49:45,400 --> 00:49:47,560
Then the platform behaves exactly as designed.

1374
00:49:47,560 --> 00:49:50,320
It executes whatever you ask for, not what you meant.

1375
00:49:50,320 --> 00:49:53,520
The better method is to treat T-Sql as a control surface,

1376
00:49:53,520 --> 00:49:56,040
a place where you encode intent into enforcement,

1377
00:49:56,040 --> 00:49:58,040
so the system can't drift into randomness

1378
00:49:58,040 --> 00:49:59,080
without tripping alarms.

1379
00:49:59,080 --> 00:50:00,520
If you remember nothing else, remember

1380
00:50:00,520 --> 00:50:01,760
these three invariants--

1381
00:50:01,760 --> 00:50:05,200
enforce shape, enforce access, enforce cost.

1382
00:50:05,200 --> 00:50:07,200
Enforce shape means you stop accepting,

1383
00:50:07,200 --> 00:50:08,800
will fix it downstream.

1384
00:50:08,800 --> 00:50:10,760
In practice, that means explicit schemas,

1385
00:50:10,760 --> 00:50:13,840
typed columns, and constraints where business invariants exist.

1386
00:50:13,840 --> 00:50:16,600
It means validation gates that quarantine bad rows,

1387
00:50:16,600 --> 00:50:19,040
instead of spreading them across every consumer.

1388
00:50:19,040 --> 00:50:21,680
When shape is enforced, the warehouse stops being a rumor mill

1389
00:50:21,680 --> 00:50:23,120
and becomes a contract.

1390
00:50:23,120 --> 00:50:25,120
Enforce access means you stop confusing,

1391
00:50:25,120 --> 00:50:28,040
can open the workspace with, can read the table.

1392
00:50:28,040 --> 00:50:32,000
You use schemas, roles, and control surfaces, views,

1393
00:50:32,000 --> 00:50:35,160
and stored procedures to make the safe path the easy path.

1394
00:50:35,160 --> 00:50:38,080
You assign identities to roles and roles to objects,

1395
00:50:38,080 --> 00:50:40,240
and you make broad access painful by design.

1396
00:50:40,240 --> 00:50:42,520
Lease privilege becomes a configuration reality,

1397
00:50:42,520 --> 00:50:43,880
not a training outcome.

1398
00:50:43,880 --> 00:50:46,240
Enforce cost means you stop treating performance

1399
00:50:46,240 --> 00:50:48,480
as a late stage tuning activity.

1400
00:50:48,480 --> 00:50:51,120
You treat execution plans as risk signals,

1401
00:50:51,120 --> 00:50:54,880
scanned versus returned rows, scans on large tables,

1402
00:50:54,880 --> 00:50:57,920
expensive sorts, spills, unstable plan shapes

1403
00:50:57,920 --> 00:50:59,600
under parameter variability.

1404
00:50:59,600 --> 00:51:03,560
Cost predictability is not a nice to have in cloud platforms.

1405
00:51:03,560 --> 00:51:05,160
It's the difference between a stable service

1406
00:51:05,160 --> 00:51:06,480
and a weekly fire drill.

1407
00:51:06,480 --> 00:51:07,760
Now here's the uncomfortable truth,

1408
00:51:07,760 --> 00:51:08,880
where people regress.

1409
00:51:08,880 --> 00:51:10,320
They regress at the first exception.

1410
00:51:10,320 --> 00:51:12,920
A pipeline fails, so someone grants more access.

1411
00:51:12,920 --> 00:51:15,080
A report breaks, so someone patches the DAX,

1412
00:51:15,080 --> 00:51:17,640
a query runs slow, so someone scales capacity.

1413
00:51:17,640 --> 00:51:20,680
A copilot generated query works, so someone deploys it.

1414
00:51:20,680 --> 00:51:22,760
These are not fixes, they are entropy generators.

1415
00:51:22,760 --> 00:51:25,600
An entropy generators accumulate, they don't announce themselves,

1416
00:51:25,600 --> 00:51:28,400
they just change your system from deterministic to probabilistic,

1417
00:51:28,400 --> 00:51:30,720
one temporary shortcut at a time.

1418
00:51:30,720 --> 00:51:32,840
So the practical checklist mindset is this.

1419
00:51:32,840 --> 00:51:35,360
Every change must declare intent at the data layer.

1420
00:51:35,360 --> 00:51:38,840
If you add a new source, declare it schema and validation expectations.

1421
00:51:38,840 --> 00:51:42,040
If you add a new consumer, declare it's access boundary.

1422
00:51:42,040 --> 00:51:45,800
If you add a new query pattern, declare it's acceptable plan shape.

1423
00:51:45,800 --> 00:51:48,360
If you can't declare those things, you don't have a design.

1424
00:51:48,360 --> 00:51:49,160
You have hope.

1425
00:51:49,160 --> 00:51:51,600
Fabric and synapse give you powerful abstractions.

1426
00:51:51,600 --> 00:51:53,680
Lake houses, warehouses, notebooks, pipelines,

1427
00:51:53,680 --> 00:51:54,960
direct lake, all of it.

1428
00:51:54,960 --> 00:51:57,160
Useful, but abstraction is not governance.

1429
00:51:57,160 --> 00:51:59,520
Architecturally, every layer you add increases

1430
00:51:59,520 --> 00:52:01,600
the number of places truth can drift.

1431
00:52:01,600 --> 00:52:03,640
So the control plane has to be enforced somewhere

1432
00:52:03,640 --> 00:52:07,720
in these ecosystems that somewhere is still the relational engine

1433
00:52:07,720 --> 00:52:11,200
and the T-SQL surface area that defines what the system is allowed to do.

1434
00:52:11,200 --> 00:52:13,320
That's why this episode is titled Beyond Select.

1435
00:52:13,320 --> 00:52:14,600
Select is the easy part.

1436
00:52:14,600 --> 00:52:17,680
The contract is the part that keeps your system from decaying.

1437
00:52:17,680 --> 00:52:19,400
T-School isn't a querying language.

1438
00:52:19,400 --> 00:52:22,880
It's the relational contract that keeps fabric era systems deterministic

1439
00:52:22,880 --> 00:52:26,280
instead of drifting into entropy, costs, bikes, and security debt.

1440
00:52:26,280 --> 00:52:29,640
If you want the next layer, watch the episode on reading execution plans

1441
00:52:29,640 --> 00:52:30,840
as risk signals.

1442
00:52:30,840 --> 00:52:33,840
Subscribe, this channel assumes platforms decay unless governed.