GitLab Data Team, 8 Dec 2022

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Data Engineer Internship - DBT sharding

Description

Data Engineer Internship - DBT sharding, Chris Sharp and Radovan Bacovic discussing how to shard DBT models per provider

A

Yeah, actually I I run the code. Give me a second to check, am I on the right. Yes, I'm on the round range.

A

This is our guy right.

B

A

I run this locally and for some reason, I, don't know why and what I can't find the duck here. It's really crazy.

B

C

Usually, events.

A

Let's check the name.

B

I see yet I see on mine, dbt6 hourly.

A

You see it's localhost I pick up all statuses here anyway, we can do it on your computer, but the main point here is I found some errors. You because I know you copy and paste it from.

B

A

Extra options or extra things you don't need so I can show you here. This is okay, and this import is usually okay with some linters later on. We can check what is not in use. It's not a problem, but this is the catch. This poor commit is not needed to you, because it's used only for specific tags. In case you want to get the commit ID. In our case, you can just comment out or delete this. This is not needed for us. We have isolated problems or isolated. Duct run, feel.

C

A

To to delete this line.

A

A

You can remove this as well. This line is not it sorry, this one and also.

C

A

I would say this part is not needed for test, because this command, along with select or our DBP job, we'll pick up everything regarding the BTN push. It I mean in this case in in Snowflake table for results of DBT I. Think we don't need that anymore. Just for this testing we can, we can put it in a comment or somewhere but feel free to remove this. It's not made it definitely.

B

A

And EBT results. We don't need this one at least now.

C

A

You think we need it because here we run our results and upload results, and actually it's the same command as this one I would say.

A

C

A

C

A

This is not needed because for now we won't stick only with one one one task inside our deck, so actually this is what we need. I will send to you.

A

Something like that I, don't know the reason why I can see it here. Yeah.

C

A

My best, but actually what we should do, yeah also put recording session number two here. It was an agenda, but not here, so everything is complete.

C

A

And now what you need to do is to define the models and see how to fit everything inside the dock, so actually uh just to check one more time. You tagged all models for our experiment, right.

B

Yeah so I've I've done the the models that are related to Salesforce opportunity up to the marked level.

A

Okay, so, which one is sort of true for us about the time of execution, actually we'll pick up all of these models, so only this.

B

No, it was a subset that this one it was more yeah. It was the opportunity Source. The prep table had done the fact and the marked mm-hmm so yeah, that's that's as far as.

C

B

Far so that's five five models that will be running yeah.

A

All certainly cool to finalize this one.

A

What they want to do is to completely that list here.

B

A

A is it a full cam check.

B

That they're all full.

A

C

The next one is box your opportunity.

A

Yeah we pick up the good examples: they're not too big.

C

B

A

What about this? No.

B

I haven't looked into that one uh we'll just just take it as far as the marked just now, and then uh so I think it's the one above that.

A

uh Sorry this one.

B

I think it was actually less than that I think you copied the duration for the one below.

C

I think Knights appear right. Yes, four.

C

A

Dash left, we will see what is about this one. This is ah oh.

C

C

I mean it's much easier to hear from the rise you can.

B

So it's the pool right.

A

There everything.

C

C

A

And he said nothing else should be considered here right, foreign.

B

Legacy one so I, don't think I, don't think that we should be touching the Legacy ones really. If.

C

You want to refresh.

B

Data switch to using the new stuff.

A

Also, this one will not be in euros, something.

C

A

What do you think is it? Okay now.

C

Yeah, okay, hey the applicable.

B

C

A

And also, as you said, all of this that should be moved to this SDS big salary, right yeah,.

A

C

Once per day, oh pretty much closer, yes,.

A

And what do you think uh we will get any any speed or any any cost efficiency saving money, both changing model for fully refresh to incremental, as per experience.

C

A

That yeah, what do you think.

B

I mean looking at the duration of these I. Don't think there would be a.

A

B

A

I'm, just thinking.

B

Yeah could have a look at it and see if, um if any of them would benefit.

A

B

There's updated because I don't know how the opportunities are modeled, but if it's, if it's updated time, then um yeah, we could have a look at that and see.

A

Yeah, if you want to share a screen, please we can continue. It click that part and also try to to run the deck. As I said, it will be fine now, as per my experience, but let's stick with BBT models. Can we I.

