Topic lessonTopic: SQL Server recovery and analytics data
Data lakes and warehouses: from SQL Server transactions to analytics
Separate authoritative transaction records from data prepared for analysis. Assign roles to source storage, transformation, routine reporting and reprocessing in an order example.
Assign roles to source storage, transformation, routine reporting and reprocessing in an order example.
The records are synthetic. No business data is handled.
Separate storage, transformation and analysis
Athena runs interactive queries over data on S3. Redshift Spectrum enables queries over external S3 data from Redshift.
- Operational record → Lake storageSelect the time range
- Lake storage → TransformationCheck grain and types
- Transformation → WarehouseServe the consumer
- Operational record
- Order committed in SQL Server
- Lake storage
- Retain extracted order details
- Transformation
- Validate keys and fields
- Warehouse
- Serve curated reports
Match each purpose to its data boundary
| Purpose | Candidate | Required condition |
|---|---|---|
| Commit an order | Operational DB transaction | Preserve the authoritative result |
| Keep evidence for reprocessing | Original extract in S3 | Know the version and time range |
| Investigate a short interval | Scoped Athena SQL | Verify definitions and access |
| Produce the daily report | Curated warehouse result | Record refresh time and definitions |
Lakes and warehouses can work together. Retain extract time, granularity and transformation version so analytics can be compared with the source.
Avoid counting one order twice
Declare the data grain. Record order versus line-item grain and whether the timestamp refers to acceptance, commit or extraction.
Distinguish ad hoc and recurring work. Compare the cost and work involved in a scoped investigation and a routinely prepared report.
Plan corrections and reprocessing. Track input version and execution ID. Verify replacement output before making it visible alongside earlier results.
Check the worked example
Choose an option to see the result and explanation.
Check, then explain in your own words
- Separate operational and analytical needs.
- Record storage grain and timestamps.
- Verify refreshes and reprocessing.
Check your understanding: Does a data lake provide every business and warehouse guarantee?
Check transformations, report definitions, freshness and transaction requirements separately.
Storage location alone does not establish data meaning.
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.
- Redshift Spectrum overviewChecked:
Scope and conditionsExternal queries over data in S3.
Used in this lessonSeparate storage, transformation and analysis Match each purpose to its data boundary Avoid counting one order twice
- What is Amazon Athena?Checked:
Scope and conditionsInteractive SQL analysis over S3 data.
Used in this lessonSeparate storage, transformation and analysis Avoid counting one order twice