Shadowing Practice: Uber Data Engineering Mock Interview - Ride-Sharing Data Warehouse Schema - Learn English Speaking with Video

is designing a data model for a ride -sharing service app.

Hello, everyone.

Welcome to another data engineering mock interview with Exponent.

Today, we are going to talk a little bit about data modeling with our guest, Kajal.

Thank you so much for the job being here, Kajal.

Would you take a moment to introduce yourself to our viewers?

Yeah.

Hey, my name is Kajal.

I work as a data engineer in Uber.

I've been working in that company for a

while we take care of everything from requirement gathering to data modeling to detail design

and then finally the implementation part of it

and of course the data modeling plays a very very key role

when it comes to the pre and the proposed

because eventually you want your users to be able to query your data in the most optimized way.

And data design is actually the core of it.

If your design is not accurate, the query performance will never be accurate at the end of the day, actually.

Exactly.

So we are going to do one such data model today.

If you're enjoying this video, you can watch dozens more of videos like this at tryexponent .com.

Check out our real interview questions with full written solutions.

Over a million people use Exponent to ace their interviews in product management, software engineering, engineering management, machine learning, data science, and much, much more.

Get started for free at tryexponent .com.

Let's just jump right into our question for today, which is designing a data model for a ride -sharing service app.

So we are creating a ride -sharing app.

So my understanding of this ride -sharing app is

that we want to build data model for basically a service app which allows the users to book gaps via the app.

And basically the driver comes, picks that user, and then drops them to a desired location, right?

That is the expectation is, right?

From this, I have three outstanding questions actually.

What are the use cases?

Second is, who are the users?

What are the impact of these use cases?

Yes, let's tackle it by one.

What are the use cases?

My use case is twofold.

One is going to be more operational specific.

I would like to understand the driver conversion rate.

By driver conversion rate, I'm meaning optimizing the driver onboarding process from sign up through to the activation.

So as you can imagine, there could be a lot of steps in between.

So I would like to know how we can optimize the driver onboarding.

The second is more trip specific week i should be able

to understand the trip requested how many the trip completion rates uh for the user uh

and you can spice it up with more additional details if you want

user actually makes sense okay these are the use cases

and who will be the users for these okay

so the users could be

so for like i said the operational user operational metrics would

be would be useful for you know data used by data analysts machine learning engineers

or operational personnels as well or even for business optimization purposes to

get the driver onboarding time looking at data analysis right data analytics yes okay

and uh could you share the impact like what would you

do what would be the impact of these use cases

so as to um like once you build these data models

and then how yes uh yes that's that's a good question

so the the immediate impact

which i could think of is like a business for the first type of use cases is a business optimization

so i would like to get the driver onboarding time lesser uh the more metrics

and the more pain points we could understand uh the better.

The second is more trip related data that can directly contribute to the profits or improving the business,

understanding where the trips are going well

and more importantly where it's not going well and any other analytical points that we could gather.

basically cost opportunity analysis awesome from these use cases i thing we want to build a data warehouse data model

so I think first would I would like to call out the entities

that I would be building upon the data model first will

be the driver itself right second will be the user I think third I can call out offers

and then we can have trips and

if we have a driver i think we will need vehicle also there could be a partner associated to vehicle

and driver then we can have ratings essentially this will be the feedback which we share

based on this and then of course the most important part of it the payments i think

if you there is every we all work for money

so one quick question sorry the what about the offer seems

to be very interesting to me could you explain what it is sure sure

so essentially what my understanding of a trip is

that um whenever i as a user try to book a ride right

there is a request that goes to each of the drivers

and then once they accept it then only it becomes a trip

so essentially it would be an offer first to the driver

and once they accept it then only it becomes a trip before

that it doesn't make sense to include all of

that request data into a trip

so okay gotcha nice yeah um should i start with the um basically design of how my dimensions

and facts would look like yeah go for it yeah