B

A

Yeah, even even your range time is kind of hollow right. Okay,.

B

So I've changed I pasted your changes in there um so so yeah I can see the dbt6 early here. No.

C

um And I can do.

A

That here I expect one job.

A

Also we can you can explore that part. It's just a part of that dog. You you copy paste. It see it number line, one on six.

A

Including every round date yeah, this is dark short circuit. uh You can. You can remove that part and this function above that line started from 99. yep.

B

A

B

Just go because.

A

I reviewed this original dug because you know Paul Austin, he put this circuit operator, which is just talk about someday in special cases. For us, this is fine yeah. This is what you need.

B

B

That's quite good. You have sort of instant feedback just by refreshing your age. That's.

A

Good, sometimes yeah when it comes to the dog, it's enough to refresh your code, but it comes to code. You call sometimes in deck or in task under the deck. You need to push it to master right or to remote repository yeah.

C

A

You're on Branch, yet to be bored.

A

This is something new for me.

B

I just succeeded or.

A

Tonight: okay,.

B

So it looks like it didn't, it didn't run the DBT Commander.

B

A

Did you push these chains to remote repository because you need it because you have this DBT install that dependency command and I think we also need.

B

All right, but this is running locally, just looking at this code. Isn't it yes.

A

But that will show you the difference and give a second to check how DPP is running and which command we're using uh or commit here. Do we need it or not,.

C

A

Yes, uh how is going in our case I think this will be fine, don't discard why it's not running. You have airflow local installed and run a ratchet when you execute your dag or task on the rear. Duct it spin up the port on kubernetes. Cluster name is Facebook test, and for that reason our code will be executed there, not here under the machine locally, it will be executed cluster under the inside the port yeah.

A

So for that reason you have a dynamic command to call DBT no use colors and if that command will be executed there on 25., because you see a line, 105 said: okay, dbt6 are the model starts equal plus is equivalent spot operator. It's okay create a port in our cluster, where it's defined or predefined somewhere and around the command. Your name is here inside ebitivity.

A

Okay, can you put your chains to be sure yeah.

A

Okay, that means repeat some shortcuts. You defined right.

B

Yeah, let's just get pushed so you can do um this message is gcmsg and yeah. There is some good Sith, because I was typing out the same things over and over again.

A

Yes, this makes sense. Yeah, so git status said what it states.

B

uh uh Yeah, that's that's all pushed so it should be in.

A

Here: okay, go there to: yes, let's see commit.

C

Yep one minute ago, yeah.

B

So we'll try running again. Yes,.

A

Sorry sorry go back to Mr, please and see the code refresh this. Please because I see in the last line the old Ducks Independence we just deleted, go to the dbt6r.py file, the first one on the list. Yes, last.

C

A

Down up it's okay, it's okay! Now, yep!

A

No, it's fine! Okay, go go to airflow and rerun it again to see. What's going on, yeah trigger deck, it's active.

A

Okay, it's going for now, foreign.

A

Job is done like, um but why? The social? Sorry, sorry, sorry.

C

Is it Target produce.

A

I I don't know if it took care or not, but when you sorry, when you run the command locally, it's going fine right.

B

uh Yeah, um because.

A

B

Do activity yeah.

A

C

A

The same command because you have Target prod there, maybe that's the catch.

B

Yeah I mean this stuff I was playing about. Then it does. It runs the four or five models successfully, so maybe yeah.

A

Target prod, maybe death I, don't know to be honest. I have never on DBT models like the entire job, usually.

B

A

Run it Locker or rear pipeline.

B

A

Let's see what will happen here.

A

Target there, maybe then you will refer your schemas yeah.

C

A

Don't know what what's going on if you put prod, maybe it's forbidden, maybe something is immediately.

B

I think you'd think you'd get a message that says that it can't can't create tables or something.

A

The only concern here for me is fatalia or on the branch yet to be born, like somehow.

A

Deputy doesn't get or airflow doesn't get that command. That branch is existing, somehow I, don't know why.

C

I will take this action points with me.

B

Well, it says here fatal, you are on a branch yet to be born. Yes,.

A

Yes, that's concerned: okay, I'll put a comment for me to remind myself, and it said for this issue check airflow.

