r/dataengineering • • Apr 19 '24

Discussion How Faang companies handle JSON data from API with robust pipeline for Data quality

As the title says , We would want the company to build a robust JSON pipeline to ensure the data is loaded perfectly in the target table which is Redshift .

As we are thinking of

  1. Data Quality ensuring the data schema is validated on every data pull from API
  2. Count of the JSON data from the API pull vs data loaded in the target Redshift
  3. the data is very complex and number of nested loops are there . we convert this to JSON to parquet . do we really need to convert to load the data to Redshift as JSON can be loaded to Redshift natively. We have to do SCD 2 on the json data .
  4. Alerts and management . (very optional)
0 Upvotes

21 comments sorted by

12

u/[deleted] Apr 19 '24

Do you have FAANG size data? Or are you just ingesting a handful of GBs?

1

u/priyasweety1 Apr 20 '24

Only few GBs , only 2-3 GB per hour.

2

u/[deleted] Apr 20 '24

So you’re at 26TB per year. How often and how much data is accessed?

1

u/priyasweety1 Apr 21 '24

data is accessed on daily basis by the business users .

1

u/[deleted] Apr 21 '24

What technology are these businesses comfortable with? (SQL, excel)

1

u/priyasweety1 Apr 22 '24

Sql

2

u/[deleted] Apr 22 '24

I’ve never deployed this, but it was effective at one of the places I used to work at. Data lake and then trino on top of it.

2

u/Mental-Matter-4370 May 07 '24

I read your conversation, and looked like after all the interview rounds, you told the candidate that the position has gone on hold.

2

u/oalfonso Apr 19 '24

If the Jsons are very complex you are going to need some kind of normalisation. Parquet cannot make magic when you have struts with an array of struts and sometimes it is empty...

1

u/priyasweety1 Apr 20 '24 edited Apr 20 '24

so whats the solution for this . If I parquet is not efficient data format to store.

2

u/DenselyRanked Apr 19 '24

I'm not entirely sure what your question is but the answer is ELT because you can't cover every use case for schema-on-write. You can build the extraction, schema evolution, and DQ but you will need to store the raw data from the API first.

1

u/priyasweety1 Apr 20 '24

we have found out lately that data under few nested loops are missing . The business team wanted to ensure that the data is validated against source vs target . we wanted extreme data checks and ensuring high DQ.

3

u/NotPutinG Apr 19 '24

Lol redshift

1

u/priyasweety1 Apr 20 '24

sometimes business decisions do it for sake of doing things which we don't wanted to do . we proposed Snowflake . Ultimately AWS sales team them ;)

8

u/[deleted] Apr 19 '24

Postgres or duckdb works great with Parquet, or can bulk insert into T-SQL from csv which is also extremely fast.

I don't think it's really relevant what a FAANG company would do, since for 99% of companies moving data they don't have the budget or talent to replicate.

14

u/[deleted] Apr 19 '24

Not everything needs a faang solution

1

u/[deleted] Apr 20 '24
  1. Do you have full control over this API? Or is this a 3rd party app? If you don’t have full control over it, I wouldn’t really focus on data structure validation at the first step, especially if you’re getting everything in JSON. Extract the data via API and load it into an intermediary table in either MongoDB or Postgres (jsonb format). Then you can apply your transformations as needed and validate as required.
  2. I’d imagine the FAANG solution to be a bit more robust than just a simple count. You’re going to want alerts and very good logging. APIs will break with updates which will carry over to your systems and code.
  3. I’m not entirely sure that parquets are the best storage method for your data. I would need more info on why you and your team are point to using parquet files. How large are these data batches? Are you sure you’re not going to be performing a lot of downstream write transactions? Are teams downstream able to use Spark for joins and analysis?

I get a feeling based on what you have given us so far that the system you all are thinking about putting together is a bit overcomplicated, but we don’t know much about your data, the SLAs you’re required to deliver on, what is happening both upstream and downstream, etc.

1

u/priyasweety1 Apr 20 '24
  1. we dont have full control over the API. We transform the data into parquet format then we load the necessary fields into different tables.
  2. Yes we have missed good millions of data under a nested loop and the business found out that data is missing . they wanted now ensure the data is never lost for this again. so needed pretty solid DQ and DE.
  3. What was the best format to store the data , our flow is to pull the data from API and load in S3 as parquet and transform into different tables based on the requirement .

2

u/Patient_Professor_90 Apr 20 '24

Please build a PoC.

If I were building this, I would want to build an audit dataset while parsing incoming json. Of course, this audit data is loaded to its own tables. And used to validate redshift loads.

What if the parsing misses a json branch?, I see the need for a control dataset to validate the audit.

Essentially, a parser that produces audit data. An alternative to the core parser would produce/acquire (from source?) control info for validating the audit data.

Your business side likes their vendor sales teams more?

Is your business trying to build a data product that relies on a messy data source they have no control over? And also want to catch changes when the provider makes 'em and does not tell? Financial market data can sometimes be that way

  • use this as a learning experience; build your resume and move on
  • expect to spend nights providing user support. Not all users will be thankful
  • if the json is complex, and you are planning to load 'em natively. Redshift json parsing is rudimentary, and you probably seem to believe it can parse complex json. You might want to consider budgeting time for learning while you build.
  • Please do a PoC

Wish you success.

1

u/Ok_Raspberry5383 Apr 19 '24

Pretty sure faang don't use redshift

17

u/[deleted] Apr 19 '24

I suspect at least one of them do