Slack Export

Good afternoon Ryan, and happy Friday. Would you be available sometime Monday afternoon to review the updates I made for setting up the snapshot as well as some dbt best practices?

deleted-U0410U6Q8J3

2024-09-06 16:24:21

We could set it for 30 minutes or an hour but an hour would allow us some time to discuss everything in detail. And I can record the meeting for you to review later as well.

Ryan (ryan@themedialab.agency)

2024-09-06 16:25:44

Let’s do Tuesday, my Monday is slammed

https://calendar.app.google/HKTuVhLVGZdk7d2W7

Google Workspace

Google Calendar - Easier Time Management, Appointments & Scheduling

Learn how Google Calendar helps you stay on top of your plans - at home, at work and everywhere in between.

Original URL: https://calendar.app.google/HKTuVhLVGZdk7d2W7

deleted-U0410U6Q8J3

2024-09-06 16:25:55

Ok sure, sounds good.

Ryan (ryan@themedialab.agency)

2024-09-06 16:26:58

Great

deleted-U0410U6Q8J3

2024-09-06 16:27:37

I scheduled it for 1, feel free to revise that to 1 hour if you like or else I can condense everything into 30 minutes.

Ryan (ryan@themedialab.agency)

2024-09-06 16:27:54

I’ll make an hour

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-06 16:28:10

Ok thanks, have a nice weekend.

Ryan (ryan@themedialab.agency)

2024-09-06 16:51:18

You too

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-10 14:52:55

Hi Ryan I think to fix this we will need to make staging the default branch in Github and clean it all up. Can you add me as another admin to in the github repo temporarily?

Ryan (ryan@themedialab.agency)

2024-09-10 14:53:15

I can change that real quick

👍 deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-09-10 14:54:38

@deleted-U0410U6Q8J3, done

image.png

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-10 15:09:50

Ok I have it all set up if you'd like to hop back on for a minute

B03GWN8RZN0

2024-09-10 15:09:54

chris.goodell has started a meeting

Ryan (ryan@themedialab.agency)

2024-09-10 15:11:31

Be right there

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-10 15:40:04

Hey Ryan sorry I lost your audio there at the end, but I will follow up with you tomorrow.

Ryan (ryan@themedialab.agency)

2024-09-10 15:40:13

Coming back

deleted-U0410U6Q8J3

2024-09-10 15:40:16

ok sure

Ryan (ryan@themedialab.agency)

2024-09-10 15:40:21

Internet died

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-10 16:59:54

Refresh snapshots hourly production job is set up.

Ryan (ryan@themedialab.agency)

2024-09-10 17:00:03

Thank you!

👍 deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-09-10 17:13:42

@deleted-U0410U6Q8J3

how do I exclude rows where _fivetran_deleted is TRUE in this DBT model?


    {{ dbt_utils.star(from = source("aws_legagcy_public", "financial_log")) }}

from {{ source('aws_legagcy_public', 'financial_log') }

deleted-U0410U6Q8J3

2024-09-10 19:40:10

You would just add a where clause after from, same as a normal sql query

👏:skin_tone_4: Ryan

Ryan (ryan@themedialab.agency)

2024-09-16 15:28:28

@deleted-U0410U6Q8J3, how you coming? Can we meet Wednesday to review this first SL project step for DBT tables?

deleted-U0410U6Q8J3

2024-09-16 15:46:09

Hi @Ryan, thanks for reaching out. Yes I should have this wrapped up end of day today, and Wednesday sounds good to meet. When would be a good time for you?

Ryan (ryan@themedialab.agency)

2024-09-16 15:47:44

Does 2PM CT work? Kinda a crazy day but I can get an hour with you.

deleted-U0410U6Q8J3

2024-09-16 15:49:12

Looks like I am free anytime after 1:00 pm CST except 2:30_3:00, and I'll be online til 6pm CST.

Ryan (ryan@themedialab.agency)

2024-09-16 16:17:22

Got your invite, ty!

👍 deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-09-18 09:44:30

Morning, please add @Brian Hirst to the call today. Ty.

Ryan (ryan@themedialab.agency)

2024-09-18 09:44:39

brian@shield-legal.com

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-18 10:29:46

Sure, added Brian.

🙏:skin_tone_4: Ryan

deleted-U0410U6Q8J3

2024-09-19 15:41:29

Hey Ryan, I should have everything done that we discussed by my end of day today, would you like to have that follow up meeting tomorrow where we try to switch it over in Looker?

Ryan (ryan@themedialab.agency)

2024-09-19 16:16:32

Lets do 11AM PT on Tuesday next week, I'm just swamped with some financials and our AI product demo tomorrow.

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-19 16:37:46

Sure, sounds good. I just sent the invite to both you and Brian.

🙏:skin_tone_4: Ryan

Ryan (ryan@themedialab.agency)

2024-09-25 20:43:14

Hey Chris, I have some orchestration issues on our jobs. We need to clean up. I’m traveling tomorrow, can we meet Friday?

deleted-U0410U6Q8J3

2024-09-26 06:09:37

Hey Ryan, sure. I can be free anytime from 8 am - 11 am CST, or else 12 pm - 2 pm CST. Just let me know what works for your schedule.

Ryan (ryan@themedialab.agency)

2024-09-26 11:29:30

Great, sent invite.

👍 deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-09-27 09:53:34

@deleted-U0410U6Q8J3 , need to push to Monday. What times?

deleted-U0410U6Q8J3

2024-09-27 09:54:41

Hi Ryan, sure I am free anytime before 11 am CST, or else anytime after 3pm CST.

deleted-U0410U6Q8J3

2024-09-30 09:41:33

Good morning Ryan, I realized today's meeting it in conflict with another meeting. Are you free any other times today? (I'm only unavailable from 12-1 pm CST.)

