Exam Certified Data Engineer Professional topic 1 question 60 discussion

The answer should be A. it is the safest to generate accurate report

daily_store_sales is type1 table, no history is maintained. You have to treat every record as new record and do aggregation for every store. Overwrite is much efficient than upsert.

I will go with c. upsert

A is not correct because the table is daily. If you overwrite you delete all history. You need to insert/update to keep history.

Not sure if that's right but I would go for A. What do you think? Type1: Data is overwritten Type 2: History is maintained, new data is inserted as new rows Type 3: Stores two versions per record: a previous and a current value A. batch + overwrite -> Match Type 1 requirements. YES B: batch + append new rows -> Would be for type 2. NO C. Batch + Upsert -> Data is not being overwritten (which is required for Type 1). NO D. ReadStream + Upsert -> Data is not being overwritten (which is required for Type 1). NO E. Change Data Feed to update -> Problem is manual edits + not overwriting (required for type 1). No I have doubts around "which approach is the safest". Maybe because due to some manual changes it is hard to track changes or do upsert, so to make sure that the stats are right overwriting is safer.

Not A because overwriting will only provide a daily based data not the history of it. Not B because it will not fix the issue of incorrect sales amount As these data are fit for natch processing so neither D or E. C will only upsert the changes while making sure we are updating the records based on sales_date & store_id

E definitely because it say that the total_sales column may be change by manual auditing so not via a job, so streaming with CDF is the only option here !

I would go with Option A. Because it has manual auditing hence values can change. Uses type 1 hence replace original data

It should be E, as structure streaming has built-in fault-tolerance feature.

It said type 1 so A is the correct answer !

The question is unclear whether the aggregated table needs to support a rolling history. Note the aggregated table does not have a date column to distinguish which date the summary is generated for so one could assume the table is maintained only for the current snapshot. Assuming the above - A would be the safest option as all stores and aggregates would need to be refreshed nightly

A is correct because it's a static table that is written nightly through a batch job. The summary table does not maintain history and so an upsert results in having extra, unecessary records. Overwrite it nightly with updated aggregates for the required time period.

The answer is A. Note that the target table has columns which stores quarter to date,previous day sates etc, which will result in daily updates, i.e. large volume of records will be updated, hence better to overwirte than to update large volume of records.

Batch processing so you need to update and insert - C

Answer C is correct. Answer E would do the job too, but the table schema and the question indicates, that there will be only one update daily needed. Therefore a structured streaming job is way too expensive to archive the outcome.

Correct answer A Type 1 data is overwritten https://streamsets.com/blog/slowly-changing-dimensions-vs-change-data-capture/#:~:text=In%20Type%201%2C%20any%20new,change%20to%20maintain%20a%20history.

I would say that it is E. If daily_store_sales table is implemented as a Type 1 table, this means that values are overwritten, and we do not keep the history. So we would need to create a streaming from CDF and apply those changes into the aggregated table.