C

C

A

Okay- and it said us.

C

And also, to put it here,.

A

It's sick, so I will take action points for myself.

C

Check? Why DBT? Your is not fun.

C

Instance of fair law.

C

I checked there tomorrow.

C

A

Anyway, uh I can take the testing point. We just don't know why it's too much time, uh what about the focus, maybe on these DBT jobs and what we can do next for that? What do you, what you have on your mind, I'm thinking anyway, we can run it locally right, as of now from.

B

Here it's just it's just nearly finished. It's just running the final March just now so yeah that that selector command will just pick up. Was um that tag and.

A

That selector sorry selector option is working locally right. As you said, it.

B

Says yeah, so you can see it's running here and that's using the.

B

Selectors.Yaml so.

A

Yeah yeah: this is a great source, of course yeah. So for as of now, we don't see the good benefit of, let's say more from Full to incremental, but maybe for reason of experiment. You prefer to check that part because it will go at least for one, the first model or something to see what we can get right. Yeah.

B

A

B

A there's a small number of rows, but.

A

Yes, nothing special, but anyway, it's more like accessories like okay, let's see how it will go off. Can we check the models together? So first one is what we have opportunity: Source right, so opportunity, source.

B

A

B

In two seconds, it's oh yeah, it's a view. It's a view, a table for prep CRM opportunity and 49 seconds. Let's.

A

Start with that one, maybe perhaps here because first one is, you doesn't make sense to do anything.

B

um Yeah this one's a bit unusual because it's.

C

um Obviously, a.

B

Macro that runs this one mm-hmm uh you can see. The fields in here has I, don't know why it's set up the microbe and we should speak to Mission I'll find out.

A

And why this is hardcoded. This is also. ah This is static. Things yeah.

B

Yeah there's there's lots of static numbers in here. I've haven't actually worked on this, but.

A

And it's a bit complex.

B

huh It is quite complex and I haven't sat down to was live, yeah I, think I think these numbers might change with the sort of sales targets. Perhaps um so I think that's why there's the hard-coded numbers yeah.

A

B

um But yeah it's a big. It's a large large model.

A

And the next question is: who is using this macro? Is it only our model or their more than one? Can we check that it's called as the sfdc opportunity, Fields right, yeah foreign.

B

B

I put CRM opportunity is the one that we looked at. In fact,.

B

A

Couple of them.

B

Is documented here actually only.

A

A snapshot and live yeah.

B

A

Microsoft store.

B

Is all of the shared logic between live and snapshots models? It takes two values to model variables, snapshot or live to create either the snapshot or the Lively model.

C

A

So actually, we are using which one live now right for our model.

B

For yeah, so this is live with. Prep CRM opportunity passes the variable of live, so that means that.

A

B

Lineage is not complex, but it's the model itself is software model type equals live.

B

Together, yeah.

A

I'm thinking, uh where is the okay? If you go to that macro and search for opportunity, Source I mean the source table. We can maybe just add one more parameter to be incremental. If you know what I mean yep, because now you have one parameter, you can put second parameter as a default, nothing and or Inc four, and if you choose to be incremental, we can yeah.

A

We need from opportunity source wrong, I, don't know. Maybe it's not name like that.

A

No from from scroll down, where is that scroll down.

C

No, no, no yep.

B

So it's just a join to that table.

B

A

In that case, you know what is my idea to to extract this part to the top of the macro and say no, no, if incremental then greater than date, otherwise select star from without anywhere close ah here.

B

um Yes, you could do f, is incremental.

A

On the top of the macro yeah and added as a second parameter default value, I think it's doable in ginger. As per my experience, yeah like load type default is full and it means not will change right. If you expect to say decremental, then we.

B

A

B

A

Something like that, we can try it play a bit. I mean no harm incremental.

B

All right so yeah you can put the config in here. Actually, um yes,.

B

I agree: I, don't know how, but.

A

How it will work yeah, how it will work with if you put on model level incremental as a config and inside oh yeah, yeah interpreting the the way what you will show okay got it.

B

Yeah, it's doable. Definitely the thing is that you know we should really have the the reference at.

A

The top yep and then just call the reference.

B

And then we can uh yeah.

A

Inside and inside the reference said: okay, if it's incremental, oh.

B