Ryan (ryan@themedialab.agency)

2024-09-30 09:42:08

Got it, sure

👍 deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-09-30 09:42:23

Moved it to 1:30

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-09-30 09:42:26

Ok thanks

Ryan (ryan@themedialab.agency)

2024-09-30 09:42:34

No problem

deleted-U0410U6Q8J3

2024-10-07 10:06:05

Hi Ryan, hope you are doing well. I wanted to follow up to let you know, I have updated the generateschemaname macro to use a qa_ prefix for the STG builds for both the SL and TIP projects.

I think regarding your desire to have a multi-step merge process for production code, something like

dev branch > team_dev > staging > prod or dev branch > staging > team_dev > prod

I think you can simplify it into just having the process be: dev branch > staging > production, and you would do so via restricting who can merge staging to production to maybe just yourself or whoever owns the development lifecycle. This has worked well for our other clients.

We can accomplish this with an updated configuration in Github, where we restrict who can merge to the subsequent branches and the production branch via a codeowners configuration.

I'd be happy to meet with you sometime this week if you'd like me to explain this some more in detail and we can walk through the updates needed to configure this change. For now, I would go ahead and create a codeowners team (you can just add yourself for now if you like) within your Github organization, and then we can follow up for next steps and testing this functionality.

Ryan (ryan@themedialab.agency)

2024-10-07 10:16:31

Got it, @deleted-U0410U6Q8J3, lets do your recommended dev branch > staging > production , You can add my rvaspraml GitHub user as a "Code Owners" team and get GitHub ready for tomorrow? Doing this for SL BI Project and TIP BI Projects, so when you free tomorrow or Wednesday to review? Preferably tomorrow, as I have a SL client data store I am working on and don't want to overlap your clean up and mess it up.

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-10-07 11:05:07

Sure that sounds good, I can be free Tuesday or Wednesday, whenever works best for you if you'd like to send the invite.

I will follow up on setting this up today, and I'll let you know if I need anything further in the interim.

Ryan (ryan@themedialab.agency)

2024-10-07 11:18:43

Thank you. Will send invite soon.

👍 deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-10-07 13:24:01

Sent for tomorrow.

:thanks1: deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-10-07 14:30:39

Ok great, talk to you then.

🙏:skin_tone_4: Ryan

deleted-U0410U6Q8J3

2024-10-07 14:57:51

I realized I have access to the relevant repos, but I am not an organization admin for your Github, so you will have to set up the team in Github.

If you can do so ahead of the meeting, create a team named code-owners, and add yourself only to it. (If you have any issues with this we can take care of it on the call tomorrow.)

Then on tomorrow's call we will configure the repos to only allow you to merge to the production branch.

Ryan (ryan@themedialab.agency)

2024-10-07 15:49:50

Sounds good.

👍 deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-10-08 12:02:32

My flight was delayed , pushed this up

deleted-U0410U6Q8J3

2024-10-08 13:25:22

Sure, no problem.

Ryan (ryan@themedialab.agency)

2024-10-08 14:35:34

I'm in the call

deleted-U0410U6Q8J3

2024-10-08 16:40:31

Hi Ryan I am really sorry I mixed up the invitations and thought this was rescheduled for 10/11; saw your subsequent invite for 10/14.

Ryan (ryan@themedialab.agency)

2024-10-08 17:00:39

