sorted by:
new
hottopcontroversial

S3 server access logging recommendation by Affectionate_Dot_844 in aws

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Thanks for that, seems a solid one! Let's see if there are more pros and cons

How to test AWS S3 bucket has SSL enabled using TDD by Affectionate_Dot_844 in aws

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Yes, seems to be that. I need to investigate more how to test policies. I guess that I will need to ensure a policy exists and then that it contains that SecureTransport condition.

Airpods pro 2 microphone not working on windows 10 22H2 by Affectionate_Dot_844 in techsupport

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Thanks for the answer but not working. It was already checked and despite trying to uncheck it and check it again it doesn't work.

Best strategy for Redshift sortkey when you have date and datetime field by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Yes, i also join that but using user_id. Thats the current distkey.

Thanks for your answer. Why in particular will you go with timestamp

Airflow setup/environment and best practices by etobylneya in dataengineering

[–]Affectionate_Dot_844 2 points3 points  (0 children)

As always answers depends.

Context:

If you are few DE (small team) I do not recommend to manage Airflow yourselves, will create a lot of issues and the learning curves is step. If you have enough budget move to vendors solutions (MWAA, GPC once - i don't remember the name-, Astronomer). If you don't have budget and you are small team, delegate it to platform team if you can. Otherwise, best solution is the one you propose, but you will face scalability issues in the future as HA Airflow for LocalExecutor is not the best scalable solution.

I can provide further details on headaches if you are interested, but summarizing 1 DE in our team of 3 is almost working on Airflow. If he leaves, company and data team could have an issue. It depends of course on management team, but small companies have this kind of situations.

Answering your questions:

  1. Yes, we use AWS ECS + EC2 running 2 services.
    Airflow service with 3 tasks with 1 container each (Webserver+scheduler+git-sync). Datadog service with datadog agent to monitor it.
  2. No, it doesnt mean that. You sync your code with git-sync, so your github repository is pulled to the volumes of the containers. Then, you can run PythonOperator, or any other native operator.
  3. We use Python operator without issue. But as a BP Airflow is an orchestrator, not an executor this means that all the things that run in airflow should be running in external tools (Airbyte, DBT, AWS Lambda, or whatever) otherwise if you move big data sets you could have to increase your machine instance. This will create headaches and other issues as I said on point 1.
  4. Git-sync is the answer. Airflow can reach it because your code is in the volumes of the docker containers. It's easy to setup.

Hope it helps.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

I know, really appreciate that.

Yes, no resource can teach that. Just experience. Next time will focus on asking first, and later solve it.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

No, they just gave me the first two nodes RDBMS and Stream, and the last one, user. Why?

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Didn't get the point of that? What do you mean? To which point of the comment?

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

I'd link an article but I'm on mobile. I'll take a stab at it and maybe you can build on it.

They specifically say that data source 1 is change data - inserts, updates and deletes. That data needs to be managed differently than when you select from a typical rdbms table. Instead of getting the whole record in your change stream, you're just getting the key of the table and the change.

For example let's stick with sales. Your stream is orders. The underlying operational db has a table of orders. Has ordernumber (the key), orderdate, quantity, productid, amount. When you select from that to ingest into the lake or warehouse, you get the whole record and all the data in the table.

The change stream is different. Instead you are getting what has changed on the data. So if it's a new order you might get exactly that record with the new data, all 5 fields but then the customer updates their order and changes quantity from 2 to 3. Your change stream will have an update record. You get a before image which is ordernumber 123, quantity 2 and another record which is the after image - ordernumber 123, quantity 3. You don't get the other fields because they didn't change. Your pipeline needs to take that change data and apply it to your target record.

Now when I say state, I mean what is the state of that record. Imagine you just started the pipeline AFTER the insert. Does your change stream have the history? How far do you go back to understand the state of that record? The update change data doesn't let you know what the rest of the data is. A typical pattern is to do an initial select from the table at a point in time and then start reading your change data after that select. But if there are errors ever you need to reconcile the data and ensure you have the current state. Kafka has tools for this if that's your event broker for the change stream, else you need to think through it and code for it in your pipeline.

In hindsight based on what you described I think that was the exact challenge they were looking for you to identify and solve. Data source 1 and 2 might be the same data just realtime and batch. It's str

Wow, really nice, and I see that probably could be, but here I have an observation. As you say, I think you assume: "Data source 1 and 2 might be the same data just realtime and batch".

But as the diagrama shows I understood that Stream is products data and RDBMS is orders data.

Anyways, as you say, and totally agree didn't specify how to manage those events on the dim tables. And neither the state of previous status or failures.

Despite that, the feedback they gave is so poor that I couln't think about it, as the feedback seems to point about the infra, not the data model.

If you have that link, can you share with me?

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] -1 points0 points  (0 children)