so firstly i would like to call out my dimensions right

so the first dimension that i would want to look like

start off with this driver itself

so in this case my driver uuid will be the key itself driver uuid is nothing

but the unique identifier of the driver

and then we can take up sign up sign up city

maybe basically this will help us with the demographics of the driver

and then sign up city

and then we can have sign up times time then essentially

now the whole the we can also start with the onboarding columns for example

which will help us with the conversion rates so for example

when we whenever i am assuming whenever we onboard try to onboard the driver we start with pgc

that is the background check

and then uh not background check maybe first we will look for some documents maybe uh pre -start off documents

and then maybe we'll go for background check and then the final documentation

and then uh if everything goes out right uh everything gets approve the the driver gets activated right

100

so i would love to include all of those columns also into this

101

that is bgc approval time then i would actually then first

102

go with the first bgc approval time then first oh i'm sorry first document approval time then uh

103

and then first activation time first first activation time time

104

and then actually i would want to have latest timestamp of

105

each also then we can have all of this in the last

106

and then the current status of the driver

107

so essentially he could be currently active ineligible it could actually be helpful to analyze

108

that like if the driver was active like some time ago uh

109

and he's ineligible now it can also help us understand like uh

110

when he became last active and then

111

if he's inactive then uh it can help the um users

112

to deep dive on why is it happening now um should i move ahead

113

or do you have any further questions on why i made choices

114

so uh you are capturing different times here that is very interesting um

115

so could you explain why you would want to do

116

that in this in this dimension opposed to using a different tables or anything Okay,

117

so the whole idea behind the driver dimension was that this dimension should be,

118

basically it's a, we can call it a type 1 dimension

119

because we are not maintaining sort of history of like of the driver itself.

120

Driver UUID is the key, so it's just simply the same record will be updated.

121

The whole idea behind having this is that when we also look at the conversion rate, so for example, from BGC to DOC approval to activation finally,

122

we also would want to eventually see,

123

that's my observation from my previous experiences is that they can also become ineligible eventually and then become again active, right?

124

So, in those cases, you will see a differentiator

125

or there could be basically a case wherein the driver tries to become active by re -uploading a document,

126

but they become ineligible.

127

So, we want to also capture that information.

128

Maybe today we need activation, but in future we might need inactivation, reactivation rates

129

so these all those things will will be very helpful uh

130

when we capture this kind of yeah makes sense yeah yeah

131

that's good that's good the type one type one is really interesting

132

because we don't really have to maintain history for that that's a that's a good type yeah

133

and the second call out here is that um i think uh what i wanted to call is is that

134

having history is also a kind of a burden on the user

135

if in in case we are very much clear on what

136

the requirement is for me the requirement is very clear

137

that we want to look at the activation rate itself uh

138

and i just don't want to burden the users to keep

139

on aggregating the data based on the timestamps okay yeah

140

and moving on to the next uh thing then in this case i think there is some information

141

that i would keep which will be kind of replicated

142

that is the user uid which will be the key okay the reason

143

that i'm using the user uuid user not the rider

144

because um maybe a rider would be someone who has actually signed up already

145

but there could be a case where without signing up they

146

are just requesting rights right we don't want to lose out an opportunity to earn

147

so it's better to refer to the user instead of rider

148

and then the current status of the user The whole idea behind this is

149

that we also want to capture users who maybe we want to blacklist for their certain behaviors

150

or how they are behaving over a period of time.

151

Maybe we want to blacklist them or make them in an eligible for whatsoever possible.

152

So, yeah, we would also want to capture the status of the user.

153

This will leverage the platform itself to see the user behavior as well.

154

then uh we will have so can this user be both a rider

155

and uh and a driver right it can like it's kind of like a no

156

or it's like no no there is a user itself it can only be a rider the whole idea is

157

that um i want to i always wanted to keep these

158

both things separate even though there are some similarities uh

159

because they are different entities all together and i assume

