Stream-Static Basic¶

Description¶

Suitable for when you have a streaming table that you need to join to one or many additional static tables to derive your desired target data set.

Use when:

You have a single streaming table driving the data flow and want to join to one or more other tables.
You only need to reflect changes when the driving streaming table updates.
The source tables do not share common business keys.
You only need to perform basic single row transforms.

Layers:

Silver
Gold (no complex transforms or aggregations)

Models:

3NF such as ODS, Inmon and Enterprise Models
Data Vault
Dimensional: dimensions and basic transactional facts

Data Flow Components:¶

No.	Component	Description	M / O
1	Input View	Input view created over the streaming source table that will ultimately static join to one or more additional source tables, in the next view below. This view can optionally read from CDF if the source table is CDF enabled.	M
2	View	A view that defines SQL, joining the input view above to one or more additional source tables.	M
3	Append or auto CDC Flow	An Append Flow (for transactional or fact based target tables) or an SCD1/2 auto CDC flow that loads the data into the final target table.	M
4	Target Table	A streaming table, the schema of which is specified in the dataflowspec. This table is the final target table for the given flow.	M

* M / O: Mandatory or Optional.

Feature Support¶

Supported	Not Supported
Append Only & SCD 1/2 Basic transforms such as: - Data type conversion - Concatenation - Single row calculations - Formatting - Cleansing & Data Quality Rules - Conditionals and calculations (single row) across multiple source tables - Joins	Complex transforms such as aggregations Window By

Considerations and Limitations¶

Important

Updates in joined tables will not be reflected until a row with matching keys comes through on the driving streaming table.

Sample¶

Bundle: samples/pattern-samples
Pipeline: Lakeflow Framework - Pattern - Stream Static - Silver
Sample: samples/pattern-samples/src/dataflows/stream_static_samples/dataflowspec/silver_customer_stream_static_basic_main.json

Example Data Flow¶

Important

The Day 3 load below demonstrates a limitation of this pattern. Updates in any of the static tables will only be loaded once corresponding rows (with the same PK’s) in the primary streaming table are updated. This is resolved by the Streaming Data Warehouse pattern below.

Note this behavior may be perfectly acceptable in a given scenario, which is why this pattern remains relevant, as it is a simpler implementation than the Streaming Data Warehouse pattern

Day 1 Load¶

Source Tables (Append-Only)¶

CUSTOMER

customer_id	first_name	last_name	email	load_timestamp
1	John	Doe	john.doe@example.com	2023-01-01 10:00
2	Jane	Smith	jane.smith@example.com	2023-01-01 10:00

CUSTOMER_ADDRESS

customer_id	city	state	load_timestamp
1	Melbourne	VIC	2023-01-01 10:00
2	Melbourne	VIC	2023-01-01 10:00
4	Hobart	TAS	2023-01-01 10:00

Target Table¶

Append-Only Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	load_timestamp
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00

SCD1 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC

SCD2 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	_START_AT	_END_AT
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00	NULL
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00	NULL

Day 2 Load¶

Day 2 Source Tables (Append-Only)¶

CUSTOMER

customer_id	first_name	last_name	email	load_timestamp
1	John	Doe	john.doe@example.com	2023-01-01 10:00
2	Jane	Smith	jane.smith@example.com	2023-01-01 10:00
1	John	Doe	jdoe@example.com	2023-01-02 10:00
3	Alice	Green	alice.green@example.com	2023-01-02 10:00
4	Joe	Bloggs	joe.bloggs@example.com	2023-01-02 10:00

CUSTOMER_ADDRESS

customer_id	city	state	load_timestamp
1	Melbourne	VIC	2023-01-01 10:00
2	Melbourne	VIC	2023-01-01 10:00
4	Hobart	TAS	2023-01-01 10:00
2	Perth	WA	2023-01-02 10:00
3	Sydney	NSW	2023-01-02 10:00

Day 2 Target Table¶

Day 2 Append-Only Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	load_timestamp
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC	2023-01-02 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA	2023-01-02 10:00
3	Alice	Green	alice.green@example.com	alice.green@example.com	Sydney	NSW	2023-01-02 10:00
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS	2023-01-02 10:00

Day 2 SCD1 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA
3	Alice	Green	alice.green@example.com	alice.green@example.com	Sydney	NSW
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS

Day 2 SCD2 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	_START_AT	_END_AT
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC	2023-01-02 10:00	NULL
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00	2023-01-02 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA	2023-01-02 10:00	NULL
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00	2023-01-02 10:00
3	Alice	Green	Alice Green	alice.green@example.com	Sydney	NSW	2023-01-01 10:00	NULL
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS	2023-01-02 10:00	NULL

Note

Customer 1’s change of location to Brisbane did not propagate as there was no corresponding new row in the primary Customer table on this Day.
This change in location will only be reflected once a new row for customer 1 is loaded into the Customer source table.

