Shadowing Practice: How to Create Gold, Silver, and Bronze Tables in Postgres for a Medallion Data Architecture! - Learn English Speaking with Video

Choose a practice mode

Each mode opens a full-screen practice room.

202 sentences

If sentences are too short or too long, click Edit to adjust them.

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,

silver, and gold medallion tables within Postgres.

And don't worry if you're not using Postgres, the same patterns will apply for really any kind of SQL database,

you're just going to need to alter some of the syntax for your specific database.

And what I'm using here to run it is just Postgres app for MacBooks, you can download it online super easy

and then i'm going to be just connecting

and running my scripts within dbeaver just local ui for actually running actions within a database and

so what i'm going to actually also want to be using

as the data set for this is just the classic taxi

vendor data set you can download it really anywhere online just

search for taxi vendor data set it's the most classic nyc

taxi kind of pattern for running these kind of operations and examples.

And so without further ado, let's get into it and start creating our bronze schema.

And so the first thing you're going to need to do is obviously just create your schema.

If it doesn't exist, create bronze, and then drop any existing bronze table if it already exists and it doesn't already exist.

So you're going to see that error message there at start.

And then assuming that that hasn't been created right

so we just want to create a fresh table of any new data

so any pre -existing data that was already around don't want it anymore

so now we're going to create this blank table it doesn't exist

and then on the we're going to also create an index on the load timestamp for more efficient querying

so instead of just using timestamp to kind of order our

data we want to create an index make it easier and more organized to actually just query our raw data downstream.

So we're not stuck always just having to use that timestamp or use that raw timestamp as our identifying index.

And this also going to help queries run faster as well.

Then what we'll do is insert some sample data into our bronze layer.

So this would likely just be something you're going to be doing, you know, either as part of your data ingestion.

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?

So I'm just going to kind of create an example.

And this is again, just data that copied over from that taxi trip data set.

So you can download and use and basically just copy that in yourself as well.

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,

we're actually going to clean this data out.

So I'm going to show you how you clean your data as part of this process.

So we have one entry that has a negative fare and then one entry that actually has some invalid coordinates.

I just took an entry and adjusted to break it.

So then once we have run that insert command, we've got our data loaded up.

The next step is going to just be running a quick verify bronze data layer load.

So, you know, just making sure every time this script gets run

that our data is of the expected size and

so here we just have a simple select statement hey count all as total records from bronze taxi trips raw

and then just sample the first five rows to make sure

the data isn't corrupted it's of the expected you know type

and look and feel that that we're anticipating here

so this is our first step so just creating that bronze table

and so you'll see we'll run this script

and now we just have our first kind of five rows of this taxi data within our database, right?

So here we go to tables.

Now we will see our taxi table.

So here have taxi trips raw.

And then next, what we'll do is start building our silver table.

So here we'll add a new silver, a new script.

So new SQL script here, and this will be our silver SQL script.

And so next we're going to do is within silver layer, typically, you want take hey we've got our raw data in

the ingestion layer now we want to clean it do some validation

standardization so that it is you know it's not fully transformed for analytics

but we've gotten you know all the bad data we have in a really easily transformable format

and so first thing we're going to do here is same thing just creating the schemas

if they don't already exist so creating silver schema dropping an existing table

so taxi trips cleaned

and then what we're going to do here is create a new table with all the different fields

that we want to track in our silver stage

so here we're just creating a surrogate key

so serial number trip trip ID and for easier joins

and this is how you know imagine is an ID you'd be using to join with other pieces of data

and we also are creating some metadata fields

so timestamp for bronze load silver loads we can see the Delta between bronze

and silver the actual source file the raw source file

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,

trip details, also starting to calculate things like financial data, and then also data quality flags.

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.

And then we also have some fields

that we're going to calculate like the trip duration in minutes and the trip speed in mile per hour as well.

So once we've created the table, the next thing we're going to do is also create some index for common query patterns.

So these will make it easier to find the data for, you know, these particular values.

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,

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.

And let me just create a few of two rows here for spacing.

And then after we've done, we're done creating the indexes, then we're just going to run a very long insert command