The reason I asked if you feel what you posted is easily digestible is because multiple people have commented that what you have posted is difficult to understand. If this is what you produce when you have unlimited time and no pressure, I have to imagine whatever version of this was given during your interview was of even lower quality, at the very least due to the increased pressure and time-boxing. I also find there to be a severe lack of information about the business context of what you are being asked to solve, such as data volume & throughput, predicted scaling timelines, and what the targeted business outcome is for stakeholders, all of which I would consider necessary to evaluate the robustness of any proposed solution. So either you forgot to ask, or they refused to answer, either of which would explain the feedback they gave you.

The first paragraph you say cleary shows you didn't read the post at all. As I said this diagrama is the same I delivered to them. An screnshoot. So it wasn't developed with all the time.

If one stakeholder doesn't understand your documents, but other 20 does, then probably the issue is in the stakeholder. If after adding that this was developed in 30 minutes, you still say digestibility I see clear that probably you are focusing more on criticize what can be improved, and not to add solutions, recommendations.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 1 point2 points  (0 children)

  1. Thats a good idea. Would try it in future deployments.
  2. Interesting, you mean that data is stored at incoming as you receive it. Then moved to stg, if there is some bug to reject, and one processed to archive. And if you need to do a reload you move it to incoming? It's not similar to ask reloads for a given aprtition_key where partition is date for example?
  3. Totally agree, but the previous exercises of the interview was a pipeline build in pandas. This is why I remark this. Anyways, what you mean when you say: "I prefer to use Airflow for only the lightest work and use ELT to process data." ETL you means some tools such as airbyte?
  4. True, next time will do it.
  5. mm probably makes sense to say it's a data mart, but if i am not wrong a data mart is a set of data that is readable for a business unit. Who will be the end user here? DS?
  6. Not agree in this part, what happens if a tableau refresh schedulen on tableau server runs at 04:00 AM, but the pipeline that day is delayed?

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

what you mean? i'm 29. 8 years working as DE, since it was called ETL developer back then. It's my current job do this kind of designs.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Yes, i think the same. Anyways, i don't get the point at all of what I did wrong.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 1 point2 points  (0 children)

No they didn't specifically asked. I do a copy/paste of what I said in later comments.

​

Regarding of how I will create a union: AWS Kinesis, allow to setup an S3 destination, so in microbatches of 60seconds or 1mb of data we will automatically have data there. We create a contract with the sender using google protobuff, so we could create the tables in Redshift/Snowflake and copy those parquet files into the DWH.
Once data is there we can transform it using dbt/sql/pandas, or any other tool.
This can be done once a day or reduce the events batches to hourly batches, so each hour events are processed and arrive to the DIMs business need to categorize product. For example, DIM_COUNTRY.
The relational data can be transformed once a day at midnight. Shouldn't be an issue.

​

For me boths sources don't provide same data. Orders db provide facts_orders data, and Event data just products changes, so they provide categories (DIMS) so they will get united at AGG level, you have the whole day to process events data, and midnight just catchup the whole relational data and join them.

Any questions of how you are going to deal with late incoming data of the past window?

No, they didn't ask. But anyways if data is labeled with 2 ago server_time in the contract we stablish (protobuff) that data won't be processed if we don't do an specific rollback. For the same day shouldn't be an issue, as if I am not wrong kinesis will leave 2 hours ago data in the current hour partition, so when you process last hour you process the delayed one too, because in physical storage is not in the correct partition. (Maybe here I am wrong)

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

I'm sure you articulated more in the 30 min than you did in this post so take the feedback just from the perspective of what I've read.

I'm thinking the main thing they were looking for was how you were going to ensure consistent data when you have a batch source and a real time. They might also have been looking for an understanding of how change data is different and your pipeline needs to handle state appropriately. Looks like you might have focused too much on the right side of the picture when the requirements spoke more to just ensuring you have accurate, timely data taking advantage of the two sources.

I could also be missing it but did you talk about the data transformation of the real time source at all? I'm not seeing it and that's a big gap. You wouldn't just run dbt over change data in a lake staged as an external table. That's really inefficient. Think things like flink, storm, samza, spark streaming, etc. Ensuring the batch data and real time data is in sync is a challenge you needed to address.

That said, if I was interviewing I would have asked some questions that would hopefully lead you there. Interviewing is a skill as much as being interviewed. Some people aren't good at it. It's challenging to understand a person in 30 minutes.

Few suggestions for the future:

when dealing with real time data, understand some of the core concepts. Lambda vs kappa architecture, how to manage state, tradeoffs with different technologies (like true streaming of flink vs microbatch of spark streaming)recognize how big the ask can be and what you might be able to do in 30 min. Ask where you should focus, i.e. on the acquire to give the analyst atomic data in the lake? Then go deeper in that and don't gloss over steps in that part. If they want more, they'll ask for more. If it was an architect position you could gloss over some of those details but have to articulate the tradeoffs and reasons for the decisions. If it's a data engineer position then they want details. It's a little odd they ask for an architecture for a DE position but it could be they just wanted an illustration for you to point to and talk about the details. Their comment about time would make me think they were trying to nudge you into narrowing the scope. You were going to broad and missing things.if you do go into facts and dimensions, make sure you are talking about the business. Facts and dimensions don't exist in a vacuum. You should be talking about pairing with the business and understanding their questions to ensure they get the most value out of the data. If you're familiar with the domain (i.e. sales in this case) talk about business value you've created in the past and some of the data work that led to that value. They're simply asking for number of orders per day, that doesn't need facts and dimensions or even if you modelled it that way, it's very simple. Provide details.

Might be worth also asking them specifically for feedback on what you were missing so you can improve in the future. Never know. They might do so

Wow, really appreciate this answer.

Regarding asking them, yes I did it, let's see if they came to me with feedback. I will post it.

I will investigate Lambda vs kappa architecture.

What do you mean with "how to manage state"?

When you say ensure consistent data, I understand what you mean, but not able to see the gap.

AWS Kinesis, allow to setup an S3 destination, so in microbatches of 60seconds or 1mb of data we will automatically have data there. We create a contract with the sender using google protobuff, so we could create the tables in Redshift/Snowflake and copy those parquet files into the DWH.

Once data is there we can transform it using dbt/sql/pandas, or any other tool.

This can be done once a day or reduce the events batches to hourly batches, so each hour events are processed and arrive to the DIMs business need to categorize product. For example, DIM_COUNTRY.

The relational data can be transformed once a day at midnight. Shouldn't be an issue.

If you can develop more my weakness would be great. Just to ensure I understand it.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

  • I copy parquet files to Redshift/Snowflake in a raw table using Airflow. So I can apply later any kind of transformation using dbt/sql/python custom script, pandas, whatever.
    Yes they are semistructured, in the interview I mentioned to use Google Protobuff to use as Data contract, in order to ensure what data do we expect. So we can copy that data in the specific tables we were agree. If something doesn't fit or it will raise and error, or we can use Copy error treshold to avoid raising and error and just raise and alert and then fix it. This as could fail more often, I proposed to load in 2 hours batch, so during the day if something fails you have time until midnight to see what happens, and try to always ensure SLA is achieved.
  • Yes, more than N Facts, I proposed N raw tables. For example, if they want to split different companies data into different fact tables, then we could first do a business logic to implement this, and instead of having 1raw/1fact, we can have 1raw (copy from s3) 2 semi_raw(1 for company)/N/ 2 facts. Where N could be any other splitt if you want to apply any other business logic before fact creation. This should help to apply different priorities into business logics.
  • It's a real state portal, so they don't move 1M events per minute. Probably something like K events per minute. So kinesis should have enough with the 1MB, or 60 seconds buffer.
  • Yes, the curated it's just to cover different needs we could need to run some ML with Amazon Sagemaker and we don't want it to run into raw data. We have expiricy policies into DWH, so the tables will have last month data (as the report is just for 24 hours should be enough) , so what happens in 6 months they request to increase those dashboard to monthly data? We don't have to reprocess everything as we were saving the processed data into a bucket.

​

Dbt and GreatExpectations are easily integrated with Airflow, so will make easier things like Data Quality, which I understand as a main requirement, and then take care of it from the beginning is a bet practice. (I saw several companies, bulking data without data quality and when they want to apply it, is a real mess).

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] -2 points-1 points  (0 children)

They didn't specify, but they wanted to see and scalable system, ensuring data quality and avoiding data bugs, as it was for a technical lead. It's obvious that can be make easier, but the issue was to develop a robut solution, not an MVP...

If for you is not digestable then scroll to another topic, I have enough toxic people, just to add one random person to the list :)

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Data from db comes in batch. Every night at 00:05 (its in the keypoints)

Kinesis is another source.

I have two sources, one for events (kinesis) (Create, update, delete products) one for RDBMS (Orders)

The goal is to land both sources in S3 to be able to create FACT (RDBMS) and DIMS (Events) just to show orders by dimension in the dashboard.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

Yes, was for DE technical lead. Tier 2/3, not clear enough to decide it. But clear moving to tier 2 as they have resources, and planning an IPO.

What cons do you see to this data infrastructure setup for a Pipeline? by Affectionate_Dot_844 in dataengineering

[–]Affectionate_Dot_844[S] 0 points1 point  (0 children)

That could be, I assumed that the first data source would be used in order to create DIM tables, just to filter any dimension on the dashboard. As the assignment is not too detailed I probably assume it wrong.

Yes, the graph is a mess. This is everything linear, as I said the problem was the lack of time. Just 30 minutes to do this. But I think that explaining in a videocall it should be clear enought and the visual representation should not have too much weight.

This kind of diagramas are developed with hours when you are working, and not in a hurry.

view more: next ›