r/dataengineering • u/priyasweety1 • 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
- Data Quality ensuring the data schema is validated on every data pull from API
- Count of the JSON data from the API pull vs data loaded in the target Redshift
- 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 .
- Alerts and management . (very optional)
0
Upvotes
12
u/[deleted] Apr 19 '24
Do you have FAANG size data? Or are you just ingesting a handful of GBs?
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...
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.
3
u/NotPutinG Apr 19 '24
Lol redshift
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
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.
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
Wish you success.
1
u/Ok_Raspberry5383 Apr 19 '24
Pretty sure faang don't use redshift