to insert all of our different data into this new table we just created.

So here we have insert into, go up here into silver taxi trips, all those fields we just created

and then selecting from our bronze table source file casting the

vendor id changing the integer types to the proper integer types changing cases to make sure that they're

capped at hey you can't have passengers less than one

because you don't have a ride if you don't have a passenger

and you can't have more than six

because we don't have let's say larger vans in our fleets we know

that legally you're never allowed to have more than six

and then these are just again casting different variables to the correct type

and then same thing here casting valid coordinates making sure they're within the new york city approximate bounds

so testing the coordinates for whether they're actually taking place in new york city also running fair validation

so making sure the fair is actually greater than zero for

each each entry duration has to be between one hour

and three minutes you can't have a ride less than a minute

and we don't allow rides more than three hours obviously you can change

that then we're also calculating the trim duration in minutes

so just basically here subtracting the drop -off time minus the pickup time

and just getting a value in numbers or in you know minutes

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.

Download App

Everything you need to speak fluently

AI PronunciationScore every sentence

IPA PracticeMaster every sound

VocabularyBuild your word bank

Vocab GameLearn while playing

Scan QR

Download on theApp Store Get it onGoogle Play

Same Channel

Data Ingestion Best Practices! How to Ingest Data Efficiently at Enterprise Scale!

Daily ConversationWorking Adults

What is Master Data Management? MDM Explained for Beginners

Daily ConversationWorking Adults

FOR YOU

Recommend Video

120 sentences

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

Business EnglishUniversity StudentsWorking Adults

309 sentences

How to Create a Women’s Bodice Block from Body Measurements [Pattern Making Tutorial]

Business EnglishUniversity StudentsWorking Adults

193 sentences

The Real Reason We Left the Gold Standard

Business EnglishDaily ConversationUniversity Students

131 sentences

Understanding BGA Pads: Solder Mask Defined vs Non-Defined

Business EnglishUniversity StudentsWorking Adults

172 sentences

The AI Tool You Actually Need for Ancient Greek (NotebookLM)

Business EnglishUniversity StudentsWorking Adults

180 sentences

Your API Can’t Handle Real-World Integrations

Business EnglishUniversity StudentsWorking Adults

130 sentences

Password Storage Tier List: encryption, hashing, salting, bcrypt, and beyond

Business EnglishUniversity StudentsWorking Adults

243 sentences

Proximity Search & Geospatial Indexes Explained

Business EnglishUniversity StudentsWorking Adults

159 sentences

So kannst du ein Kassenbuch in Excel mit Tabelle und Formeln erstellen und führen!

Business EnglishWorking Adults

Vocabulary and speaking notes for this lesson

This video has 202 sentences and 2901 words to shadow. The speech runs for 15:04. The speaker talks fast, about 193 words per minute, so expect linked and reduced sounds. 85% of the words are among the 3,000 most common in English; the rest is worth studying before you start.

Key vocabulary in this video

15 words from the video worth learning, with pronunciation and meaning:

Word	Pronunciation	Meaning
vendor noun	/ˈvɛn.dɚ/	A person or a company that vends or sells.
bronze noun	/bɹɑnz/	A naturally occurring or man-made alloy of copper, usually in combination with tin, but also with one or more other metals.
valid adjective	/ˈvælɪd/	Well-grounded or justifiable, pertinent.
taxi noun	/ˈtæksi/	A vehicle that may be hired for single journeys by members of the public, particularly one with an automated meter to calculate the fare.
validation noun	/ˌvæl.əˈdeɪ.ʃən/	The act of validating something.
layer noun	/ˈleɪ̯ɚ/	A single thickness of some material covering a surface.
script noun	/skɹɪpt/	A writing; a written document.
duration noun	/dʒʊˈɹeɪ.ʃən/	An amount of time or a particular time interval.
metric adjective	/ˈmɛt.ɹɪk/	Of or relating to the metric system of measurement.
query noun	/ˈkwɪɹ.i/	A question, an inquiry (US), an enquiry (UK).
select verb	/sɪˈlɛkt/	To choose one or more elements of a set, especially a set of options.
passenger noun	/ˈpæs.ɪn.d͡ʒɚ/	One who rides or travels in a vehicle, but who does not operate it and is not a member of the crew.
insert verb	/ɪnˈsɝt/	To put in between or into.
coordinate verb	/koʊˈɔɹ.dəˌneɪt/	To place in the same order or rank.
database noun	/ˈdeɪtəˌbeɪs/	A collection of (usually) organized information in a regular structure, usually but not necessarily in a machine-readable format accessible by a computer.

