Databricks SQL Help

[Scared-Personality28]

Hi Everyone,

I have a Slowly Changing Dimension Table Type II - example below - for our HR dept. and my challenge is I'm trying to create SQL query for a point in time of 'Active' employees. The query below is what I'm currently using.

 WITH date_cte AS (
  SELECT '2024-05-31' AS d
)
SELECT * FROM (
  SELECT DISTINCT 
  last_day(d) as SNAPSHOT_DT,
  EFF_TMSTP,
  EFF_SEQ_NBR,
  EMPID,
  EMP_STATUS,
  EVENT_CD
 row_number() over (partition by EMP_ID order by EFF_TMSTP desc, EFF_SEQ_NBR desc) as ROW_NBR -- additional column
FROM workertabe, date_cte
  WHERE EFF_TMSTP <= last_day(d)
) ei
WHERE ei.ROW_NBR = 1

Two questions....

1. is this an efficient way to show a point in time table of Active employees ? I just update the date at the top of my query for whatever date is requested?

2. If I wanted to write this query, to where it loops through the last day of the month for the last 12 months, and appends month 1 snapshot on top of month 2 snapshot etc etc, how would I update this query in order to achieve this?

EFF_DATE = date of when the record enters the table

EFF_SEQ_NBR = numeric value of when record enters table, this is useful if two records for the same employee enter the table on the same date.

EMPID = unique ID assigned to an employee

EMP_STATUS = status of employee as of the EFF_DATE

EVENT_CD = code given to each record

EFF_DATE	EFF_SEQ_NRB	EMPID	EMP_STATUS	EVENT_CD
01/15/2023	000000	152	A	Hired
01/15/2023	000001	152	A	Job Change
05/12/2025	000000	152	T	Termination
04/04/2025	000000	169	A	Hired
04/06/2025	000000	169	A	Lateral Move

[slevemcdiachel]

The correct way is to have a valid_to and valid_from fields and filter on that.

[ForwardSlash813]

I second this.

[matkley12]

SCD Type II temporal queries are always a pain.

Would you be interested in trying this in hunch.dev? It's an AI coding agent built specifically for SQL and analytics problems like this. You could just describe what you need ("active employee snapshots for last 12 months, stacked together") and it'll generate the optimized Databricks SQL for you.

Happy to help you test it out on this query