Shadowing-Übung: How to Create Gold, Silver, and Bronze Tables in Postgres for a Medallion Data Architecture! - Englisch Sprechen Lernen mit Video
Übungsmodus wählen
Jeder Modus öffnet einen Übungsraum im Vollbild.
202 Sätze
Wenn Sätze zu kurz oder zu lang sind, klicke auf Edit, um sie anzupassen.
1
Hey guys, DataGuy here, and today I have a video for you that has been requested for a long time, which is how you can set up bronze,
2
silver, and gold medallion tables within Postgres.
3
And don't worry if you're not using Postgres, the same patterns will apply for really any kind of SQL database,
4
you're just going to need to alter some of the syntax for your specific database.
5
And what I'm using here to run it is just Postgres app for MacBooks, you can download it online super easy
6
and then i'm going to be just connecting
7
and running my scripts within dbeaver just local ui for actually running actions within a database and
8
so what i'm going to actually also want to be using
9
as the data set for this is just the classic taxi
10
vendor data set you can download it really anywhere online just
11
search for taxi vendor data set it's the most classic nyc
12
taxi kind of pattern for running these kind of operations and examples.
13
And so without further ado, let's get into it and start creating our bronze schema.
14
And so the first thing you're going to need to do is obviously just create your schema.
15
If it doesn't exist, create bronze, and then drop any existing bronze table if it already exists and it doesn't already exist.
16
So you're going to see that error message there at start.
17
And then assuming that that hasn't been created right
18
so we just want to create a fresh table of any new data
19
so any pre -existing data that was already around don't want it anymore
20
so now we're going to create this blank table it doesn't exist
21
and then on the we're going to also create an index on the load timestamp for more efficient querying
22
so instead of just using timestamp to kind of order our
23
data we want to create an index make it easier and more organized to actually just query our raw data downstream.
24
So we're not stuck always just having to use that timestamp or use that raw timestamp as our identifying index.
25
And this also going to help queries run faster as well.
26
Then what we'll do is insert some sample data into our bronze layer.
27
So this would likely just be something you're going to be doing, you know, either as part of your data ingestion.
28
I'm only doing it here because I don't have any, you know, I just don't want to bother going to the process of uploading data from an external database, right?
29
So I'm just going to kind of create an example.
30
And this is again, just data that copied over from that taxi trip data set.
31
So you can download and use and basically just copy that in yourself as well.
32
And then once we're done with that, we're also going to, you'll notice here, I added some data that does have data quality issues within the silver layer,
33
we're actually going to clean this data out.
34
So I'm going to show you how you clean your data as part of this process.
35
So we have one entry that has a negative fare and then one entry that actually has some invalid coordinates.
36
I just took an entry and adjusted to break it.
37
So then once we have run that insert command, we've got our data loaded up.
38
The next step is going to just be running a quick verify bronze data layer load.
39
So, you know, just making sure every time this script gets run
40
that our data is of the expected size and
41
so here we just have a simple select statement hey count all as total records from bronze taxi trips raw
42
and then just sample the first five rows to make sure
43
the data isn't corrupted it's of the expected you know type
44
and look and feel that that we're anticipating here
45
so this is our first step so just creating that bronze table
46
and so you'll see we'll run this script
47
and now we just have our first kind of five rows of this taxi data within our database, right?
48
So here we go to tables.
49
Now we will see our taxi table.
50
So here have taxi trips raw.
51
And then next, what we'll do is start building our silver table.
52
So here we'll add a new silver, a new script.
53
So new SQL script here, and this will be our silver SQL script.
54
And so next we're going to do is within silver layer, typically, you want take hey we've got our raw data in
55
the ingestion layer now we want to clean it do some validation
56
standardization so that it is you know it's not fully transformed for analytics
57
but we've gotten you know all the bad data we have in a really easily transformable format
58
and so first thing we're going to do here is same thing just creating the schemas
59
if they don't already exist so creating silver schema dropping an existing table
60
so taxi trips cleaned
61
and then what we're going to do here is create a new table with all the different fields
62
that we want to track in our silver stage
63
so here we're just creating a surrogate key
64
so serial number trip trip ID and for easier joins
65
and this is how you know imagine is an ID you'd be using to join with other pieces of data
66
and we also are creating some metadata fields
67
so timestamp for bronze load silver loads we can see the Delta between bronze
68
and silver the actual source file the raw source file
69
so you can trip for traceability then just create a nice clean shrimp trip information with the proper data types, location data with precision for GPS coordinates,
70
trip details, also starting to calculate things like financial data, and then also data quality flags.
71
So this is how we're going to filter bad data downstream to make sure, you know, you don't want to delete bad data, but you want to kind of quarantine it.
72
And then we also have some fields
73
that we're going to calculate like the trip duration in minutes and the trip speed in mile per hour as well.
74
So once we've created the table, the next thing we're going to do is also create some index for common query patterns.
75
So these will make it easier to find the data for, you know, these particular values.
76
So, you know, for things like, hey, pick up date time, the amount of silver taxi trips that are, you know, running at a certain pickup or drop off time,
77
the vendor IDs, you can track trips by vendor, payment types, you can track by payment type, valid trips, so you can filter out any invalid trips or any invalid data.
78
And let me just create a few of two rows here for spacing.
79
And then after we've done, we're done creating the indexes, then we're just going to run a very long insert command
80
to insert all of our different data into this new table we just created.
81
So here we have insert into, go up here into silver taxi trips, all those fields we just created
82
and then selecting from our bronze table source file casting the
83
vendor id changing the integer types to the proper integer types changing cases to make sure that they're
84
capped at hey you can't have passengers less than one
85
because you don't have a ride if you don't have a passenger
86
and you can't have more than six
87
because we don't have let's say larger vans in our fleets we know
88
that legally you're never allowed to have more than six
89
and then these are just again casting different variables to the correct type
90
and then same thing here casting valid coordinates making sure they're within the new york city approximate bounds
91
so testing the coordinates for whether they're actually taking place in new york city also running fair validation
92
so making sure the fair is actually greater than zero for
93
each each entry duration has to be between one hour
94
and three minutes you can't have a ride less than a minute
95
and we don't allow rides more than three hours obviously you can change
96
that then we're also calculating the trim duration in minutes
97
so just basically here subtracting the drop -off time minus the pickup time
98
and just getting a value in numbers or in you know minutes
99
and then finally calculating the average speed distance over time in mile per hour
100
so we have some clean data rather than just raw you know entries
101
and makes it easier for obviously querying this all downstream
102
because you don't care about pickup
103
and drop off time mostly most likely you care about how long were these trips taking
104
and then also just doing some basic not null checks to make sure
105
that hey drop off is always after pickup
106
and also there has to be a pickup
107
and a drop off you obviously can't have one in the other
108
so once we've done that we've inserted all our data
109
and done some transformations in the process we're then also going to perform some data validation
110
or create a data quality table to create summaries from some data quality checks we'll run on this table.
111
So here we're creating, we're saying, hey, making sure every field has valid coordinates, valid fares, valid durations, has fully valid records.
112
So it has all those things as valid.
113
And then also the quality percentage.
114
So saying, hey, total amount of times when we don't actually have quality rides versus quality rides, so you can get an overall view of how well -maintained and how well -recorded your data is.
115
And then just saving in an external table for easier analysis and validation.
116
It also makes it easier if you're collecting data over time, right?
117
You can have each entry in this table be the sum data, sum validation of each time you ingested that data.
118
Then what we'll do is just show the data quality report.
119
So just add a select statement here.
120
And then also show another sample of our clean data.
121
Just showing that it is, in fact, cleaned.
122
And boom, that is our silver stage, all nice and complete
123
so now it's time to move on to gold and
124
so here again we can see some of the output here of our top fields within our now silver table
125
so has valid coordinates valid fair making sure this is all valid data
126
so cool now creating our next
127
and final sql script we are then going to create our gold table
128
so here let's go and this is the table
129
that is going to be you know really tightly design business -ready aggregations analytics.
130
So we're really taking high -level metrics, analytics, and doing a lot of calculations here that will be used in downstream business processes and reporting.
131
So first what we'll do is just do our classic, hey, create a schema if it doesn't exist.
132
So creating that gold table schema.
133
Then we're going to drop it if it doesn't exist and create a new table.
134
um with a select statement so here what we'll do is drop table
135
if it doesn't exist the daily trip summary table
136
and then create gold daily trip summary as selecting pickup date
137
time from as trip date the total number of trips by
138
counting all the number of trips um count distinct vendor ids as active vendors
139
so only counting distinct vendors
140
so duplicating vendors you know exactly how many you know drivers you have um also passenger metrics here
141
so the passenger count for total passengers average passengers per trip
142
averaging the total distance trip duration trip distance speed also creating
143
a lot of these average financial metrics like average total fare
144
amount total tips total revenue average trip cost the max trip cost tip analysis for tip percentage
145
and average tip
146
and then also data quality metrics analyzing you know at a
147
high level hey are a lot of these trips uh valid
148
and you're seeing pulling all this data from that silver table
149
and grouping it by date time ordered by trip date which is again that index we created at the start
150
now then what we're going to do is create another index for date based queries
151
so we're here we're going to create index daily date based on daily trip summary
152
and then we're going to do some analysis on vendor performance metrics
153
so this is very typical in a gold table you know
154
you're going to want to perform analysis some of these queries
155
so here what we're doing is you know basically same thing we did up there dropping vendor performance
156
that already doesn't know if it already exists and then here creating a new table where we're actually assigning our vendors
157
names so here we have you know create a mobile verifone else's other
158
and we're also you know making the data a lot cleaner
159
to look at instead of just you know vendor ids calculating
160
some of these things like volume metrics the amount of total trips stays active per vendor average trips per day per vendor.
161
And, you know, basically all that same kind of average, all those averages we counted at the start, but now broken down per vendor.
162
And that's really a lot of what gold tables are doing.
163
It's taking the same information, but slicing it in different ways that analysts might use, right?
164
So understanding, hey, what, you know, vendors using more cash versus credit card or what vendors are getting the most tips, understanding all of these processes.
165
And then another one, it could be something like payment type analysis.
166
So let's say you want to understand, you know, what type of payments people are using and how that has input, you know, affects the number of trips or the tips you're getting, right?
167
So here we have credit card, cash, no charge or dispute.
168
And here what we can do is start tracking, hey, people that are paying cash, what's their average trip distance?
169
What's their total revenue?
170
And tip analysis, I'd say is most relevant here for credit versus cash is saying, hey, how much do I normally get tipped when people pay with cash versus credit?
171
And then maybe, you know, you want to say, hey, I want to actually only, you know, accept cash or credit if I expect,
172
if one is giving vastly larger tips than the other, right?
173
And so I could really go on for days with all the different types of analysis you could do here, but I'll cap it off as,
174
you know, another one for, hey, trip distance duration.
175
So here, you know, let's say I want to, or, you know, analyze, hey, what is the hourly demand patterns right so here you know
176
if i want to look at what is you know i
177
want to say hey more people need to be on the road at a certain time understanding hour by hour
178
when are people most likely to you know create to schedule rides to book rides
179
and you can group them in these different periods
180
and then analyze you know schedule your drivers accordingly
181
so you don't have extra drivers on the road just sitting idle
182
when you could be having them sit home and and then also So, you know, something like a trip distance duration is also a great one to have.
183
So just, you know, really simple, hey, understanding different categories of trip distances.
184
So if you want to analyze costs and tips on that as well, you can.
185
And then another very common thing to do after you've, you know, created your tables is also create views, right?
186
And so these are basically, you know, windows within SQL that you can use to say, hey, this is an executive dashboard that's going to sum up today's metrics,
187
with the manner of trips today, revenue today, trips yesterday, revenue yesterday to compare, week to date, month to date.
188
And this is, you know, really a executive level view ready to be sent out to
189
an external party where it just has only the relevant information that's gone through these series of cleaning, validation, calculation to arrive at these actual data points.
190
And that's, again, the purpose of a gold table.
191
And then similarly, you create something like a data quality monitor, right?
192
Where here, we're creating a monitor where we're selecting,
193
you know hey bronze layer validation silver layer validation gold layer validation having all their data quality percentages added there
194
so you can actually see hey these are you know this is how data is evolving
195
and being cleaned and changing as it flows through our systems and
196
that is really everything i have for you today you know
197
i wanted to make this video i thought it was something
198
that been getting requested enough that i finally had to get around to it
199
so it took some time so i hope you enjoyed this video.
200
I hope you got some value out of it.
201
I hope you have a great rest of your day.
202
Data Guy out.
App herunterladen
Alles, was du zum fließenden Sprechen brauchst
KI-AussprachebewertungJeder Satz bewertet
IPA-TrainingJeden Laut meistern
WortschatzBaue deinen Wortschatz auf
VokabelspielSpielend lernen
Gleicher Kanal
FOR YOU
Empfohlenes Video
C11
120 Sätze
Learn Data Modeling in 8 minutes: Dimensional Data Modeling, Data Vault, and One Big Table
Business EnglishUniversity StudentsWorking Adults
C11
309 Sätze
Claude Code: The Advanced Guide (99% of Devs Skip These Features)
Business EnglishUniversity StudentsWorking Adults
C11
186 Sätze
Why Shopify Moved Inventory Reservations from Redis to MySQL
Business EnglishUniversity StudentsWorking Adults
C12
339 Sätze
Easy Migration from Postgres to Databricks Lakebase
Business EnglishUniversity StudentsWorking Adults
![How to Create a Women’s Bodice Block from Body Measurements [Pattern Making Tutorial]](https://img.youtube.com/vi/Bn594SV1nXA/mqdefault.jpg)
C12
122 Sätze
How to Create a Women’s Bodice Block from Body Measurements [Pattern Making Tutorial]
Business EnglishUniversity StudentsWorking Adults
C12
193 Sätze
The Real Reason We Left the Gold Standard
Business EnglishDaily ConversationUniversity Students
C13
131 Sätze
Understanding BGA Pads: Solder Mask Defined vs Non-Defined
Business EnglishUniversity StudentsWorking Adults
C13
172 Sätze
The AI Tool You Actually Need for Ancient Greek (NotebookLM)
Business EnglishUniversity StudentsWorking Adults
C11
180 Sätze
Your API Can’t Handle Real-World Integrations
Business EnglishUniversity StudentsWorking Adults
C12
130 Sätze
Password Storage Tier List: encryption, hashing, salting, bcrypt, and beyond
Business EnglishUniversity StudentsWorking Adults
C11
159 Sätze
So kannst du ein Kassenbuch in Excel mit Tabelle und Formeln erstellen und führen!
Business EnglishWorking Adults
C13
96 Sätze
Build a Real-Time Leaderboard on AWS with DynamoDB
Business EnglishUniversity StudentsWorking Adults
Wortschatz und Sprechhinweise zu dieser Lektion
Dieses Video enthält 202 Sätze und 2901 Wörter zum Nachsprechen. Der gesprochene Teil dauert 15:04. Der Sprecher spricht schnell, etwa 193 Wörter pro Minute – rechne mit verbundenen und verkürzten Lauten. 85 % der Wörter gehören zu den 3.000 häufigsten im Englischen; den Rest solltest du dir vor dem Üben ansehen.
Wichtiger Wortschatz in diesem Video
15 lernenswerte Wörter aus dem Video, mit Aussprache und Bedeutung:
| Wort | Aussprache | Bedeutung |
|---|---|---|
| vendor Substantiv | /ˈvɛn.dɚ/ | Anbieter, Vertreiber |
| bronze Substantiv | /bɹɑnz/ | Bronze |
| valid Adjektiv | /ˈvælɪd/ | gültig |
| taxi Substantiv | /ˈtæksi/ | Taxi, Taxe |
| validation Substantiv | /ˌvæl.əˈdeɪ.ʃən/ | Bestätigung, Gültigkeitserklärung |
| layer Substantiv | /ˈleɪ̯ɚ/ | Lage, Schicht |
| script Substantiv | /skɹɪpt/ | Skript, Skriptum |
| duration Substantiv | /dʒʊˈɹeɪ.ʃən/ | Dauer |
| metric Adjektiv | /ˈmɛt.ɹɪk/ | metrisch |
| query Substantiv | /ˈkwɪɹ.i/ | Frage |
| select Verb | /sɪˈlɛkt/ | auswählen |
| passenger Substantiv | /ˈpæs.ɪn.d͡ʒɚ/ | Passagier, Passagierin |
| insert Verb | /ɪnˈsɝt/ | einsetzen, einfügen |
| coordinate Verb | /koʊˈɔɹ.dəˌneɪt/ | koordinieren, abstimmen |
| database Substantiv | /ˈdeɪtəˌbeɪs/ | Datenbank |
Phrasal Verbs, die du hören wirst
| Wort | Aussprache | Bedeutung |
|---|---|---|
| drop off Verb | wegtreten, wegdämmern | |
| break down Verb | versagen, den Geist aufgeben | |
| get around to Verb | zu etwas kommen | |
| go up Verb | hinaufgehen, steigen | |
| pick up Verb | aufheben, aufnehmen | |
| set up Verb | /ˌsɛt ˈʌp/ | aufbauen, für den Einsatz vorbereiten |
Grammatik in diesem Video
Die Strukturen, die der Sprecher am häufigsten verwendet, mit den genauen Worten aus dem Video:
| Struktur | Im Video |
|---|---|
| Present Perfect have/has + Partizip Perfekt – eine vergangene Handlung, die jetzt noch wichtig ist | has been requested · hasn't been created · have run |
| Passiv be + Partizip Perfekt – wichtig ist, was geschieht, nicht wer es tut | has been requested · been created · isn't corrupted |
| Relativsätze who / which + Satz – eine Zusatzinformation über eine Person oder Sache | time, which is · date which is |
Aussprache, auf die du achten solltest
Der Sprecher verwendet 97 Kurzformen und abgeschwächte Formen, zum Beispiel we're, don't, you're. Sprich sie in der kurzen Form, so wie du sie hörst.
- Die Laute „sh“ und „zh“: validation /ˌvæl.əˈdeɪ.ʃən/, duration /dʒʊˈɹeɪ.ʃən/, calculation /ˌkælkjuˈleɪʃn̩/, efficient /ɪˈfɪʃənt/, transformation /ˌtɹæns.fɔɹˈmeɪ.ʃən/
- Lange Wörter – auf die Betonung achten: validation /ˌvæl.əˈdeɪ.ʃən/, coordinate /koʊˈɔɹ.dəˌneɪt/, analytics /ˌæn.əˈlɪt.ɪks/, similarly /ˈsɪm.ə.lə.li/, transformation /ˌtɹæns.fɔɹˈmeɪ.ʃən/
So übst du mit diesem Video
- Höre dir das ganze Video einmal an, ohne zu sprechen, und notiere die Wörter, die du nicht kennst.
- Beginne mit 0,75-facher Geschwindigkeit, sprich Satz für Satz nach und wechsle zur normalen Geschwindigkeit, sobald es leichtfällt.
- Nimm dich auf und vergleiche mit dem Original; achte dabei auf Wörter wie vendor, bronze, valid.
Was ist die Shadowing-Technik?
Shadowing ist eine wissenschaftlich fundierte Sprachlerntechnik, die ursprünglich für die professionelle Dolmetscherausbildung entwickelt und durch den Polyglotten Dr. Alexander Arguelles populär gemacht wurde. Die Methode ist einfach aber wirkungsvoll: Du hörst englisches Audio von Muttersprachlern und wiederholst es sofort laut — wie ein Schatten, der dem Sprecher mit nur 1–2 Sekunden Verzögerung folgt. Anders als passives Hören oder Grammatikübungen zwingt Shadowing dein Gehirn und deine Mundmuskulatur, gleichzeitig echte Sprachmuster zu verarbeiten und zu reproduzieren. Studien zeigen, dass es Aussprachegenauigkeit, Intonation, Rhythmus, verbundene Sprache, Hörverständnis und Sprechflüssigkeit signifikant verbessert — was es zu einer der effektivsten Methoden für die IELTS Speaking-Vorbereitung und reale englische Kommunikation macht.