CERTIFIED-DATA-ENGINEER-PROFESSIONAL · Question #60
The data engineering team maintains a table of aggregate statistics through batch nightly updates. This includes total sales for the previous day alongside totals and averages for a variety of time…
The correct answer is A. Implement the appropriate aggregate logic as a batch read against the daily_store_sales table. A Type 1 SCD table overwrites old values with new values - there is no historical record preserved. If total_sales is manually corrected after auditing, the previously correct value is gone. This means any incremental or streaming approach that only processes 'new' records…
Question
The data engineering team maintains a table of aggregate statistics through batch nightly updates. This includes total sales for the previous day alongside totals and averages for a variety of time periods including the 7 previous days, year-to-date, and quarter-to-date. This table is named store_saies_summary and the schema is as follows:
The table daily_store_sales contains all the information needed to update store_sales_summary. The schema for this table is:
store_id INT, sales_date DATE, total_sales FLOAT If daily_store_sales is implemented as a Type 1 table and the total_sales column might be adjusted after manual data auditing, which approach is the safest to generate accurate reports in the store_sales_summary table?
Options
- AImplement the appropriate aggregate logic as a batch read against the daily_store_sales table
- BImplement the appropriate aggregate logic as a batch read against the daily_store_sales table
- CImplement the appropriate aggregate logic as a batch read against the daily_store_sales table
- DImplement the appropriate aggregate logic as a Structured Streaming read against the
- EUse Structured Streaming to subscribe to the change data feed for daily_store_sales and apply
How the community answered
(27 responses)- A74% (20)
- C15% (4)
- D4% (1)
- E7% (2)
Explanation
A Type 1 SCD table overwrites old values with new values - there is no historical record preserved. If total_sales is manually corrected after auditing, the previously correct value is gone. This means any incremental or streaming approach that only processes 'new' records would miss corrections to previously ingested rows, producing inaccurate aggregates. The safest approach (A) is a full batch read of the entire daily_store_sales table each night to recompute all aggregates from scratch. This guarantees that any corrected values are reflected in store_sales_summary. Streaming or change-data-feed approaches (options D and E) would fail to capture silent in-place corrections that Type 1 tables allow.
Topics
Community Discussion
No community discussion yet for this question.