Day 3 Load¶

Day 3 Source Tables (Append-Only)¶

CUSTOMER

customer_id	first_name	last_name	email	load_timestamp
1	John	Doe	john.doe@example.com	2023-01-01 10:00
2	Jane	Smith	jane.smith@example.com	2023-01-01 10:00
1	John	Doe	jdoe@example.com	2023-01-02 10:00
3	Alice	Green	alice.green@example.com	2023-01-02 10:00
4	Joe	Bloggs	joe.bloggs@example.com	2023-01-02 10:00

CUSTOMER_ADDRESS

customer_id	city	state	load_timestamp
1	Melbourne	VIC	2023-01-01 10:00
2	Melbourne	VIC	2023-01-01 10:00
4	Hobart	TAS	2023-01-01 10:00
2	Perth	WA	2023-01-02 10:00
3	Sydney	NSW	2023-01-02 10:00
1	Brisbane	QLD	2023-01-03 10:00

Day 3 Target Table¶

Day 3 Append-Only Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	load_timestamp
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC	2023-01-02 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA	2023-01-02 10:00
3	Alice	Green	alice.green@example.com	alice.green@example.com	Sydney	NSW	2023-01-02 10:00
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS	2023-01-02 10:00

Day 3 SCD1 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA
3	Alice	Green	alice.green@example.com	alice.green@example.com	Sydney	NSW
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS

Day 3 SCD2 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	_START_AT	_END_AT
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC	2023-01-02 10:00	NULL
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00	2023-01-02 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA	2023-01-02 10:00	NULL
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00	2023-01-02 10:00
3	Alice	Green	Alice Green	alice.green@example.com	Sydney	NSW	2023-01-01 10:00	NULL
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS	2023-01-02 10:00	NULL

Note

Customer 1’s change of location to Brisbane did not propagate as there was no corresponding new row in the primary Customer table on this Day.
This change in location will only be reflected once a new row for customer 1 is loaded into the Customer source table.

Day 4 Load¶

Day 4 Source Tables (Append-Only)¶

CUSTOMER

customer_id	first_name	last_name	email	load_timestamp
1	John	Doe	john.doe@example.com	2023-01-01 10:00
2	Jane	Smith	jane.smith@example.com	2023-01-01 10:00
1	John	Doe	jdoe@example.com	2023-01-02 10:00
3	Alice	Green	alice.green@example.com	2023-01-02 10:00
4	Joe	Bloggs	joe.bloggs@example.com	2023-01-02 10:00
1	John	Doe	john.doe@another.example.com	2023-01-04 10:00

CUSTOMER_ADDRESS

customer_id	city	state	load_timestamp
1	Melbourne	VIC	2023-01-01 10:00
2	Melbourne	VIC	2023-01-01 10:00
4	Hobart	TAS	2023-01-01 10:00
2	Perth	WA	2023-01-02 10:00
3	Sydney	NSW	2023-01-02 10:00
1	Brisbane	QLD	2023-01-03 10:00

Day 4 Target Table¶

Day 4 Append-Only Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	load_timestamp
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC	2023-01-02 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA	2023-01-02 10:00
3	Alice	Green	alice.green@example.com	alice.green@example.com	Sydney	NSW	2023-01-02 10:00
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS	2023-01-02 10:00
1	John	Doe	John Doe	john.doe@another.example.com	Brisbane	QLD	2023-01-04 10:00

Day 4 SCD1 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state
1	John	Doe	John Doe	john.doe@another.example.com	Brisbane	QLD
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA
3	Alice	Green	alice.green@example.com	alice.green@example.com	Sydney	NSW
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS

Day 4 SCD2 Scenario¶

customer_id	first_name	last_name	full_name	email	city	state	_START_AT	_END_AT
1	John	Doe	John Doe	john.doe@another.example.com	Brisbane	QLD	2023-01-04 10:00	NULL
1	John	Doe	John Doe	jdoe@example.com	Melbourne	VIC	2023-01-02 10:00	2023-01-04 10:00
1	John	Doe	John Doe	john.doe@example.com	Melbourne	VIC	2023-01-01 10:00	2023-01-02 10:00
2	Jane	Smith	Jane Smith	jane.smith@example.com	Perth	WA	2023-01-02 10:00	NULL
2	Jane	Smith	Jane Smith	jane.smith@example.com	Melbourne	VIC	2023-01-01 10:00	2023-01-02 10:00
3	Alice	Green	Alice Green	alice.green@example.com	Sydney	NSW	2023-01-01 10:00	NULL
4	Joe	Bloggs	Joe Bloggs	joe.bloggs@example.com	Hobart	TAS	2023-01-02 10:00	NULL

Note

Both the change of address on day 3 and the change of email on day 4 come through on this load.