160

that the users of the data that is the ml

161

and they will have separate use cases

162

and yeah it is better to keep the data placed yeah okay like

163

that yeah then we have vehicle duty

164

and then status of the vehicle essentially i want to keep the status of the vehicle also

165

because possibly the vehicle itself is not um sorry the vehicle itself is ineligible

166

because of maybe expired documents of the vehicle itself itself right

167

and the if

168

and in those cases we do not want to allow those driver to be making trips

169

if they are if their vehicles are uh not available with correct correct documentation

170

or they have expired documents yeah then we have partner um in this case partner would be like any fleet partner,

171

maybe it could be owning a vehicle or it could be a company which is providing drivers to the platform itself.

172

So in those cases, we can have a different partner entity and then the status associated.

173

Then at next, we can have our offers. In this case.

174

So in this case, yeah, of course, like the partner UUID will be the unique key or primary key we can say.

175

Maple UUID will be the primary key and then in case of offers what I would like to see

176

what we have discussed in now right offer is something

177

that we request to the driver and once

178

that offer is converted then it makes to a trip right

179

so we will have a key column called is offer uuid unique okay uh and then trip uuid

180

so this will also be unique actually because

181

when offer can only be converted into a one trip uh

182

it cannot one offer cannot can never convert to multiple trips

183

so that's the whole idea behind it is that

184

and then offer timestamp maybe right timestamp start uh okay yeah start i think

185

that is something like maybe we can it can eventually be used to see

186

that when was the offer made and finally

187

when it got converted to a trip right these kind of

188

matrices also can be eventually helpful of uh to see how

189

much is the wait time for the owner right from the

190

offered point to the time they actually get the right

191

so then we have trip right

192

so of course it will be like a foreign key for

193

with the offers then we have trip uuid then of course

194

this will be a unique key for this table okay uh

195

till now we have only seen the dimensions uh coming back

196

to how the fact will look like i i believe

197

that uh it's the best to keep um trip as facts

198

because uh trip will also can um actually include the things

199

that uh what would be the earnings

200

and all of those things as part of the trip itself like what was a fair

201

and then whether they got any uh additional incentives with for the trip itself right

202

so it would be great that if we include it as a fact So trip UUID, trip, who was the driver involved in the trip,

203

driver UUID, then we can have, of course, the user UUID,

204

and of course, these will also be the foreign key for the user

205

and the driver rate

206

and then we will have trip start time stamp trip

207

and time stamp and then you can have trip start tip trip

208

and the fare that was shared to the user for this

209

and then maybe any additional um incentives that were given to the earner for the trip

210

by the platform itself maybe to incentivize them

211

if they're maybe a new driver or something like that

212

and then should we include anything else hmm i think this

213

looks good to me as of now right um then then finally i think uh we can have payments

214

and rating right so okay then we can have um right okay so with the trip i think ideally we can have

215

payment uid which will actually act as the foreign key for the payment and then we can have

216

payment uid which will be the primary key here right and then actually the payment amount

217

right essentially what was the amount actually paid by the uh

218

driver sorry uh payment found essentially paid by the user okay um

219

and then payment method

220

and then we can have payment type right so okay

221

and then we went down and then who paid it user id right so essentially we can this is this

222

this can actually help us eventually understand

223

that given the geolocations where the platform is earning the most right like where we are getting the highest traction

224

or highest number of rides is one thing maybe you are getting more rides in one geolocation

225

but the payment actually is more because the people are actually traveling more distances hence leading to higher payment amount

226

Kind of touches on our second use case as well where we wanted to do the trip rejections or trip completions.

227

You have a geo -related analysis would be helpful there.

228

And then we have the ratings.

229

Yep.

230

So what I want to do is

231

that associate rating to a trip

232

because a driver rates a user for a trip a user trades a driver for a trip

233

so i want to keep it as one entity

234

and essentially it can help us eventually do the analysis uh

235