All good. Enjoy your OOO

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-10-14 15:47:43

Hey Ryan, saw your follow up invite... I am booked unfortunately at 1-1:30pm CT, but I am free anytime after 2:30 pm CT, or also 10:00_11:00 am CT. Would one of those times be good?

Ryan (ryan@themedialab.agency)

2024-10-14 17:09:34

I sent invite for 10am CT

Ryan (ryan@themedialab.agency)

2024-10-14 17:09:55

My calendars were still on PT

deleted-U0410U6Q8J3

2024-10-14 17:11:01

Ah ok, got it thanks

👏:skin_tone_4: Ryan

Ryan (ryan@themedialab.agency)

2024-10-22 12:31:01

@deleted-U0410U6Q8J3, you free for 5 minutes? I'm stuck in DBT trying to create a simple new model

deleted-U0410U6Q8J3

2024-10-22 12:36:21

Hey Ryan, sure I am free for the next 20 minutes. Happy to hop on.

deleted-U0410U6Q8J3

2024-10-22 12:36:33

Here is a zoom link

B03GWN8RZN0

2024-10-22 12:36:36

chris.goodell has started a meeting

deleted-U0410U6Q8J3

2024-10-22 15:25:32

Hey Ryan it looks like the ryandev branch is containing the newer version of everything along with the older version of everything. Would it be ok if I reset your branch to the state of the staging branch and we start fresh and implement the new lrdata model?

deleted-U0410U6Q8J3

2024-10-22 15:26:07

Not sure how its got both old and new so its probably best to just get it back to whats current first.

deleted-U0410U6Q8J3

2024-10-22 15:49:30

The reason that model was erroring is because the lr_inbox data was ingested to lr_data which is in us-west4; if you ingest it to the US multiregion it will work the same as the others I believe.

image.png

deleted-U0410U6Q8J3

2024-10-22 15:50:14

If you want to go ahead and update that, we can then update the dbt development branches to be cleaned up first, then test adding that additional model.

Ryan (ryan@themedialab.agency)

2024-10-22 16:06:56

What do I do ?

Ryan (ryan@themedialab.agency)

2024-10-22 16:07:10

Yes you can reset that branch

Ryan (ryan@themedialab.agency)

2024-10-22 16:07:14

@deleted-U0410U6Q8J3

Ryan (ryan@themedialab.agency)

2024-10-22 16:08:29

We need to update the region in BigQuery?

deleted-U0410U6Q8J3

2024-10-22 16:09:27

Yes that is the only dataset in us-west4, we should align it with the others in US region

deleted-U0410U6Q8J3

2024-10-22 16:09:45

How was it created the first time? Via a an etl tool or manually created?

Ryan (ryan@themedialab.agency)

2024-10-22 16:10:00

Someone else.

deleted-U0410U6Q8J3

2024-10-22 16:10:15

Ah ok

Ryan (ryan@themedialab.agency)

2024-10-22 16:10:33

Any workaround?

deleted-U0410U6Q8J3

2024-10-22 16:13:33

Yeah I don't think so, you can override the location in a yaml configuration but not at the dataset level, its at the whole project level.

deleted-U0410U6Q8J3

2024-10-22 16:13:52

We'd need to have him update the ingestion to a US location dataset

deleted-U0410U6Q8J3

2024-10-22 16:14:09

I could copy that data temporarily into another dataset but its not a permanent fix

Ryan (ryan@themedialab.agency)

2024-10-22 16:14:19

Let me ask

deleted-U0410U6Q8J3

2024-10-22 16:14:21

Ryan (ryan@themedialab.agency)

2024-10-22 16:16:00

@deleted-U0410U6Q8J3, now, what about my DBT login?

Ryan (ryan@themedialab.agency)

2024-10-22 16:16:24

Is that branch reset also done?

deleted-U0410U6Q8J3

2024-10-22 16:16:42

Yes let me go ahead and reset the branch now, just a moment and I'll follow up.

🙏:skin_tone_4: Ryan

deleted-U0410U6Q8J3

2024-10-22 16:44:12

Ok its now reset

Ryan (ryan@themedialab.agency)

2024-10-22 16:48:46

Great, will advise on the data location issue. Also, if I create a "new table" from BigQuery for the iolrstatusesservices table from that lrinbox table, and ensure it's in US location, that might work?

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-10-22 17:03:15

yeah I think it should

Ryan (ryan@themedialab.agency)

2024-10-30 09:04:07

@deleted-U0410U6Q8J3, how are the PK Snapshot fixes coming? I am getting snapshot error on Build Jobs in DBT in TIP project.

