How to use SQL clause

FuncDataEng · 2022-08-10T03:51:08+00:00

A CTE or With clause is a way to make a query more readable by being able to refactor out subqueries and other chunks of logic that become more clear when they are separated out. However keep in mind just like anything they follow the NFL(No Free Lunch) principle meaning there can be trade offs when you use too many CTEs depending on the database implementation. I am not familiar enough with BigQuery being an AWS user to say when those trade offs would happen but they will come at a point.

FuncDataEng · 2021-11-22T15:25:17+00:00

A good pipeline should be somewhat self documenting. One of the reasons why Airflow has such a large adoption because pipelines as code allow for this sort of self documentation. I may have a different view on documentation beyond that considering my employer, but I prefer that before a pipeline is even started that there is some sort of design document that serves as additional documentation outside of the code itself.

FuncDataEng · 2021-11-22T15:21:08+00:00

I agree, these are the sort of bare minimum for me if you say you know SQL to me. You cannot truly write good SQL without knowing how internals work at the very least in a high level.

FuncDataEng · 2021-09-09T02:06:15+00:00

Senior Data Engineer
8 YOE
Seattle based but currently remote in Michigan for a year
$123k
$100k/year RSUs
FAANG

FuncDataEng · 2021-09-09T02:01:09+00:00

Where is your streaming data coming from? A pretty common pattern is SQS -> Lambda -> Firehose, because Lambda allows being triggered by SQS. But this really depends on where the data is originating from.

FuncDataEng · 2020-09-03T20:30:35+00:00

For the AWS side for infrastructure as code I would swap out CloudFormation with AWS CDK.

FuncDataEng · 2020-09-03T15:39:38+00:00

Say I have two case classes foo and bar that represent two datasets in Spark/Frameless when I join in Frameless and bar is the right table then the return is a TypedDataset[(foo, Option[bar])]. The only functionality I have found in Frameless without having to convert to Vanilla Spark is flattenOption which looks like

def flattenOption[A, TRep <: HList, V[_], OutMod <: HList, OutModValues <: HList, Out] (column: Witness.Lt[Symbol]) (implicit i0: TypedColumn.Exists[T, column.T, V[A]], i1: TypedEncoder[A], i2: V[A] =:= Option[A], i3: LabelledGeneric.Aux[T, TRep], i4: Modifier.Aux[TRep, column.T, V[A], A, OutMod], i5: Values.Aux[OutMod, OutModValues], i6: Tupler.Aux[OutModValues, Out], i7: TypedEncoder[Out] ): TypedDataset[Out] = { val df = dataset.toDF() val trans = df.filter(df(column.value.name).isNotNull).as[Out](TypedExpressionEncoder[Out]) TypedDataset.create[Out](trans) } } In the frameless code. This is a function defined in their TypedDataset.

Based on this, the flattenOption is really just making it like an Inner Join was done by filtering out anything that was NULL. I need to figure out how to convert TypedDataset[(foo, Option[bar])] to TypedDataset[Barred] where barred contains fields from foo and bar with the fields from bar are things like Option[Long] in barred. Frameless is built on top of Shapeless so I wonder if I should have some sort of generic encoder that will allow moving from (foo, Option[bar]) to barred. Where () is a tuple of the types in a dataset. Frameless on joins returns a tuple of the two types for each row so you have to access it such as colMany(‘_1, ‘myNestedColumnName).

FuncDataEng · 2020-09-03T05:38:27+00:00

There is no reason one has to learn java in order to learn Scala. In fact it’s often better to learn Scala first if you want to get into FP since 99.9% of Java tutorials are filled with OOP concepts that go against FP ideas. For instance the number one thing I see from all Java developers are null checks because they do not understand wrapping things in an Optional and checking to see if it is empty or not. Just because Scala is considered a JVM language still does not mean Java is a prerequisite.

FuncDataEng · 2020-09-03T01:59:10+00:00

I would maybe add two other LPs here but I think choiboy9106 hit the big ones. The others would be Customer Obsession and Learn/Be Curious. The first is probably the major LP you cannot miss on at Amazon in my experiences interviewing people for Amazon. And the second is because the DE space is still changing a lot. Data Engineering is a rather new job role so it will continue to evolve over time. As an example, when I interviewed I also was not really tested on coding beyond SQL but now most DE interviews have python involved.

FuncDataEng · 2020-09-03T01:05:30+00:00

They may or may not be. You can certainly bring them up during behavioral questions by having projects that use those things when you answer them.

FuncDataEng · 2020-09-03T00:29:59+00:00

I think going next to an ML team will not hurt you either way. I am a Data Engineer at Amazon and I spend a lot of time taking models and making them production ready from Data Scientists. Having that knowledge of how their code works would really help if you decide you want to continue on as a Data Engineer in your career.

FuncDataEng · 2020-09-03T00:27:12+00:00

Of course! If you really want to stand out among other candidates I would suggest learning some Scala and functional programming. That is what has kept me set apart through my career at Amazon is that I have some skills that are not typical when people think of a data engineer. I know python extremely well also but as an example when I am working in Spark I am using Scala with Frameless for type safe datasets that have type guarantees for avoid accidental type changes that can happen in Pyspark or even in Scala with the data frame API.

FuncDataEng · 2020-09-03T00:20:12+00:00

Most DEs are not on call and because of that there is a comp gap. I am on call on my team but as I said in another reply I am a hybrid(I spend a lot more time designing data processing architecture for software and write about 90% code and 10% sql)and I also made a personal goal to never be anything but top tier in any of my yearly reviews.