in a better way wherein we want to also see um

236

for a given trip uh what the experience has been uh for the user as well as the driver

237

which can actually

238

help us with the cost cost increase our cost opportunities eventually

239

if we look at feedbacks properly from the users or the drivers also so yeah so driver rating user and then user

240

rating driver and then maybe complement if for driver if there is a specific complement that

241

is given by the user to the driver maybe comment or driver and then maybe we can also see

242

uh complement for user right essentially maybe if there is a feedback by the sorry

243

complement for and then maybe we can have complement for sorry comment for user sorry comment

244

right so these are i think the basic and also we would also want to see if

245

the experience for the user has been so good

246

that they have actually went ahead

247

and given a tip right a tip could be directly associated to the rating the better the rating i think

248

if there's a tip involved

249

and of course the experience has been tremendously good for the right so

250

so should we also have a rating uid here for the

251

rating table actually the thing is initially i was not thinking of having a rating uid as such because

252

that will actually be a simple lookup table wherein uh rating

253

uuid would have just like ratings from one to five uh eventually i don't see

254

that changing um i feel that it is going to be something very static what

255

that is my observation do you think otherwise that's why i was not not about the rating

256

so yeah the rating is a column the rating uid what

257

i'm what i was referring to is more of a key for this table oh awesome actually good

258

because the trip by trip

259

so you will have a same i mean the whatever we

260

qualify a driver rating as a separate rating rating id

261

and then a user rating as a separate rating id it will have two different columns

262

but the trip would be same it's right

263

so for the same trip both people both may rate at

264

a different point in time is what i'm like what i'm is

265

that the not sure if that is your understanding as well

266

but that's what i was understanding so curious whether we should have

267

that information as well yeah actually i think is the whole idea behind it was

268

that again this was supposed to be a type one dimension table within in this for the same trip

269

if there is an update for example driver gave the rating to the uh user on day one itself

270

and user is giving rating to the driver or maybe on the 10th day, the data should be updated in this table itself, making the trip with a unique key, right?

271

Okay.

272

Yeah, that's what idea I had it in my mind.

273

Okay.

274

Yeah, if I would have actually planned to do it in a different way, wherein with the trip, I want to make rating UUID,

275

then I can implement it as a key value kind of format, wherein a key could be driver rating user and then the value with it.

276

But in this case, I'm having it as a separate column itself um i think

277

i can um thank you for calling this out it's a good point actually

278

but in this case i'm making this decision to have one row for each trip uh

279

so i think i can live with this kind of yeah yeah i think that i have included all the entities possible

280

I think I should be able to include all the use cases

281

and I've also tried to foresee what future use cases could come and design the data model according to that.

282

Does this look good or do you have any?

283

Yeah, it looks good.

284

I think we are on time.

285

Yeah, let's move on to the next section okay uh

286

so given the data model

287

and uh we would also be of course the ultimate goal is the query capability right um

288

so given the case first is the conversion rate driver conversion rate we also we want to see like uh

289

and given um from the sign of like how much time

290

they are actually taking uh uh from sign up to activation rate right

291

so in this case we can simply go ahead

292

and refer to the driver table and actually do a check where in from the signup,

293

how much time they are actually taking to basically...

294

So this is a high level logic that I'm giving.

295

It's kind of a solo code.

296

Yeah, okay and then this I can call the time actually I can call it the BGC

297

BGC rate because

298

if it is taking more than 10 -15 days then of

299

course there is something we would want to look at in the same way sign up to

300

of course like dogs approval because these things can happen in parallel dogs approval

301

and same way we can call it as talk

302

wait then eventually yes sign up to actual activation time right activation time

303

and then it could be activation rate.

304

This will actually give us the high -level overview of how the sign up to activation rate looks like.

305

Then also, Then we would also want to look at the TRIP request to completion rate.

306

In this case, I think I would like to look at two entities at least.

307

That is the user, offer, and then another third entity,

