Topic lessonTopic: AWS data pipelines and SQL Server integration
Redshift local tables and Spectrum for SQL Server extracts
Separate a recurring approved report from occasional access to old detail. Compare data locations with the same metric definition, period and row counts.
Compare data locations with the same metric definition, period and row counts.
This exercise uses fictional evidence and makes no changes in a live environment.
Separate frequent reporting and history
Spectrum queries external tables on S3 and can join them with local Redshift tables. Estimate ingestion and quality checks separately for recurring reports.
- Extract → LocalFix the period
- Local → ExternalAlign identifiers
- External → CompareMeasure frequency
- Extract
- Freeze the SQL Server output
- Local
- Curate recurring metrics
- External
- Retain low-frequency S3 detail
- Compare
- Check results and workload
Daily reporting and history requests
| Use | Candidate | Acceptance |
|---|---|---|
| Daily sales report | Curated local table | Same cutoff and counts |
| Annual detail review | Spectrum external table | Bounded date range |
| Join with legacy codes | Local and external join | Check types and duplicate keys |
| Revised cutoff | Same frozen input | Explain differences after rerun |
Timing comparisons require equivalent answers and equivalent workload conditions.
Fix the comparison conditions
Record the layout Check table location and compatible column types.
Freeze business meaning Specify the cutoff, time zone and how cancellations count.
Measure equivalent work Compare scanned data, runtime and maintenance after confirming the same answer.
Check your decision
Choose an option to see the result and explanation.
Check, then explain in your own words
- Separate recurring and occasional uses.
- Fix comparison conditions.
- Reconcile output before choosing the layout.
Check your understanding: Is the smallest scan always the right choice?
Compare result equality, freshness, recurrence and operating cost.
A reporting design must keep answers correct and reproducible.
Sources and conditions checked
The official references used in this lesson are collected here with their scope, conditions and corresponding sections. Independent expert review and live AWS comparison have not been performed.
Scope and conditionsWorking with external S3 tables and local tables.
Used in this lessonSeparate frequent reporting and history Daily reporting and history requests Fix the comparison conditions