Partitioning of file based targets

[nixent]

I'd like to run a backfill from SQL server to parquet files stored in ADLS and partition target data by date parts of update_key column, like raw/my_system/{stream_table}/YYYY({update_key})/MM({update_key})/DD({update_key})

Currently timestamp variables YYYY, DD, MM are calculated based on run timestamp. Is there any way to make it to take update_key instead?

As a workaround I'm running multiple backfills and controlling partitions like this:

source: sql_source
target: adls_target

defaults: 
  mode: backfill
  object: raw/my_system/{stream_table}/{partition}/
  primary_key: my_key
  source_options:
    range: '{start_date},{end_date}'  
  target_options:
    format: parquet
      
streams: 
  dbo.MyTable:
    update_key: TransDate  

env:
  start_date: '${START_DATE}'
  end_date: '${END_DATE}'
  partition: '${PARTITION}'

[flarco]

hey @nixent this will be possible soon, as sling will be using duckdb under the hood to read/write parquet files (allowing partitioning). Stay tuned.

[flarco]

Ok, should be good in latest dev build.

For example: object: my/folder/{part_year_month}/{part_day}.

Available variables:

• part_year: The 4 digit year partition value of the update_key.
• part_month: The 2 digit month partition value of the update_key.
• part_year_month: Combination of the 4 digit year and the 2 digit month partition values of the update_key (e.g. 2024-11 as one value).
• part_day: The 2 digit day partition value of the update_key.
• part_week: The ISO-8601 2 digit week partition value of the update_key.
• part_hour: The 2 digit hour partition value of the update_key.
• part_minute: The 2 digit minute partition value of the update_key.