308

which is the TRIP because we want to see how many offers are actually getting converted to TRIP right?

309

Yeah.

310

Yeah.

311

So second form first trip because no,

312

actually the offer then left will be kind

313

of left join with trip right offer dot trip uuid

314

which is true um trip dot trip uuid

315

and then of course i would also want to join trip with the user to avoid any blacklisted users right

316

Then we should directly have left train user.

317

where the status of the user is actually active,

318

essentially, the whole idea is that we want to avoid blacklisted ones, because then it could actually go ahead and impact our metrics.

319

and then we want to see by the user, right?

320

Then we will have our key as user UUID, which will come.

321

Okay.

322

Actually, no, the user UUID should come from the for itself.

323

And then, oh, I see.

324

Sorry for the miss from my side.

325

It should be the user UID also here because that is the person who is making the request here.

326

So offer .userUID, and then maybe account of offer.

327

Maybe I just want to put a case where there is when trip .uservil .id is not null.

328

If not, now only then,

329

then trip .userUID, else should end and then give me those count only.

330

And then, okay, okay.

331

If I just simply want a percentage, I think I would rather expect to put the offer in the denominator

332

and then this could actually give me a percentage of

333

of the given user what is the percentage of offers to

334

trip conversions yep that's good I think the left joint does the trick here

335

when you have the trip ID so

336

when the offer is not converted into the trip then the

337

trip you are not populating the trip UID in the offer table

338

so that helps you in this college that's good uh i think i think this one we can extend even

339

to not specific to user id uh if we want to uh get metrics of how many offers overall were converted

340

into uh trips and then how many were not that is another use case that we can do yeah correct

341

yes make sense yeah that's good and like say uh

342

if uh just yeah just um just sort of curiosity

343

if you want to

344

if i want to say for example uh you have uh you are you're getting offered

345

and trips um and then i would want to include some uh geo which which i think your the your offer table

346

currently do not have or maybe

347

or maybe i'm missed it uh do you have an option to for example from the location wise

348

if we want to find out how many offers we have got from each location

349

or which has uh got a higher number of trip conversion

350

that is also a use case

351

that we can look at for profitability yeah yeah it's actually something

352

that has been missed here there should be another entity which

353

which i'm actually realized that in between the interview itself

354

that there should be a location entity as well wherein each of the trip can be tied to a location

355

and then based on the geolocation also we can make where the density of the trips is higher

356

and where the opportunity earning opportunities are higher yes of course

357

that should be that be really helpful

358

if you can include the location entities amazing that's good that's

359

good i think this is a good time to pause uh probably um uh

360

if you could share you know given if you

361

if you are an interviewer and then if you could share what went well

362

and what would have would you have done differently

363

so i think what went well was that uh in the start itself yourself we clarified the exact requirements, right?

364

And once we had clarified the requirements,

365

that played the biggest role in helping us design the data model itself because once you know the use case,

366

everything eventually falls right into place because from the use case itself, we were able to identify that we are looking at driver entity, we are looking at trips,

367

We are also looking at users, right?

368

And then if it's a request, then, of course, offers.

369

So I think from the start itself, we had the clarity on however data model will fall into place given the requirements.

370

I think that went well uh

371

and also what what i really like in the interview is

372

that it it is always a collaborative effort uh between the interviewer

373

and the candidate itself which i i think

374

which went well between us since you were always available for the feedbacks

375

and i think

376

which since the feedback was always on time i was able

377

to work on my data model better what i could have improved on

378

is something yeah which which was later called out by you is

379

that a geolocation is actually one of the very key factors

380

when we come to the ride sharing app

381

and i think even though it was not mentioned in the

382

use cases uh this is one of the common asks

383

that once we have clarified the requirements that location is something

384

that which can be looked on next i think we could have foreseen

385

that requirement uh

386

and then build my data model on top of it accordingly

387

you did really well i mean i think um uh the thing uh

