openapi: 3.0.3
info:
  title: Data Engineering ETL API
  description: |
    # Data Engineering Best Practices API

    This API specification serves as a universal guide for data engineers implementing ETL (Extract, Transform, Load) processes. 
    It is based on best practices abstracted from real-world implementations and provides a standardized approach to building 
    robust, maintainable, and scalable data pipelines.

    ## Key Features

    - **Comprehensive Logging**: Detailed logging of all ETL operations
    - **Error Handling**: Robust error handling with detailed error reporting
    - **Delta Updates**: Support for incremental data processing
    - **Timestamp Handling**: Proper management of timestamps for data lineage and auditing
    - **Partitioning**: Advanced partitioning capabilities for optimized data storage and retrieval
    - **Metadata Management**: Tracking of ETL events, checkpoints, and metrics

    ## Authentication

    This API uses API key authentication. Contact your administrator to obtain an API key.
  version: 1.0.0
  contact:
    name: Data Engineering Team
    email: data-engineering@example.com
servers:
  - url: https://etl-service.example.com
    description: Production ETL Service
  - url: https://etl-service-staging.example.com
    description: Staging ETL Service
tags:
  - name: ETL
    description: ETL process operations
  - name: Data
    description: Data retrieval operations
  - name: Monitoring
    description: Monitoring and health check operations
  - name: Metadata
    description: ETL metadata operations
