London Stock Exchange Group Snowflake Data Engineer Interview Questions, Process, and Tips

Data can be loaded from JSON using Snowflake's COPY INTO command.

• Use the COPY INTO command in Snowflake to load data from JSON files.

• Specify the file format as JSON in the COPY INTO command.

• Map the JSON attributes to the columns in the target table.

• Example: COPY INTO target_table FROM 's3://bucket_name/file.json' FILE_FORMAT = (TYPE = 'JSON');

Performance tuning options in Snowflake include clustering, materialized views, query profiling, and resource monitoring.

• Use clustering keys to organize data for faster query performance

• Create materialized views to pre-aggregate data and improve query speed

• Utilize query profiling to identify and optimize slow queries

• Monitor resource usage to ensure efficient query execution

Snowpipe is configured using a Snowflake account, specifying the source data location and the target table.

• Configure a stage in Snowflake to specify the source data location.

• Create a pipe in Snowflake to define the target table and the stage.

• Set up notifications for the pipe to trigger loading data automatically.

• Monitor the pipe for any errors or issues in data loading.

• Example: CREATE STAGE my_stage URL = 's3://my_buck...read more

Various data modelling techniques like dimensional modelling, ER modelling, and data vault are used.

• Dimensional modelling is used for data warehousing and involves organizing data into facts and dimensions.

• ER modelling is used to visualize the data relationships in an entity-relationship diagram.

• Data vault modelling is used for agile data warehousing and involves creating a flexible and scalable data model.

Streams in Snowflake are used to continuously replicate data from a table to another destination in real-time.

• Streams capture changes made to a table, such as inserts, updates, and deletes.

• They can be used to track changes and replicate data to other tables or external systems.

• Streams are created on a specific table and can be monitored for changes using SQL commands.