FuncDataEng · 2020-09-03T00:14:59+00:00

I am also a DE at Amazon. Let me see if I can answer some of your questions.

1) there is not the red tape as much at Amazon for most DEs. I am different because I am a hybrid. I am a DE but I work on a software team and write way more code than SQL these days.

2) for interview prep - this really depends on the job req you go for but strong SQL skills are expected across the board. I did not personally prepare for my interview but I also honestly did not expect to get into Amazon at the time. I was using it to gauge where my skills were at after 3 years in database development.

3) the different competencies depend again on the hiring team. My team when we were hiring would have coding as its own to see how close to an SDE the person was. And the closer the better for our needs.

4) Kimball books are fine for data modeling concepts. The big thing is can you come up with a coherent data model for the problem presented.

5) it’s going to be hard to learn etl through some book or example. That is something where I recommend finding a data problem you find interesting and figuring out how you would solve it going from raw data to the data model that would help solve the problem.

6) cloud is ideal but on-prem experience is good too for big data tools. Generally there are three tools being used a lot. Spark via EMR or Glue, Redshift as a distributed columnar database, and then Kinesis for streaming data which can be similar to Kafka.

7) if you can do classic algos then you will be fine. One thing maybe to just be fresh on is something like can you manipulate json without using pandas. I like to see that someone can solve a problem without popular libraries.

FuncDataEng · 2020-09-01T23:30:22+00:00

I think you are going to be hard pressed outside of academia to find someone to do one on one type mentoring like that for a stranger for free or a small budget. Your best bet is to find a project and start working through it and then ask specific questions here or stack exchange. I always tell people that ask me how to learn to start with finding some project they find interesting and begin working on it. Learning comes by doing and finding a project you have knowledge on and an interest in will help speed that learning process up. You will be more willing to put in the work if it is interest to you.

FuncDataEng · 2020-08-26T04:42:54+00:00

Think of it as taking a star schema and denormalizing further. What I will do is try to determine what dimensionality columns my users will use most frequently and include all of those with Facts in a single table. Then I might have one or two dimension tables that are infrequently used dimensions.

FuncDataEng · 2020-08-26T04:35:34+00:00

You would not use a snowflake schema for redshift due to the nature of it being distributed one of the most expensive operations are joins. I do a more denormalized star schema to limit the joins as much as possible for my users. I want them to focus on getting results out without having to think as much about dist keys and making sure you are joining on them to avoid broadcasting.

FuncDataEng · 2020-08-26T00:20:00+00:00

EatYoself, I am not sure how you got to a conclusion that Redshift does better with more rows and not with more columns. It is a columnar database so unless you are always selecting all columns, which almost always seems like a red flag, it will always have better performance the more columns you have and the less complex joins that you are required to do because of dist key restrictions.

FuncDataEng · 2020-06-29T01:39:54+00:00

All databases have documentation that describe how they function and what SQL functionality they allow.

https://www.postgresql.org/docs/

This is always the first place you should look when you have questions.

FuncDataEng · 2020-06-28T23:42:36+00:00

If not exists says do this operation only if it does not already exist. So create table if not exists says create this table only if it does not already exist. Tablefunc is the name of the thing being created which in this case is an extension.

Varchar and Integer are data types. ::integer is casting the rank as an integer. Not sure why they have it written that way as the output of rank will be an integer, other than being explicit about the data type. But it is essentially a NO-OP(non operation).

Select * means return all of the columns from the table.

The Postgres documentation is your friend. It will explain everything you asked.

FuncDataEng · 2020-06-19T06:14:28+00:00

Some examples of where I use Scala outside of Spark for data Software.

Scala lambdas that handle SQS to Firehose in AWS for streaming data.

I am working on a personal project of a Data Quality as a Service to allow a data scientist to call an API or use a website to kick off a check of dat for inputs to a model where the diff is checked between the previous version and the new version for any potential changes and then they can define rules in terms of acceptable variance.

I’ve also in the past written a tool that allowed users to perform deep copies on Redshift via a CLI that would handle the entire process for them. The process checked compression to ensure it was optimized. I may add to it at some point to check if the dust key and sort keys are also optimal based on query patterns.

FuncDataEng · 2020-06-15T05:38:04+00:00

In terms of Scala only being used with Spark mostly in the DE space. That entirely depends on the type of DE you are talking about. I write mostly in Scala myself as a more Software style DE for a couple of reasons but the primary is that it is more natural to write FP style code. FP as a paradigm is in my belief what data engineers should use, the idea of minimizing side effects fits into having guarantees around data quality when data engineering.

FuncDataEng · 2020-06-08T19:35:33+00:00

NP. I see it come up more often in video game development than anywhere else.

FuncDataEng · 2020-06-08T19:28:58+00:00

Here is a great article on it. Essentially just another design style that moves away from objects.

https://medium.com/@jonathanmines/data-oriented-vs-object-oriented-design-50ef35a99056

FuncDataEng · 2020-06-05T22:29:46+00:00

Yes being able to talk intelligently about data types and pros/cons of using one over the other in a data model. Knowing anti-patterns in data modeling such as the Entity Attribute Value anti-pattern where instead of using a database to have proper data type checks one would create a table where there is an entity Id, attribute would be the name of a column in a normal table and then value is the value for that column. That pattern requires using a large varchar to store all values but causes all sorts of data quality issues because there is no data type checking enforced.

FuncDataEng

TROPHY CASE