deleted-U0410U6Q8J3

2024-10-30 11:39:12

Good morning @Ryan, yes I went ahead and moved that update into your ryan_dev branch, I haven't committed it in case you would like to look at it. Sorry for the delay, it is my birthday today so I just was checking Slack and email. If the current state looks good to go you probably can go ahead and commit and merge it.

Essentially what we can do is add a post_hook SQL DML statement into the model config for the snapshot itself. So everytime the snapshot is taken and the output table is built, it is adding the id field as the primary key via an ALTER TABLE statement.

BigQuery doesn't enforce primary keys of course like an RDBMS, but it does have beneficial performance implications on joins for having primary keys declared. We can add some evaluation for ensuring primary keys exist on tables where they are supposed to be declared in BigQuery if you think that also is helpful.

https://cloud.google.com/blog/products/data-analytics/join-optimizations-with-bigquery-primary-and-foreign-keys

Google Cloud Blog

Join Optimizations with BigQuery Primary and Foreign Keys | Google Cloud Blog

Understand how unenforced Key Constraints can benefit queries in BigQuery.

Original URL: https://cloud.google.com/blog/products/data-analytics/join-optimizations-with-bigquery-primary-and-foreign-keys

Ryan (ryan@themedialab.agency)

2024-10-30 11:41:14

Happy birthday, can we set up a time tomorrow or Friday to review it so I clearly understand?

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-10-30 11:42:24

Sure, yeah that sounds good. Whatever time works for you.

👏:skin_tone_4: Ryan

Ryan (ryan@themedialab.agency)

2024-11-17 11:49:57

@deleted-U0410U6Q8J3,

Good morning. DBT for the TIP project is failing on occasion, can you investigate. https://xa302.us1.dbt.com/deploy/251196/projects/357639/runs/70471824444898

17:07:17 Completed with 1 error and 0 warnings: 17:07:17 17:07:17 17:07:17 Database Error in snapshot dbt_lr_case_types_revenue_rates_snapshot (snapshots/dbt_lr_case_types_revenue_rates_snapshot.sql) UPDATE/MERGE must match at most one source row for each target row compiled Code at target/run/tip_dbt/snapshots/dbt_lr_case_types_revenue_rates_snapshot.sql 17:07:17 17:07:17

xa302.us1.dbt.com

dbt Cloud

Original URL: https://xa302.us1.dbt.com/deploy/251196/projects/357639/runs/70471824444898

deleted-U0410U6Q8J3

2024-11-25 08:25:07

*Thread Reply:* Good morning Ryan, following up on this, it looks like the snapshot (dbt_lr_case_types_revenue_rates_snapshot.sql) is failing because the columns in the underlying table (dbt_lr_case_types_revenue_rates.sql) has changed (missing some fields that used to be in it) and so there's an incompatibility with what already is there in the snapshot. When the snapshot model is run, it goes to compare the existing snapshot to the table's new data, but finds a different shaped table. So it throws the error.

Resolution for this would be to revert the model to the old structure, or to wipe the snapshot and start it again. But in the future to maintain integrity we will want to keep that model as it is.

Ryan (ryan@themedialab.agency)

2024-11-25 09:07:11

*Thread Reply:* @deleted-U0410U6Q8J3, we added some columns to the table for application purposes, see below. So I need to update the Raw Data models and snapshot and start again by deleting the snapshot from BigQuery?

👍 deleted-U0410U6Q8J3

deleted-U0410U6Q8J3

2024-11-25 09:08:08

*Thread Reply:* Yes thats right, and in the future to make it a bit more flexible we could add a model in between it and the snapshot, which only passes through the needed fields, so maintenance of the base table won't break the ongoing snapshot.

deleted-U0410U6Q8J3

2024-11-25 09:08:57

*Thread Reply:* We could also do that now instead of deleting the existing snapshot, unless you want the new fields in the snapshot.

Ryan (ryan@themedialab.agency)

2024-11-25 09:41:01

*Thread Reply:* @deleted-U0410U6Q8J3, think I fixed it but shouldn't the "staging" environment have it's own version of the snapshot? I noticed we are "fixing the BigQuery dataset" in the snapshot sql model in DBT

deleted-U0410U6Q8J3

2024-11-17 13:31:53

Hi Ryan, sure I will have a look.

Ryan (ryan@themedialab.agency)

2024-11-17 13:59:49

It did it, then stopped and worked. So something in orchestration is my guess

Ryan (ryan@themedialab.agency)

2024-11-17 14:00:20

Meaning the snapshot is running into some scenario we haven’t handled.