Daily sales use an order header and multiple order-detail rows.
Do not add a header amount once for every detail row after joining the tables.
Which transformation is most appropriate?
Aggregate details to the order grain before joining, or otherwise make the join and aggregation grain explicit.
Detailed explanation
Aligning grains prevents one header amount from being repeated for each detail row.
Aligning grains prevents one header amount from being repeated for each detail row.
Storage class affects cost and access, not join grain or aggregate results.
Storage class affects cost and access, not join grain or aggregate results.
A one-to-many join repeats header values and overstates sales.
A one-to-many join repeats header values and overstates sales.
IAM defines access permissions and does not execute SQL aggregation.
IAM defines access permissions and does not execute SQL aggregation.
Check official documentation
Compare the following points with the official documentation.
Redshift SQLのJOIN、GROUP BY、集計粒度に関する公式説明を確認する。
What to confirm
保存設定ではなく、結合キーと1行の意味を基準に変換を設計できる。
Key points
- Aggregation grain
- JOIN
- GROUP BY
- Double counting
Notes
- Environment: AWS公式DEA-C01試験ガイドと各サービスの公式ドキュメントの確認
- Confirm current AWS service behavior in the official DEA-C01 documentation.
Foundation review
Grain contract
Documenting row meaning and key uniqueness makes joins and totals testable.
Reconciliation
Compare row counts, key duplicates, and totals before and after a transformation.