Architecture

I have design a typical DataLake Medallion's architecture, where data moves between different layers

1. Bronze
2. Silver
3. Gold

Data Engineering

Bronze Layer

We only read JSON files and append all in one table using the notebook (landing_to_bronze/transformations/000_bronze_apps.py). In this layer, we dont touch any data, we only append snapshot_date column

    df = pd.read_json(full_path)
    df['snapshot_date'] = snapshot
    all_apps_dfs.append(df)

To Do

Currently is not able to reproccess single partition's files in this layer. In a production enviroment, we must. This is controlled by variable LAST_SNAPSHOT that could be saved in DynamoDb

LAST_SNAPSHOT = "20251001"
snapshots = [d for d in os.listdir(base_path) if not d.startswith('.') if int(d) > int(LAST_SNAPSHOT)]

Silver Layer

I have detected an N:M to relation between apps and categories, because each app could have more than one categotory and a category could be in zero or more apps

We split table app_categories (comes from bronze layer) into three tables:

• app
• categories
• app_categories

Gold Layer

In silver layer we have an historical data from all snapshots, but for API we need current stats so we need to get last snapshot data.

How-to

Databricks

I have deployed a Databricks's pipeline landing_to_bronze (/Users/luiscaman98@gmail.com/landing_to_bronze) where code is placed

API

You have to create a .env file in the root folder, following .env.example file

DATABRICKS_TOKEN="<TOKEN>"
DATABRICKS_SERVER_HOST="dbc-<id>.cloud.databricks.com" 
DATABRICKS_HTTP_PATH="/sql/1.0/warehouses/<id>"
DATABRICKS_SCHEMA="interview_data_pde.username_zapiercom"

I have coded a docker-compose service to allow deploy API easily, just clone the repo and run docker-compose up

(zapier) luis.cabezon:~/code/lucabem/zapier$ docker-compose up

Creating network "zapier_default" with the default driver
Creating fastapi_app ... done
Attaching to fastapi_app

fastapi_app | INFO:     Will watch for changes in these directories: ['/app']
fastapi_app | INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
fastapi_app | INFO:     Started reloader process [1] using StatReload

Examples

curl -X GET "http://localhost:8000/stats/historical/apps/popularity?app_name=gmail" -H "accept: application/json" | jq .

[
  {
    "date": "20251022",
    "app_name": "Gmail",
    "popularity": 2,
    "zap_usage_count": 237455
  },
  {
    "date": "20251023",
    "app_name": "Gmail",
    "popularity": 2,
    "zap_usage_count": 237455
  }
]

curl -X GET "http://localhost:8000/stats/apps/by-update?sort=oldest" -H "accept: application/json" | jq .

[
  {
    "app_id": 408,
    "app_name": "Android",
    "description": "Android is a ...",
    "snapshot_date": null,
    "age_in_days": 4628,
    "zap_usage_count": 0,
    "current_implementation": null,
    "days_since_last_update": 3009
  },
  {
    "app_id": 340,
    "app_name": "ExactTarget",
    "description": "ExactTarget is a suite of ...",
    "snapshot_date": null,
    "age_in_days": 4676,
    "zap_usage_count": 0,
    "current_implementation": null,
    "days_since_last_update": 2630
  }
]

AI/LLM usage notes

I used AI in three specific areas:

• Databricks: To bridge my knowledge gap regarding the environment. It helped resolve issues with cloud protocols (S3/GCP) for Delta tables, though I eventually opted for materialized views.

• Docker: To find the most secure way to handle .env files in docker-compose.

• Security: To verify best practices for preventing SQL injection when passing parameters to cursors.

Next steps

If I have had more time for API:

• I would like to deploy new models and not reusing the same for all endpoints
• Use more decorators and middleware for JWT Auth for example
• Split main.py in Blueprint's > app and stats

As Data Engineer, I would like imrpove schema's table doing aggreation and creating some historical series to try foucsing on Apps that are being used vs not