INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

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.

What you will learn

Assign roles to source storage, transformation, routine reporting and reprocessing in an order example.

Prerequisite

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.

From order transactions to analytics
From order transactions to analytics1: Operational record — Select the time range — Lake storage. 2: Lake storage — Check grain and types — Transformation. 3: Transformation — Serve the consumer — Warehouse123Operational recordLake storageTransformationWarehouseFrom order transactions to analytics1: Operational record — Select the time range — Lake storage. 2: Lake storage — Check grain and types — Transformation. 3: Transformation — Serve the consumer — Warehouse123OperationalrecordLake storageTransformationWarehouse
  1. Operational record → Lake storageSelect the time range
  2. Lake storage → TransformationCheck grain and types
  3. 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

A fictional business needs daily reporting and later investigation.
PurposeCandidateRequired condition
Commit an orderOperational DB transactionPreserve the authoritative result
Keep evidence for reprocessingOriginal extract in S3Know the version and time range
Investigate a short intervalScoped Athena SQLVerify definitions and access
Produce the daily reportCurated warehouse resultRecord 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.

Which evidence determines whether an order committed?
Read the answer and reason

Answer: The committed outcome in SQL Server · Use the operational data that owns the transaction.

Which option can investigate a bounded interval in S3?
Read the answer and reason

Answer: Query the scoped S3 data in SQL · Athena can query the relevant S3 data.

Which evidence matters for a repeated report?
Read the answer and reason

Answer: Grain, data time and transformation version · Report consumers need definitions and freshness.

Check, then explain in your own words

  1. Separate operational and analytical needs.
  2. Record storage grain and timestamps.
  3. 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.