Phrasal verbs you will hear

Word	Pronunciation	Meaning
drop off verb		To fall asleep.
break down verb		To stop functioning.
do up verb		To fasten (a piece of clothing, etc.); to tighten (a nut etc.)
get around to verb		To eventually begin or return to some task.
go through verb		To travel from one end of something to the other.
go up verb		To move upwards.
pick up verb		To lift; to grasp and raise.
set up verb	/ˌsɛt ˈʌp/	To make ready for use.

Grammar in this video

The structures the speaker uses most, with the exact words from the video:

Structure	In the video
Present perfect have/has + past participle — a past action that still matters now	has been requested · hasn't been created · have run
Passive voice be + past participle — the focus is on what happens, not who does it	has been requested · been created · isn't corrupted
Relative clauses who / which + clause — extra information about a person or thing	time, which is · date which is

Pronunciation to watch

The speaker uses 97 contractions and reduced forms, such as we're, don't, you're. Say them the short way, as you hear them.

The “sh” and “zh” sounds: validation /ˌvæl.əˈdeɪ.ʃən/, duration /dʒʊˈɹeɪ.ʃən/, calculation /ˌkælkjuˈleɪʃn̩/, efficient /ɪˈfɪʃənt/, transformation /ˌtɹæns.fɔɹˈmeɪ.ʃən/
Long words — get the stress right: 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/

How to practise with this video

Listen to the whole video once without speaking and note the words you do not know.
Start at 0.75× speed, shadow it sentence by sentence, then go back to normal speed once it feels easy.
Record yourself and compare with the original, paying attention to words like vendor, bronze, valid.

What is the Shadowing Technique?

Shadowing is a science-backed language learning technique originally developed for professional interpreter training and popularized by polyglot Dr. Alexander Arguelles. The method is simple but powerful: you listen to native English audio and immediately repeat it out loud — like a shadow following the speaker with just a 1–2 second delay. Unlike passive listening or grammar drills, shadowing forces your brain and mouth muscles to simultaneously process and reproduce real speech patterns. Research shows it significantly improves pronunciation accuracy, intonation, rhythm, connected speech, listening comprehension, and speaking fluency — making it one of the most effective methods for IELTS Speaking preparation and real-world English communication.

Shadowing technique: read the full step-by-step guide →

Shadowing Practice: How to Create Gold, Silver, and Bronze Tables in Postgres for a Medallion Data Architecture! - Learn English Speaking with Video

Choose a practice mode

Download App

Data Ingestion Best Practices! How to Ingest Data Efficiently at Enterprise Scale!

What is Master Data Management? MDM Explained for Beginners

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

Claude Code: The Advanced Guide (99% of Devs Skip These Features)

Why Shopify Moved Inventory Reservations from Redis to MySQL

Easy Migration from Postgres to Databricks Lakebase

How to Create a Women’s Bodice Block from Body Measurements [Pattern Making Tutorial]

The Real Reason We Left the Gold Standard

Understanding BGA Pads: Solder Mask Defined vs Non-Defined

The AI Tool You Actually Need for Ancient Greek (NotebookLM)

Your API Can’t Handle Real-World Integrations

Password Storage Tier List: encryption, hashing, salting, bcrypt, and beyond

Proximity Search & Geospatial Indexes Explained

So kannst du ein Kassenbuch in Excel mit Tabelle und Formeln erstellen und führen!

Vocabulary and speaking notes for this lesson

Key vocabulary in this video

Phrasal verbs you will hear

Grammar in this video

Pronunciation to watch

How to practise with this video

What is the Shadowing Technique?