paths:
  /run-etl:
    post:
      tags:
        - ETL
      summary: Run ETL process
      description: |
        Triggers the ETL process to fetch data from the source system and process it.

        If `use_postgres` is True (default), data will be loaded into PostgreSQL.
        If `gcs_bucket` is provided, data will also be exported to GCS as Parquet files.
        If `use_postgres` is False, data will be exported directly to GCS, skipping PostgreSQL.

        When exporting to GCS, you can use the advanced GCS adapter with enhanced partitioning options:
        - Time-based partitioning with different granularities (year, month, day, hour)
        - Table-specific partitioning schemes
        - Control over Parquet file format (compression, row group size)
      operationId: runEtl
      parameters:
        - name: write_disposition
          in: query
          description: How to write data - append to existing data or replace it
          required: false
          schema:
            type: string
            enum: [WRITE_APPEND, WRITE_TRUNCATE]
            default: WRITE_APPEND
        - name: delta_update
          in: query
          description: Whether to use delta update logic (fetch only changed data)
          required: false
          schema:
            type: boolean
            default: true
        - name: since_date
          in: query
          description: Only fetch data updated since this date (ISO format)
          required: false
          schema:
            type: string
            format: date-time
            example: "2023-01-01T00:00:00.000Z"
        - name: gcs_bucket
          in: query
          description: GCS bucket name to export data to as Parquet file
          required: false
          schema:
            type: string
        - name: partition_cols
          in: query
          description: List of column names to partition by when exporting to GCS
          required: false
          schema:
            type: array
            items:
              type: string
            default: ["last_update"]
        - name: use_postgres
          in: query
          description: Whether to use PostgreSQL. If False, data will be exported directly to GCS
          required: false
          schema:
            type: boolean
            default: true
        - name: use_advanced_gcs
          in: query
          description: Whether to use the advanced GCS adapter with enhanced partitioning
          required: false
          schema:
            type: boolean
            default: true
        - name: entity_id
          in: query
          description: Entity ID to filter by
          required: false
          schema:
            type: string
        - name: time_partitioning
          in: query
          description: Whether to enable time-based partitioning for GCS export
          required: false
          schema:
            type: boolean
            default: true
        - name: time_partition_field
          in: query
          description: Field to use for time-based partitioning
          required: false
          schema:
            type: string
            default: "last_update"
        - name: time_partition_type
          in: query
          description: Type of time partitioning
          required: false
          schema:
            type: string
            enum: [year, month, day, hour, custom]
            default: "day"
        - name: time_partition_format
          in: query
          description: Format for time partitioning if using custom format
          required: false
          schema:
            type: string
            default: "%Y-%m-%d"
        - name: parquet_compression
          in: query
          description: Compression to use for Parquet files
          required: false
          schema:
            type: string
            enum: [snappy, gzip, brotli, none]
            default: "snappy"
        - name: parquet_row_group_size
          in: query
          description: Row group size for Parquet files
          required: false
          schema:
            type: integer
            default: 100000
      security:
        - ApiKeyAuth: []
      responses:
        '200':
          description: ETL process completed successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ETLResponse'
              example:
                run_id: "5611cbad-819b-4c2b-8a15-3f5efc64dc02"
                status: "success"
                message: "ETL process completed successfully"
                details:
                  markets_count: 1
                  headers_count: 1
                  features_count: 15
                  feature_geos_count: 45
                  gcs_export:
                    bucket_name: "your-team-bucket"
                    file_path: "data_entity_WRITE_APPEND_True_20250615_123045.parquet"
                    partition_cols: ["last_update"]
                    row_count: 15
        '400':
          description: Bad request
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
              example:
                run_id: "7a9c8b3d-6e5f-4c2d-1a3b-9e8d7c6f5a4b"
                status: "error"
                message: "GCS bucket is required when use_postgres is False"
                details: null
        '401':
          description: Unauthorized
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '500':
          description: Internal server error
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
              example:
                run_id: "2a1b3c4d-5e6f-7g8h-9i0j-1k2l3m4n5o6p"
                status: "error"
                message: "ETL process failed: Error connecting to source system"
                details: null
  /data/{entity_type}:
    get:
      tags:
        - Data
      summary: Get data by entity type
      description: Retrieves data for a specific entity type with pagination support
      operationId: getDataByEntityType
      parameters:
        - name: entity_type
          in: path
          description: Type of entity to retrieve (e.g., feature, feature_geo)
          required: true
          schema:
            type: string
            enum: [feature, feature_geo, market, header]
        - name: page_size
          in: query
          description: Number of items per page
          required: false
          schema:
            type: integer
            minimum: 1
            maximum: 1000
            default: 100
        - name: cursor
          in: query
          description: Cursor for pagination
          required: false
          schema:
            type: string
        - name: filter
          in: query
          description: JSON filter criteria
          required: false
          schema:
            type: string
      security:
        - ApiKeyAuth: []
      responses:
        '200':
          description: Data retrieved successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/PaginatedResponse'
        '400':
          description: Bad request
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '401':
          description: Unauthorized
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '404':
          description: Entity type not found
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '500':
          description: Internal server error
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /metadata/events:
    get:
      tags:
        - Metadata
      summary: Get ETL events
      description: Retrieves ETL events with filtering and pagination
      operationId: getEtlEvents
      parameters:
        - name: run_id
          in: query
          description: Filter by run ID
          required: false
          schema:
            type: string
        - name: service_name
          in: query
          description: Filter by service name
          required: false
          schema:
            type: string
        - name: event_type
          in: query
          description: Filter by event type
          required: false
          schema:
            type: string
        - name: status
          in: query
          description: Filter by status
          required: false
          schema:
            type: string
        - name: from_timestamp
          in: query
          description: Filter events from this timestamp
          required: false
          schema:
            type: string
            format: date-time
        - name: to_timestamp
          in: query
          description: Filter events to this timestamp
          required: false
          schema:
            type: string
            format: date-time
        - name: page_size
          in: query
          description: Number of items per page
          required: false
          schema:
            type: integer
            default: 100
        - name: page
          in: query
          description: Page number
          required: false
          schema:
            type: integer
            default: 1
      security:
        - ApiKeyAuth: []
      responses:
        '200':
          description: Events retrieved successfully
          content:
            application/json:
              schema:
                type: object
                properties:
                  items:
                    type: array
                    items:
                      $ref: '#/components/schemas/ETLEvent'
                  total:
                    type: integer
                  page:
                    type: integer
                  page_size:
                    type: integer
        '401':
          description: Unauthorized
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '500':
          description: Internal server error
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /metadata/checkpoints:
    get:
      tags:
        - Metadata
      summary: Get ETL checkpoints
      description: Retrieves ETL checkpoints with filtering and pagination
      operationId: getEtlCheckpoints
      parameters:
        - name: run_id
          in: query
          description: Filter by run ID
          required: false
          schema:
            type: string
        - name: service_name
          in: query
          description: Filter by service name
          required: false
          schema:
            type: string
        - name: entity_type
          in: query
          description: Filter by entity type
          required: false
          schema:
            type: string
        - name: entity_id
          in: query
          description: Filter by entity ID
          required: false
          schema:
            type: string
        - name: status
          in: query
          description: Filter by status
          required: false
          schema:
            type: string
        - name: page_size
          in: query
          description: Number of items per page
          required: false
          schema:
            type: integer
            default: 100
        - name: page
          in: query
          description: Page number
          required: false
          schema:
            type: integer
            default: 1
      security:
        - ApiKeyAuth: []
      responses:
        '200':
          description: Checkpoints retrieved successfully
          content:
            application/json:
              schema:
                type: object
                properties:
                  items:
                    type: array
                    items:
                      $ref: '#/components/schemas/ETLCheckpoint'
                  total:
                    type: integer
                  page:
                    type: integer
                  page_size:
                    type: integer
        '401':
          description: Unauthorized
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '500':
          description: Internal server error
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /metadata/metrics:
    get:
      tags:
        - Metadata
      summary: Get ETL metrics
      description: Retrieves ETL metrics with filtering and pagination
      operationId: getEtlMetrics
      parameters:
        - name: run_id
          in: query
          description: Filter by run ID
          required: false
          schema:
            type: string
        - name: service_name
          in: query
          description: Filter by service name
          required: false
          schema:
            type: string
        - name: metric_name
          in: query
          description: Filter by metric name
          required: false
          schema:
            type: string
        - name: from_timestamp
          in: query
          description: Filter metrics from this timestamp
          required: false
          schema:
            type: string
            format: date-time
        - name: to_timestamp
          in: query
          description: Filter metrics to this timestamp
          required: false
          schema:
            type: string
            format: date-time
        - name: page_size
          in: query
          description: Number of items per page
          required: false
          schema:
            type: integer
            default: 100
        - name: page
          in: query
          description: Page number
          required: false
          schema:
            type: integer
            default: 1
      security:
        - ApiKeyAuth: []
      responses:
        '200':
          description: Metrics retrieved successfully
          content:
            application/json:
              schema:
                type: object
                properties:
                  items:
                    type: array
                    items:
                      $ref: '#/components/schemas/ETLMetric'
                  total:
                    type: integer
                  page:
                    type: integer
                  page_size:
                    type: integer
        '401':
          description: Unauthorized
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
        '500':
          description: Internal server error
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /health:
    get:
      tags:
        - Monitoring
      summary: Health check
      description: Checks the health of the ETL service
      operationId: healthCheck
      responses:
        '200':
          description: Service is healthy
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    example: "healthy"
        '500':
          description: Service is unhealthy
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    example: "unhealthy"
                  details:
                    type: object