388

that stand that stood out is like we're able to uh take the use cases scope it out

389

and then like the icing on the cake was you finished

390

out with the query as well to exemplify how the analytics look like thank you

391

so much for joining us Kajal

392

and thank you everyone to everyone watching until next time good luck on your next interview and thanks for watching

📺 Mismo canal

✨ Recomendar Video

100 oraciones

How to Answer Situational Questions for Product (PM) Behavioral Interview | Framework + Solution

University StudentsBusiness English+2

356 oraciones

Concurrency in Low-level Design Interviews w/ Staff Engineer

University StudentsBusiness English+2

50 oraciones

0.0.1 How to become a CAE Engineer CAE | What are the ways to become a CAE Engineer as a fresher

University StudentsBusiness English+1

231 oraciones

Redis Crash Course - the What, Why and How to use Redis as your primary database

University StudentsBusiness English+1

43 oraciones

Business Partner B2 Unit 4.1 Alternative video

University StudentsBusiness English+1

120 oraciones

Learn Data Modeling in 8 minutes: Dimensional Data Modeling, Data Vault, and One Big Table

University StudentsBusiness English+1

239 oraciones

Learn how to SOLVE a data analytics case study problem

University StudentsBusiness English+2

154 oraciones

Manual Testing: QA Interview Preparation Questions and Answers

University StudentsBusiness English+2

4 oraciones

What is Event Driven Architecture (EDA)?

University StudentsBusiness English+1

315 oraciones

Azure App Service and Virtual Network Integration Options

University StudentsBusiness English+1

¿Qué es la Técnica de Shadowing?

Shadowing es una técnica de aprendizaje de idiomas respaldada por la ciencia, desarrollada originalmente para la formación de intérpretes profesionales y popularizada por el políglota Dr. Alexander Arguelles. El método es simple pero poderoso: escuchas audio en inglés nativo y lo repites en voz alta de inmediato, como una sombra que sigue al hablante con solo 1-2 segundos de retraso. A diferencia de la escucha pasiva o los ejercicios de gramática, el shadowing obliga a tu cerebro y músculos de la boca a procesar y reproducir simultáneamente patrones de habla reales. Las investigaciones muestran que mejora significativamente la precisión de la pronunciación, la entonación, el ritmo, el habla conectada, la comprensión auditiva y la fluidez al hablar, convirtiéndola en una de las metodologías más efectivas para la preparación del IELTS Speaking y la comunicación en inglés en el mundo real.

Técnica de shadowing: lee la guía completa paso a paso →

Shadowing Practice: Uber Data Engineering Mock Interview - Ride-Sharing Data Warehouse Schema - Learn English Speaking with Video

FULL Data Science Mock Interview | SQL, Statistics, Business

What is an API?

System Design Mock Interview: Design Facebook Messenger

How to Answer System Design Interview Questions (Complete Guide)

System Design Mock Interview: Design WhatsApp

Senior Product Designer Mock Interview: App Critique of Yelp

Understanding TCP vs UDP

What REALLY Happens in Meta’s AI Product Sense Round

Anthropic Culture Fit | Behavioral Interview

How to Answer Situational Questions for Product (PM) Behavioral Interview | Framework + Solution

Concurrency in Low-level Design Interviews w/ Staff Engineer

0.0.1 How to become a CAE Engineer CAE | What are the ways to become a CAE Engineer as a fresher

Redis Crash Course - the What, Why and How to use Redis as your primary database

Business Partner B2 Unit 4.1 Alternative video

Learn Data Modeling in 8 minutes: Dimensional Data Modeling, Data Vault, and One Big Table

Learn how to SOLVE a data analytics case study problem

Manual Testing: QA Interview Preparation Questions and Answers

What is Event Driven Architecture (EDA)?

Azure App Service and Virtual Network Integration Options

Sobre esta lección

¿Qué es la Técnica de Shadowing?