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: 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.

What you will learn

Compare data locations with the same metric definition, period and row counts.

Prerequisite

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.

From a business snapshot to two reporting paths
From a business snapshot to two reporting paths1: Extract — Fix the period — Local. 2: Local — Align identifiers — External. 3: External — Measure frequency — Compare123ExtractLocalExternalCompareFrom a business snapshot to two reporting paths1: Extract — Fix the period — Local. 2: Local — Align identifiers — External. 3: External — Measure frequency — Compare123ExtractLocalExternalCompare
  1. Extract → LocalFix the period
  2. Local → ExternalAlign identifiers
  3. 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

Reconcile counts for the same orders before selecting a layout. Benefits depend on the workload.
UseCandidateAcceptance
Daily sales reportCurated local tableSame cutoff and counts
Annual detail reviewSpectrum external tableBounded date range
Join with legacy codesLocal and external joinCheck types and duplicate keys
Revised cutoffSame frozen inputExplain 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.

What should a daily approved report establish first?
Read the answer and reason

Answer: Cutoff, definition and rerun rules · Its business definition must be reproducible.

Which option queries occasional S3 history from Redshift?
Read the answer and reason

Answer: A Redshift Spectrum external table · Evaluate an external table.

The join doubles the row count. What should you inspect?
Read the answer and reason

Answer: Key types, duplicates and expected cardinality · Check why the join multiplies rows.

Check, then explain in your own words

  1. Separate recurring and occasional uses.
  2. Fix comparison conditions.
  3. 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.