Extract and Load: the data pipeline

This page explains what the agent writes to the data lake, how the raw tables and views are built, and the two mechanisms that keep large tables manageable: field partitions and incremental updates.

Data lake directory structure 

For each table (or for each field-based partition of each table, when enabled) the agent generates one CSV file and uploads it. The file has the same name as the table, with a .csv extension, unless datalake_s3_table is configured. The layout is:

s3://<bucket>/[<base prefix>/]<table name>/[<base partitions>/][field partitions/]<table name>.csv

Where:

bucket — the panorama_raw_data_bucket setting.
base prefix — optional subdirectory grouping tables of the same kind of system (for example openedx). It can receive files from multiple sources as long as the table names and field structure match.
table name — base location of the data lake table. All text files inside this directory must have exactly the same column structure.
base partitions — partitions common to a whole installation, written in Hive format. These are not source fields but appear as columns in the data lake. For multiple Open edX installations the default is lms=<LMS_HOST>, for example lms=openedx.example.com.
field partitions — optional. For large tables you can split the source into multiple CSV files by the value of one or more fields. The field is removed from the CSV and becomes a partition column. The default Open edX setting partitions courseware_studentmodule by course_id.

Raw tables 

create-datalake-tables registers each uploaded table as an external Athena table over the CSV files. Key characteristics of the generated table:

Every column is declared as string (the raw layer does no typing).
Base partitions and field partitions become partition columns.
The table uses the OpenCSV serde with a comma separator, " quote character, \ escape character, and skips one header line.
The LOCATION points at s3://<bucket>/<base prefix>/<table>/ (the partition subdirectories are discovered underneath).

Because the table is external, dropping it (drop-datalake-tables) removes only the catalog entry, not the CSV data in S3.

Table views (first transformation layer)

create-table-views creates a CREATE OR REPLACE VIEW over each raw table. This is the first layer of the pipeline and its job is to make the string data usable:

Numeric types (INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT) become TRY_CAST(... AS BIGINT).
Floating types (FLOAT, DOUBLE, DECIMAL) become TRY_CAST(... AS DOUBLE).
Date/time types (DATETIME, DATE, TIMESTAMP, TIME, YEAR) are parsed with date_parse(..., '%Y-%m-%d %H:%i:%s.%f') inside TRY(...).
JSON and string types get NULLIF(<field>, 'NULL'), so the literal text NULL becomes a real NULL.
Base partitions are appended to the view as additional columns.

The default view name is <base_prefix>_table_<table name>. These typed views are what the next pipeline layers (and the BI tool) build on.

Curated views 

Further pipeline steps are implemented as additional Athena views that join the typed table views, filter them, and add calculated fields. The repository includes ready-made Open edX examples under openedx_views/:

openedx_view_enrollments.sql — a wide enrollment view joining student_courseenrollment with users, profiles, grades, course overviews, course structures, country coordinates, course access roles, and manual enrollment history. It derives fields such as days_since_enrolled, percent_grade, is_student, and role flags.
openedx_view_student_courseaccessrole.sql — aggregates course access roles per user and course into boolean flags (is_course_staff, is_course_instructor, and so on).
openedx_view_student_manualenrollment.sql — collapses the manual enrollment audit into one row per enrollment with the last transition, counts, and ordered history.

These are provided as SQL you run in Athena to build the curated layer that dashboards read.

Field partitions 

To partition a table by a field, add a partitions section listing the fields under partition_fields:

tables:
  - name: courseware_studentmodule
    datalake_table_name: courseware_studentmodule_raw
    partitions:
      partition_fields:
      - course_id
    fields:
      ...

Each distinct value in the specified column produces a separate CSV file, placed in a Hive format subdirectory such as course_id=<value>. The partition field is removed from the CSV body and becomes a partition column in the data lake.

Warning

This can create a very large number of subdirectories. Plan field partitions carefully.

Incremental updates 

Incremental updates let periodic runs upload only the partitions that changed recently, instead of the whole table. A table must be partitioned by field to support incremental updates.

To enable them, add a timestamp_field and an interval to the table’s partitions section:

tables:
  - name: courseware_studentmodule
    datalake_table_name: courseware_studentmodule_raw
    partitions:
      interval: 2 hour
      partition_fields:
      - course_id
      timestamp_field: modified
    fields:
      ...

timestamp_field must be a datetime field holding the last-modified time.
interval uses MySQL interval syntax. As a rule of thumb, set it to double your query period: if you extract hourly, use a 2 hour interval.

How it works. The agent first queries the distinct values of the partition fields that have records with a timestamp within the last interval from now. Then it updates only the field-based partitions that changed. Note that the whole matching partition is rewritten, not just the changed rows.

This is why the first run uses --force: it uploads every partition once, including those older than the interval, so the data lake starts complete.

Back to Home

Extract and Load: the data pipeline

Data lake directory structure

Raw tables