Yeah here we go so, let's start from.

C

B

But yeah we can set the whole thing as incremental. What's.

C

B

Can dig into that, but I mean it's only. It only takes it's under a minute, so.

A

um Yeah, as I said directly in real life, for this example, we determined it is not a big benefit to choose sacramental over four, but I mean just for playing. We can do that as an exercise for us nothing more than that yeah. This is super complex, I would say.

B

A

Or maybe just think about that idea from my point of view, I can check why command is not working, DBT command in airflow Dag. You can check this. Maybe and also I will include that is for next session and then.

B

A

Can discuss that dim and fact and also check with him about the expectations he has about? Let's say granulation of our project: yeah.

B

Because, as of.

A

Now, theoretically speaking, what we will do, we will run instead of let's say daily: we will run each six hours full amount of data right, because.

C

A

Not incremental, but maybe it's really good to think about that option, to play a bit with that yeah and then expose next time. Okay, at least we have one model, an idea how to let's say, fix this or make it incremental, because this specific iteration we use.

B

Yeah I mean the longest. The longest running one is the smart, so that might be.

A

Yeah, but you can imagine we can save a lot of money or and machine power, because you feel it still run four times six times ten times per day, instead of once per day right, yeah.

B

A

From from that perspective makes sense to optimize this to make it run incrementally yeah, because Normal forbido to run a full refresh. That means okay, it's just regular. Aha, this is a big one right. It's.

B

Yeah there's a lot of um a lot of sources. Yeah.

A

Yeah the catch here is that that marker Simplicity is complex right, because you can't parameters anything there. It will select star yeah, that's that's the but you'll, see because I dealt with similar stuff and want to check something and with Simplicity. You have select star from that table. Yeah.

B

So a lot of these you have to break out, and then you know, if you want, if you want it to be incremental, you'd have to specify which you would you want it to look at and.

A

In our case, it is which one.

B

um Crm opportunity the CRM opportunity.

A

B

But, okay, to see how many you know, how large is this table if it runs that quickly then give.

A

Me a second give a second little bit.

A

How big is that.

B

Yes, it's 268 000 rows.

A

It's it's humble I would say, but still as I said, if you run it more than once per day, it can be costly. What.

C

They're thinking.

A

I think maybe for this experiment, maybe to create somehow um simple, City incremental macro.

A

You know what I mean and then we can do the dynamic like a simple City and you can say, Okay simple City, incremental yeah, it's possible I mean it's more generic approach because you can write it once and use it, and thanks from that point of view, that's my.

C

A

Of course, I'm thinking, maybe to focus what they want to focus I think like Stephanie is I. Will I will pick up that airflow tag and also you can pick up one or I can pick it together. One of these models, so you can figure out idea. We can do it together. You know what I mean just one model here.

C

A

Then maybe you can do it more generic. So yes, you see here we can select from and also to add a few more details inside the similar macro.

B

A

Simple City, incremental and.

B

You don't yeah, you just pass the fields that you wanted to be increased on yeah. It can be beneficial.

A

Because it's always the same pattern right.

B

A

It could be topless and at the end of the story it could be, let's say date, name or column name like maybe imagine. If you have one model per day, it is updated. Another model, it's update time. Usually it's new unified but yeah. If it's not the resolution of parameter, but maybe something like that, you know what I mean. So, from my point of view should pick up.

A

You can pick up one of these models, optimize it to be incremental and then we can apply the same approach but maybe think was considering democracy instead of just coding. I I, don't know.

B

Yeah, it's a good idea.

A

Because it will be easier if, if you sort it out sorted out in one model, then we can apply it for each of these models right, yeah, yeah! That's that's! That's my idea. That's my idea on this. If you agree with it, of course, just thinking loud.

B

Yes, you'd have simple CTE incremental, but then yeah it gets. These are quite look fairly. Complicated models, so I'd have to yeah.

A

This is this, is the top one I mean lineage is very simple. If you have five models, but logic inside the model is complex, I would say, I, don't know even this. This thing this thing is repeatable, so also it can be the future subject to some optimization here, more macros, but still for us, let's take like simple City incremental or something like that and as I said sort it out for one model by your choice and when we fix that we can apply the same yeah.

B

That's a good idea: yeah, okay, I can have a look at that for next time.