components:
  securitySchemes:
    ApiKeyAuth:
      type: apiKey
      in: header
      name: X-API-Key
  schemas:
    ETLResponse:
      type: object
      properties:
        run_id:
          type: string
          description: Unique identifier for the ETL run
          format: uuid
        status:
          type: string
          description: Status of the ETL process
          enum: [success, error, warning]
        message:
          type: string
          description: Human-readable message about the ETL process
        details:
          type: object
          description: Additional details about the ETL process
          nullable: true
      required:
        - run_id
        - status
        - message
    ErrorResponse:
      type: object
      properties:
        run_id:
          type: string
          description: Unique identifier for the request
          format: uuid
        status:
          type: string
          description: Error status
          enum: [error]
        message:
          type: string
          description: Error message
        details:
          type: object
          description: Additional error details
          nullable: true
      required:
        - run_id
        - status
        - message
    PaginatedResponse:
      type: object
      properties:
        items:
          type: array
          description: Array of data items
          items:
            type: object
        total:
          type: integer
          description: Total number of items
        page:
          type: integer
          description: Current page number
        page_size:
          type: integer
          description: Number of items per page
        next_cursor:
          type: string
          description: Cursor for the next page
          nullable: true
      required:
        - items
        - total
        - page
        - page_size
    ETLEvent:
      type: object
      properties:
        id:
          type: integer
          description: Unique identifier for the event
        run_id:
          type: string
          description: Identifier for the ETL run
          format: uuid
        service_name:
          type: string
          description: Name of the service that generated the event
        event_type:
          type: string
          description: Type of event
        status:
          type: string
          description: Status of the event
          enum: [success, error, warning, info]
        message:
          type: string
          description: Event message
        details:
          type: object
          description: Additional event details
          nullable: true
        timestamp:
          type: string
          description: When the event occurred
          format: date-time
      required:
        - id
        - run_id
        - service_name
        - event_type
        - status
        - timestamp
    ETLCheckpoint:
      type: object
      properties:
        id:
          type: integer
          description: Unique identifier for the checkpoint
        run_id:
          type: string
          description: Identifier for the ETL run
          format: uuid
        service_name:
          type: string
          description: Name of the service that created the checkpoint
        entity_type:
          type: string
          description: Type of entity
        entity_id:
          type: string
          description: Identifier for the entity
        status:
          type: string
          description: Status of the checkpoint
          enum: [success, error, warning, skipped]
        details:
          type: object
          description: Additional checkpoint details
          nullable: true
        processed_at:
          type: string
          description: When the entity was processed
          format: date-time
      required:
        - id
        - run_id
        - service_name
        - entity_type
        - entity_id
        - status
        - processed_at
    ETLMetric:
      type: object
      properties:
        id:
          type: integer
          description: Unique identifier for the metric
        run_id:
          type: string
          description: Identifier for the ETL run
          format: uuid
        service_name:
          type: string
          description: Name of the service that recorded the metric
        metric_name:
          type: string
          description: Name of the metric
        metric_value:
          type: number
          description: Value of the metric
          format: double
        metric_unit:
          type: string
          description: Unit of the metric
          nullable: true
        dimensions:
          type: object
          description: Dimensions for the metric
          nullable: true
        details:
          type: object
          description: Additional metric details
          nullable: true
        timestamp:
          type: string
          description: When the metric was recorded
          format: date-time
      required:
        - id
        - run_id
        - service_name
        - metric_name
        - metric_value
        - timestamp
    EntityModel:
      type: object
      properties:
        id:
          type: string
          description: Unique identifier for the entity
        parent_id:
          type: string
          description: Parent entity ID
        entity_type:
          type: string
          description: Type of entity
        category:
          type: integer
          description: Entity category
        reference_id:
          type: string
          description: Reference ID
          nullable: true
        attribute_name:
          type: string
          description: Attribute name
          nullable: true
        attribute_type:
          type: string
          description: Attribute type
          nullable: true
        last_update:
          type: string
          description: Last update timestamp
          format: date-time
      required:
        - id
        - parent_id
        - entity_type
        - category
        - last_update
    EntityGeometryModel:
      type: object
      properties:
        id:
          type: string
          description: Unique identifier for the geometry
        entity_id:
          type: string
          description: Reference to entity ID
        angle:
          type: number
          description: Angle
          format: float
          nullable: true
        coord_x:
          type: number
          description: X coordinate
          format: float
          nullable: true
        coord_y:
          type: number
          description: Y coordinate
          format: float
          nullable: true
        coord_z:
          type: number
          description: Z coordinate
          format: float
          nullable: true
        geometry_type:
          type: string
          description: Geometry type
          nullable: true
        last_update:
          type: string
          description: Last update timestamp
          format: date-time
      required:
        - id
        - entity_id
        - last_update
